SQLite in production with Prisma and pm2: how we fixed "database is locked"

We run Escrozon, an escrow marketplace for digital assets, on Next.js with Prisma and a single SQLite file. SQLite suits a small production app well: one file, no database server to look after, and fast reads. But for a few weeks we saw failures that looked random. Some requests died with a Prisma timeout, and our nightly backup job kept failing with database is locked.

They weren't random. Three settings were working against each other. Here's what we found and exactly how we fixed it.

The symptoms

• Occasional 500 errors on ordinary pages, more often when several people were active at once
• Prisma errors saying the operation timed out while waiting for the database
• The nightly backup script stopping with Error: database is locked

The backup script was written to abort instead of saving a broken copy, which was the right call. But it also meant new backups had quietly stopped being made.

Cause 1: the default rollback journal

Check which journal mode your database uses:

sqlite3 db/app.db "PRAGMA journal_mode;"
# delete

delete is SQLite's default rollback-journal mode. In this mode a write needs an exclusive lock on the database file to commit, and readers have to wait until it finishes. A web app reads on almost every request, so those waits pile up. Once a wait is longer than Prisma's timeout, the request fails.

Switch to write-ahead logging (WAL):

sqlite3 db/app.db "PRAGMA journal_mode=WAL;"
# wal

In WAL mode, reads and writes don't block each other. There is still only one writer at a time, but readers keep working while it writes. The setting is saved inside the database file, so you run it once. (There's one exception, covered under restores below.)

Cause 2: two copies of the app writing to the same file

pm2 list looked normal at a glance. pm2 jlist told a different story:

pm2 jlist | jq -r '.[] | "\(.pm_id) \(.name) \(.pm2_env.status)"'
# 5 web online
# 7 web online

Two processes with the same name were running the same server and writing to the same SQLite file. Our ecosystem.config.js declares one app. The second was left over from an old manual pm2 start, and a pm2 save had made it come back after every reboot.

SQLite allows one writer at a time, so a second process only adds lock contention.

pm2 delete 7
pm2 save   # without this, the duplicate returns on the next reboot

Cause 3: Prisma's connection pool

Prisma opens a pool of several connections by default. With SQLite, extra connections in the same process just queue behind the same file lock. We limited the pool to one connection and gave queries a clear lock timeout:

DATABASE_URL="file:./db/app.db?connection_limit=1&socket_timeout=10"

socket_timeout=10 lets a query wait up to 10 seconds for the lock before it fails, instead of failing almost at once.

The result

We fired 24 concurrent writes through Prisma. All 24 succeeded, none hit a lock, and the whole run took about 50 ms. The backup job started working again, and the random 500s stopped.

Here's a small script to run the same test on your own setup:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Use a table where test rows are harmless, and delete them afterwards.
const results = await Promise.allSettled(
 Array.from({ length: 24 }, (_, i) =>
   prisma.auditLog.create({ data: { action: `lock-test-${i}` } })
 )
);

const failed = results.filter((r) => r.status === "rejected").length;
console.log({ ok: results.length - failed, failed });

Four gotchas that cost us time

1. pm2's ecosystem file wins over .env. pm2 injects the env block from ecosystem.config.js, and Next.js doesn't overwrite a variable that is already set. Changing only .env did nothing. We had to change the ecosystem file too, plus the .env inside .next/standalone, because a standalone build carries its own copy.

2. pm2 reload web --update-env doesn't re-read the ecosystem file. It reuses the environment pm2 already stored. This picks up the new value:

pm2 reload ecosystem.config.js --only web --update-env
pm2 save

3. The sqlite3 CLI reports its own settings, not your app's. PRAGMA busy_timeout in the CLI shows the CLI's connection (0). It tells you nothing about what Prisma uses.

4. A typo in the database path creates a new, empty database. Running sqlite3 against a file that doesn't exist silently creates it and reports the defaults. We briefly "found" a regression that was really a wrong filename. Check the path with ls first.

Backups and restores in WAL mode

WAL mode adds two files next to the database: app.db-wal and app.db-shm. Copying only app.db while the app is running can give you an incomplete backup. Use SQLite's own backup command, which is safe in WAL mode, and check the copy:

sqlite3 db/app.db ".backup 'backups/app-$(date +%F).db'"
sqlite3 "backups/app-$(date +%F).db" "PRAGMA integrity_check;"
# ok

If you ever restore an older snapshot, check the journal mode again. A copy taken before the switch is still in delete mode.

Checklist

• PRAGMA journal_mode; returns wal
• Only one process writes to the database file (check pm2 jlist, not only pm2 list)
• connection_limit=1 is set in DATABASE_URL
• The new DATABASE_URL is in every place pm2 and Next.js read it from
• Backups use .backup and pass PRAGMA integrity_check
• The journal mode is checked again after any restore

Set up this way, SQLite handles far more production traffic than people expect. Almost all of our "database is locked" errors came from our configuration, not from SQLite itself.

This post was written with AI assistance, based on our own incident notes and the commands we ran in production.