Drizzle Team•5mo ago

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

dhakanOP•5mo ago

Generating 3 different custom migration files (after the one that created the table without said column):

1. First change
-- Custom SQL migration file, put you code below! --
ALTER TABLE videos ADD `thumbnail` text(256);

1. First change
-- Custom SQL migration file, put you code below! --
ALTER TABLE videos ADD `thumbnail` text(256);

2. Second change
-- Custom SQL migration file, put you code below! --
UPDATE videos SET `thumbnail` = CONCAT('https://my-domain.com/', source_id, '/thumbnail.jpg');

2. Second change
-- Custom SQL migration file, put you code below! --
UPDATE videos SET `thumbnail` = CONCAT('https://my-domain.com/', source_id, '/thumbnail.jpg');

3. Third change
-- Custom SQL migration file, put you code below! --
ALTER TABLE videos ADD `thumbnail` text(256) NOT NULL;

3. Third change
-- Custom SQL migration file, put you code below! --
ALTER TABLE videos ADD `thumbnail` text(256) NOT NULL;

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:

-- Custom SQL migration file, put your code below!

-- Step 1: Disable foreign key checks
PRAGMA foreign_keys = OFF;
--> statement-breakpoint

-- Step 2: Add the new column
ALTER TABLE videos ADD `thumbnail` text(256);
--> statement-breakpoint

-- Step 3: Update the new column with the desired data
UPDATE videos SET `thumbnail` = 'https://i.ytimg.com/vi/' || source_id || '/sddefault.jpg';
--> statement-breakpoint

-- Step 4: Create the new table with the desired schema
CREATE TABLE `videos_copy` (
    `id` text(36) PRIMARY KEY NOT NULL,
    `source_id` text(256) NOT NULL,
    `name` text(256) NOT NULL,
    `description` text NOT NULL,
    `duration` integer NOT NULL,
    `thumbnail` text(256) NOT NULL, -- We add NOT NULL, something not existing on original 'videos' table
    `created_at` text DEFAULT (current_timestamp) NOT NULL,
    `updated_at` text DEFAULT (current_timestamp) NOT NULL
);
--> statement-breakpoint

-- Step 5: Copy the data from the old table to the new table
INSERT INTO videos_copy (id, source_id, name, description, duration, thumbnail, created_at, updated_at)
SELECT id, source_id, name, description, duration, thumbnail, created_at, updated_at
FROM videos;
--> statement-breakpoint

-- Step 6: Drop the old table
DROP TABLE videos;
--> statement-breakpoint

-- Step 7: Rename the new table to the original table name
ALTER TABLE videos_copy RENAME TO videos;
--> statement-breakpoint

-- Step 8: Recreate indexes
CREATE UNIQUE INDEX `videos_source_id_unique` ON `videos` (`source_id`);
--> statement-breakpoint
CREATE UNIQUE INDEX `source_id_idx` ON `videos` (`source_id`);
--> statement-breakpoint

-- Step 9: Verify we didn't violate any foreign key constraints
PRAGMA foreign_key_check;
--> statement-breakpoint

-- Step 10: Re-enable foreign key checks
PRAGMA foreign_keys = ON;

-- Custom SQL migration file, put your code below!

-- Step 1: Disable foreign key checks
PRAGMA foreign_keys = OFF;
--> statement-breakpoint

-- Step 2: Add the new column
ALTER TABLE videos ADD `thumbnail` text(256);
--> statement-breakpoint

-- Step 3: Update the new column with the desired data
UPDATE videos SET `thumbnail` = 'https://i.ytimg.com/vi/' || source_id || '/sddefault.jpg';
--> statement-breakpoint

-- Step 4: Create the new table with the desired schema
CREATE TABLE `videos_copy` (
    `id` text(36) PRIMARY KEY NOT NULL,
    `source_id` text(256) NOT NULL,
    `name` text(256) NOT NULL,
    `description` text NOT NULL,
    `duration` integer NOT NULL,
    `thumbnail` text(256) NOT NULL, -- We add NOT NULL, something not existing on original 'videos' table
    `created_at` text DEFAULT (current_timestamp) NOT NULL,
    `updated_at` text DEFAULT (current_timestamp) NOT NULL
);
--> statement-breakpoint

-- Step 5: Copy the data from the old table to the new table
INSERT INTO videos_copy (id, source_id, name, description, duration, thumbnail, created_at, updated_at)
SELECT id, source_id, name, description, duration, thumbnail, created_at, updated_at
FROM videos;
--> statement-breakpoint

-- Step 6: Drop the old table
DROP TABLE videos;
--> statement-breakpoint

-- Step 7: Rename the new table to the original table name
ALTER TABLE videos_copy RENAME TO videos;
--> statement-breakpoint

-- Step 8: Recreate indexes
CREATE UNIQUE INDEX `videos_source_id_unique` ON `videos` (`source_id`);
--> statement-breakpoint
CREATE UNIQUE INDEX `source_id_idx` ON `videos` (`source_id`);
--> statement-breakpoint

-- Step 9: Verify we didn't violate any foreign key constraints
PRAGMA foreign_key_check;
--> statement-breakpoint

-- Step 10: Re-enable foreign key checks
PRAGMA foreign_keys = ON;

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...

rphlmr ⚡•5mo ago

