Ecto migration helpers for PostgreSQL.

ecto_postgresql_migration.ex

defmodule MyApp.Migration do
  @moduledoc """
  Additional helpers for PostgreSQL.
  """

  import Ecto.Migration, only: [execute: 2]

  defmacro __using__(_) do
    quote do
      use Ecto.Migration
      import unquote(__MODULE__)
    end
  end
  
  def create_extension(names) when is_list(names) do
    Enum.each(names, &create_extension/1)
  end

  def create_extension(name) do
    execute(
      "CREATE EXTENSION IF NOT EXISTS #{name}",
      "DROP EXTENSION IF EXISTS #{name}"
    )
  end
  
  @doc """
  Sets a timestamp column to NOW() on every UPDATE. Requires moddatetime extension.
  """
  def create_moddatetime_trigger(table, column \\ :updated_at) do
    execute(
      """
      CREATE TRIGGER moddatetime_#{column}
      BEFORE UPDATE ON #{table}
      FOR EACH ROW
      WHEN (OLD IS DISTINCT FROM NEW)
      EXECUTE FUNCTION moddatetime(#{column});
      """,
      "DROP TRIGGER IF EXISTS moddatetime_#{column} ON #{table};"
    )
  end
end

# in ./config/config.exs
config :my_app, MyApp.Repo, migration_timestamps: [type: :timestamptz, null: false]

defmodule MyApp.Repo.Migrations.CreateUsers do
  use MyApp.Migration
  @tbl :users
  
  def change do
    create_extension([:moddatetime, :citext]))
    
    create table(@tbl) do
      add(:name, :text)
      add(:email, :citext)

      timestamps(default: fragment("NOW()"))
    end

    create(index(@tbl, [:email], unique: true))
    create_moddatetime_trigger(@tbl)
  end
end