Loadbare/db

Loadbare/db builds Postgres databases that materialize derived columns directly into tables. Loadbare/db leverages the foreign key as the channel for denormalization: values can flow from parent to child, and aggregate up from child to parent. Loadbare/db also materializes derived columns within a table, which can depend upon other derived columns.

Loadbare/db also expands the foreign key functionality to include:

• ranged (floor/ceiling) matches, not just for time series
• automatic creation of parent rows
• automatic creation of ranged sets of child rows

At run-time, a write may hit multiple tables, to the limit of the schema's aggregation chain for that table. In exchange, Loadbare/db offers declarative and non-subvertible business logic, guaranteed termination, no application race conditions, correctness guarantees, and simpler reads and writes.

Read more at: The Loadbare/db Tradeoff.

Status

Loadbare/db is on branch 1.x. Release 1.0.0 was made on September 6, 2026. Loadbare/db is ready for 1.x because the syntax is fixed and stable, changes will be additive or bug fixes.

The Loadbare/db DSL

A Loadbare/db schema is a Postgres schema specified in the Loadbare/db language. Here is a simple example, docs/examples/00-example.lb.

table us_states
  column state_code char(2) primary key
  column state_name varchar(20)
  column children_count integer count child_table

table child_table
  column child_id serial primary key

  fk us_states references us_states pushes child_table
  column state_code is us_states.state_code

  column state_name sync us_states.state_name

Once the database is built, reads are against plain tables, because the derived values are already in the rows:

-- state_name came from us_states when the row was written
select child_id, state_code, state_name from child_table;

-- children_count was maintained by the insert and delete of every child row
select state_code, state_name, children_count from us_states;

The complete language reference is at language-reference.md.

Try It Out

Check a Schema and Generate DDL

Install:

npm install -g @loadbare/db

Fetch the schema shown above:

curl -o example.lb \
  https://gitlab.com/kendowns/loadbare/-/raw/main/packages/db/docs/examples/00-example.lb

If you have the repository cloned, it is docs/examples/00-example.lb — copy that instead, and the rest of this section reads the same.

Check that the schema is valid. This needs no database and no credentials. A clean schema prints nothing:

loadbare-db check example.lb

Now generate the build script. The --from-scratch flag does not need a database connection. The -d <database-name> flag is required because Loadbare/db will generate two specific roles.

loadbare-db -d example -s example.lb --from-scratch -o example.sql

The build script goes to stdout, use -o to send it to a file.

loadbare-db -d example -s example.lb --from-scratch > example.sql

Open example.sql. Its header records the Loadbare/db version, the schema it came from, and when it was generated. Below that is the entire DDL that produces the two tables, the roles, the triggers and trigger functions.

By default, Loadbare/db writes no other files. If desired, inspection artifacts can be generated by providing --dump-dir <dir>. See the CLI Reference.

Create a Live Database

Set up the build user and .env as described in Production Postgres Setup, then drop --from-scratch:

loadbare-db -d example -s example.lb 

Loadbare/db creates the database if it does not exist and applies the migration in a single transaction. It also emits the same build script it applied, and that goes to stdout — expect the DDL to scroll past. Add -o example.sql to keep it in a file instead.

The build creates two NOLOGIN roles. The first is example_loadbare_triggers, which owns the trigger functions and is the SECURITY DEFINER identity they run as. The second is example_loadbare_app_user, an unprivileged role which can read and write to all tables but cannot subvert derived values. For regular application access to the database, use LOGIN roles that are members of the example_loadbare_app_user role.

CREATE ROLE example_login_user WITH LOGIN PASSWORD 'change-me';
GRANT example_loadbare_app_user TO example_login_user;

Write some rows:

insert into us_states (state_code, state_name) values ('NY', 'New York');
insert into child_table (state_code) values ('NY');
insert into child_table (state_code) values ('NY');

and read them back:

-- children_count is 2, maintained by the two inserts above
select state_code, children_count from us_states;

-- state_name is 'New York' on both rows, pushed down as each was written
select child_id, state_code, state_name from child_table;

Import an Existing Database

If you already have a Postgres database, import writes a schema describing it:

loadbare-db import -d example -o example.lb

The connection is read-only. Unlike a build, import will not create the database when it is absent and does not need CREATEROLE — an account that can connect and read the catalogs is enough.

This feature is intended to create a starting schema in the Loadbare/db DSL for enhancement with Loadbare/db. Present limitations:

