{"dataset":{"id":"00000000-0000-0000-0000-000000000029","slug":"sqlite-best-practices","name":"SQLite best practices","description":"Source-linked guidance on SQLite fundamentals: WAL mode, query plans, type affinity, foreign key enforcement, and online backup. Scoped to SQLite; not a general SQL or relational-database corpus.","visibility":"public","version":1,"documentCount":5,"publisher":"Koskamo editorial","license":"Original summaries; linked sources retain their own terms.","reviewedAt":"2026-10-04T00:00:00.000Z","readOnly":true,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},"total":5,"documents":[{"title":"Online backup with the Backup API","sourceUrl":"https://sqlite.org/backup.html","text":"The SQLite Online Backup API copies the contents of one database into another while the source is running, replacing the historical approach of taking a shared lock on the database file, copying it with an external tool such as the unix cp utility, and releasing the lock — a method that works well in many scenarios and is usually very fast but has documented shortcomings. The pattern is three calls: sqlite3_backup_init() creates an sqlite3_backup object to copy data between two databases, from a file into an in-memory database or vice-versa; sqlite3_backup_step() copies pages, and a parameter of -1 copies the entire source database to the destination; sqlite3_backup_finish() releases the object, with errors read via sqlite3_errcode() on the destination connection. For progress reporting, sqlite3_backup_remaining() and sqlite3_backup_pagecount() return the pages left to copy and the total pages, as stored by the previous step call. Step in small batches on busy databases so concurrent readers are not held off (Koskamo editorial guidance). Scope: SQLite. Reviewed October 4, 2026.","id":"sqlite-best-practices:Online backup with the Backup API","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Foreign key enforcement pragma","sourceUrl":"https://sqlite.org/foreignkeys.html","text":"SQLite has supported SQL foreign key constraints since version 3.6.19, but enforcement is disabled by default for backwards compatibility: every database connection must enable it at runtime with PRAGMA foreign_keys = ON, separately for each connection. The library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER defined; with OMIT_TRIGGER defined, foreign key definitions are parsed and queryable via PRAGMA foreign_key_list but not enforced, and PRAGMA foreign_keys is a no-op in that configuration, while OMIT_FOREIGN_KEY makes the definitions a syntax error. Future releases might enable enforcement by default, so careful developers will not assume the current default holds. Supported features include composite foreign key constraints, deferred constraints, and ON DELETE and ON UPDATE actions. Because the pragma is per-connection, enable it on every pooled connection or driver checkout rather than once at startup (Koskamo editorial guidance). Scope: SQLite. Reviewed October 4, 2026.","id":"sqlite-best-practices:Foreign key enforcement pragma","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Type affinity and dynamic typing","sourceUrl":"https://sqlite.org/datatype3.html","text":"Most SQL database engines use static, rigid typing, where a value's datatype is determined by its container column; SQLite uses a dynamic type system in which the datatype is associated with the value itself. For tables not declared STRICT, each column still receives a type affinity from its declared type, by rules applied in order: a declared type containing \"INT\" gets INTEGER affinity; containing \"CHAR\", \"CLOB\", or \"TEXT\" gets TEXT affinity (VARCHAR contains \"CHAR\"); containing \"BLOB\" or having no declared type gets BLOB affinity; containing \"REAL\", \"FLOA\", or \"DOUB\" gets REAL affinity; anything else gets NUMERIC affinity. Rule order matters: \"CHARINT\" gets INTEGER affinity because the first matching rule wins, and \"STRING\" gets NUMERIC, not TEXT. SQLite applies type conversions based on affinity before comparisons, so an unexpected affinity changes query results, not just storage. Declare column types deliberately rather than relying on defaults (Koskamo editorial guidance). Scope: SQLite. Reviewed October 4, 2026.","id":"sqlite-best-practices:Type affinity and dynamic typing","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Indexes and EXPLAIN QUERY PLAN","sourceUrl":"https://sqlite.org/eqp.html","text":"EXPLAIN QUERY PLAN returns a high-level description of the strategy SQLite uses to implement a specific SQL query — most significantly, how the query uses database indexes. For each table read, the detail column begins with SCAN or SEARCH: SCAN means a full-table scan, including iterating all records in an order defined by an index, while SEARCH means only a subset of the table rows is visited. Each SCAN or SEARCH record also reports whether an index or automatic index is used, whether the covering index optimization applies, and which WHERE-clause terms are used for indexing; further output covers temporary sorting B-trees, subqueries, and compound queries. Treat the output as interactive debugging only: the format may change between releases and changed substantially in 3.24.0 and again in 3.36.0, so applications must not depend on it. Run EXPLAIN QUERY PLAN on every slow query and investigate any SCAN on a large table where a SEARCH was expected (Koskamo editorial guidance). Scope: SQLite. Reviewed October 4, 2026.","id":"sqlite-best-practices:Indexes and EXPLAIN QUERY PLAN","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Transactions and WAL mode","sourceUrl":"https://sqlite.org/wal.html","text":"A SQLite database connection defaults to journal_mode=DELETE; convert it to write-ahead logging with PRAGMA journal_mode=WAL, which returns the string \"wal\" on success or the prior mode (for example \"delete\") if the VFS does not support the shared-memory primitives WAL needs. WAL is significantly faster in most scenarios and provides more concurrency: readers do not block writers and a writer does not block readers, so reading and writing proceed concurrently. Disk I/O tends to be more sequential, and WAL uses many fewer fsync() operations, making it less vulnerable on systems where fsync() is broken. The trade-off is that every process using the database must run on the same host computer; WAL does not work over a network filesystem. By default SQLite checkpoints automatically when a COMMIT grows the WAL file to 1000 pages or more, or when the last database connection closes. Monitor WAL file growth in long-lived deployments (Koskamo editorial guidance). Scope: SQLite. Reviewed October 4, 2026.","id":"sqlite-best-practices:Transactions and WAL mode","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"}]}