SQLite PRAGMAs

We have spent a lot of time on the schema. Tables, relationships, indexes, transactions. All of that lives in migration files and is applied once. But there is a different category of setting that does not belong in a migration, because it has to be applied every time the application connects to the database. These are called PRAGMAs, and if you skip them, SQLite quietly behaves in ways that surprise you in production.

PRAGMAs are SQLite-specific settings that control how the database behaves. They are not standard SQL. You will not find PRAGMA in PostgreSQL or MySQL. Think of them as SQLite’s configuration system, a set of knobs you can turn to change performance, safety, and behavior.

The syntax looks like this:

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys;        -- read the current value

Most databases expose their settings through configuration files or server flags. SQLite does not have a server, so it uses PRAGMAs instead. There are dozens of them, but only a handful matter for everyday development. This lesson covers the three you should set on every connection, a few extra ones for performance tuning, and how to inspect your database using built-in commands.

The three essential PRAGMAs

journal_mode = WAL

This is the most important pragma. WAL stands for Write-Ahead Logging. It changes how SQLite records writes.

Without WAL (the default mode, called “rollback journal”), SQLite locks the entire database during writes. While one connection is writing, no other connection can read or write. In a web app handling multiple requests at the same time, this means every request that touches the database waits in line behind any write. A single slow INSERT blocks everything.

With WAL mode, readers do not block writers and writers do not block readers. Multiple connections can read simultaneously while one connection writes. For any application that handles more than one request at a time, this is essential.

PRAGMA journal_mode = WAL;

When you enable WAL, SQLite creates two extra files next to your database: app.db-wal and app.db-shm. These are normal. Do not delete them while the database is open. They are part of the WAL mechanism.

One important detail: WAL is a persistent setting. Once you set it, it survives database restarts. You only need to set it once. But calling it on every connection is harmless and defensive, so most people do.

foreign_keys = ON

Here is a setting that catches almost everyone off guard the first time. SQLite does not enforce foreign keys by default. This pragma enables enforcement:

PRAGMA foreign_keys = ON;

Without it, you can insert a book with author_id = 999 and SQLite will not complain, even if no author with that id exists. The foreign key column exists, but the constraint is not checked. With this pragma on, the insert fails because the referenced author does not exist.

Unlike WAL mode, this pragma does not persist. It resets to OFF every time you open a new connection. You must set it on every connection, every time. If you forget, your foreign keys are decoration. They look like constraints but they do not actually constrain anything.

[!NOTE] Every relationship pattern we covered in this course (one-to-many, one-to-one, many-to-many) depends on foreign key enforcement being on. Without it, your foreign keys are syntax with no teeth. A book can reference an author that does not exist, a join table can point to rows that were deleted long ago, and your data quietly becomes inconsistent.

busy_timeout

Even in WAL mode, only one connection can write at a time. When a second connection tries to write while the first is still writing, it gets blocked. By default, the wait time is zero. The blocked writer immediately receives a “database is locked” error.

PRAGMA busy_timeout = 5000;

With busy_timeout = 5000, a blocked writer waits up to 5 seconds (5000 milliseconds) for the lock to be released before returning an error. Most writes finish in a few milliseconds, so a 5-second timeout handles the vast majority of contention without your users ever seeing an error.

Like foreign_keys, this setting does not persist. Set it on every connection.

Connection setup pattern

Now that you know the three essential PRAGMAs, here is how to set them up in code. The idea is the same in every language: open the connection, then immediately configure it before running any queries.

Node.js (node:sqlite)

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("app.db");

// WAL mode: concurrent reads, non-blocking writes
db.exec("PRAGMA journal_mode = WAL");

// Enforce foreign key constraints
db.exec("PRAGMA foreign_keys = ON");

// Wait up to 5 seconds for locked database
db.exec("PRAGMA busy_timeout = 5000");

export default db;

node:sqlite is the SQLite module built into the Node runtime (Node 22+). There is nothing to install, and Deno supports the same import so the exact same file works on both runtimes. Pragmas are just SQL, so we run them via db.exec("PRAGMA ..."), same as any other statement.

Python (sqlite3)

import sqlite3

db = sqlite3.connect("app.db")

db.execute("PRAGMA journal_mode = WAL")
db.execute("PRAGMA foreign_keys = ON")
db.execute("PRAGMA busy_timeout = 5000")

