Native support for CockroachDB

[ChuckMoe]

Good evening together,

I would like to use CockroachDB in my project. It is very easy to use it with SQLAlchemy as you can see here. All it needs is the installation of a python package and the dialect 'cockroachdb'.
My question now is, how you I achieve this elegantly? The only way that comes to mind currently is to fork the project, add the dependency and add COCKROACHDB as an allowed DATABASE_TYPE.

Cheers
Moe

[frankie567]

Hi @ChuckMoe 👋

I would love to add support to CockroachDB! Actually, I've tried some time ago but didn't push forward as I got some issues with it. To add support, several things would be involved:

• Add it as a new type in DatabaseType:

  fief/backend/fief/db/types.py

  Lines 9 to 12 in a45fd7a

  	class DatabaseType(str, Enum):
  	POSTGRESQL = "POSTGRESQL"
  	MYSQL = "MYSQL"
  	SQLITE = "SQLITE"

• Define its SSL modes:

  fief/backend/fief/db/types.py

  Lines 15 to 37 in a45fd7a

  	class PostreSQLSSLMode(str, Enum):
  	DISABLE = "disable"
  	ALLOW = "allow"
  	PREFER = "prefer"
  	REQUIRE = "require"
  	VERIFY_CA = "verify-ca"
  	VERIFY_FULL = "verify-full"
  	class MySQLSSLMode(str, Enum):
  	DISABLED = "DISABLED"
  	PREFERRED = "PREFERRED"
  	REQUIRED = "REQUIRED"
  	VERIFY_CA = "VERIFY_CA"
  	VERIFY_IDENTITY = "VERIFY_IDENTITY"
  	SSLMode = Union[PostreSQLSSLMode, MySQLSSLMode]
  	SSL_MODES: Dict[DatabaseType, Type[SSLMode]] = {
  	DatabaseType.POSTGRESQL: PostreSQLSSLMode,
  	DatabaseType.MYSQL: MySQLSSLMode,
  	}

  • Add corresponding tests: https://github.com/fief-dev/fief/blob/d950f240016a196d269518a968243a5e471571f2/backend/tests/test_db_types.py
• Define its drivers:

  fief/backend/fief/db/types.py

  Lines 39 to 49 in a45fd7a

  	SYNC_DRIVERS: Dict[DatabaseType, str] = {
  	DatabaseType.POSTGRESQL: "postgresql",
  	DatabaseType.MYSQL: "mysql+pymysql",
  	DatabaseType.SQLITE: "sqlite",
  	}
  	ASYNC_DRIVERS: Dict[DatabaseType, str] = {
  	DatabaseType.POSTGRESQL: "postgresql+asyncpg",
  	DatabaseType.MYSQL: "mysql+aiomysql",
  	DatabaseType.SQLITE: "sqlite+aiosqlite",
  	}

  — That's probably the tricky part, because I remember asyncpg had issues with Cockroach
• Add it in the text matrix:

  fief/.github/workflows/build.yml

  Lines 46 to 67 in 2549456

  	strategy:
  	fail-fast: false
  	matrix:
  	include:
  	- database_type: POSTGRESQL
  	database_host: localhost
  	database_port: 5432
  	database_username: fief
  	database_password: fiefpassword
  	database_ssl_mode: disable
  	- database_type: MYSQL
  	database_host: localhost
  	database_port: 3306
  	database_username: root
  	database_password: fiefpassword
  	database_ssl_mode: DISABLED
  	- database_type: SQLITE
  	database_host: null
  	database_port: null
  	database_username: null
  	database_password: null
  	database_ssl_mode: null

• Check that the tests are passing, in particular the migrations.

From my experience, this can be quite complex and frustrating. But I would be glad if someone could have a first try with this.

[ChuckMoe]

Hi @frankie567 👋

Setting up CockroachDB with the right certificates and a useraccount, at least automatically, is bothersome. There is a docker image you can use but it isn't documented, so I don't know if or how I could set an initial user.

* Define its drivers: https://github.com/fief-dev/fief/blob/a45fd7abb1faa25dd8c5e9d9e940344b5b2ad15f/backend/fief/db/types.py#L39-L49
   — That's probably the tricky part, because I remember asyncpg had issues with Cockroach

They also state that support for asyncpg is still experimental, yeah.

I will try do dig a little deeper. Can't say exactly when though..

[frankie567]

Had a stab at this myself in branch cockroachdb-support and quickly ran into issues with asyncpg (can't even create a workspace).

It looks like issue cockroachdb/cockroach#71908 is the root cause.

Anyway, this is rather promising and I think it could be quickly implemented when asyncpg support will be more stable.

[ChuckMoe]

In theory it's straight forward but I also quickly noticed that it won't work in its current state. I'll watch the issue and await the fix.
Thank you once more :)

[frankie567]

Apparently, CockroachDB team fixed the issue with asyncpg: cockroachdb/cockroach#95234

Who knows, maybe we'll be able to going forward with this when they release it!

[ChuckMoe]

I got a notification of this feature merge as well! I am really curious on the next release!