[BUG]: Drizzle Kit push to Postgres 18 produces unecessary DROP SQL when the schema was NOT changed

[JohnCido]

Report hasn't been filed before.

• [x] I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.44.5

What version of drizzle-kit are you using?

0.31.5

Other packages

No response

Describe the Bug

Considering this given table:

import { sql } from "drizzle-orm";
import { pgTable, uuid } from "drizzle-orm/pg-core";

export const tests = pgTable("tests", {
	id: uuid("id").primaryKey().default(sql`uuidv7()`),
});

And this config:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
	dialect: "postgresql",
	schema: "./index.ts",
	dbCredentials: {
		url: "postgresql://postgres:password@localhost:63104/postgres",
		ssl: false,
	},
	strict: true,
	verbose: true,
});

If you run the push command, it prints this log, which is OK:

[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE "tests" (
        "id" uuid PRIMARY KEY DEFAULT uuidv7() NOT NULL
);


[✓] Changes applied

But, if you run push again without making any changes to the schema, instead of telling you there're no changes to be pushed, it prints the following SQL:

ALTER TABLE "tests" DROP CONSTRAINT "tests_id_not_null";

And of course it fails:

789 |     query.execute()
790 |   }
791 | 
792 |   function ErrorResponse(x) {
793 |     query && (query.cursorFn || query.describeFirst) && write(Sync)
794 |     const error = Errors.postgres(parseError(x))
                               ^
PostgresError: column "id" is in a primary key
 severity_local: "ERROR",
   severity: "ERROR",
       file: "tablecmds.c",
    routine: "dropconstraint_internal",
       code: "42P16"

[ruckc]

Looks like this is from their changelog

[LowArmour]

I can confirm this is happening in drizzle-kit versions 0.30.4 up to 0.31.5.

[zhelezkov]

any workaround for that? looks like a showstopper to use postgres 18 with drizzle

[jboctor]

Also confirming that this is happening with drizzle-kit 0.31.5 and postgres 18.0.

PostgresError: column "id" is in a primary key
    at ErrorResponse (file:///app/node_modules/postgres/src/connection.js:794:26)
    at handle (file:///app/node_modules/postgres/src/connection.js:480:6)
    at Socket.data (file:///app/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:508:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P16',
  file: 'tablecmds.c',
  line: '14121',
  routine: 'dropconstraint_internal'
}

[raldone01]

uuidv7() seemed to work fine using: export const uuidv7 = (name: string) => uuid(name).default(sql`uuidv7()`)

[TimFL]

uuidv7() seemed to work fine using: export const uuidv7 = (name: string) => uuid(name).default(sql`uuidv7()`)

Probably referring to this bug making schema changes gnarly and not the fact that uuidv7 isn't working with PG18.

Also running in this issue, thankfully it's a WIP project yet where it's no show stopper yet.

[hai-trung-le]

As a workaround, I patched drizzle-kit/src/serializer/pgSerializer.ts to

const tableChecks = await db.query(`SELECT 
	tc.constraint_name,
	tc.constraint_type,
	pg_get_constraintdef(con.oid) AS constraint_definition
FROM 
	information_schema.table_constraints AS tc
	JOIN pg_constraint AS con 
		ON tc.constraint_name = con.conname
		AND con.conrelid = (
			SELECT oid 
			FROM pg_class 
			WHERE relname = tc.table_name 
			AND relnamespace = (
				SELECT oid 
				FROM pg_namespace 
				WHERE nspname = tc.constraint_schema
			)
		)
WHERE 
	tc.table_name = '${tableName}'
	AND tc.constraint_schema = '${tableSchema}'
	AND tc.constraint_type = 'CHECK'
	AND con.contype != 'n';`); // new

The last line excludes non-null constraints, which have type 'n' in the pg_constraint table, from the list of check constraints.

[yousafsabir]

As a workaround, I patched drizzle-kit/src/serializer/pgSerializer.ts to

const tableChecks = await db.query(SELECT tc.constraint_name, tc.constraint_type, pg_get_constraintdef(con.oid) AS constraint_definition FROM information_schema.table_constraints AS tc JOIN pg_constraint AS con ON tc.constraint_name = con.conname AND con.conrelid = ( SELECT oid FROM pg_class WHERE relname = tc.table_name AND relnamespace = ( SELECT oid FROM pg_namespace WHERE nspname = tc.constraint_schema ) ) WHERE tc.table_name = '${tableName}' AND tc.constraint_schema = '${tableSchema}' AND tc.constraint_type = 'CHECK' AND con.contype != 'n';); // new

The last line excludes non-null constraints, which have type 'n' in the pg_constraint table, from the list of check constraints.

I want to upgrade to pg18 as well. Did you face other errors as well while upgrading?

[jboctor]

For those not working on anything time critical. I suggest waiting if you can and sticking to pg17. The team is working on a new engine here (https://github.com/drizzle-team/drizzle-orm/pull/4439). This should fix all upgrade issues. It's past due to get merged, but they've been working ceaselessly on it from my observations and will hopefully get it out to us soon.

[hai-trung-le]

As a workaround, I patched drizzle-kit/src/serializer/pgSerializer.ts to const tableChecks = await db.query(SELECT tc.constraint_name, tc.constraint_type, pg_get_constraintdef(con.oid) AS constraint_definition FROM information_schema.table_constraints AS tc JOIN pg_constraint AS con ON tc.constraint_name = con.conname AND con.conrelid = ( SELECT oid FROM pg_class WHERE relname = tc.table_name AND relnamespace = ( SELECT oid FROM pg_namespace WHERE nspname = tc.constraint_schema ) ) WHERE tc.table_name = '${tableName}' AND tc.constraint_schema = '${tableSchema}' AND tc.constraint_type = 'CHECK' AND con.contype != 'n';); // new The last line excludes non-null constraints, which have type 'n' in the pg_constraint table, from the list of check constraints.

I want to upgrade to pg18 as well. Did you face other errors as well while upgrading?

So far not really. This only affects my dev environment which pushes schema. In prod, migrations work normally.

[TimFL]

No plans to have a hotfix for this issue? Seems like a low hanging fruit to get this out as a patch and unblock PG18 projects that way.

Would hate to wait for the big new version to drop to use PG18.

[blalezarian]

Any updates on this one? Postgres 18 has so many speed improvements, would love to get going on this upgrade ASAP.

[notkiran]

when to expect a fix for this issue?

[ruckc]

I just want to use PostgreSQL 18's JWT authentication to integrate cleanly with Row Level Security in PostgreSQL, so it's on demand authentication with JWTs, instead of hardcoded credentials.

[lessquo]

just upgraded to [email protected] and it's fixed, thanks!

[notkiran]

Yes, can confirm [email protected] issue is fixed.

[RomanNabukhotnyi]

Hey everyone! This has been fixed in version 0.31.7.

[marsender]

I confirm, problem fixed in version 0.31.7.