Bootstrapping Adds PostgreSQL Support, CSV Seed Files and Seed Plans on Pull Requests

Bootstrapping 0.2 is out. It runs on PostgreSQL now, as well as SQLite, with the same seed files, the same three-way merge and the same ledger. Long lists like countries and currencies can live in a CSV file instead of TOML. And bst plan -markdown prints the plan as a pull request comment, so a seed change shows what it will do before anyone merges it.

Most of the applications that ship reference data run on PostgreSQL, so it was the obvious next step. Nothing in the seed files changes. Point -db at a URL instead of a file and the same plan, apply, explain and history work there:

bst apply -db 'postgres://[email protected]/app?sslmode=require&search_path=app' -seeds seeds

The plan on a pull request is the other half of the release. A CI step applies the base branch’s seeds to a scratch database, plans the branch’s seeds against it, and posts the result. Reviewers see which rows a seed change inserts, updates, retires or deletes, as a table, next to the diff.

What bst plan -markdown prints, as a pull request shows it.

Values the Way PostgreSQL Stores Them

The hard part of a three-way merge is deciding when two values are the same. SQLite makes that a question of type affinity. PostgreSQL makes it a question of types: 7 and 7.00 are the same value in a numeric(10,2) column, two jsonb documents with their keys in a different order are the same document, and a timestamptz written with a +03:00 offset is the same moment written in UTC.

So Bootstrapping doesn’t guess. Every seed value is cast to its column’s type by the server, and every comparison uses the text the server prints for the stored value, under settings fixed for the transaction: UTC, ISO dates, hex for bytea. A role or database with its own time zone or date style changes nothing. Enums, domains, arrays, intervals, uuids and inet values all go through the same path. A value that doesn’t fit its column, such as a word too long for a varchar(10), stops the plan with the file, row and column named, before anything is written.

Locking Without Stopping the Admins

On SQLite an apply holds the write lock from start to finish. PostgreSQL gets something gentler. An apply takes an advisory lock, so two deploys at once still take turns, and then plans and writes in one REPEATABLE READ transaction. Admins keep working while it runs. If one of them edits a row after the apply planned and before it wrote that row, the apply fails with nothing written, and the next one sees the edit and keeps it. An edit is never overwritten because it arrived at the wrong moment.

A few things come with the database. Tables are found through the connection’s search_path, and the ledger goes in its first schema, so each schema has its own. When a seed sets ids in an identity or serial column, the apply moves the sequence past them, so the application’s next insert doesn’t collide with a seed row.

CSV Seed Files

A TOML file per table reads well in review until the table has 250 rows. Now the TOML file can keep the settings and name a CSV file for the rows:

table = "countries"
key = "code"
rows = "countries.csv"

[refs]
currency_id = "currencies.code"

The first line of the CSV names the columns, an empty cell is NULL, and every cell is stored the way the column’s type stores it. Moving rows from TOML to CSV changes nothing in the database. Errors name the CSV line, and the file is part of the seed set’s hash.

Tested on Both Databases

The tests that guard the merge now run on both databases. On PostgreSQL 16 the random histories, with the same seeds and the same admin edits as on SQLite, came out identical figure for figure: 492,228 values checked against the merge rules with none overwritten without a conflict, and 6,000 applies through linked tables with foreign keys enforced and none broken. 100 applies killed with SIGKILL halfway through left the database exactly as it was (73) or exactly as a finished apply leaves it (27). In 60 rounds of eight processes applying at once, exactly one wrote each time. A types test runs values through 24 column types, then plans again under five other session settings, and nothing looks changed. CI runs all of it on every push with PostgreSQL 13 and 18, and the testing post has the rest.

Before release, an independent review of the PostgreSQL code found ten ways it could go wrong, among them strings cut to a column’s length without an error, sequences left behind seeded ids, and a ledger found in the wrong schema. All ten are fixed and each has a test of its own.

The download page has the binaries, the reference has every setting, and the quick start now ends with the PostgreSQL version of the same walk-through.