SQLite

Database / 19 Sep 2026 / 5 min read

Run an Embedded Turso Database in SvelteKit Without Turso Cloud

A SvelteKit application does not need Turso Cloud to use Turso. The @tursodatabase/database package can open a database file directly inside the Node.js process: import { connect } from '@tursodatabase/database'; const db = await connect('local.db'); There is no database URL, authentication token, or network round trip in this configuration. The application reads and writes a local database file.

Database Turso

Database / 09 Sep 2026 / 10 min read

Recover Durable Object SQLite Data with Point-in-Time Recovery

A database mistake is rarely dramatic at first. It is usually one bad UPDATE, an application bug that overwrites valid state, or a deployment that writes data in a shape we did not expect. With a normal SQLite database, I would think about backups before making a risky change. SQLite-backed Cloudflare Durable Objects add another useful option: point-in-time recovery, or PITR. Cloudflare keeps enough history to restore an object’s embedded SQLite database to a point within the previous 30 days.

Cloudflare Durable Objects

Database / 09 Sep 2026 / 9 min read

Back Up a Live SQLite Database Safely with Python

Copying an SQLite file looks like an obvious backup strategy: find the .db file and copy it somewhere safe. That can be acceptable when the database is definitely idle, but it is the wrong abstraction for a database that may be changing while the copy runs. SQLite provides an Online Backup API specifically for this problem. Python exposes it as sqlite3.Connection.backup(), so an application can copy a live database into another SQLite database while preserving a consistent database snapshot.

SQLite Python

Database / 08 Sep 2026 / 7 min read

Store JSON Faster with SQLite JSONB

I like SQLite’s JSON functions because they let me keep a small amount of flexible data without immediately turning every property into a column. The trade-off is easy to miss: if I store JSON as text, SQLite has to parse that text before it can navigate the structure. Since SQLite 3.45.0, there is another option. SQLite can persist its binary JSON representation, called JSONB, directly in a BLOB. Here’s the idea: if the database is going to inspect the same JSON repeatedly, I can let SQLite store the representation it already wants to process instead of making it parse the text again.

Database SQLite

Database / 08 Sep 2026 / 8 min read

Get Changed Rows Directly with SQLite RETURNING

A database write often creates information the application immediately needs. An INSERT may generate an ID and timestamp. An UPDATE may calculate a new counter. A DELETE may need to return enough data for an audit event. The familiar approach is to write first and query afterward, but SQLite has a cleaner option for many of these cases: RETURNING. Here’s the idea: INSERT INTO jobs (name) VALUES ('resize-images') RETURNING id, name, created_at; The write and the values I care about stay in one SQL statement. That is convenient, but the more interesting part is understanding exactly what RETURNING promises—and what it does not.

Database SQLite

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

Database / 02 Sep 2026 / 7 min read

SQLite WAL Mode: Concurrency, Checkpoints, and Operational Pitfalls

SQLite is often chosen because it keeps deployment simple: an application can get transactional storage without operating a separate database server. As workloads become more concurrent, however, the default rollback journal can make read and write activity interfere more than expected. Write-ahead logging (WAL) changes that coordination model. Readers can usually continue while a writer commits changes, but WAL does not turn SQLite into a multi-writer database. Correct operation still depends on short transactions, sensible busy handling, and checkpoints that can make progress.

Database SQLite

Database / 02 Sep 2026 / 8 min read

SQLite Savepoints: Partial Rollback Inside a Transaction

A transaction normally gives application code an all-or-nothing boundary: either commit its changes or roll them all back. Some workflows need a smaller recovery point inside that larger unit of work. SQLite provides that recovery point with savepoints. A savepoint lets code mark a position inside a transaction, perform additional work, and later undo only the changes made after that position. The surrounding transaction can remain active. Savepoints are useful for batch processing, optional sub-operations, library code that may run inside an existing transaction, and workflows where one recoverable step should not discard earlier valid work.

Database SQLite