Migrate creates unnecessary migration with SQL syntax error

[janpio]

Reproduction repository: https://github.com/janpio/prisma-sqlite-bug
See README for how this was created.

1. Check out https://github.com/janpio/prisma-sqlite-bug/tree/353667e9b4bd8b813d84546178f648fe505bd825
2. Run npm install
3. Run npx prisma db pull to introspect the schema so it definitely matches the database
4. Run npx prisma migrate dev --create-only
5. Observe invalid SQL on line 7 of the migration.sql: INSERT INTO "new_USER" ("email", "id") SELECT "email", coalesce("id", ) AS "id" FROM "USER";
   Complete file:

   -- RedefineTables
   PRAGMA foreign_keys=OFF;
   CREATE TABLE "new_USER" (
       "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
       "email" TEXT
   );
   INSERT INTO "new_USER" ("email", "id") SELECT "email", coalesce("id", ) AS "id" FROM "USER";
   DROP TABLE "USER";
   ALTER TABLE "new_USER" RENAME TO "USER";
   Pragma writable_schema=1;
   CREATE UNIQUE INDEX "sqlite_autoindex_USER_1" ON "USER"("email");
   Pragma writable_schema=0;
   PRAGMA foreign_key_check;
   PRAGMA foreign_keys=ON;
   

6. Just to see the error message, run npx prisma migrate deploy:

   λ npx prisma migrate deploy
   Environment variables loaded from .env
   Prisma schema loaded from prisma\schema.prisma
   Datasource "db": SQLite database "dev.db" at "file:./dev.db"
   
   2 migrations found in prisma/migrations
   Error: P3018
   
   A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
   
   Migration name: 20210910212119_should_be_empty
   
   Database error code: 1
   
   Database error:
   near ")": syntax error
      0: sql_migration_connector::sql_database_step_applier::apply_script
              with migration_name="20210910212119_should_be_empty"
                at migration-engine\connectors\sql-migration-connector\src\sql_database_step_applier.rs:99
      1: migration_core::commands::apply_migrations::Applying migration
              with migration_name="20210910212119_should_be_empty"
                at migration-engine\core\src\commands\apply_migrations.rs:68
      2: migration_core::api::ApplyMigrations
                at migration-engine\core\src\api.rs:69
   

---

If you want you can replace steps 4 to 6 with running npx prisma db push instead and get this error message directly:

λ npx prisma db push
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": SQLite database "dev.db" at "file:./dev.db"
Error: near ")": syntax error
   0: sql_migration_connector::sql_database_step_applier::apply_migration
             at migration-engine\connectors\sql-migration-connector\src\sql_database_step_applier.rs:11
   1: migration_core::api::SchemaPush
             at migration-engine\core\src\api.rs:161

---

Seems the initial migrated SQL from the repository https://github.com/janpio/prisma-sqlite-bug/blob/main/prisma/migrations/init/migration.sql creates a SQLite database, that when introspected, creates a schema that Migrate will try to migrate again (although it should of course not) and then create invalid SQL to do that.

[pimeys]

So this issue is interesting. Your "mistake" here was to not define the PRIMARY KEY column as NOT NULL. What the current version does in this case is the schema says the column is NOT NULL, but this is only due compatibility reasons. Actually without setting it NOT NULL and not defining a default value, we get an autoincrement column and if you explicitly try to insert a null value to the column, you'll get the next rowid integer instead.

My change now is to just consider even NULL columns as required, if the column is part of a primary key and the key holds only one column.