Branches and environments

A branch is a whole database that starts as another one is right now, with every table, view, function and file, without copying any data. Make one per developer, per pull request or for a test run, change it as you like, compare it with where it came from, and drop it when you’re done.

Make a branch

Say production is this node’s lake. A lake is named after its folder; here it is called lake:

CREATE TABLE orders (id BIGINT, amount DOUBLE);
INSERT INTO orders VALUES (1, 10.0), (2, 20.0), (3, 30.0);
CREATE MATERIALIZED VIEW big AS SELECT id, amount FROM orders WHERE amount >= 20;

CREATE DATABASE … CLONE makes dev from it:

CREATE DATABASE dev CLONE lake;
SELECT * FROM dev.orders ORDER BY id;

id | amount
---+-------
 1 |   10.0
 2 |   20.0
 3 |   30.0

dev lists its base’s files where they are, so making it takes about the same time for a table of a thousand rows or a billion. Only the rows the base hasn’t written to files yet, and the workspace’s files, are copied. Rows keep their _row_id and _version. The database you clone can be this node’s own lake or any database attached to it.

pondra.databases shows which database is a branch of which:

SELECT name, base, branches FROM pondra.databases ORDER BY name;

name | base | branches
-----+------+---------
dev  | lake |        0
lake |      |        1

To take only some schemas, or only the definitions with no rows (for tests that load their own):

CREATE DATABASE fixtures CLONE lake WITH NO DATA;
SELECT count(*) AS n FROM fixtures.orders;

n
-
0

WITH (schemas = (sales, finance)) takes just those schemas.

Change it apart

Writes to a branch stay in the branch. Its materialized views follow its own rows:

INSERT INTO dev.orders VALUES (4, 40.0);
UPDATE dev.orders SET amount = 25.0 WHERE id = 2;
SELECT (SELECT count(*) FROM dev.big) AS in_dev, (SELECT count(*) FROM big) AS here;

in_dev | here
-------+-----
     3 |    2

The base carries on too: its merges, compactions and retention never take away a file dev reads. It keeps them for as long as dev exists.

Compare two databases

pondra.diff('a', 'b') gives the rows of table b that differ from table a, in the change feed’s words:

SELECT _change_type, id, amount FROM pondra.diff('orders', 'dev.orders') ORDER BY id, _change_type;

_change_type     | id | amount
-----------------+----+-------
update_postimage |  2 |   25.0
update_preimage  |  2 |   20.0
insert           |  4 |   40.0

Rows both hold alike are matched by their _row_id and _version, so comparing a big table reads only its rows, never a join on every column.

Bring tables up to date

A branch stays as its base was when it was made. ALTER DATABASE … REFRESH brings tables up to the base’s present, with the materialized views that follow them:

INSERT INTO orders VALUES (5, 50.0);
ALTER DATABASE dev REFRESH orders;
SELECT id, amount FROM dev.orders ORDER BY id;

id | amount
---+-------
 1 |   10.0
 2 |   20.0
 3 |   30.0
 5 |   50.0

The branch’s own changes to those tables go (here row 4 and the update); every other table stays as the branch has it.

ALTER DATABASE dev REFRESH with no names brings every table the branch took from its base.
ALTER DATABASE dev REFRESH orders, customers brings those, in one commit.
Naming a materialized view brings the tables it reads, and the view comes with them.

REFRESH says what to do instead of guessing when the branch has a view of those tables that its base doesn’t have, defines one otherwise, or keeps state of its own (a window emitted once, sessions, a stream join): drop it, refresh, then make it again.

Drop a branch

DROP DATABASE fixtures;
DROP DATABASE dev;

Only what the branch wrote goes. Its base lets go of the files it kept for it. A database that still has branches can’t be dropped; drop the branches first.

What a branch leaves behind

Tasks start suspended, so a branch never runs production’s schedules. Resume one with ALTER TASK … RESUME.
Secrets stay with the base, and a view fed by a Kafka topic arrives as a plain table: nothing in a branch reaches outside on its own.
A branch lives where its base does: both in the same bucket, or both on the same machine.

Deploy a project

A project is a folder, usually in Git, that says what every object is. pondra deploy makes a database match it, and pondra plan shows what that would change first:

pondra.toml: the project’s name and its environments;
objects/: CREATE statements (tables, views, materialized views, functions, macros, procedures, tasks, secrets, roles) and GRANTs, in any layout, several to a file;
migrations/: one-off steps, such as a rename or a backfill, each run once in each database, in name order, after the objects and columns a deploy adds and before what it replaces;
tests/: queries; any row one returns is a failure.