• only reads the public schema (necessary for round-trip tests, as Loadbare/db currently only writes to the public schema).
• does not capture Loadbare/db automations. If this is run against a Loadbare/db database, all Loadbare/db columns appear as inert column declarations.

Anything the language cannot express is reported twice, as a # comment in the file where the fact would have gone and again on stderr, so it survives both the run and the file. An import that left something behind exits with 2 rather than 0, which is how a pipeline can tell. The CLI Reference lists what gets reported.

How Migrations Behave

Loadbare/db builds are idempotent, cumulative, and additive only. A build goes from any current state to the final state described by the schema. An empty database is created and built from scratch.

A build is safe to repeat. The schema file is the entire description of where the database should end up. If there is nothing to add, nothing gets done.

Loadbare/db never drops data on its own. Tables, columns and views that are present in the database but not referred to in the schema are considered inert. Loadbare/db strips any triggers from unreferenced tables, so accidental writes cannot update live tables. A drop script can be generated, reviewed and executed that will drop all unreferenced tables, columns and views in the database.

The CLI Reference covers the drop script and the other inspection artifacts.

Backing Up and Restoring

A Loadbare/db database is backed up with pg_dump, as any Postgres database is:

pg_dump -h localhost -U loadbare_builder -d example -Fc -f example.dump

Restoring takes one step more. The two NOLOGIN roles a build creates are cluster-global, so they are not in the dump, while the GRANT and ALTER ... OWNER TO statements that name them are. restore-roles.sql ships with Loadbare/db, recreates the roles from the database name, and runs before the archive:

createdb -h localhost -U loadbare_builder example
psql -h localhost -U loadbare_builder -d example \
  -f "$(npm root -g)/@loadbare/db/scripts/restore-roles.sql"
pg_restore -h localhost -U loadbare_builder -d example --exit-on-error example.dump

If you have the repository cloned, the script is scripts/restore-roles.sql and the rest of these commands read the same.

Backup and Restore covers restoring under a different database name, what a restore leaves behind when the roles are missing, and which parts of the cluster this procedure does not carry.

Editor Support

Experimental language support for .lb files ships as a language server, loadbare-db-lsp, with instructions for neovim, vim, and VSCodium / VS Code in the repository. It is the least tested part of Loadbare/db. Setup is in editors/.

Using an LLM to Author Schemas

The simplest way to get LLM assistance is to apply the skill:

npx loadbare-db skills install

Note from Ken Downs, Sept 6, 2026. I have experienced higher-than-average results using Loadbare/db to create schemas, using Anthropic models from Sept 2025 to this writing, Sept 2026. The LLM can understand how to take advantage of Loadbare/db features, and code a spec accordingly.

I believe there is one very concrete reason, that the agent can run loadbare-db check schema.lb w/o a database connection and get a report of errors with line numbers and positions.

Beyond that, I can only speculate as to why it works so well, but I tend to think it is because Loadbare/db is just a set of declarations over ordinary SQL types and constraints. It may be as well the exclusive use of the foreign key for denormalization means there is only one trick to learn. I also like to think that the syntax is uniform enough to work well with the pattern-matching nature of an LLM.

Other Details

Full Language Reference

Production Postgres Setup

Application DB Access

Backup and Restore

CLI Reference

Editor Support

Potential Features

Design Decisions

Contributing

Status

The feature set is stable in terms of syntax. The language expresses everything Loadbare/db does today. The current release (0.9x.x) is considered pre-1.0, pending more real-world use.

Loadbare/db is developed and tested on Linux, against Postgres 18.4. Other platforms and older Postgres majors are untested rather than unsupported — Production Postgres Setup says what that distinction is worth.

One schema per database: public

Loadbare/db works in the public schema only. The generated DDL is unqualified, the grants are on public, and every trigger function pins SET search_path = public. A table in any other schema is not seen by a build, a migration, or loadbare-db import.

Nothing is harmed by another schema being present; it is passed over. What is not available is putting a loadbare-managed database anywhere but public, or importing from anywhere but public. Naming the schema is a plausible addition and has not been made, because the writing half would have to follow the reading half through roles, grants, and every generated statement.

Versioning

Loadbare/db uses Semver as follows:

• Major release: breaking change to syntax or to behavior of syntax
• Minor release: new large features
• Point release: bug fixes and small features

License

Loadbare/db is Copyright (C) 2026 by Kenneth Downs, and is licensed under the Apache License, Version 2.0.

The schemas you write and the SQL Loadbare/db generates from them are your own work. Loadbare/db claims no rights over its output, and nothing in this license reaches the databases you build with it.