Ecto and Binary IDs Generated By PostgreSQL

Version Info: Elixir 1.7 / OTP 21, Ecto 3.0 and Postgres 10

I want to use UUIDs as the primary keys in an Elixir system I am developing but struggled to find out how I could set things up so that the Ecto migrations which create the database would define the ID field as being both a UUID and one which is generated by the PostgreSQL database. I wanted the database to generate the UUIDs because the database might have records added manually.

Rather than repeating the same information, start with Ecto and Binary IDs by Eric Sullivan which explains how to configure migrations to use a UUID as the primary key and it also explains how to have your schemas handle primary and foreign keys which are UUIDs. I found Eric’s blog post after getting the migrations to work and when I was trying to get schemas to work with UUIDs too.

Further to Eric’s post, there are two things you need to do: add some new config options and add an extension to PostgreSQL itself.

Add New Config Options

Eric’s config options are:

config :my_app, MyApp.Repo,
  ...
  migration_primary_key: [name: :id, type: :binary_id]

To have PostgreSQL generate the UUIDs you need to have:

config :my_app, MyApp.Repo,
  ...
  migration_primary_key: [name: :id, type: :binary_id, autogenerate: false, read_after_writes: true, default: {:fragment, "uuid_generate_v1()"}]

The three new options are:

autogenerate: false which tells Ecto not to generate the primary key UUIDs because PostgreSQL will do that.

read_after_writes: true tells Ecto to read the UUID value back from the database after every write.

default: {:fragment, "uuid_generate_v1()"} defines how the default value is generated. This is explained in more detail below.

Add A PostgreSQL Extension

The uuid-ossp module generates UUIDs but, as a module, it needs to be loaded into PostgreSQL before it can be used [source].

To do this, in PostgreSQL 9.1 and newer) you need to execute the following command:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

To perform this within an Ecto migration you simply need this:

def change do
    execute "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""
end

I put it in its own migration whose filename’s datetime prefix ensures that it is the first migration file to be executed.

The Default option

If you remember, the config option used to define the default value for the UUID primary key was default: {:fragment, "uuid_generate_v1()"}.

uuid_generate_v1() is the name of the uuid-ossp function I want PostgreSQL to use to generate my UUIDs. To specify this in an Ecto migration you need to use the fragment function. This essentially allows you to send a ‘raw’ command to the database. Here we are setting the default value for the primary key field to a function name which we don’t want Elixir to try to parse.

The syntax to use for this was the thing which actually caused me the most grief and I eventually found an example in a test in ecto_sql.

UUIDs

I wanted the UUID values generated by PostgreSQL to be version 1 UUIDs so that they are unique to each server within a database cluster. The problem with version 1 UUIDs is that they reveal the identity of the computer that created them as well as when they did so. Therefore think before you expose them via, for example, URLs.