👋 @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?

dhakanOP•5mo ago

Exactly! Every existing row needs a value for this column. In postgres this would be trivial, but not for sqlite it seems.

rphlmr ⚡•5mo ago

Ok I am trying something, I come back soon ok maybe I have a simple solution

rphlmr ⚡•5mo ago

Given your initial video table:

export const video = sqliteTable("videos", {
  id: text("id").primaryKey(),
  sourceId: text("source_id").notNull(),
  name: text("name").notNull(),
});

export const video = sqliteTable("videos", {
  id: text("id").primaryKey(),
  sourceId: text("source_id").notNull(),
  name: text("name").notNull(),
});

When you update it to add thumbnail not null:

export const video = sqliteTable("videos", {
    id: text("id").primaryKey(),
    sourceId: text("source_id").notNull(),
    name: text("name").notNull(),
    thumbnail: text("thumbnail")
        .notNull()
        .generatedAlwaysAs(
            (): SQL =>
                sql`'https://i.ytimg.com/vi/' || ${video.sourceId} || '/sddefault.jpg'`,
        ),
});

export const video = sqliteTable("videos", {
    id: text("id").primaryKey(),
    sourceId: text("source_id").notNull(),
    name: text("name").notNull(),
    thumbnail: text("thumbnail")
        .notNull()
        .generatedAlwaysAs(
            (): SQL =>
                sql`'https://i.ytimg.com/vi/' || ${video.sourceId} || '/sddefault.jpg'`,
        ),
});

Look at this: generatedAlwaysAs. It will produce a new sql migration like this:

ALTER TABLE `videos` ADD `thumbnail` text NOT NULL GENERATED ALWAYS AS ('https://i.ytimg.com/vi/' || "source_id" || '/sddefault.jpg') VIRTUAL;

ALTER TABLE `videos` ADD `thumbnail` text NOT NULL GENERATED ALWAYS AS ('https://i.ytimg.com/vi/' || "source_id" || '/sddefault.jpg') VIRTUAL;

Demo: https://drizzle.run/qgc22h7aju3c3np7z04gpopw

Drizzle Run

Add non null column with generated value based on existing data - D...

rphlmr ⚡•5mo ago

On 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

rphlmr ⚡•5mo ago

before/after

dhakanOP•5mo ago

Thanks! I’ll try this out tomorrow probably. Are you aware why my existing migration file is not working?

rphlmr ⚡•5mo ago

No 😦 I have never really used SQLite 😬

dhakanOP•5mo ago

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…

rphlmr ⚡•5mo ago

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 😂

dhakanOP•5mo ago

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

Property 'generatedAlwaysAs' does not exist on type 'SQLiteTextBuilderInitial

Property 'generatedAlwaysAs' does not exist on type 'SQLiteTextBuilderInitial

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.

rphlmr ⚡•5mo ago

yes but you need the latest Drizzle version this is a new thing of 0.32

dhakanOP•5mo ago

Oh so this is really a recent feature? Interesting. Because I installed drizzle only a couple months ago

rphlmr ⚡•5mo ago

Drizzle moves slow but fast 😄

rphlmr ⚡•5mo ago

https://github.com/drizzle-team/drizzle-orm/releases/tag/0.32.0

GitHub

Release 0.32.0 · drizzle-team/drizzle-orm

Release notes for [email protected] and [email protected] It's not mandatory to upgrade both packages, but if you want to use the new features in both queries and migrations, you will need t...

dhakanOP•5mo ago

Bumped, and it's now defined! I just assumed this couldn't be the issue, bad on my part

rphlmr ⚡•5mo ago

No pb 🫡

dhakanOP•5mo ago

But wait a second, this solution assumes that I always want to generate these for the unforseeable future, right? Not just at the time of migrating. That might actually be a problem... I mean, right now it's not. But at some point these urls might change, and when I get that new fresh url from the external source, then I can't insert that value because I'm locked to this "always generated" one. Or am I missing something?

rphlmr ⚡•5mo ago

yes you are true but you can maybe use generate, do a migration, then remove the generate. I can't test right now but I can tomorrow a 2 steps migration hum nope it will drop the col 💀

dhakanOP•5mo ago

Yeah I was thinking that it will drop it... Everything in my drizzle config has broken since I updated to this version. My god, too many changes

rphlmr ⚡•5mo ago

Should be “just” adding dialect property and adjusting driver property (can be omitted). Ping me if you need help, I could review your config (drizzle and tsconfig if you have types issues)

dhakanOP•5mo ago

I think I was tired and irritated at this problem. Did seemingly the same changes today as I did yesterday and it now just worked... I did need to bump the format of the snapshots with drizzle-kit up, but that was it I think... I'll go with the "generated always as" for now, and make a note that this might break as it's not coming from the external source. Suboptimal, but I can't be spending days on a thumbnail sqlite migration 😛

rphlmr ⚡•5mo ago

Yeah I feel you. I usually have success with postgres but if it is too complicated, I do a node script. I set a a nullable default empty string on the schema, migrate, run my script that does its thing, remove the default, migrate again. That’s fine if you don’t have to apply your migration on many customers db I know this is not 2024 but I stop my app (maintenance mode) when I have sensitive schema changes 🥸

Gaming

Programming

Multi-step migration in SQLite with 'MODIFY'