In both cases, the three PRAGMAs run immediately after opening the connection. This is a pattern you will see in almost every SQLite tutorial and production codebase. It is worth committing to memory.

Other useful PRAGMAs

The three above are the essentials. These next ones are optional but worth knowing about when you want to squeeze more performance out of SQLite.

cache_size controls how much memory SQLite uses for caching database pages in memory. The default is about 2 MB. For read-heavy workloads, increasing it means SQLite can keep more of the database in memory and make fewer disk reads:

PRAGMA cache_size = -64000; -- 64 MB (negative value means kilobytes)

The negative sign is not a typo. Positive values mean “number of pages” (which depends on page size). Negative values mean kilobytes, which is easier to reason about. -64000 means 64,000 KB, which is about 64 MB.

synchronous controls how aggressively SQLite syncs data to disk after writes. The default (FULL) is the safest because it guarantees data is written to disk before the transaction completes. NORMAL is faster and still safe when combined with WAL mode, because WAL provides its own durability guarantees:

PRAGMA synchronous = NORMAL;

temp_store controls where SQLite stores temporary tables and indexes (the ones it creates internally during complex queries). MEMORY keeps them in RAM instead of writing them to disk:

PRAGMA temp_store = MEMORY;

If you want to use these, add them to your connection setup right after the three essentials. Here is what a more complete setup looks like:

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("app.db");

db.exec("PRAGMA journal_mode = WAL");
db.exec("PRAGMA foreign_keys = ON");
db.exec("PRAGMA busy_timeout = 5000");
db.exec("PRAGMA cache_size = -64000");
db.exec("PRAGMA synchronous = NORMAL");
db.exec("PRAGMA temp_store = MEMORY");

export default db;

This is roughly how production apps configure SQLite. The three essentials plus a few tuning knobs, set once when the connection opens, and then never touched again.

Checking pragma values

You can read the current value of any pragma by calling it without a value:

PRAGMA journal_mode;     -- Returns: wal
PRAGMA foreign_keys;     -- Returns: 1 (on) or 0 (off)
PRAGMA busy_timeout;     -- Returns: 5000
PRAGMA cache_size;       -- Returns: -2000 (default)
PRAGMA synchronous;      -- Returns: 1 (NORMAL) or 2 (FULL)

This is useful for debugging. If foreign keys are not being enforced, check PRAGMA foreign_keys to confirm the setting is actually on. If you are seeing “database is locked” errors, check PRAGMA busy_timeout to confirm it is not zero.

Inspecting your database

SQLite also has built-in ways to inspect the structure of your database. These are not PRAGMAs in the strict sense, but they fit naturally here because they are tools you reach for when configuring and debugging.

sqlite_master is a special table that lists every table, index, trigger, and view in the database:

SELECT name, type FROM sqlite_master WHERE type = 'table';

This shows you all the tables in the database. It is the SQLite equivalent of “show me what is in here.”

PRAGMA table_info shows the columns of a table:

PRAGMA table_info(books);

This returns the column name, type, whether it allows NULL, its default value, and whether it is part of the primary key. It is invaluable when you are working with a database you did not create and need to understand the schema.

PRAGMA index_list shows the indexes on a table:

PRAGMA index_list(books);

This tells you which indexes exist on a table, whether they are unique, and what they are named. Combined with PRAGMA index_info(index_name), you can see exactly which columns an index covers.

Exercises

Exercise 1: Create a new database file. Before setting any PRAGMAs, check the defaults: run PRAGMA journal_mode, PRAGMA foreign_keys, and PRAGMA busy_timeout. Then set all three essential PRAGMAs and check the values again.

Exercise 2: Open a connection without foreign_keys = ON. Create a books table with a foreign key to an authors table. Insert a book with a non-existent author_id. It should succeed. Now enable foreign keys and try the same insert. It should fail.

Exercise 3: Set busy_timeout = 0 on a connection. Start a long write on one connection (a transaction that inserts thousands of rows). Try to write on another connection. You should get a “database is locked” error immediately. Set busy_timeout = 5000 on the second connection and try again.

Exercise 4: Use sqlite_master, PRAGMA table_info, and PRAGMA index_list to explore the structure of a database you have been building throughout this course. List all tables, then inspect the columns and indexes of each one.

There is one more SQLite-specific feature worth knowing before we wrap up: full-text search. It solves a problem we have already bumped into in the query optimization lesson, the one that LIKE cannot. That is where we are headed next.