SQLite PRAGMA: Which Settings Persist and Which Reset (Tested)

Some SQLite PRAGMA settings are saved in the database file, and others last only as long as the connection. That’s why foreign_keys = ON has to be set again on every new connection. In this article I’ll walk you through the pragmas you’ll reach for and which ones stick.

How to use this page

Start with the table of common pragmas, then jump to the task you need. Unless a section says otherwise, each sqlite> line below ran as a separate connection to the same file, so you can see what persists.

Pragma	Scope	What it does	Guide
foreign_keys = ON	Connection	Enforce foreign keys	foreign keys
busy_timeout = 5000	Connection	Wait up to 5 s for locks	database is locked
journal_mode = WAL	File	Readers and a writer at once	WAL
user_version	File	Your own schema version number	this page
auto_vacuum	File	Reclaim free pages automatically	VACUUM
table_info(t)	Read only	Columns of a table	describe a table
integrity_check	Read only	Check the file for corruption	this page

The scope column reflects the runs on this page and in the linked guides. The full list is in the official reference: PRAGMA Statements.

Read and set a pragma

PRAGMA name; reads a setting, and PRAGMA name = value; changes it:

Task	Statement
Read a setting	PRAGMA user_version;
Change a setting	PRAGMA user_version = 3;

user_version is a number stored in the file header that SQLite never uses itself: “The user-version is an integer that is available to applications to use however they want” (user_version). It works as a schema-version counter for migrations:

sqlite> PRAGMA user_version;
user_version
------------
0
sqlite> PRAGMA user_version = 3;
sqlite> PRAGMA user_version;
user_version
------------
3

Per-connection settings

These reset on every new connection:

Pragma	Default in a new connection
foreign_keys	0 (off)
busy_timeout	0 ms

Set in one connection, they’re back to their defaults in the next:

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
foreign_keys
------------
0
sqlite> PRAGMA busy_timeout = 5000;
timeout
-------
5000
sqlite> PRAGMA busy_timeout;
timeout
-------
0

Within one connection, the setting holds:

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
foreign_keys
------------
1

So set these in the code that opens your connection, not once in a migration.

Settings stored in the database file

These are written into the file and seen by every later connection:

Pragma	Stored where
journal_mode = WAL	The database file
user_version	The file header

sqlite> PRAGMA journal_mode = WAL;
journal_mode
------------
wal
sqlite> PRAGMA journal_mode;
journal_mode
------------
wal

More in SQLite WAL mode.

Inspect the schema

“PRAGMAs that return results and that have no side-effects can be accessed from ordinary SELECT statements as table-valued functions”, named with a pragma_ prefix (PRAGMA functions). That lets you filter and join them:

Task	Statement
Columns of a table	PRAGMA table_info(users);
The same as a query	SELECT ... FROM pragma_table_info('users')
File size	SELECT page_size * page_count FROM pragma_page_size, pragma_page_count;

sqlite> PRAGMA table_info(users);
cid  name     type     notnull  dflt_value  pk
---  -------  -------  -------  ----------  --
0    id       INTEGER  0                    1
1    email    TEXT     1                    0
2    created  TEXT     0                    0
sqlite> PRAGMA index_list(users);
seq  name                      unique  origin  partial
---  ------------------------  ------  ------  -------
0    idx_users_created         0       c       0
1    sqlite_autoindex_users_1  1       u       0

sqlite> SELECT name, type, "notnull" FROM pragma_table_info('users') WHERE pk = 0;
name     type  notnull
-------  ----  -------
email    TEXT  1
created  TEXT  0
sqlite> SELECT m.name AS table_name, p.name AS column_name FROM sqlite_schema AS m JOIN pragma_table_info(m.name) AS p WHERE m.type = 'table' ORDER BY 1, 2;
table_name  column_name
----------  -----------
users       created
users       email
users       id

sqlite> SELECT page_size, page_count, page_size * page_count AS bytes FROM pragma_page_size, pragma_page_count;
page_size  page_count  bytes
---------  ----------  -----
4096       4           16384

Check and maintain

Task	Statement
Full corruption check	PRAGMA integrity_check;
Faster, lighter check	PRAGMA quick_check;
Refresh query planner statistics	PRAGMA optimize;

quick_check is a faster, less thorough integrity_check. Since SQLite 3.46.0, “the recommended way of running ANALYZE is with the PRAGMA optimize command” (optimize). It returns no rows when there’s nothing to report:

sqlite> PRAGMA quick_check;
quick_check
-----------
ok
sqlite> PRAGMA integrity_check;
integrity_check
---------------
ok
sqlite> PRAGMA optimize;

integrity_check “does a low-level formatting and consistency check of the database”. The docs list what it looks for, from missing pages to “UNIQUE, CHECK, and NOT NULL constraint errors” (integrity_check). To show you a failure, I slipped a negative price past a CHECK constraint with ignore_check_constraints, then ran the check in the same connection:

sqlite> CREATE TABLE prices (amount REAL CHECK (amount >= 0));
sqlite> PRAGMA ignore_check_constraints = ON;
sqlite> INSERT INTO prices VALUES (-5);
sqlite> PRAGMA ignore_check_constraints = OFF;
sqlite> PRAGMA integrity_check;
CHECK constraint failed in prices

Common mistakes

Watch for these when you work with pragmas:

• A typo fails silently. “No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored” (PRAGMA Statements). Read the setting back after changing it:

sqlite> PRAGMA no_such_pragma;

• A per-connection pragma set only once. foreign_keys and busy_timeout have to be set on every connection.
• Expecting portability. Pragmas are SQLite-only, so they won’t run on another database engine.

Further reading

• SQLite foreign keys
• SQLite WAL mode
• SQLite VACUUM
• Describe a table in SQLite
• SQLite docs: PRAGMA Statements

Tested with SQLite 3.51.0 on 7 October 2026.