CockroachDB connector

[tsirysndr]

This feature requests serves as a central place to discuss development and progress for the CockroachDB connector.

[nvanbenschoten]

This would be great! CockroachDB implements the PostgreSQL wire protocol, almost all of its syntax, and the majority of its data types like jsonb and arrays. As such, as long as no particularly obscure features are used, this should come for free with https://github.com/graphcool/prisma/issues/1641.

[jensneuse]

Cockroachdb's underlying architecture is a k/v store which leads to poor join performance in the current release. Therefore I'd recommend to currently not invest into this.

[nvanbenschoten]

Cockroachdb's underlying architecture is a k/v store which leads to poor join performance in the current release.

@jensneuse may be referring to https://www.cockroachlabs.com/blog/cockroachdbs-first-join/, which discusses the performance of CockroachDB's join implementation while it was in beta, about a year and a half ago. A lot has changed since then, and CockroachDB now has fully-distributed hash join, merge join, and lookup join implementations.

[vilterp]

@nvanbenschoten tried running Prisma 1.7.1 connecting to Cockroach as if it was Postgres and ran into the following issues:

• can't create the user-defined function raise_exception, since CRDB doesn't support UDFs (it's unlikely to anytime soon, so will probaby need to do something different on the Prisma side)
• pg_advisory_lock undefined (we can add this on the CRDB side, even if just as a no-op)
• CREATE SCHEMA syntax error (CRDB doesn't currently support multiple schemas (docs); we could add that or maybe Prisma could use multiple databases instead…)

[obibring]

Prisma is a dream to work with. The prospect of pairing it with an infinitely scalable DB like Coackroach and AWS Lamba / Zeit now / Apex Up seems like a dream come true from a developers perspective. Would love to see this implemented!

[obibring]

@vilterp, can you confirm this?

[do4gr]

We could probably work around the user-defined function if need be. We are using it to trigger an exception to roll back transactions. Is there another way to get this effect in CRDB? In MySql we started off with just using a hack (returning several values from a select when only one is allowed) to cause a rollback.

 select case
      when exists( some query )
      then 1
      else (select COLUMN_NAME
      from information_schema.columns
      where table_schema = project)
end

Maybe you have an idea what we could use to get the same effect in CRDB. We basically need the ability to cause a rollback based on the result of a query.

[obibring]

Any updates on this, @nvanbenschoten @vilterp ? I'm about to launch my app in production and would love to do so with Cockroach.

[nvanbenschoten]

We basically need the ability to cause a rollback based on the result of a query.

@do4gr CockroachDB has a function called crdb_internal.force_error (documented here) that can be used to force an error. Would this work to accomplish your goal?

@obibring unfortunately I don't know of any updates on our end.

[do4gr]

Thanks for the answer @nvanbenschoten , as soon as we start looking at a CockroachDB connector I'll see whether that is already enough to make it work.

[flexzuu]

Hey folks i got prisma to run on cockroach with some simple changes today. I am not sure what works yet (needs some testing). If someone is interested i can push my fork do Dockerhub.

can't create the user-defined function raise_exception, since CRDB doesn't support UDFs (it's unlikely to anytime soon, so will probaby need to do something different on the Prisma side)

• I used crdb_internal.force_error as suggested by @nvanbenschoten but i am not sure if it works

pg_advisory_lock undefined (we can add this on the CRDB side, even if just as a no-op)

• I just made the lock function a noop for now (this is problematic because it will break stuff i don't really know what)

CREATE SCHEMA syntax error (CRDB doesn't currently support multiple schemas (docs); we could add that or maybe Prisma could use multiple databases instead…)

• I made prisma run as root and created databases instead. An other approach i will test is to just use namespacing in table names.

[vilterp]

@flexzuu Nice! I'd definitely be interested in seeing this. Were all your changes on the Prisma side?

[flexzuu]

@vilterp yep all changes where made in the postgresql connector for prisma. I have not looked into make production mode of cockroach work with prisma though (only --insecure for now)

[do4gr]

Hey @flexzuu, that sounds awesome! If you want you can make a PR against our alpha branch. This will trigger our CI and if you rewrote the Postgres Connector we can tell you which tests still fail and maybe help you out with the ones that are broken.

[obibring]

Is there any high-level timeline for when work will begin on this? I'd really love to see it land especially since its low hanging fruit!

[ghost]

@flexzuu knock knock :)I can help if needed for cockroachdb.

[kylemclaren]

Cockroach currently does not support the ENUM type in SQL... would this need to be accounted for?

[obibring]

All I want for Christmas is this :(

[adamfeldman-ris]

Has anyone tried using prisma introspect against an existing CRDB database? These are the known issues with introspection of Postgres databases: prisma/prisma#2377

[mzygmunt]

Any progress or perspective for future?

[KempWatson]

This would be a truly amazing integration, CRDB+Prisma would be a distributed dream.

[saeid-ir]

Any progress or news for this amazing feature?

[aaahrens]

I think it might be a giveaway if the frigging founder of cockroachdb is speaking on prisma day :]

[andreyk-code]

@flexzuu could you please share the changes you made to get this going?

[jordanlewis]

Hey all, I work at Cockroach Labs - we're definitely interested in helping get this work going!

Who should I talk to about this? Maybe @mavilein?

[mavilein]

Hey @jordanlewis 👋 ,
i wonder whether our existing Postgres Connector works with Cockroach out of the box. Could you give that a try? We will happily work an on any bugs you report. 🙏

[steebchen]

@mavilein It does not, as we use schemas and CockroachDB does not support creating schemas, it just has the default schema 'public' already available. This happens for both Prisma 1 and Prisma 2.

❯ go run github.com/prisma/photongo lift save --create-db --name init
Response {"message":"Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: Error querying the database: db error: ERROR: at or near \"if\": syntax error: unimplemented: this syntax)","backtrace":null} doesn't have an id and I can't handle that (yet)
Got result for unknown id undefined
 ERROR  Oops, an unexpected error occured!
Error in migration engine: Dec 15 13:36:43.413  INFO migration_engine: msg="Starting migration engine RPC server" git_h
ash="4028eec09329a14692b13f06581329fddb7b2876"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:47] "here" = "here"
Dec 15 13:36:43.431  INFO quaint::single: Starting a postgresql pool with 1 connections.    
[migration-engine/connectors/sql-migration-connector/src/lib.rs:52] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:60] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:66] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:107] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/error.rs:117] &error = QueryError(
    Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: None, code: SqlState("0A000"), message:
 "at or near \"if\": syntax error: unimplemented: this syntax", detail: Some("source SQL:\nCREATE SCHEMA IF NOT EXISTS 
\"public\"\n              ^"), hint: Some("You have attempted to use a feature that is not yet implemented.\nSee: https
://github.com/cockroachdb/cockroach/issues/26443"), position: None, where_: None, schema: None, table: None, column: No
ne, datatype: None, constraint: None, file: Some("lexer.go"), line: Some(160), routine: Some("UnimplementedWithIssueDet
ail") }) }
    
    stack backtrace:
       0: backtrace::backtrace::trace
       1: backtrace::capture::Backtrace::new_unresolved
       2: failure::backtrace::internal::InternalBacktrace::new
       3: <failure::backtrace::Backtrace as core::default::Default>::default
       4: quaint::connector::postgres::error::<impl core::convert::From<tokio_postgres::error::Error> for quaint::error
::Error>::from
       5: <std::future::GenFuture<T> as core::future::future::Future>::poll
       6: <std::future::GenFuture<T> as core::future::future::Future>::poll
       7: <std::future::GenFuture<T> as core::future::future::Future>::poll
       8: std::future::poll_with_tls_context
       9: std::future::poll_with_tls_context
      10: <std::future::GenFuture<T> as core::future::future::Future>::poll
      11: tracing_core::dispatcher::with_default
      12: std::thread::local::LocalKey<T>::with

[mavilein]

@steebchen : If Cockroach does not support it you shoud also not try to create the schema. It would try to do this:

• Specify the public schema in the connection string in the Prisma schema.
• Do not use --create-db in lift save. As it cannot work anyway.

[steebchen]

I implemented your suggestions but it still fails with the same error:

datasource db {
	provider = "postgres"
	url      = "postgresql://root@localhost:26257/postgres?sslmode=disable&schema=public"
}

$ prisma2 lift save
Response {"message":"Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: Error querying the database: db error: ERROR: at or near \"if\": syntax error: unimplemented: this syntax)","backtrace":null} doesn't have an id and I can't handle that (yet)
Got result for unknown id undefined
 ERROR  Oops, an unexpected error occured!
Error in migration engine: Dec 16 11:33:49.232  INFO migration_engine: msg="Starting migr
 ERROR  Oops, an unexpected error occured!
Error in migration engine: Dec 16 11:33:49.232  INFO migration_engine: msg="Starting migr
ation engine RPC server" git_hash="4028eec09329a14692b13f06581329fddb7b2876"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:47] "here" = "here"
Dec 16 11:33:49.247  INFO quaint::single: Starting a postgresql pool with 1 connections. 
   
[migration-engine/connectors/sql-migration-connector/src/lib.rs:52] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:60] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:66] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/lib.rs:107] "here" = "here"
[migration-engine/connectors/sql-migration-connector/src/error.rs:117] &error = QueryErro
r(
    Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: None, cod
e: SqlState("0A000"), message: "at or near \"if\": syntax error: unimplemented: this synt
ax", detail: Some("source SQL:\nCREATE SCHEMA IF NOT EXISTS \"public\"\n              ^")
, hint: Some("You have attempted to use a feature that is not yet implemented.\nSee: http
s://github.com/cockroachdb/cockroach/issues/26443"), position: None, where_: None, schema
: None, table: None, column: None, datatype: None, constraint: None, file: Some("lexer.go
"), line: Some(160), routine: Some("UnimplementedWithIssueDetail") }) }
    
    stack backtrace:
       0: backtrace::backtrace::trace
       1: backtrace::capture::Backtrace::new_unresolved
       2: failure::backtrace::internal::InternalBacktrace::new
       3: <failure::backtrace::Backtrace as core::default::Default>::default
       4: quaint::connector::postgres::error::<impl core::convert::From<tokio_postgres::e
rror::Error> for quaint::error::Error>::from
       5: <std::future::GenFuture<T> as core::future::future::Future>::poll
       6: <std::future::GenFuture<T> as core::future::future::Future>::poll
       7: <std::future::GenFuture<T> as core::future::future::Future>::poll
       8: std::future::poll_with_tls_context
       9: std::future::poll_with_tls_context
      10: <std::future::GenFuture<T> as core::future::future::Future>::poll
      11: tracing_core::dispatcher::with_default
      12: std::thread::local::LocalKey<T>::with
      13: tokio::runtime::threadpool::Runtime::block_on

since this is prisma 2, tracking is continued in https://github.com/prisma/prisma2/issues/1353