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

PRAGMA statements read and change SQLite’s settings: foreign key enforcement, the journal mode, lock timeouts, schema details and integrity checks. They are “specific to SQLite and … not compatible with any other SQL database engine” (PRAGMA Statements). The most important thing to know is which settings stick to the database file and which last only for the current connection.

PRAGMA name; reads a setting and PRAGMA name = value; changes it. foreign_keys and busy_timeout reset on every new connection, so set them each time you connect. journal_mode = WAL and user_version are stored in the file. Unknown pragmas are silently ignored, so check the spelling.

Every example on this page was run on 6 October 2026 with SQLite 3.51.0. Each sqlite> line below was run as a separate connection to the same file, unless the section says otherwise, so you can see what persists. The lines after a statement are its output.

Read and set

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’s a handy 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 reset every time

Set in one connection, foreign_keys and busy_timeout are 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

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

More in SQLite WAL mode.

Inspect the schema

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

“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:

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

Check and maintain

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 is nothing to report:

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

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

Typos fail silently

“No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored” (PRAGMA Statements):

sqlite> PRAGMA no_such_pragma;

Read the setting back after changing it to be sure it took effect.

Common pragmas

The scope column reflects the runs above and the linked tested guides. The full list is in the official reference: PRAGMA Statements.