I'm using Prisma as an ORM for my NodeJS project. So far I have one initial migration, which creates the basic DB schema. Now I want to add a new feature, so I need to extend and modify my schema.

My goal is to add a new table, and a field to an existing table that's a foreign key referencing this new table. What I would logically do with MySQL would be to create the table, insert the row that needs to be referenced:

CREATE TABLE `new_table` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255) NOT NULL
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO `new_table`
(`id`, `title`, `description`, `created_at`, `updated_at`)
VALUES
(1, 'Foo', 'Bar', '2022-07-21 12:35:44.514', '2022-07-21 12:35:44.513');

and then add the column and the constraint to the other table:

ALTER TABLE `old_table` ADD COLUMN `new_table_id` INTEGER NOT NULL DEFAULT 1;

ALTER TABLE `old_table` ADD CONSTRAINT `old_table_new_table_fkey` FOREIGN KEY (`new_table_id`) REFERENCES `old_table`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

The problem is that, when I run npx prisma db push, I get this error:

Error: Cannot add or update a child row: a foreign key constraint fails (`testdb`.`#sql-1_c`, CONSTRAINT `old_table_new_table_fkey` FOREIGN KEY (`new_table_id`) REFERENCES `old_table` (`id`) ON UPDATE CASCADE)

When I then check the DB after this command fails, I can see that I have the new_table in the DB, but it's empty, which causes the error. Meanwhile, the rows on old_table also have the new new_table_id column correctly added, with value 1.

Why does npx prisma db push execute every other command, but not the INSERT? And how can I change the migrations, so that I can achieve what I need (which seems like pretty easy and standard SQL fare to me)?

I am aware of npx prisma db seed, and in fact I do have a seeds file, but that command should be executed after the migrations have run, so while I added an entry for the new_table with that row, that doesn't really help me.