Schema Design

Database 08 Sep 2026 8 min read

Enforce Column Types with SQLite STRICT Tables

SQLite’s flexible typing is useful until an application accidentally relies on it. I have seen schemas declare an INTEGER column and then assume that every stored value must be an integer. In an ordinary SQLite table, that assumption is too strong: SQLite can preserve a value that cannot be converted to the column’s preferred type. That flexibility is intentional, but for application data I often want mistakes to fail at the write boundary instead of surfacing later in a query.

Database SQLite

Database 06 Sep 2026 12 min read

Use SQLite WITHOUT ROWID for Composite Primary Keys

A table with a composite primary key often looks straightforward in SQL: two or more columns together identify one row. In SQLite, however, the storage layout depends on whether the table is an ordinary rowid table or a WITHOUT ROWID table. That difference matters when the natural key is already the identity you use for nearly every lookup. An ordinary SQLite table normally keeps a hidden integer rowid as its storage key and implements a non-integer or composite PRIMARY KEY with a separate unique index. A WITHOUT ROWID table instead makes the declared primary key the key of the table’s main B-tree.

Database SQLite

Database 05 Sep 2026 10 min read

Derive Row Values in SQLite with Generated Columns

Applications often store values that can be calculated from other columns: an order-line total from quantity and unit price, a normalized search key from text, or a duration from two timestamps. The tempting approach is to calculate the value in application code and save both the inputs and the result. That creates two sources of truth. If one code path updates the inputs but forgets to update the derived value, the row becomes internally inconsistent.

Database SQLite