uuid as primary key for Ecto and Postgres

[me.m...@gmail.com]

I want to use UUID as primary key, which is automatically managed by Postgres at each insert. Is it possible to define this entirely using Ecto.Model and Ecto.Migration

I came across two links
http://stackoverflow.com/questions/28060582/how-to-use-uuids-with-ecto-models

(I think this requires generating related SQL create table query manually in migration)

and this
https://labria.github.io/2013/04/28/rails-4-postgres-uuid-pk-guide/

This is actually for rails. but the structure of create_table method is very similar to the equivalent in Ecto.Migration. Will this work same way for Ecto.Migration?

I'm also open to other suggestions, including generating UUID through Elixir and inserting in Postgres (it looked repetitive work though)

thanks
miwee

[Steve Domin]

Hi,

Here is a gist with a model and a migration that uses UUID as primary key: https://gist.github.com/stevedomin/0ea9d9af96b565cbd0b7

The UUIDs are generated by Postgres using the uuid-ossp module. You can change the function for UUID generation to any of the ones listed here: http://www.postgresql.org/docs/devel/static/uuid-ossp.html

Let me know if you run into trouble with this example.

[me.m...@gmail.com]

Thanks. It looks neat and now I have more clarity on some ecto settings. It works ok.

[mar...@radiokit.org]

Hello,

I've tried to follow this gist, but it seems that Ecto is keeping adding id = NULL to the query even if id wasn't specified in the params. The outcome is that PostgreSQL is shouting about failing constraints, instead of generating UUID. When I add record manually, without specyfing id in the VALUES claues, UUID is properly generated by PostgreSQL.

I have to manually generate UUID in before_insert callback (like here: http://stackoverflow.com/questions/30004008/setting-up-phoenix-framework-and-ecto-to-use-uuids-how-to-insert-the-generated) this however seems to be very redundant. Can anyone help? How can I disable appending id to INSERT queries? (it really doesn't make any sense)

Please take look at the stack trace:

[info] POST /api/rest/v1/input/stream/http
[info] Processing by MyApp.Input.Stream.HTTPController.create/2
  Parameters: %{"format" => "json", "http" => %{"location" => "http://wp.pl"}}
  Pipelines: [:api]
[debug] BEGIN [] OK query=126.8ms
[debug] INSERT INTO "input_stream_http" ("id", "inserted_at", "location", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id" [nil, {{2015, 6, 20}, {20, 32, 45, 0}}, "http://wp.pl", {{2015, 6, 20}, {20, 32, 45, 0}}] ERROR query=9.4ms
[debug] ROLLBACK [] OK query=0.4ms
[info] Sent 500 in 291ms
[error] #PID<0.289.0> running MyApp.Endpoint terminated
Server: localhost:4000 (http)
Request: POST /api/rest/v1/input/stream/http
** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR (not_null_violation): null value in column "id" violates not-null constraint
        (ecto) lib/ecto/adapters/sql.ex:210: Ecto.Adapters.SQL.log_and_check/7
        (ecto) lib/ecto/adapters/sql.ex:433: Ecto.Adapters.SQL.model/5
        (ecto) lib/ecto/repo/model.ex:34: anonymous fn/10 in Ecto.Repo.Model.insert/4
        (ecto) lib/ecto/adapters/sql.ex:485: Ecto.Adapters.SQL.transaction/3
        (ecto) lib/ecto/repo/model.ex:206: Ecto.Repo.Model.with_transactions_if_callbacks/6
        (radio_kit_mixer) web/controllers/input/stream/http_controller.ex:18: MyApp.Input.Stream.HTTPController.create/2
        (radio_kit_mixer) web/controllers/input/stream/http_controller.ex:1: MyApp.Input.Stream.HTTPController.phoenix_controller_pipeline/2
        (radio_kit_mixer) lib/phoenix/router.ex:297: MyApp.Router.dispatch/2

Model definition:

  def model do
    quote do
      use Ecto.Model
      @primary_key {:id, Ecto.UUID, read_after_writes: true}
      @foreign_key_type Ecto.UUID
    end
  end

defmodule MyApp.Input.Stream.HTTP do
  use MyApp.Web, :model

  schema "input_stream_http" do
    field :location, :string

    timestamps
  end

  @required_fields ~w(location)
  @optional_fields ~w()

  @doc """
  Creates a changeset based on the `model` and `params`.

  If `params` are nil, an invalid changeset is returned
  with no validation performed.
  """
  def changeset(model, params \\ :empty) do
    model
    |> cast(params, @required_fields, @optional_fields)
  end
end

Migration

defmodule MyApp.Repo.Migrations.CreateInput.Stream.HTTP do
  use Ecto.Migration

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

    create table(:input_stream_http, primary_key: false) do
      add :id, :uuid, primary_key: true, default: fragment("uuid_generate_v4()")
      add :location, :string, null: false

      timestamps
    end

  end
end

I am using phoenix 0.13.1 and ecto 0.12.0-rc.

Marcin