sqlite PRAGMA Statements PRAGMAs with permanent effects

Example

Most PRAGMA statements affect only the current database connection, which means that they have to be re-applied whenever the database has been opened.

However, the following PRAGMAs write to the database file, and can be executed at any time (but in some cases, not inside a transaction):

• application_id
• journal_mode when enabling or disabling WAL mode
• schema_version
• user_version
• wal_checkpoint

The following PRAGMA settings set properties of the database file which cannot be changed after creation, so they must be executed before the first actual write to the database:

• auto_vacuum (can also be changed before VACUUM)
• encoding
• legacy_file_format
• page_size (can also be changed before VACUUM)

For example:

-- open a connection to a not-yet-existing DB file
PRAGMA page_size = 4096;
PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x);              -- database is created here, with the above settings