Multi-step migration in SQLite with 'MODIFY'
I'm trying to create a multi-step migration in SQLite. First an alter to add the field (nullable), then a CONCAT to amend the field, lastly alter to make it non-nullable. I'm getting The supplied SQL string contains more than one statement. Not sure if I'm thinking about this the wrong way...
Trying to split up the migrations into three step, but it seems like the last step (ALTER TABLE table MODIFY) to apply the not null constraint is not permitted in sqlite... I'm not sure how to handle this.
Any ideas?
24 Replies
Generating 3 different custom migration files (after the one that created the table without said column):
tl;dr: These 3 steps don't work as part of one single migration, and the third step does not work at all, even individually.
It seems like this is really a SQLite issue... Not sure how to work around adding the null constraint. Maybe by creating a duplicate column and moving over the data, and then renaming the column to the old one after having removed the old column?
This is what I have thus far:
The PRAGMA statement of
OFF
doesn't prevent timestamps related to videos from being deleted when DROP TABLE videos
is issued.
I'm not really sure what the statement-breakpoints
mean, but without that I need to run every step in separate files...👋 @Angelelz Do you have an idea?
@dhakan If I understand it right, you want to add a new
thumbnail
col that is not null but you also want to set a value for it?Exactly! Every existing row needs a value for this column.
In postgres this would be trivial, but not for sqlite it seems.
Ok I am trying something, I come back soon
ok maybe I have a simple solution
Given your initial
video
table:
When you update it to add thumbnail
not null:
Look at this: generatedAlwaysAs
.
It will produce a new sql migration like this:
Demo: https://drizzle.run/qgc22h7aju3c3np7z04gpopwOn Drizzle Run you will not able to properly test that (db is cleared every run) but I have tested on a demo project and it updates the existing rows
before/after
![No description](https://cdn.answeroverflow.com/1265955169154105486/CleanShot_2024-07-25_at_10.53.382x.png)
Thanks! I’ll try this out tomorrow probably.
Are you aware why my existing migration file is not working?
No 😦 I have never really used SQLite 😬
Gotcha… yeah, maybe I never should have myself 😂
What about the statement breakpoints? I’ve read the drizzle docs on this part, but I still don’t get it…
Not sure but I think it is a "step by step" command read by Drizzle during migration.
Instead of sending all the SQL, it runs one by one in a a transaction
you will notice that I am never sure about anything 😂
This is a good trait that make me trust people. Not being certain 😂
Anyways, big thanks for the help thus far!
Trying this out, I'm getting
And in your code you're also doing it based on
text
from drizzle-orm/sqlite-core
I'm assuming? So I'm not sure what's up with that. I'll continue digging.yes but you need the latest Drizzle version
this is a new thing of 0.32
Oh so this is really a recent feature? Interesting. Because I installed drizzle only a couple months ago
Drizzle moves slow but fast 😄