SQLite pragma (journal_mode) statement persistence

user_2421277 0
Viewed 500

Let's say that I have a script called schema.sql which is used to allocate a new SQLite db with my desired schema.

If this script contains PRAGMA journal_mode = 'wal'; before any DML, is the database continually set to WAL mode? Or is journal mode something that needs to be configured on each connection/command?

sqlite
2 Answers

After some experimentation it turns out the journal mode is persisted.

To demonstrate:

PS C:\> sqlite3 TestDb.db

sqlite> PRAGMA journal_mode;
delete
sqlite> PRAGMA journal_mode = 'wal';
wal
// terminate session (ctrl+c)
PS C:\> sqlite3 TestDb.db
sqlite> PRAGMA journal_mode;
wal
user_24212770

The journal mode is only persisted for WAL mode. From the docs:

Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. If a process sets WAL mode, then closes and reopens the database, the database will come back in WAL mode. In contrast, if a process sets (for example) PRAGMA journal_mode=TRUNCATE and then closes and reopens the database will come back up in the default rollback mode of DELETE rather than the previous TRUNCATE setting.

user_42063930
Related