Drizzle Team•2mo 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

dhakan•2mo 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 ⚡•2mo 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?

dhakan•2mo ago

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

rphlmr ⚡•2mo ago

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

rphlmr ⚡•2mo 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 ⚡•2mo 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 ⚡•2mo ago

before/after

dhakan•2mo ago

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

rphlmr ⚡•2mo ago

No 😦 I have never really used SQLite 😬

dhakan•2mo 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 ⚡•2mo 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 😂

dhakan•2mo 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 ⚡•2mo ago

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

dhakan•2mo ago

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

rphlmr ⚡•2mo ago

Drizzle moves slow but fast 😄

Gaming

Programming

Multi-step migration in SQLite with 'MODIFY'