# Strata · Malte Therkildsen

> Strata: A SQL database written from nothing, held against SQLite.

Source: https://therkildsen.dev/projects/strata

# Strata

An app that keeps its data in the browser needs a database there that answers SQL correctly and survives a crash.

## What it is

An app that keeps its data in the browser needs a database there that answers SQL correctly and survives a crash.

Every statement of the corpus was run in SQLite and its answer recorded, and the page against SQLite runs the same statements in Strata and compares each answer.

## The parts

### SQL as SQLite reads it

Strata reads tables with types, keys and constraints, indexes, joins of every kind, subqueries, window functions, recursive queries, RETURNING and transactions with savepoints. Each value is typed as SQLite types it, and NULL follows three-valued logic.

### Pages, a log and a crash

Tables and indexes are B-trees on pages. COMMIT appends each changed page to a write-ahead log with a checksum that runs on from the frame before, so after a crash Strata replays the log up to the last whole commit and drops the rest.

### A planner that weighs cost

For each table the planner weighs a scan, a seek on a key or an index and a covering index, orders the joined tables by dynamic programming, and pushes each filter down to the first step where it can run. EXPLAIN shows the rows each step was expected to give and the rows it gave.

### Kept in the browser

The console stores its database in the browser’s private file system as a database file and its log. A worker writes the bytes and flushes them, so a reload opens the files again through the same recovery.