Over the weekend I spun up a new Elixir :ecto, "2.2.7" project that I was able to get working with CockroachDB and this adapter fork - with some caveats I wanted to share to help others.

1. Only the root user can create databases This requires you configure the Ecto.Adapters.Postgres username as root or else the mix ecto.create command will always fail. You can go back and change your configured username to something else after the database has been created, or create your database and user permissions using cockroach sql and skip the mix ecto.create command.

2. Configuring Ecto primary_key ID to be created by CockroachDB By default when configuring your Ecto.Schema using autogenerate: false it appears either CockroachDB, Ecto or the Postrex adapter (I did not investigate this) uses the BIGINT unique_rowid() function as the default value for IDs

@primary_key {:id, :id, autogenerate: false, read_after_writes: true}
@foreign_key_type :id

To auto-generate unique row IDs, use the UUID column with the gen_random_uuid() function as the default value: Configure your Ecto.Schema as:

@primary_key {:id, :binary_id, autogenerate: false, read_after_writes: true}
@foreign_key_type :binary_id

⚠️ To configure gen_random_uuid() as your binary_id primary_key type you will also need to configure your Ecto Migrations manually telling Ecto not to create your primary_key ID and add an SQL fragment:

create table(:comments, primary_key: false) do
  add :id, :uuid, primary_key: true, default: fragment("gen_random_uuid()")

3. Ecto Associations Due to some CockroachDB limitations/features I had to create a couple Ecto Migration files and do some manual configuration to get Ecto Associations to work.

It appears the Ecto references function always attempts to alter the COLUMN TYPE and TYPE changes on ALTER TABLE & ALTER COLUMN are not currently supported by CockroachDB. This causes an error on the sequence to create the foreign_key associations. CockroachDB also does not support adding REFERENCES from an Ecto alter command, so I could NOT just create the REFERENCE COLUMN ID in the main migration and then:

alter table(:comments) do
  modify :post_id, references(:posts, on_delete: :nothing, type: :uuid)
end

To work around this I needed to:

1. Add the reference to the initial migration:

create table(:posts, primary_key: false) do
  add :id, :uuid, primary_key: true, default: fragment("gen_random_uuid()")
  ...
  add :comment_id, references(:comments, on_delete: :nothing, type: :uuid)

2. Then create an additional Ecto Migration to add the 'foreign_key' ID column:

alter table(:comments) do
  add :post_id, :uuid
end
create index(:comments, [:post_id])

3. Then create one last Ecto Migration to create the DB CONSTRAINT REFERENCE manually using the execute function:

def change do
  execute("ALTER TABLE comments ADD CONSTRAINT comments_id_fkey FOREIGN KEY (post_id) REFERENCES posts(id)")
end

As I am finishing this up I realize this is more suitable for a blog post with an example repo - but the kids are up from their naps so I will save this here and revisit later.

postgrex CockroachDB fork

https://github.com/jumpn/postgrex

https://hexdocs.pm/postgrex_cdb/readme.html

https://hexdocs.pm/ecto_replay_sandbox/readme.html

cockroachdb/cockroach#5582