Issue with `db-utils/cleanupDB` function when actual foreign key constraints exists

[ovistoica]

I added some tables to the database and after inserting data in, the DB couldn't clean anymore given foreign key constraints.

This was weird since cleanupDb set PRAGMA foreight_keys = off in the transaction:

export async function cleanupDb(prisma: PrismaClient) {
  const tables = await prisma.$queryRaw<
    { name: string }[]
  >`SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE '_prisma_migrations';`

  await prisma.$transaction([
    // Disable FK constraints to avoid relation conflicts during deletion
    prisma.$executeRawUnsafe(`PRAGMA foreign_keys = OFF`),
    // Delete all rows from each table, preserving table structures
    ...tables.map(({ name }) =>
      prisma.$executeRawUnsafe(`DELETE from "${name}"`),
    ),
    prisma.$executeRawUnsafe(`PRAGMA foreign_keys = ON`),
  ])
}

The issue, according to chatGPT, is this:

The error occurs because the PRAGMA foreign_keys = OFF statement within the prisma.$transaction block does not persist across the individual prisma.$executeRawUnsafe statements. Each prisma.$executeRawUnsafe execution is a separate statement, and SQLite's PRAGMA commands do not affect the behavior of other statements outside of the immediate execution context.

Here’s an updated approach to ensure that the PRAGMA foreign_keys = OFF setting persists while deleting the rows:

Execute the PRAGMA foreign_keys = OFF and PRAGMA foreign_keys = ON statements outside the transaction to ensure they are applied properly. Use a single transaction to delete the data from all tables. Here's a revised version of your code:

const tables = await prisma.$queryRaw<
{ name: string }[]
`SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE '_prisma_migrations';`

// Disable FK constraints to avoid relation conflicts during deletion await prisma.$executeRawUnsafe(PRAGMA foreign_keys = OFF)

// Delete all rows from each table, preserving table structures await prisma.$transaction([ ...tables.map(({ name }) => prisma.$executeRawUnsafe(DELETE FROM "${name}"), ) ])

// Re-enable FK constraints await prisma.$executeRawUnsafe(PRAGMA foreign_keys = ON)

I changed it locally, and the DB is cleaned correctly now, with pragma setting outside the transaction

I opened the issue because I didn't know if there was a specific reason for setting pragma inside the transaction and what implications it has on correctly cleaning, or what happens if the transaction fails. I suppose the transaction should be further wrapped in a try catch and setting foreign keys back on if transaction throws

If this is ok, I'm happy to open a PR with the change

Thank you

[kentcdodds]

Interesting. I welcome a PR. Thank you!