ALTER COLUMN: REFERENCES and FOREIGN KEY

[michaeldebetaz]

Hello everybody 👋 I discovered libSQL through drizzle and Turso. As I am loving working with sqlite so far, I am putting in the work to understand how to be autonomous with my database and migration. I am not skilled enough to contribute to the codebase, but I hope my noobie questions could help improve the docs. By the way, I tried to enter the discord but the invitation link has expired. So I'll ask my question here:

In the docs, I was confused with how to alter a column with a foreign key (in order to drop it). As I understand it, in Sqlite, you can add a REFERENCES constraint to a column, that's the way described in the docs:

CREATE TABLE `password` (
	`user_id` text REFERENCES `user`(`id`),
	`hash` text ,
);

The equivalent and the result of the drizzle-kit generation is:

CREATE TABLE `password` (
	`user_id` text NOT NULL,
	`hash` text ,
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
);

I am however unable to drop the constraint with libSQL following the docs when the table is created the second way. Any help?

Thank you for your amazing tool 🔥

[psarna]

Yeah, ALTER TABLE ALTER COLUMN only works with simple key definitions, and the one generated by drizzle-kit is "complex", because it's moved to a separate foreign key definition. Our implementation does not yet support that. The only workaround would be to enable schema modifications with pragma writable_schema=on, followed by modifying the schema definition in the sqlite_master table directly, and then applyinh the schema changes with pragma writable_schema=reset. Meanwhile, let's leave this issue open until we implement complex schema modifications for ALTER TABLE ALTER COLUMN

[michaeldebetaz]

Thank you for the reply! I'll gladly wait for support 👍 I'll try to see what the drizzle team think of generating the schema differently when using libSQL.

[u2ix]

@michaeldebetaz Did you try to bring this issue up with the drizzle team? Is there an issue we could link here?

[michaeldebetaz]

Hi, thanks for reminding me of that! I didn't do squat, sorry. I found the workaround of editing the sql migration file myself not that much of a bother.

I was also thinking that the drizzle team would eventually put that as they are not full release yet.