Getting SQLITE Constraint error with `foreign_keys off`

All I'm doing is dropping default fields from the tables. I had recently learned about PRAGMA foreign_key=off thing and had previously been manually moving over children tables every migration

. But this non-cursed way seems to not work as expected as I'm running into foreign key constraint check. Any help would be greatly appreciated. Below is the generated sql file and I'm using Turso.

PRAGMA foreign_keys=off;
--> statement-breakpoint

ALTER TABLE team_member RENAME TO team_member_old;--> statement-breakpoint
ALTER TABLE tournament RENAME TO tournament_old;--> statement-breakpoint

CREATE TABLE `team_member` (
    `id` text PRIMARY KEY NOT NULL,
    `team_id` text NOT NULL,
    `position` text NOT NULL,
    `invite_status` text NOT NULL,
    `user_id` text NOT NULL,
    FOREIGN KEY (`team_id`) REFERENCES `team`(`id`) ON UPDATE no action ON DELETE no action,
    FOREIGN KEY (`user_id`) REFERENCES `dancer`(`user_id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint

INSERT INTO team_member (id, team_id, position, invite_status, user_id)
SELECT id, team_id, position, invite_status, user_id
FROM team_member_old;
--> statement-breakpoint

CREATE TABLE `tournament` (
    `id` text PRIMARY KEY NOT NULL,
    `version` text NOT NULL,
    `battle_id` text NOT NULL,
    `status` text NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now'))
);
--> statement-breakpoint

INSERT INTO tournament (id, version, battle_id, status, created_at, updated_at)
SELECT id, version, battle_id, status, created_at, updated_at
FROM tournament_old;
--> statement-breakpoint

DROP TABLE team_member_old;--> statement-breakpoint
DROP TABLE tournament_old;--> statement-breakpoint

PRAGMA foreign_key_check;--> statement-breakpoint

PRAGMA foreign_keys=on;

PRAGMA foreign_keys=off;
--> statement-breakpoint

ALTER TABLE team_member RENAME TO team_member_old;--> statement-breakpoint
ALTER TABLE tournament RENAME TO tournament_old;--> statement-breakpoint

CREATE TABLE `team_member` (
    `id` text PRIMARY KEY NOT NULL,
    `team_id` text NOT NULL,
    `position` text NOT NULL,
    `invite_status` text NOT NULL,
    `user_id` text NOT NULL,
    FOREIGN KEY (`team_id`) REFERENCES `team`(`id`) ON UPDATE no action ON DELETE no action,
    FOREIGN KEY (`user_id`) REFERENCES `dancer`(`user_id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint

INSERT INTO team_member (id, team_id, position, invite_status, user_id)
SELECT id, team_id, position, invite_status, user_id
FROM team_member_old;
--> statement-breakpoint

CREATE TABLE `tournament` (
    `id` text PRIMARY KEY NOT NULL,
    `version` text NOT NULL,
    `battle_id` text NOT NULL,
    `status` text NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now'))
);
--> statement-breakpoint

INSERT INTO tournament (id, version, battle_id, status, created_at, updated_at)
SELECT id, version, battle_id, status, created_at, updated_at
FROM tournament_old;
--> statement-breakpoint

DROP TABLE team_member_old;--> statement-breakpoint
DROP TABLE tournament_old;--> statement-breakpoint

PRAGMA foreign_key_check;--> statement-breakpoint

PRAGMA foreign_keys=on;

PRAGMA foreign_keys=off;
--> statement-breakpoint

ALTER TABLE team_member RENAME TO team_member_old;--> statement-breakpoint
ALTER TABLE tournament RENAME TO tournament_old;--> statement-breakpoint

CREATE TABLE `team_member` (
    `id` text PRIMARY KEY NOT NULL,
    `team_id` text NOT NULL,
    `position` text NOT NULL,
    `invite_status` text NOT NULL,
    `user_id` text NOT NULL,
    FOREIGN KEY (`team_id`) REFERENCES `team`(`id`) ON UPDATE no action ON DELETE no action,
    FOREIGN KEY (`user_id`) REFERENCES `dancer`(`user_id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint

INSERT INTO team_member (id, team_id, position, invite_status, user_id)
SELECT id, team_id, position, invite_status, user_id
FROM team_member_old;
--> statement-breakpoint

CREATE TABLE `tournament` (
    `id` text PRIMARY KEY NOT NULL,
    `version` text NOT NULL,
    `battle_id` text NOT NULL,
    `status` text NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now'))
);
--> statement-breakpoint

INSERT INTO tournament (id, version, battle_id, status, created_at, updated_at)
SELECT id, version, battle_id, status, created_at, updated_at
FROM tournament_old;
--> statement-breakpoint

DROP TABLE team_member_old;--> statement-breakpoint
DROP TABLE tournament_old;--> statement-breakpoint

PRAGMA foreign_key_check;--> statement-breakpoint

PRAGMA foreign_keys=on;

PRAGMA foreign_keys=off;
--> statement-breakpoint

ALTER TABLE team_member RENAME TO team_member_old;--> statement-breakpoint
ALTER TABLE tournament RENAME TO tournament_old;--> statement-breakpoint

CREATE TABLE `team_member` (
    `id` text PRIMARY KEY NOT NULL,
    `team_id` text NOT NULL,
    `position` text NOT NULL,
    `invite_status` text NOT NULL,
    `user_id` text NOT NULL,
    FOREIGN KEY (`team_id`) REFERENCES `team`(`id`) ON UPDATE no action ON DELETE no action,
    FOREIGN KEY (`user_id`) REFERENCES `dancer`(`user_id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint

INSERT INTO team_member (id, team_id, position, invite_status, user_id)
SELECT id, team_id, position, invite_status, user_id
FROM team_member_old;
--> statement-breakpoint

CREATE TABLE `tournament` (
    `id` text PRIMARY KEY NOT NULL,
    `version` text NOT NULL,
    `battle_id` text NOT NULL,
    `status` text NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now'))
);
--> statement-breakpoint

INSERT INTO tournament (id, version, battle_id, status, created_at, updated_at)
SELECT id, version, battle_id, status, created_at, updated_at
FROM tournament_old;
--> statement-breakpoint

DROP TABLE team_member_old;--> statement-breakpoint
DROP TABLE tournament_old;--> statement-breakpoint

PRAGMA foreign_key_check;--> statement-breakpoint

PRAGMA foreign_keys=on;

goofysystemOP•7/28/24, 5:22 PM

It seems that is no way to manipulate PRAGMA during migration with drizzle:
https://github.com/drizzle-team/drizzle-orm/issues/1813
I even tried doing this to my migration setup to set foreign_keys as off but found no success:

import { migrate } from 'drizzle-orm/libsql/migrator';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

export const client = createClient({
    url: process.env.TOURNAMENT_DB_URL as string,
    authToken: process.env.TOURNAMENT_DB_AUTH_TOKEN as string,
});

export const db = drizzle(client);

async function main() {
    try {
        await client.execute('PRAGMA foreign_keys = OFF');
        await migrate(db, {
            migrationsFolder: './migrations',
        });
        await client.execute('PRAGMA foreign_keys = ON');
        console.log('Tables migrated!');
        process.exit(0);
    } catch (error) {
        console.error('Error performing migration: ', error);
        process.exit(1);
    }
}

main();

import { migrate } from 'drizzle-orm/libsql/migrator';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

export const client = createClient({
    url: process.env.TOURNAMENT_DB_URL as string,
    authToken: process.env.TOURNAMENT_DB_AUTH_TOKEN as string,
});

export const db = drizzle(client);

async function main() {
    try {
        await client.execute('PRAGMA foreign_keys = OFF');
        await migrate(db, {
            migrationsFolder: './migrations',
        });
        await client.execute('PRAGMA foreign_keys = ON');
        console.log('Tables migrated!');
        process.exit(0);
    } catch (error) {
        console.error('Error performing migration: ', error);
        process.exit(1);
    }
}

main();

GitHub

[BUG]: Cannot update SQLite database due to foreign key constraints...

What version of drizzle-orm are you using? 0.29.3 What version of drizzle-kit are you using? 0.20.13 Describe the Bug I don't have a reproducible demo created, but after making the following ch...

goofysystemOP•10/10/24, 5:51 PM

Running into the same issue again. Anyone able to get PRAGMA foreign_key working with drizzle migration?

goofysystemOP•10/11/24, 12:05 AM

nevermind works fine with latest drizzle and libsql/client

import { migrate } from 'drizzle-orm/libsql/migrator'; import { drizzle } from 'drizzle-orm/libsql'; import { createClient } from '@libsql/client'; export const client = createClient({ url: process.env.TOURNAMENT_DB_URL as string, authToken: process.env.TOURNAMENT_DB_AUTH_TOKEN as string, }); export const db = drizzle(client); async function main() { try { await client.execute('PRAGMA foreign_keys = OFF'); await migrate(db, { migrationsFolder: './migrations', }); await client.execute('PRAGMA foreign_keys = ON'); console.log('Tables migrated!'); process.exit(0); } catch (error) { console.error('Error performing migration: ', error); process.exit(1); } } main();

Getting SQLITE Constraint error with `foreign_keys off`

Similar Threads

Similar Threads

Similar Threads