[FEATURE]: How much support for cockroachdb?

[mthmcalixto]

Describe what you want

I like to use cockroachdb because it's serverless but I found out it's not supported yet, how much will it be?

[Enalmada]

I too am curious given I thought it was a drop in replacement for Postgres.

[a-eid]

it seem to be working for me personally, not sure what limitations it has however.

[dankochetov]

If it's working for you in Postgres mode, you can use it for sure. It's not "officially" supported yet, though, which means that if you start facing any CockroachDB-specific issues, we probably won't address them yet.

[a-eid]

@dankochetov thank you for the clarification, could you please let us know if support is in active development ?

[mbrimmer83]

I'm gonna try it out. I bet most things will work in Postgres Mode and you just have to watch out for some of the things CockroachDB doesn't support like some complex queries. I'd probably use Prisma to manage migrations until it is supported.

[roboncode]

Would be nice to support migrations.

[aydrian]

Hello. I'm a Dev Advocate at CockroachDB. I've started investigating our Drizzle support. Would love to hear your experiences. CockroachDB is postgres compatibile but doesn't support 100% of the features, like store procedures or triggers. For most things, you should be just fine using Drizzle. I did log #1267 to help it work better with CockroachDB and other distributed databases.

I'm also seeing an issue after I add a foreign key. We don't support the following:

DO $$ BEGIN
 ALTER TABLE "count_downs" ADD CONSTRAINT "count_downs_id_lists_id_fk" FOREIGN KEY ("id") REFERENCES "lists"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

It probably has something to do with not supporting stored procedures, but we are working on that. The above can be rewritten using an IF NOT EXISTS.

[Enalmada]

Thanks @aydrian for logging the issue. I did confirm rewriting what drizzle outputs by default seems to work:

// Drizzle default
DO $$ BEGIN
 CREATE TYPE "status" AS ENUM('ACTIVE', 'COMPLETED');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

// Manual rewrite
CREATE TYPE IF NOT EXISTS "status" AS ENUM ('ACTIVE', 'COMPLETED');

I have a simple test app but for anyone thinking of playing around with Drizzle and CockroachDB, everything did seem to work with Drizzle after rewriting some migrations.

Issues like this with your very first interaction will probably stop most people planning to go to production with Drizzle from continuing with CockroachDB. Getting prioritization for something in CockroachDB that at least handles this DO duplicate_object would go a long way towards giving people confidence that most things should be just fine.

[aydrian]

Curious as to why you're using the DO for your example above when the IF NOT EXISTS works? I realize not every statement has this.

I'll try to see who kind of effort and priority support for duplicate_object has on my end.

[Enalmada]

@aydrian the DO is what drizzle migrate outputs by default and I wanted to put an exact before and after but I probably used confusing wording in my post.

[robinbraemer]

drizzle-kit is not supported for cockroachdb. It's a must for even considering migrating from Prisma db pull.

drizzle-kit introspect:pg
drizzle-kit: v0.20.10
drizzle-orm: v0.29.3

No config path provided, using default path
Reading config file 'website/drizzle.config.ts'
[✓] 9 tables fetched
[⣷] 0 columns fetching
[⣷] 0 enums fetching
[⣷] 0 indexes fetching
[⣷] 0 foreign keys fetching
website/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:24462
              Error.captureStackTrace(err2);
                    ^

error: at or near "from": syntax error
    at website/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:24462:21
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PgPostgres.query (website/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:25423:21)
    at async website/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:12462:35 {
  length: 360,
  severity: 'ERROR',
  code: '42601',
  detail: 'source SQL:\n' +
    'SELECT a.attrelid::regclass::text, a.attname, is_nullable, a.attndims as array_dimensions\n' +
    "        , CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[])\n" +
    '             AND EXISTS (\n' +
    '                SELECT FROM pg_attrdef ad\n' +
    '                       ^',
  hint: 'try \\h SELECT',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'lexer.go',
  line: '404',
  routine: 'Error'
}

Node.js v18.17.1