1

I have a python app which is using the SQLmodel library as an ORM wrapper. After successfully creating a database and multiple tables with sqlite3, I like to switch to cockroach db. Therefore I changed the connection string in the create_engine command. I followed the instructions on how to connect to cockroach db here - https://www.cockroachlabs.com/docs/v22.2/connect-to-the-database.html?filters=python&filters=sqlalchemy

My understanding is that SQLmodel fully supports SQLAlchemy connections.

But when I run the create_engine command..

engine = create_engine(os.environ["DB_CONNECTION"], echo=True)

I get the following error:

Exception has occurred: OperationalError
(psycopg2.OperationalError) FATAL:  codeParamsRoutingFailed: missing cluster identifier

(Background on this error at: https://sqlalche.me/e/14/e3q8)
psycopg2.OperationalError: FATAL:  codeParamsRoutingFailed: missing cluster identifier


The above exception was the direct cause of the following exception:

  File "/workspace/app/main.py", line 8, in <module>
    SQLModel.metadata.create_all(engine)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  codeParamsRoutingFailed: missing cluster identifier

(Background on this error at: https://sqlalche.me/e/14/e3q8)

The environment variable is set in the VSCode devcontainer.json file

"containerEnv": {
"DB_CONNECTION": "cockroachdb://wg:<removed-password>@cheeky-panther-6202.8nj.cockroachlabs.cloud:26257/wartgeld?sslmode=verify-full"
}

I downloaded the certificate as instructed.

curl --create-dirs -o $HOME/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/47771648-29f6-4b5d-b684-aeb905732cd7/cert

As for the packages I have installed the required packages according to this instructions - https://github.com/cockroachdb/sqlalchemy-cockroachdb

...
psycopg2-binary        2.9.5
pydantic               1.10.2
PyYAML                 6.0
requests               2.28.1
setuptools             65.5.0
SQLAlchemy             1.4.41
sqlalchemy-cockroachdb 1.4.4
sqlalchemy2-stubs      0.0.2a29
sqlmodel               0.0.8
typing_extensions      4.4.0
urllib3                1.26.13
uvicorn                0.20.0
wheel                  0.38.4

I am now unsure if the connection string is correct although I copy pasted it directly from the cockroachdb admin interface.

comboner
  • 11
  • 3

3 Answers3

0

The cluster identifier can be found under parameters (host).

YOUR-IDENTIFIER-521.h4f.cockroachlabs.cloud

The YOUR-IDENTIFIER-521 is your cluster identifier.

In the connection string, this must be used before the database name e.g. YOUR-IDENTIFIER-521.dbname.

Peter Utekal
  • 87
  • 10
0

I have the same issue trying to connect my Django app to serverless CockroachDB. It turned out I needed to set options in the connection URL.

postgres://username:<removed-password>@cheeky-panther-6202.8nj.cockroachlabs.cloud:26257/wartgeld?sslmode=verify-full&options=--cluster=yourroutingid
abmap
  • 141
  • 5
0

I got the same problem and the error message is extremely misleading. I think the error is probably coming from weird initialization issues. Actually, after trying this example, the problem was solved and I don't have an error running the example code for connect below anymore.

import os
import psycopg2

conn = psycopg2.connect(os.environ["DATABASE_URL"])

with conn.cursor() as cur:
    cur.execute("SELECT now()")
    res = cur.fetchall()
    conn.commit()
    print(res)
user559678
  • 11
  • 2