Kit bugfixes

[L-Mario564]

Addresses 19 bugs:

https://github.com/drizzle-team/drizzle-orm/issues/3254 bit data type in PG generated with quotes around it. Not anymore.

https://github.com/drizzle-team/drizzle-orm/issues/2630 Added a proper migration strategy when altering a serial column to a integer generated always as identity column in PG.

Before:

ALTER TABLE "table" ALTER COLUMN "id" SET DATA TYPE integer;
ALTER TABLE "table" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "table"."table_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);

Now:

ALTER TABLE "table" ALTER COLUMN "id" DROP DEFAULT;
DROP SEQUENCE "table_id_seq";
ALTER TABLE "table" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "table_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);
SELECT setval(pg_get_serial_sequence('table', 'id'), coalesce(max("id"), 0) + 1, false) FROM "table";

https://github.com/drizzle-team/drizzle-orm/issues/3004 The value for maxvalue for the created sequence when using bigint data type was incorrect. This is now the real 64-bit integer limit.

https://github.com/drizzle-team/drizzle-orm/issues/3713 Check constraints didn't insert parameterized values.

check('name', sql`${table.age} > ${21}`);

Before:

... CHECK ("users"."age" > $1);

Now:

... CHECK ("users"."age" > 21);

https://github.com/drizzle-team/drizzle-orm/issues/3867 In the issue reported, the user had a .DS_Store file that Drizzle was attempting to read as a JSON file. Added the extra check to the internal prepareOutFolder function to only include .json files.

https://github.com/drizzle-team/drizzle-orm/issues/3795 This doesn't seem to be a bug present in latest. I added tests to confirm this.

https://github.com/drizzle-team/drizzle-orm/issues/3609 Default value for bigint data type wasn't handled properly, leading to an error when attempting to JSON.stringify it.

https://github.com/drizzle-team/drizzle-orm/issues/3679 & https://github.com/drizzle-team/drizzle-orm/issues/3820 Using an non-alphanumeric character within a sequence's name (like a "-") would result in changes being applied when there weren't any. This is now fixed.

https://github.com/drizzle-team/drizzle-orm/issues/3347, https://github.com/drizzle-team/drizzle-orm/issues/2806, https://github.com/drizzle-team/drizzle-orm/issues/3685 & https://github.com/drizzle-team/drizzle-orm/issues/2454 Fixed issues with geometry(point) serialization: 1- geometry(point) got instrospected as geometry(Point). 2- Default value was serialized as [Object object].

https://github.com/drizzle-team/drizzle-orm/issues/3884 Prevent type error when the table belonging to a check constraint wasn't found during introspection in MySQL.

https://github.com/drizzle-team/drizzle-orm/issues/3585 This seems to have been fixed in newer versions of Kit. Added a test to verify such.

https://github.com/drizzle-team/drizzle-orm/issues/3559, https://github.com/drizzle-team/drizzle-orm/issues/4085 & https://github.com/drizzle-team/drizzle-orm/issues/3549 Empty strings upon introspection are now handled correctly.

https://github.com/drizzle-team/drizzle-orm/issues/1680 Fixed in latest. Added test to verify.

https://github.com/drizzle-team/drizzle-orm/issues/2888 Composite unique constraint got recreated in Postgres even if the column order hasn't been modified. Fixed by retrieving the order of the constraint's columns upon introspection.

[ooflorent]

@L-Mario564 Could you also fix #3593? The bug is still present in 0.30.4.

[L-Mario564]

@L-Mario564 Could you also fix #3593? The bug is still present in 0.30.4.

I'll work on this in a separate PR. There's still a lot of issues to solve.

[hilja]

@L-Mario564 This is wonderful. Tack this along if you've got momentum, thanks https://github.com/drizzle-team/drizzle-orm/issues/2751

[dinmukhamedm]

@L-Mario564 what is the state of this? Can we get this merged for the next release please?

[gushecht]

+1 would love to see this in the next release 🙏

[f1yingbanana]

So I really can't wait for the fix in #2888 and I patched the SQL for the unique constraints to [email protected]:

WITH constraints AS (
  SELECT c.column_name, c.data_type, constraint_type, constraint_name, constraint_schema
  FROM information_schema.table_constraints tc
  JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
  JOIN information_schema.columns AS c 
    ON c.table_schema = tc.constraint_schema
    AND tc.table_name = c.table_name 
    AND ccu.column_name = c.column_name
  WHERE tc.table_name = '${tableName}' 
    AND constraint_schema = '${tableSchema}'
)
SELECT DISTINCT ON (c.column_name, c.constraint_name, c.constraint_schema) 
  c.*, 
  kcu.ordinal_position AS position 
FROM constraints AS c
LEFT JOIN information_schema.key_column_usage AS kcu 
  USING (constraint_schema, constraint_name, column_name)
ORDER BY c.column_name, c.constraint_name, c.constraint_schema, position;

And...it had 0 effect? The new code runs but I'm still getting the constraints dropped and added 💔 Since this PR has been around for a while...maybe something else broke? 🤔

EDIT: I realized that in order for this PR to work we need to change all existing schema unique constraint columns to alphabetical order 😂 I proposed a more concise and no-op fix in #4565.