Using PostgreSQL Identity Columns in Ecto SQL

Oct 26, 2024

When working with PostgreSQL and Elixir’s Ecto ORM, you are likely using SERIAL columns for auto-incrementing primary keys. However, since PostgreSQL 10 (released in October 2017), there’s a better way: IDENTITY columns.

Why Identity Columns?

Identity columns (GENERATED AS IDENTITY) are part of the SQL Standard and are the recommended approach for auto-incrementing keys in modern Postgres. While I won’t dive into why SERIAL columns should be avoided (check out Stop using SERIAL in Postgres for details), let’s explore how to use IDENTITY columns with Ecto.

Repository-Wide Configuration

The simplest way to use identity columns is to configure them for your entire Ecto repository. Add this to your config:

config :demo, Demo.Repo,
  migration_primary_key: [type: :identity]

Now, when you create a table:

defmodule Demo.Repo.Migrations.CreateUsers do
  use Ecto.Migration
  def change do
    create table(:users) do
      add :email, :string
      add :name, :string
      timestamps(type: :utc_datetime)
    end
    create unique_index(:users, [:email])
  end
end

Ecto will generate SQL using an identity column:

CREATE TABLE "users" (
    "id" bigint GENERATED BY DEFAULT AS IDENTITY,
    "email" varchar(255),
    "name" varchar(255),
    "inserted_at" timestamp(0) NOT NULL,
    "updated_at" timestamp(0) NOT NULL,
    PRIMARY KEY ("id"))

Per-Table Configuration

Need identity columns for specific tables only? You can configure them individually in your migrations:

defmodule Demo.Repo.Migrations.CreateUsers do
  use Ecto.Migration
  def change do
    create table(:users, primary_key: false) do
      add :id, :identity, primary_key: true
      add :email, :string
      add :name, :string
      timestamps(type: :utc_datetime)
    end
    create unique_index(:users, [:email])
  end
end

This gives you the flexibility to use identity columns exactly where you need them.

That’s all there is to it!

Using UUIDs Instead

If you prefer using UUIDs for your primary keys, you can easily adapt the above patterns by changing :identity to :binary_id. For repository-wide configuration:

config :demo, Demo.Repo,
  migration_primary_key: [type: :binary_id]

Or for individual tables:

defmodule Demo.Repo.Migrations.CreateUsers do
  use Ecto.Migration
  def change do
    create table(:users, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :email, :string
      add :name, :string
      timestamps(type: :utc_datetime)
    end
    create unique_index(:users, [:email])
  end
end