Drizzle TeamDT
Drizzle Team•2y ago
Derock

need help with drizzle transactions and foreign key constraints

I have a circular database schema: service references one service_generation (as the latest_generation_id) and service_generation references service.id, which poses an issue when trying to insert. To get around this, I am trying to use deferrable fk constraints + transactions.
Since drizzle does not support sqlite deferred foreign key constraints, I've manually added DEFERRABLE INITIALLY DEFERRED to the FOREIGN KEY line in the migration.

The error: 
22:17:46.594Z debug database: PRAGMA defer_foreign_keys = true;
22:17:46.594Z debug database: insert into "service" ("id", "name", "project_id", "latest_generation_id", "redeploy_secret", "deployed_generation_id", "created_at") values ((uuid_generate_v7()), ?, ?, ?, ?, null, CURRENT_TIMESTAMP) returning "id"
22:17:46.596Z error trpc:server: Internal server error on mutation: projects.services.create FOREIGN KEY constraint failed
    SqliteError: FOREIGN KEY constraint failed
        at PreparedQuery.values (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/drizzle-orm@0.30.10_@libsql+client@0.6.0_@types+better-sqlite3@7.6.8_@types+react@18.2.46_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:156:26)
        ... 4 lines matching cause stack trace ...
        at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
      [stack]: 'SqliteError: FOREIGN KEY constraint failed\n' +
        '    at PreparedQuery.values (node_modules/.pnpm/drizzle-orm@0.30.10_@libsql+client@0.6.0_@types+better-sqlite3@7.6.8_@types+react@18.2.46_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:156:26)\n' +
        '    at PreparedQuery.all (node_modules/.pnpm/drizzle-orm@0.30.10_@libsql+client@0.6.0_@types+better-sqlite3@7.6.8_@types+react@18.2.46_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:124:21)\n' +
        '    at QueryPromise.all (node_modules/.pnpm/drizzle-orm@0.30.10_[..truncated..]/node_modules/src/sqlite-core/query-builders/insert.ts:355:26)\n' +
        '    at QueryPromise.execute (node_modules/.pnpm/drizzle-orm@0.30.10_[..truncated..]/node_modules/src/sqlite-core/query-builders/insert.ts:367:40)\n' +
        '    at QueryPromise.then (node_modules/.pnpm/drizzle-orm@0.30.10_[..truncated..]/node_modules/src/query-promise.ts:31:15)\n' +
        '    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)'

Related code: 
await db.transaction(async (trx) => {
  // mark all deferrable
  // error is same with and without this line
  trx.run(sql`PRAGMA defer_foreign_keys = true;`);

  // create the service
  const [data] = await trx
    .insert(service)
    .values({
      latestGenerationId: "",
      [...]
    })
    .returning({
      id: serviceGeneration.id,
    });

  assert(data?.id, "Expected service data to be returned");

  // create initial generation
  trx.run(sql`PRAGMA defer_foreign_keys = true;`);
  const [generation] = await trx
    .insert(serviceGeneration)
    .values({
      serviceId: data.id,
      [...]
    })
    .returning({
      id: serviceGeneration.id,
    });

  assert(generation?.id, "Expected generation data to be returned");

  // update the service with the generation id
  await trx
    .update(service)
    .set({
      latestGenerationId: generation.id,
    })
    .where(eq(service.id, data.id))
    .execute();
});


Any ideas on why my deferrable fk's arent working? normally SQLite should only check the fks at the end of the transaction when commit; is ran. PRAGMA defer_foreign_keys = true; should also completely disable fk checks until the end of the transaction.
image.png
Was this page helpful?