Symfony database connection to CockroachDB

Question

after several attempts I still don't understand what's happening, I'm trying to connect my database to a CockroachDB database, so I have this in my .env:

DATABASE_URL=postgresql://user:password@serveur_address:port/database_name?sslmode=verify-full

When I do a migration I get this error:

SQLSTATE[08006] [7] FATAL: server requires encryption
FATAL: codeParamsRoutingFailed: missing cluster identifier
HINT: Ensure that your cluster identifier is uniquely specified using any of the
following methods:

1. Host name:
   Use a driver that supports server name identification (SNI) with TLS
   connection and the hostname assigned to your cluster
   (e.g. serverless-101.5xj.gcp-us-central1.cockroachlabs.cloud)

2. Options parameter:
   Use "--cluster=" as the options parameter.
   (e.g. options="--cluster=active-roach-42")

3. Database parameter:
   Use "." as the database parameter.
   (e.g. database="active-roach-42.defaultdb")

I've tried several things, nothing works... do you have any ideas?

Answer 1

I had the same issue when trying to connect to cockroach db from a GCP instance using a Python script. The Python script is working fine in my Windows local machine but it's not working in the GCP instance. I had to pass the database name with the prefix in the hostname.

for example, my hostname is:

testdatabase-7785.8nk.cockroachlabs.cloud

my database name is:

testdatabasename

so my database name in the database URL should be:

testdatabase-7785.testdatabasename

the working database url is:

export DATABASE_URL="postgresql://testuser:[email protected]:26257/testdatabase-7785.testdatabasename?sslmode=verify-full"

I had to include the testdatabase-7785. as a prefix to the database name. Hope this helps.