Search code examples
postgresqldatabase-migrationprismablitz.js

Foreign Key Violation In Prisma Migration with PostgreSQL

I have a project using Prisma with PostgreSQL. I am trying to write a migration that inserts some data. The table has a foreign key to another table. The data I am inserting has a valid foreign key (the values are present in the database). However, when executing the migration, I am getting the error:

Error: P3006

Migration `20230922143649_test_prompt_creation` failed to apply cleanly to the shadow database.
Error:
insert or update on table "Prompt" violates foreign key constraint "Prompt_topicId_fkey"
   0: sql_schema_connector::validate_migrations
           with namespaces=None
             at schema-engine\connectors\sql-schema-connector\src\lib.rs:301
   1: schema_core::state::DevDiagnostic
             at schema-engine\core\src\state.rs:266

This is the migration script:

INSERT INTO
    "Prompt"("title", "desc", "seed", "promptHint", "topicId")
VALUES
    (
        '[REDACTED]',
        '[REDACTED]',
        '[REDACTED]',
        '[REDACTED]',
        2 -- Row in table Topic with id 2 exists
    );

Running this script directly against the database succeeds.

Any ideas why applying this migration via Prisma fails? I'm assuming it has something to do with the shadow database... One item to note is the Topic rows were created via a seed file, and not a migration, which may be a problem. I am not familiar with how the shadow database works.

I have tried working with ChatGPT to investigate potential solutions, googling for similar issues, reviewed some Issues on the Prisma GitHub, and searched StackOverflow for similar issues.

Solution

  • I am pretty sure that this is caused by the Shadow Database. From the Prisma documentation:

    The shadow database is created and deleted automatically* each time you run a development-focused command and is primarily used to detect problems such as schema drift.

    In my case, we had created this data in a seeds file, which can be run by Blitz.JS. If we had put this data into a migration, it would have been present when the Shadow Database was recreated, and our migration would have succeeded.

    Moral of the story: Don't use Blitz.JS' seeds functionality: put it all in DB migrations.