---
title: "SQLite Best Practices"
slug: "sqlite"
category: "backend"
tags: ["backend", "sqlite", "embedded"]
status: "stable"
last_updated: 2026-05-29
summary: "When SQLite 3.53 is the right database, WAL mode, pragmas, backups, and driver choices."
related: ["[[backend/postgres]]", "[[backend/prisma]]", "[[ios/core-data]]"]
---

## Overview

SQLite 3.53 is the right database for single-writer workloads: edge and local-first apps, mobile, CI fixtures, embedded readers, and any service whose data fits on one disk. Pick [[backend/postgres]] when you need concurrent writers. Stay current: 3.53.0 fixed a WAL-reset corruption bug, which matters given the WAL guidance below. This page covers when to choose SQLite, the pragmas to set, backup options, and driver picks.

## Choose SQLite for single-writer workloads

SQLite handles one writer at a time across the whole database, with unlimited concurrent readers. Pick it when writes are serializable to a single process.

- Local-first and edge apps. [[ops/cloudflare-durable-objects|Cloudflare Durable Objects]], Turso, Fly.io regional replicas.
- Mobile. iOS via Core Data (which sits on SQLite) or GRDB. Android via Room.
- CI fixtures. A fresh `:memory:` database per test is faster than spinning up Postgres.
- Embedded readers. Ship a prebuilt file with your app and read it without a server.
- Desktop apps and CLI tools that need durable local state.

Pick [[backend/postgres]] when more than one process writes concurrently, or when you want managed replication and failover.

## Enable WAL mode and set `synchronous=NORMAL`

The default journal mode is conservative. WAL gives you concurrent reads alongside the single writer.

```sql
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;   -- 64 MB cache
PRAGMA temp_store = MEMORY;
```

- WAL persists across connections; set it once and the file remembers.
- `synchronous=NORMAL` is safe with WAL and noticeably faster than `FULL`. Use `FULL` only when a power loss between commit and fsync is intolerable.
- `foreign_keys=ON` must be set per connection. It is not persistent.
- `busy_timeout` lets writers wait briefly when the database is locked instead of failing immediately.

## Treat the writer as a serial queue

SQLite serializes writes. Concurrency is reader-side.

- Many readers, one writer is the sweet spot. Reads do not block reads. Reads do not block the writer in WAL mode.
- If you have multiple writer threads, funnel them through a single connection or queue.
- Wrap related writes in `BEGIN IMMEDIATE` to take the write lock up front and avoid retries.
- For batched inserts, prepare the statement once and run it in a single transaction. Expect 10x to 100x over auto-commit.

## Back up with `.backup` or `VACUUM INTO`

Copying the file with `cp` while a process has it open will corrupt the backup. Use the online tools.

```sh
sqlite3 app.db ".backup '/backups/app-$(date +%F).db'"
sqlite3 app.db "VACUUM INTO '/backups/app-$(date +%F).db';"
```

- Both take an internal lock and produce a consistent snapshot.
- `VACUUM INTO` also rebuilds the file, dropping fragmentation. Run it weekly on write-heavy databases.
- For continuous backup, use Litestream to ship WAL frames to S3-compatible storage.

## Never put a SQLite file on a network filesystem

NFS, SMB, and FUSE-mounted object stores break SQLite's locking guarantees. The database will corrupt under concurrent access; corruption is often silent until a later read.

- Keep the file on a local disk attached to the process that opens it.
- If you need shared access across machines, you do not need SQLite. Use [[backend/postgres]] or a SQLite-as-a-service provider (Turso, Cloudflare D1) that handles replication for you.
- For read-only fanout, copy the file to each reader's local disk.

## Pick the driver that matches the runtime

Native bindings beat WASM and FFI on hot paths.

- Node: `better-sqlite3` for synchronous, in-process access. Fast, simple API, blocks the event loop on long queries (usually fine).
- Bun: `bun:sqlite` is built in. Same shape as `better-sqlite3`. No install step.
- Distributed and edge: `libsql` (the Turso fork) adds replication and HTTP access while staying SQLite-compatible.
- Python: the stdlib `sqlite3` module for scripts; `aiosqlite` in async code.
- Swift: GRDB.swift, or Core Data for Apple's higher-level wrapper. See [[ios/core-data]].

[[backend/prisma]] supports SQLite, with the caveat that migrations are more limited than on Postgres.

## Related

- [[backend/postgres]]
- [[backend/prisma]]
- [[ios/core-data]]