pondra init shop
cd shop
cat > objects/sales.sql <<'SQL'
CREATE SCHEMA sales;
CREATE TABLE sales.orders (id BIGINT, amount DOUBLE);
CREATE VIEW sales.big AS SELECT * FROM sales.orders WHERE amount >= 20;
SQL
echo "INSERT INTO sales.orders VALUES (1, 10.0), (2, 20.0), (3, 30.0);" > migrations/001-first-orders.sql
echo "SELECT * FROM sales.orders WHERE amount < 0" > tests/no_negative.sql
pondra deploy --url http://127.0.0.1:8080 --test

a project in shop: pondra.toml, objects/, migrations/, tests/
dev: deploy 1
  + schema     sales                 made
  + table      sales.orders          made
  + view       sales.big             made
  ▶ migration  001-first-orders.sql  ran
  ✓ tests      1 passed

Deploy again and nothing changes. Change a file and the plan says how the database will follow it:

cd shop
cat > objects/sales.sql <<'SQL'
CREATE SCHEMA sales;
CREATE TABLE sales.orders (id BIGINT, amount DOUBLE, channel VARCHAR);
CREATE VIEW sales.big AS SELECT * FROM sales.orders WHERE amount > 20;
SQL
pondra plan --url http://127.0.0.1:8080

dev: after deploy 1
  ~ table      sales.orders  ADD COLUMN channel VARCHAR
  ~ view       sales.big     replaced

A table changes in place only by columns added at the end, types widened and its options. A column renamed, dropped or moved is refused with the migration to write (ALTER TABLE … RENAME COLUMN …): a declaration can’t tell a rename from a drop and an add, and a deploy never loses data unasked. The plan names the views that read the table: the migration drops them first, and the deploy makes them again after it.
Anything else that changed is replaced. A materialized view is made again from the rows already there, with the materialized views that follow it.
Drift shows. An object a deploy made and someone changed by hand is listed, and the deploy puts it back.
One deploy at a time, from the plan it showed: if someone deployed in between, it is refused, and you plan again. Each statement runs under its own part of the deploy’s job, so running a deploy that stopped half way again finishes it.
Each object has one owner. A deploy changes only what its own project made; another project’s objects, and objects people made by hand, are left alone. An object the project no longer declares is kept unless you deploy with --prune.

Every deploy is a row of pondra.deploys, with the commit it was of, and its files are kept under files/.deploys/:

SELECT id, project, status FROM pondra.deploys ORDER BY id;

id | project | status
---+---------+-------
 1 | shop    | ok

A project kept in the workspace deploys from SQL too: CALL plan('shop', env => 'prod') lists its plan as rows (mark, change, kind, name, what; change is a word to filter by: create, alter, replace, rebuild, migrate, drop, keep or refuse), and CALL deploy('shop', env => 'prod', test => true) does it.

pondra export DIR writes a database’s objects as a project’s files, so a lake built by hand in the console becomes a project in one command.

Environments

On a team, the databases are served by pondra serve --lakes (production can run a cluster of its own), and pondra.toml says where:

[project]
name = "shop"
server = "https://pondra.acme.com"   # every database here is /db/<name>

[env.prod]
values = { min_order = 10 }          # what $min_order is in prod's statements
attach.events = { type = "kafka", url = "kafka://kafka.acme.internal:9092" }

[env.dev]                            # what a developer's branch takes
values = { min_order = 0 }

[secrets]
crm_token = "env:CRM_TOKEN"          # $crm_token, from the deploying machine's environment

A developer’s day, and a pull request’s:

pondra login https://pondra.acme.com     # once: the token is kept in ~/.pondra
git switch -c orders-by-channel
pondra branch                            # a database orders_by_channel: prod as it is now
pondra deploy --test                     # the git branch's database, with [env.dev]'s values
pondra diff                              # its objects and rows against prod's, as Markdown
pondra branch --drop

- run: pondra branch pr-${{ github.event.number }} --from prod
- run: pondra deploy --env pr-${{ github.event.number }} --test
- run: pondra diff --env pr-${{ github.event.number }} >> "$GITHUB_STEP_SUMMARY"
- run: pondra branch pr-${{ github.event.number }} --drop

An environment with clone = "prod" (a test database, say) is made again as a branch of prod before each deploy, so it is always production plus the new code. Five developers never share a branch: each has a Git branch and a database of its name, and they merge in Git.

Not here yet: protected databases (changed only by a deploy) and a DEPLOY privilege, a materialized view rebuilt beside the old one and swapped in, and Python files in objects/.