Welcome to Spawn

Spawn is a Database Build System. It currently supports PostgreSQL via psql, but with plans to support many other databases in the future. It includes core features such as:

Create and apply migrations
Testing system
Reusable components
Easy updating of database functions and views while maintaining history

Getting started

First, initialize a new Spawn project with Docker support:

spawn init --docker

This creates a spawn.toml configuration file, a docker-compose.yaml for local PostgreSQL development, and the necessary project structure. Start your database:

docker compose up -d

You’re now ready to create migrations!

The magic

Spawn solves a few pain points for database development, including:

How do I update a database function, keeping the change history visible via git in an easy to review way, all while ensuring old migrations work?
How do I run complex tests against my database?
How can I use external data in my migrations?
How do I create per-migration (not yet available) as well as regression tests?
How can I apply some changes to local but not prod?

Spawn solves these pain points through four mechanisms:

Separate the components (views, functions, triggers, reusable snippets) from the migrations.
Content Addressable Storage (CAS), much like git, for components. Pin migrations to specific versions of components.
Dynamic SQL generation via templating.
Comprehensive testing framework.

Components

Instead of storing your functions inside the migration script, store them in your spawn/components folder. For example, you may create a component with the following contents, stored in spawn/components/functions/add_fee.sql:

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * 1.1;
END;
$$ LANGUAGE plpgsql;

Then you create your new migration:

spawn migration new add_fee

creating migration with name 20260131075608-add_fee
creating migration at spawn/migrations/20260131075608-add_fee/up.sql
New migration created: 20260131075608-add_fee

migration apply will always use the pinned version by default.

Now, open up that migration script and reference your migration:

BEGIN;

{% include 'functions/add_fee.sql' %}

COMMIT;

Finally, use build to see how the final SQL looks:

spawn migration build 20260131075608-add_fee

-- Output:
BEGIN;

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * 1.1;
END;
$$ LANGUAGE plpgsql;

COMMIT;

Easy!

Pinning via CAS

You’ve realised now that you’d rather your add_fee function pull the fee from a table, so that updating the fee is as easy as updating an entry in that table.

However, if you update the component directly, we hit two problems:

Any old migration tests (not yet implemented) that depend on that functionality will break.
If we try running old migrations against a new database, 20260131075608-add_fee will now pick up the new version of the component instead of the old.

We can solve both of these problems via pinning. Pinning pins the migration to a specific version of the components folder.

If you try and apply your migration now, you’ll hit an error:

spawn migration apply 20260131075608-add_fee

Error: Failed to generate migration '20260131075608-add_fee'. Is it pinned? Run `spawn migration pin 20260131075608-add_fee` or use `--no-pin` to apply without pinning.

Caused by:
    0: could not load pinned files lock file
    1: NotFound (permanent) at read, context: { service: fs, path: spawn/migrations/20260131075608-add_fee/lock.toml, range: 0- } => entity not found, source: No such file or dir
ectory (os error 2)
    2: No such file or directory (os error 2)

The error tells us that we need to either pin the pingration, or use --no-pin. Run like so:

spawn migration pin 20260131075608-add_fee

Migration pinned: 257372323412c39db23080ade53cb9da

This creates a lock.toml file in the migration folder:

ls spawn/migrations/20260131075608-add_fee

lock.toml       up.sql

cat spawn/migrations/20260131075608-add_fee/lock.toml

pin = "257372323412c39db23080ade53cb9da"

This stores a snapshot of the components inside spawn/pinned in a manner very similar to how git works. The files produced by pin are stored in /pinned.

Let’s apply this to our locally running database:

spawn migration apply

TARGET: postgres_psql
ENVIRONMENT: dev

The following 1 migration will be applied:
  1. 20260131075608-add_fee

Do you want to apply these migrations? [y/N]

Migration '20260131075608-add_fee' applied successfully
All migrations applied successfully.

Or alternatively, you can target this specific migration to apply:

spawn migration apply 20260131075608-add_fee

You can see that it has been applied:

spawn migration status

┌────────────────────────┬────────────┬────────┬──────────┬───────────┐
│ Migration              │ Filesystem │ Pinned │ Database │ Status    │
├────────────────────────┼────────────┼────────┼──────────┼───────────┤
│ 20260131075608-add_fee │ ✓          │ ✓      │ ✓        │ ✓ Applied │
└────────────────────────┴────────────┴────────┴──────────┴───────────┘

