need help with drizzle transactions and foreign key constraints
I have a circular database schema:
Related code:
Any ideas on why my deferrable fk's arent working? normally SQLite should only check the fks at the end of the transaction when
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:
[2;30m22:17:46.594Z[0m [2;34mdebug[0m [2;36mdatabase:[0m[2;32m[0m PRAGMA defer_foreign_keys = true;
[2;30m22:17:46.594Z[0m [2;34mdebug[0m [2;32m[2;36mdatabase:[0m[2;32m[0m 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"
[2;30m22:17:46.596Z[0m [2;31merror[0m [2;36mtrpc:server:[0m Internal server error on [2;31mmutation[0m: [2;31mprojects.services.create[0m FOREIGN KEY constraint failed
SqliteError: FOREIGN KEY constraint failed
at PreparedQuery.values (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:156:26)
[2;30m... 4 lines matching cause stack trace ...[0m
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
[stack]: [2;32m'SqliteError: FOREIGN KEY constraint failed\n' [0m+[0m[2;32m
' at PreparedQuery.values (node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:156:26)\n' [0m+[0m[2;32m
' at PreparedQuery.all (node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:124:21)\n' [0m+[0m[2;32m
' at QueryPromise.all (node_modules/.pnpm/[email protected]_[..truncated..]/node_modules/src/sqlite-core/query-builders/insert.ts:355:26)\n' [0m+[0m[2;32m
' at QueryPromise.execute (node_modules/.pnpm/[email protected]_[..truncated..]/node_modules/src/sqlite-core/query-builders/insert.ts:367:40)\n' [0m+[0m[2;32m
' at QueryPromise.then (node_modules/.pnpm/[email protected]_[..truncated..]/node_modules/src/query-promise.ts:31:15)\n' [0m+[0m[2;32m
' at process.processTicksAndRejections (node:internal/process/task_queues:95:5)'[0m
[2;30m22:17:46.594Z[0m [2;34mdebug[0m [2;36mdatabase:[0m[2;32m[0m PRAGMA defer_foreign_keys = true;
[2;30m22:17:46.594Z[0m [2;34mdebug[0m [2;32m[2;36mdatabase:[0m[2;32m[0m 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"
[2;30m22:17:46.596Z[0m [2;31merror[0m [2;36mtrpc:server:[0m Internal server error on [2;31mmutation[0m: [2;31mprojects.services.create[0m FOREIGN KEY constraint failed
SqliteError: FOREIGN KEY constraint failed
at PreparedQuery.values (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:156:26)
[2;30m... 4 lines matching cause stack trace ...[0m
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
[stack]: [2;32m'SqliteError: FOREIGN KEY constraint failed\n' [0m+[0m[2;32m
' at PreparedQuery.values (node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:156:26)\n' [0m+[0m[2;32m
' at PreparedQuery.all (node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bet_2qanqj7n56pb6fe7hpfsaedbzq/node_modules/src/better-sqlite3/session.ts:124:21)\n' [0m+[0m[2;32m
' at QueryPromise.all (node_modules/.pnpm/[email protected]_[..truncated..]/node_modules/src/sqlite-core/query-builders/insert.ts:355:26)\n' [0m+[0m[2;32m
' at QueryPromise.execute (node_modules/.pnpm/[email protected]_[..truncated..]/node_modules/src/sqlite-core/query-builders/insert.ts:367:40)\n' [0m+[0m[2;32m
' at QueryPromise.then (node_modules/.pnpm/[email protected]_[..truncated..]/node_modules/src/query-promise.ts:31:15)\n' [0m+[0m[2;32m
' at process.processTicksAndRejections (node:internal/process/task_queues:95:5)'[0m
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();
});
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();
});
commit; is ran. PRAGMA defer_foreign_keys = true; should also completely disable fk checks until the end of the transaction.
3 Replies
bump
are drizzle transactions not real database transactions?
this errors with
trying that exact same approach with drizzle-orm doesn't work:
cannot commit - no transaction is active
ok so they are actual transactions, https://discord.com/channels/1043890932593987624/1240832420945596416/1240958720033165354
still tryna figure out why this isn't working then
was able to get this working in a very hacky way:
// create a generation for the service
const trxResult = await ctx.db.transaction(async (trx) => {
// @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
const db: Database = trx.session.client;
db.pragma(`defer_foreign_keys = ON`);
const generationId = uuidv7();
const serviceId = uuidv7();
// create initial generation
const dialect = new SQLiteSyncDialect();
const createGenerationQuery = dialect.sqlToQuery(
trx
.insert(serviceGeneration)
.values({
id: generationId,
serviceId: serviceId,
source: ServiceSource.Docker,
dockerImage: "traefik/whoami",
})
.getSQL(),
);
const genCreateResult = db
.prepare(createGenerationQuery.sql)
.run(...createGenerationQuery.params);
logger.debug(
"inserted generation",
createGenerationQuery.sql,
genCreateResult,
);
// create the service
const createServiceQuery = dialect.sqlToQuery(
trx
.insert(service)
.values({
id: serviceId,
name: input.name,
projectId: ctx.project.getData().id,
latestGenerationId: generationId,
redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
"hex",
),
})
.returning({
id: serviceGeneration.id,
})
.getSQL(),
);
const createResult = db
.prepare(createServiceQuery.sql)
.run(...createServiceQuery.params);
logger.debug("inserted service", createServiceQuery.sql, createResult);
return serviceId;
})
// create a generation for the service
const trxResult = await ctx.db.transaction(async (trx) => {
// @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
const db: Database = trx.session.client;
db.pragma(`defer_foreign_keys = ON`);
const generationId = uuidv7();
const serviceId = uuidv7();
// create initial generation
const dialect = new SQLiteSyncDialect();
const createGenerationQuery = dialect.sqlToQuery(
trx
.insert(serviceGeneration)
.values({
id: generationId,
serviceId: serviceId,
source: ServiceSource.Docker,
dockerImage: "traefik/whoami",
})
.getSQL(),
);
const genCreateResult = db
.prepare(createGenerationQuery.sql)
.run(...createGenerationQuery.params);
logger.debug(
"inserted generation",
createGenerationQuery.sql,
genCreateResult,
);
// create the service
const createServiceQuery = dialect.sqlToQuery(
trx
.insert(service)
.values({
id: serviceId,
name: input.name,
projectId: ctx.project.getData().id,
latestGenerationId: generationId,
redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
"hex",
),
})
.returning({
id: serviceGeneration.id,
})
.getSQL(),
);
const createResult = db
.prepare(createServiceQuery.sql)
.run(...createServiceQuery.params);
logger.debug("inserted service", createServiceQuery.sql, createResult);
return serviceId;
})
// create a generation for the service
const trxResult = await ctx.db.transaction(async (trx) => {
// @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
const db: Database = trx.session.client;
db.pragma(`defer_foreign_keys = ON`);
const generationId = uuidv7();
const serviceId = uuidv7();
// create initial generation
await trx.insert(serviceGeneration).values({
id: generationId,
serviceId: serviceId,
source: ServiceSource.Docker,
dockerImage: "traefik/whoami",
});
logger.debug("inserted generation");
// create the service
await trx
.insert(service)
.values({
id: serviceId,
name: input.name,
projectId: ctx.project.getData().id,
latestGenerationId: generationId,
redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
"hex",
),
})
.returning({
id: serviceGeneration.id,
});
logger.debug("inserted service");
return serviceId;
})
// create a generation for the service
const trxResult = await ctx.db.transaction(async (trx) => {
// @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
const db: Database = trx.session.client;
db.pragma(`defer_foreign_keys = ON`);
const generationId = uuidv7();
const serviceId = uuidv7();
// create initial generation
await trx.insert(serviceGeneration).values({
id: generationId,
serviceId: serviceId,
source: ServiceSource.Docker,
dockerImage: "traefik/whoami",
});
logger.debug("inserted generation");
// create the service
await trx
.insert(service)
.values({
id: serviceId,
name: input.name,
projectId: ctx.project.getData().id,
latestGenerationId: generationId,
redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
"hex",
),
})
.returning({
id: serviceGeneration.id,
});
logger.debug("inserted service");
return serviceId;
})
15:20:57.412Z debug database: insert into "service_generation" ("id", "service_id", "deployment_id", "source", "environment", "docker_image", "docker_registry_username", "docker_registry_password", "github_username", "github_repository", "github_branch", "git_url", "git_branch", "build_method", "build_path", "command", "entrypoint", "replicas", "max_replicas_per_node", "deploy_mode", "zero_downtime", "max_cpu", "max_memory", "max_pids", "restart", "restart_delay", "restart_max_attempts", "healthcheck_enabled", "healthcheck_command", "healthcheck_interval", "healthcheck_timeout", "healthcheck_retries", "healthcheck_start_period", "logging_max_size", "logging_max_files", "created_at") values (?, ?, null, ?, null, ?, null, null, null, null, null, null, null, ?, ?, null, null, ?, null, ?, ?, ?, ?, ?, ?, ?, null, ?, null, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
15:20:57.421Z error trpc:server: Internal server error on mutation: projects.services.create FOREIGN KEY constraint failed
SqliteError: FOREIGN KEY constraint failed
at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)
... 3 lines matching cause stack trace ...
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
[stack]: 'SqliteError: FOREIGN KEY constraint failed\n' +
' at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)\n' +
15:20:57.412Z debug database: insert into "service_generation" ("id", "service_id", "deployment_id", "source", "environment", "docker_image", "docker_registry_username", "docker_registry_password", "github_username", "github_repository", "github_branch", "git_url", "git_branch", "build_method", "build_path", "command", "entrypoint", "replicas", "max_replicas_per_node", "deploy_mode", "zero_downtime", "max_cpu", "max_memory", "max_pids", "restart", "restart_delay", "restart_max_attempts", "healthcheck_enabled", "healthcheck_command", "healthcheck_interval", "healthcheck_timeout", "healthcheck_retries", "healthcheck_start_period", "logging_max_size", "logging_max_files", "created_at") values (?, ?, null, ?, null, ?, null, null, null, null, null, null, null, ?, ?, null, null, ?, null, ?, ?, ?, ?, ?, ?, ?, null, ?, null, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
15:20:57.421Z error trpc:server: Internal server error on mutation: projects.services.create FOREIGN KEY constraint failed
SqliteError: FOREIGN KEY constraint failed
at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)
... 3 lines matching cause stack trace ...
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
[stack]: 'SqliteError: FOREIGN KEY constraint failed\n' +
' at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)\n' +