Referential Integrity Preview with In‑Memory SQLite in Prisma Test Suites May Produce Inconsistent Foreign‑Key Enforcement
26.5K reputation · 21 Mar 2020, 09:42 UTC
Goal: Use an in‑memory SQLite database together with Prisma’s preview referentialIntegrity feature so that integration tests enforce foreign‑key constraints identical to a production PostgreSQL instance while keeping credentials out of the test environment.
Constraint: The referentialIntegrity preview is experimental and its implementation relies on SQLite PRAGMA settings that may not be automatically applied when Prisma Migrate creates the schema. Additionally, in‑memory databases are destroyed when the Node process ends, requiring the schema to be rebuilt for each test run, which could reset any PRAGMA changes.
Uncertainty: It is unclear whether the foreign‑key enforcement activated by the preview feature persists across successive test invocations or whether each migration step re‑applies the necessary SQLite pragmas, potentially leading to false‑positive or false‑negative constraint violations in the test suite.
Does enabling referentialIntegrity guarantee that foreign‑key checks are active for every test run when using file::memory:?cache=shared?
If the pragma settings are not retained, what is the recommended way to ensure consistent foreign‑key behavior without exposing production credentials?
Are there any known incompatibilities between the preview feature and the shared‑cache in‑memory mode that could cause intermittent test failures?
prisma
sqlite1 answer
1 question comment
Use comments to ask for clarification. Post a solution as an answer.
26,525 reputation · 21 Mar 2020, 19:15 UTC
Using Prisma’s connection hook to apply the pragma automatically
Even with a shared‑cache in‑memory SQLite database (file::memory:?cache=shared), the PRAGMA foreign_keys = ON; setting is scoped to each individual connection. If your test suite creates a new PrismaClient (or draws a connection from a pool) for every test file, the pragma must be reapplied each time; otherwise some connections will run with foreign‑key checking disabled, causing the referentialIntegrity preview to produce false‑negative results.
A reliable way to guarantee the pragma is set for every connection is to register it in Prisma’s $onConnect hook. This runs whenever a fresh connection is checked out from the underlying driver, ensuring the setting is active before any query executes.
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
datasources: { db: { url: process.env.TEST_DATABASE_URL } },
});
prisma.$onConnect(async () => {
// Enable foreign‑key enforcement for this connection
await prisma.$executeRawUnsafe('PRAGMA foreign_keys = ON;');
});
// Example test setup
beforeAll(async () => {
await prisma.$connect();
// run migrations, seed data, etc.
});
afterAll(async () => {
await prisma.$disconnect();
});
With this hook in place, you can rely on the referentialIntegrity preview to reflect the same constraint behavior you would see in a production PostgreSQL instance, without needing to manually call $executeRawUnsafe in every test.