And can verify by calling it via docker using psql

docker exec -it postgres-db psql -U postgres -d postgres -c "select add_fee(12.2)"

 add_fee
---------
   13.42
(1 row)

Now let’s make a new migration that updates the component:

spawn migration new update-add-func

creating migration with name 20260131080417-update-add-func
creating migration at spawn/migrations/20260131080417-update-add-func/up.sql
New migration created: 20260131080417-update-add-func

We can edit the component in place, so that we have a useful git record of changes made over time, and so that PRs are easier to review. We update the migration in place. In this example, we are going to update the function to obtain the base fee multiplier from a table rather than hard coded into the function. Create the migration first, replacing the contents of your new <timestamp>-update-add-func migration:

BEGIN;

CREATE TABLE fee (
    name TEXT PRIMARY KEY,
    amount NUMERIC(3, 2)
);

INSERT INTO fee (name, amount) VALUES ('base', 1.12);

{% include 'functions/add_fee.sql' %}

COMMIT;

And now update the component in place at functions/add_fee.sql, so it uses this new table:

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * (SELECT amount FROM fee WHERE name = 'base');
END;
$$ LANGUAGE plpgsql;

If we were to build the old migration again, we would see that it now uses the new component instead of the old, even though we pinned it!

spawn migration build 20260131075608-add_fee

BEGIN;

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * (SELECT amount FROM fee WHERE name = 'base');
END;
$$ LANGUAGE plpgsql;

COMMIT;

⚠️  This migration has been pinned. Run with --pinned to see how it will be generated when applied to a database.

However, it does give a warning at the end to tell us that this migration is pinned, and so we should use --pinned to see how it will be applied to the database:

spawn migration build 20260131075608-add_fee --pinned

BEGIN;

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * 1.1;
END;
$$ LANGUAGE plpgsql;

COMMIT;

Perfect. The old migration script is unaffected by our changes to the functions/add_fee.sql migration.

Let’s test building our new migration to ensure it shows us the new version of the component, and then pin it:

spawn migration build 20260131080417-update-add-func

BEGIN;

CREATE TABLE fee (
    name TEXT PRIMARY KEY,
    amount NUMERIC(3, 2)
);

INSERT INTO fee (name, amount) VALUES ('base', 1.12);

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * (SELECT amount FROM fee WHERE name = 'base');
END;
$$ LANGUAGE plpgsql;

COMMIT;

spawn migration status

┌────────────────────────────────┬────────────┬────────┬──────────┬───────────┐
│ Migration                      │ Filesystem │ Pinned │ Database │ Status    │
├────────────────────────────────┼────────────┼────────┼──────────┼───────────┤
│ 20260131075608-add_fee         │ ✓          │ ✓      │ ✓        │ ✓ Applied │
│ 20260131080417-update-add-func │ ✓          │ ✗      │ ✗        │ ○ Pending │
└────────────────────────────────┴────────────┴────────┴──────────┴───────────┘

spawn migration pin 20260131080417-update-add-func

Migration pinned: f12696fd7a68e4102f8c03dd23a774bf

spawn migration status

┌────────────────────────────────┬────────────┬────────┬──────────┬───────────┐
│ Migration                      │ Filesystem │ Pinned │ Database │ Status    │
├────────────────────────────────┼────────────┼────────┼──────────┼───────────┤
│ 20260131075608-add_fee         │ ✓          │ ✓      │ ✓        │ ✓ Applied │
│ 20260131080417-update-add-func │ ✓          │ ✓      │ ✗        │ ○ Pending │
└────────────────────────────────┴────────────┴────────┴──────────┴───────────┘

Let’s test our call to fee both before and after applying the new migration:

docker exec -it postgres-db psql -U postgres -d postgres -c "select add_fee(12.2)"

 add_fee
---------
   13.42
(1 row)

spawn migration apply 20260131080417-update-add-func

Migration '20260131080417-update-add-func' applied successfully
All migrations applied successfully.

docker exec -it postgres-db psql -U postgres -d postgres -c "select add_fee(12.2)"

 add_fee
---------
  13.664
(1 row)

spawn migration status

┌────────────────────────────────┬────────────┬────────┬──────────┬───────────┐
│ Migration                      │ Filesystem │ Pinned │ Database │ Status    │
├────────────────────────────────┼────────────┼────────┼──────────┼───────────┤
│ 20260131075608-add_fee         │ ✓          │ ✓      │ ✓        │ ✓ Applied │
│ 20260131080417-update-add-func │ ✓          │ ✓      │ ✓        │ ✓ Applied │
└────────────────────────────────┴────────────┴────────┴──────────┴───────────┘

