SQLite Options

Written 2022-10-02

PRAGMA journal_mode=WAL will enable the write-ahead log. This is persisted into the database and so only needs to be done once when the file is created.
PRAGMA case_sensitive_like = true will cause the LIKE operator to match case sensitively. Note that there is no ILIKE operator, so you have to choose one or the other. The GLOB operator can help here though, since it is case-sensitive (and uses * instead of % for its wildcards).
Enforcement of column types can be enabled by adding the keyword STRICT to the end of the CREATE TABLE statement.
PRAGMA defer_foreign_keys = true can be used to indicate that deferrable foreign key constraint checking should be deferred until the end of a transaction. This only applies to the current transaction, so it needs to be done again every time you are in a transaction that needs it.
Bulk Loading
- When bulk loading data, the following settings can improve write performance significantly. Most of these settings should absolutely not be used in other scenarios or you can't recreate the entire database in the event of a crash, as they trade away all the the safety guarantees in exchange for faster writes.
- ```
PRAGMA journal_mode = OFF;
PRAGMA page_size = 16384; -- if using the zstd VFS extension
PRAGMA synchronous = 0;
PRAGMA auto_vacuum = NONE;
PRAGMA cache_size = -10485760;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA temp_store = MEMORY;
```