sqlite PRAGMAs with permanent effects


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

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:

For example:

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