Testing

Let’s create a test for our migration. Spawn’s testing works by comparing the expected output from the actual output from running a series of SQL commands. In order to make our test easy to rerun without having to restart our database, we will use PostgreSQL’s WITH TEMPLATE option which creates a copy of our database from the provided template. It also means that we don’t have to run everything inside a test, which can be helpful when we want to test things that are related to transactions themselves. In this case, our add_fee func is simple enough that we could use a transaction, but we’ll create a copy anyway to demonstrate how it works.

Create a new test:

spawn test new add-func

creating test with name add-func
creating test at spawn/tests/add-func/test.sql
New test created: add-func

Edit the file in `tests/add-func/test.sql, and let’s create it like so:

-- In case a previous attempt for this test ran, let's make sure the old db is
-- deleted.
DROP DATABASE IF EXISTS add_fee_test;
CREATE DATABASE add_fee_test WITH TEMPLATE postgres;
-- connect to our new db for testing
\c add_fee_test

-- Test normal case
SELECT add_fee(12.2);

-- Update to a new fee
UPDATE fee SET amount = 1.20 WHERE name = 'base';

-- Test that the fee now reports new value correctly
SELECT add_fee(12.2);

-- connect back to postgres so we can delete this database
\c postgres

-- Clean up
DROP DATABASE add_fee_test;

Now run the test, and see the output:

spawn test run add-func

 add_fee
---------
  13.664
(1 row)

 add_fee
---------
  14.640
(1 row)

Those are the fees we expect! Since the test looks good, let’s tell spawn that this is the output we expect to see:

spawn test expect add-func

cat spawn/tests/add-func/expected

 add_fee
---------
  13.664
(1 row)

 add_fee
---------
  14.640
(1 row)

Now we can run the test and confirm success:

spawn test compare add-func

[PASS] add-func

echo $?

Perfect! Now, let’s mess with our test to verify failure. Edit tests/add-func/expected, and change one of the numbers. In this case, I have changed the first number to 10.00:

spawn test compare add-func

[FAIL] add-func
--- Diff ---
1   1    |  add_fee
2   2    | ---------
3        |-  10.00
    3    |+  13.664
4   4    | (1 row)
5   5    |
6   6    |  add_fee

-------------

Error: ! Differences found in one or more tests

echo $?

We have been given a clear diff to show us where our expected test output differed from the actual, a FAIL note, and an exit status of 1.

Dynamic templating

The power of Spawn templating comes not just from being able to organise SQL into components, but also to make use of dynamic data at generation time. This is what makes Spawn a Database Build System.

Spawn includes by default some useful variables. For example, we may have some data we only want to show in dev builds:

{%- if env == "dev" %}
INSERT INTO fee (name, amount) VALUES ('high-test', 1.98);
{% endif %}

Now if we run it, we see that it shows this new insert:

spawn migration build 20260131080417-update-add-func --pinned

BEGIN;

CREATE TABLE fee (
    name TEXT PRIMARY KEY,
    amount NUMERIC(3, 2)
);

INSERT INTO fee (name, amount) VALUES ('base', 1.12);
INSERT INTO fee (name, amount) VALUES ('high-test', 1.98);

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * (SELECT amount FROM fee WHERE name = 'base');
END;
$$ LANGUAGE plpgsql;

COMMIT;

But if we edit the target in spawn.toml to specify that it is a dev environment, like so:

...
[targets.postgres_psql]
engine = "postgres-psql"
spawn_database = "postgres"
spawn_schema = "_spawn"
environment = "prod"

Then it hides the extra insert!

spawn migration build 20260131080417-update-add-func --pinned

-- Output:
BEGIN;

CREATE TABLE fee (
    name TEXT PRIMARY KEY,
    amount NUMERIC(3, 2)
);

INSERT INTO fee (name, amount) VALUES ('base', 1.12);

CREATE OR REPLACE FUNCTION add_fee(a NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a * (SELECT amount FROM fee WHERE name = 'base');
END;
$$ LANGUAGE plpgsql;

COMMIT;

Templates open up a lot of power for managing your database migrations. Check out Recipes for some ideas, and Templating for details on how to use it.