CockroachDB fork of the postgrex adapter - Elixir Ecto caveats

README.md

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

Note that UUID arrays (as in Ecto assoc) are not supported in CockroachDB < 2.0

cockroachdb/cockroach#23063

Cockroach DB 2.0 does not work with ecto out-of-the box, as creating a database throws the following error:

database "postgres" does not exist

Also see cockroachdb/cockroach#23893.

As a temporary workaround on this problem, I've forked ecto and tagged the current version v2.2.10_cdb with the default database changed to "system". It works with Cockroach DB 2.0 using this.

To use it, add this line to your mix.exs:

{:ecto, "~> 2.1", github: "arnodirlam/ecto", tag: "v2.2.10_cdb", override: true},

Thanks for the extra information @arnodirlam

I had not revisited this gist in a while and am now getting around to updating Phoenix 1.4 RC and Ecto 3 and will push to see what can be done to get official support in Postgrex for CDB without the forks.

I also find it hard to believe that gist still does not support notifications.....isaacs/github#21

i'll let this here: cockroachdb/cockroach#33441