How do I connect Ecto to CockroachDB Serverless?

Question

I'd like to use CockroachDB Serverless for my Ecto application. How do I specify the connection string?

I get an error like this when trying to connect.

[error] GenServer #PID<0.295.0> terminating
** (Postgrex.Error) FATAL 08004 (sqlserver_rejected_establishment_of_sqlconnection) codeParamsRoutingFailed: missing cluster name in connection string
    (db_connection 2.4.1) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2

CockroachDB Serverless says to connect by including the cluster name in the connection string, like this:

postgresql://username:<ENTER-PASSWORD>@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=--cluster%3Dcluster-name-1234

but I'm not sure how to get Ecto to create this connection string via its configuration.

Answer 1

The problem is that Postgrex is not able to parse all of the information from the connection URL - notable the SSL configuration. The solution is to specify the connection parameters explicitly, including the cacertfile SSL option. Assuming that you have downloaded your cluster's CA certificate to priv/certs/ca-cert.crt, you can use the following config as a template:

config :my_app, MyApp.Repo,
  username: "my_user",
  password: "my_password",
  database: "defaultdb",
  hostname: "free-tier.gcp-us-central1.cockroachlabs.cloud",
  port: "26257",
  ssl: true,
  ssl_opts: [
    cacertfile: Path.expand("priv/certs/ca-cert.crt"),
  ],
  parameters: [options: "--cluster=my-cluster-123"]

Possible Other Issues

Table Locking

Since that CockroachDB also does not support the locking that Ecto/Postgrex attempts on the migration table, the :migration_lock config needs to be disabled as well:

config :my_app, MyApp.Repo,
  # ...
  migration_lock: false

Auth generator

Finally, the new phx.gen.auth generator defaults to using the citext extension for storing a user's email address in a case-insensitive manner. The line in the generated migration that executes CREATE EXTENSION IF NOT EXISTS citext should be removed, and the column type for the :email field should be changed from :citext to :string.

Answer 2

This configuration allows Ecto to connect to CockroachDB Serverless correctly:

config :myapp, MyApp.repo,
  username: "username",
  password: "xxxx",
  database: "defaultdb",
  hostname: "free-tier.gcp-us-central1.cockroachlabs.cloud",
  port: 26257,
  ssl: true,
  ssl_opts: [
    cert_pem: "foo.pem",
    key_pem:  "bar.pem"
  ],
  show_sensitive_data_on_connection_error: true,
  pool_size: 10,
  parameters: [
    options: "--cluster=cluster-name-1234"
  ]