db-migrate for meta

db-migrate rewritten in meta. A program written in meta ships with its migrations compiled in, as one binary. During development, a launcher compiles each migration on its own, so down, edit, up takes a fraction of a second.

The migrations table has the same columns and the same names as node db-migrate's (/20261008120000-add-pets). A database migrated by one of them can be carried on by the other.

A migration

#include <db_migrate.h>

static int up(migrator_t *db) {

  db->createTable("pets", {
    id:       {type: "int", primaryKey: true, autoIncrement: true},
    owner_id: {type: "int", notNull: true,
               foreignKey: {table: "owners", mapping: "id",
                            rules: {onDelete: "CASCADE"}}},
    name:     {type: "string", length: 48, notNull: true},
    traits:   "jsonb",
  });

  db->addIndex("pets", "pets_name_idx", ["name"]);

  json_t pets = db->all(SQL`select id, name from pets`);
  defer pets.release();

  for (int i = 0; i < pets.count(); ++i)
    db->run(SQL`update pets set slug = ${slugify(pets[i].name)}
                where id = ${(long)pets[i].id}`);

  return 0;
}

static int down(migrator_t *db) {
  return db->dropTable("pets");
}

DBM_MIGRATION(up, down)

• The specs are documents, not structs. What a column can say belongs to the driver, so {...} and [...] passed where a json_t is expected become JSON. A literal made only of constants is built once. One that contains variables is built per call and freed afterwards.
• Errors stick. The first failing call records its reason, and every later call does nothing. A migration therefore reads as a plain list of steps, and the walker rolls back the whole migration together with its record. A migration fails on its own with a template: return db->fail(TEXT`no owner called ${name}`);
• Data migrations are code. db->all returns rows as JSON, and db->run takes the SQL literal, whose values never end up in the SQL text.
• Driver-specific features need their header. With #include <db_migrate/pg.h>, db->createSequence(...) becomes available. Without the header the compiler does not know the method. Run against a different database, the call fails with a clear reason.

A v2 migration (node's _meta: {version: 2}) has no down. It is undone from what it learned while running up, recorded in node's format, so node can undo it and vice versa:

static int migrate(schema_t *db) {
  db->createTable("owners", {id: {type: "int", primaryKey: true}, name: "string"});
  return db->addColumn("owners", "email", {type: "string", length: 120});
}

DBM_MIGRATION_V2(migrate)

Like in node, it runs outside a transaction. Every step is recorded in migrations_state before the next one starts, together with how far it got: started, its undoing recorded (learned), sent to the database (done).

• A failure is undone by exactly the steps that reached the database. That includes the failed step itself if its table or column was made before a foreign key failed.
• A run that dies halfway is resumed by the next one, as in node since 1.0.0-beta.38. By default the steps that ran are skipped and the rest runs. A migration that says DBM_MIGRATION_V2_RECOVERY(migrate, "rollback") (node's _meta.recovery) is undone instead and run again. A run that was rolling back when it died is rolled back further. A file changed since is not skipped blind. This also works across the two tools: node can resume a run this one left, and the other way round.

Migrations can also be one SQL file with an up and a down section, as db-migrate-plugin-sql writes them for node db-migrate 1.0 (create --sql): migrations/<stamp>-<name>.sql with a line -- up, its SQL, and optionally a line -- down and its SQL. The same file works with node and the plugin and with this, without a plugin.

Or plain SQL in two files, as node's create --sql-file creates them: migrations/sqls/<stamp>-<name>-up.sql and -down.sql. No code file is needed next to them. Text containing only comments (the placeholder) does nothing.

What node's _meta says besides the version goes into the _WITH forms:

DBM_MIGRATION_WITH(up, down, {transactions: false})      /* v1, no transaction */
DBM_MIGRATION_V2_WITH(migrate, {release: "2.3.0", recovery: "rollback"})
DBM_MIGRATION_DML_WITH(migrate, {background: true, blocking: true})

Objects made outside v2 migrations

db->adopt() declares a table, column, index or foreign key that a v1 migration or a person made, without sending anything; v2 migrations then treat it as their own, and undoing the migration only forgets it again. Dropping something the schema does not know is refused with node's words, unless it is asked for with {irreversible: true} (db->dropTableWith("junk", {irreversible: true})): then down refuses the migration, and a failure after it is not rolled back - the next run continues after the steps that ran.

db->adopt()->createTable("legacy", {id: {type: "int", primaryKey: true}});
db->addColumn("legacy", "age", {type: "int"});

Data migrations

A dml migration (node's _meta: {version: 2, type: 'dml'}) changes data instead of the schema. Every step is recorded with what reverts it, in node's records, before it runs:

static int migrate(dml_t *db) {
  db->updateWith("pets", {kind: "hound"}, {kind: "dog"}, {batch: 500});
  db->deleteWith("pets", ["kind = 'fish'"], {});
  db->deleteWith("pets", {id: [7, 8]}, {mode: "soft", column: "deleted_at",
                                         purge: true});
  db->runSql("UPDATE pets SET age = age + 1", {revert: "UPDATE pets SET age = age - 1"});
  return db->insert("kinds", [{name: "dog"}, {name: "cat"}]);
}

DBM_MIGRATION_DML(migrate)

• insert flags its rows in __dbmigrate__flag; reverting deletes them.
• update and delete copy the rows into a backup table __dbm_backup_<hash> first and restore them from there. They change the rows in batches by their key, each in a transaction; an interrupted run continues after the last batch.
• delete in soft mode sets a column of yours and marks the rows; db->purge("pets", "<migration>") deletes them for good later, or a later release does with purge: {releases: 1, drop: "auto"}.
• where is an object ({status: "old", id: [1, 2], gone: null}), or a SQL text with its parameters as ["kind = ?", ["fish"]]. A text alone is written ["kind = 'fish'"]: a bare string is not a document in meta.
• {irreversible: true} runs a step without being able to revert it.
• db->all(SQL`...`) reads, and is no step.

A soft delete's mark #1 is found as itself, not as part of #10; on MySQL, whose flag column keeps 255 characters, a soft delete that would make a flag longer is refused instead of cut off - as node does since 1.8.1.

Background migrations

A dml migration with {background: true} is registered by up as a job in __dbmigrate_jobs__ instead of being run, and the migrations after it go on. migrate work runs the jobs:

migrate work --parallel 2 --pause 50 --batch 500 --interval 5000 --job-timeout 60000 --watch

Each slot is a process of its own. A job keeps its progress, stops after its batch on SIGINT or SIGTERM and continues with the next run, is taken over when its worker stops renewing it for --job-timeout, and is rolled back when it fails - up queues it again. {blocking: true} keeps the jobs after it waiting. While a process migrates, it pauses the jobs, so migrations always go first, and down reverts the jobs before the migrations. A program runs them with dbmWork and stops them with dbmWorkStop.

A job keeps its scope and runs in it. migrate work runs the jobs kept with the top level, those of the scopes sharing its database too; a scope with a database of its own keeps its jobs there, and migrate work:<scope> runs them. A job recovered by rolling back keeps its record, a job whose migration was recorded already is forgotten instead of run twice, and work --dry-run says what would run - as node does since 1.8.1.

Releases

A migration starts a release with release in its _meta; the ones after it without a label belong to it. A v2 migration deprecates what the application stops using:

db->deprecateTableWith("legacy_orders", {releases: 2, drop: "auto"});
db->deprecateColumn("users", "fax");

With the next release it is renamed to __dbm_deprecated_<name>_<time>, so whatever still uses it fails while the data is still there. After releases of them (4) it is dropped - with drop: "auto" by itself, otherwise up says it is due until a migration calls db->dropDeprecated(). What a release does runs before its first migration and is reverted with it. Soft deleted rows are purged and the backups of dml migrations dropped the same way. The project's defaults go into .db-migraterc: {"deprecation": {"releases": 2, "drop": "auto"}}.

migrate status says what is pending, the release, the lock, the jobs and what is deprecated or due, in node's words.

Seeds

Seeds in seeds/*.c insert rows into tables made by v2 migrations; their rows are flagged, so seed removes them before it inserts them again, seed down [name] removes them, seed reset removes all of them:

static int seed(seed_t *db) {
  return db->insert("owners", [{id: 1, name: "Ann"}, {id: 2, name: "Bob"}]);
}

DBM_SEED(seed)

Commands

up [name]          run what has not run, up to and including name
down [name]        undo the last one, or everything after name
sync name          up or down, whichever reaches name
reset              undo everything
check              list what would run
status             what is pending, the release, the lock, the jobs, what is due
fix                rebuild node's state from the v2 migrations that ran
work               run the background jobs
seed [name]        insert the seeds again; seed down [name], seed reset
create name        a new migration (--sql, --sql-file, --v2-file, --template NAME)
db:create name     create a database, if it is not there yet
db:drop name       drop a database, if it is there
command:scope      the same in migrations/<scope>/ - up:billing, create:billing

Options as in node: -e/--env, --config, -m/--migrations-dir, -c/--count, -t/--table, -s/--state-table, --lock-timeout, --lock-interval, --backup-state, --dry-run, --check, -v/--verbose, --non-transactional, --sql-file, --v2-file, --template, --ignore-on-init, --ignore-completed-migrations, --log-level, --seeds-dir, -h, -i, and for work --parallel, --pause, --batch, --interval, --job-timeout, --watch. A destination can be abbreviated: up 20261008 runs everything up to that day.

The options can also be kept in rc files, as node reads them since 1.1: /etc/db-migraterc, ~/.db-migraterc, the first .db-migraterc from the working directory upwards, the file $config names, and db-migrate_<key> variables - JSON with comments, or INI. The command line wins over them.

In database.json, {"ENV": "SHOP_PASSWORD", "default": "dev"} reads a variable and falls back to the default when it is not set, at any depth and for defaultEnv too.

• --ignore-on-init is for taking over a database that already has what the migrations make. create --sql-file --ignore-on-init writes -- db-migrate: ignore-on-init as the first line of the up. Such an up is recorded without being run when up --ignore-on-init is given. A migration in code checks db->ignoreOnInit itself, which the code template created with the flag already does.
• --log-level info|warn|error|sql works as in node: it selects which [INFO], [WARN], [ERROR] lines and statements are printed (-v, and a dry run).
• A dry run reads which migrations have run and prints what would actually happen.

While it migrates, a process holds node's migration lock: the __dbmigrate_state__ row in migrations_state, renewed every third of --lock-timeout. A second process, node or this one, waits. It only takes the lock over after the row has stayed unchanged for the whole timeout (60 s by default), measured on its own monotonic clock. The state lives on a connection of its own, so it survives a migration's rollback.

A failed migration is reported the way node reports it since 1.0.0-beta.38: which migration failed, at which step for v2, the message, the statement with a marker under the position the database names, and the driver's diagnostic fields (code, detail, hint, ...):

[ERROR] Migration "20261009100000-broken" failed: relation "nope_table" does not exist
    SQL: select *
           from nope_table
                ^
    code: 42P01

Scopes are subfolders of migrations/. Their migrations are recorded as billing/<name> and those at the top level as /<name>, exactly as in node. Each command only sees its own scope; up:all and the other commands with all run the top level and every scope, nested ones too. A scope is a folder with migrations in it - unlike node, which counts every folder, an empty one is not entered. A scope's config.json is laid over the environment: one that says only database or schema switches to it on the same connection, one that says more connects on its own and keeps its own lock and state there.

In a program

int main(int argc, char **argv) {
  if (argc > 1 && strcmp(argv[1], "migrate") == 0)
    return dbmCli(argc - 1, argv + 1);   /* every command above */
  ...
}

./build-app.sh <app> <output> [driver or plugin...] lowers the app, its plugins/ and its migrations, including scopes, embeds the SQL migrations as strings, and links libdbmigrate.a with --whole-archive, together with only the drivers you name (default: pg). A program on SQLite does not need libpq installed. Drivers and migrations register themselves through constructors, and nothing else references them, so a linker that only pulls in referenced objects would drop them.

With --static, the client libraries go into the program too: libpq, OpenSSL, SQLite and libyaml, as tools/deps.sh builds them. The program then needs glibc on the host and nothing else. test/static.sh runs one on bare Debian and Fedora.

• glibc stays dynamic. Name resolution goes through its NSS modules, which belong to the host, so DNS behaves like it does for every other program there.
• libpq is built from source, without GSSAPI and LDAP. The distributions' libpq.a needs Kerberos, and no distribution ships that static.
• mysql stays dynamic. libmysqlclient is GPL-2.0 with the FOSS exception, which is not something to bake into a program that is not free software.

./build-app.sh --static . ./app pg yaml

The libraries come with a .pc file each, and pkg-config works out what they pull in, so the build host needs pkg-config as well as a C compiler.

Configuration works as in node db-migrate:

• database.json with environments (-e, NODE_ENV, defaultEnv, dev)
• database.yml (or .yaml) when there is no database.json, or any --config file whose ending a plugin reads
• {"ENV": "NAME"} for values taken from the environment
• DATABASE_URL when there is no file
• tunnel: {...} in a connection, to reach it through ssh (see Plugins)

From inside a program that migrates itself

A program that brings meta's runtime itself - an nginx module built with meta -module, which links lib/libmeta_runtime.a - links libdbmigrate-core.a, the core without that runtime, and its drivers, and migrates when it starts:

static void toLog(int level, const char *line) { /* the program's log */ }

dbmSetLogger(toLog);                 /* instead of stdout and stderr */

char why[512];
if (dbmMigrateUp(config, NULL, why, sizeof why) != 0)   /* NULL: node's defaults */
  fail(why);

dbmMigrateUp does what up does: a connection, a second one for node's state, the lock with its heartbeat, every migration the database has not run (v2 included), and everything closed again. It blocks. config is one connection as an environment of database.json says it; a dbm_options_t names other tables or lock timings. test/embed.sh builds such a shared object, loads it and migrates through it.

Everything in libdbmigrate is built with -fPIC, so all of it can go into a shared object.

During development

build/meta-migrate up
build/meta-migrate down
# edit the migration
build/meta-migrate up        # compiles only the edited file, ~0.15 s

• The launcher reads the migration files by name only and compiles exactly those the database says need to run. check compiles nothing.
• Each one is kept as a .so under .meta-migrate/<hash>/.
• It loads the driver the configuration names (libdbmigrate-pg.so) only at that point, so libpq is only needed where PostgreSQL is used.
• Apart from that it runs the same dbmCli as the shipped program.

Plugins

What node's plugins were for is either built in or a plugin compiled into the program. A plugin registers from its constructor, the same way a driver does, so nothing else has to name it.

node	here
plugin-yaml (init:config:overwrite:require)	shipped plugin yaml (libyaml), registered for .yml/.yaml
plugin-tunnel-ssh (connection:tunnel:ssh)	built in: tunnel in a connection
connection:tunnel:<type>	dbmRegisterTunnel("<type>", open, close)
init:config:overwrite:require	dbmRegisterConfigLoader(".ext", load)
create:template	dbmRegisterTemplate("name", write), used by create x --template name

See include/db_migrate_plugin.h.

• YAML reads like js-yaml's safeLoad: unquoted 5432 is a number, quoted text stays text, and anchors, aliases and << merges work. YAML is a plugin rather than part of the core, so only a program that reads YAML needs libyaml: ./build-app.sh . ./app pg yaml. The launcher loads libdbmigrate-yaml.so the first time it meets a .yml.

• The ssh tunnel is the system's ssh -N -L, so ~/.ssh/config, the agent and known_hosts apply. node's tunnel-ssh checked no host key at all. The keys are the ones plugin-tunnel-ssh took:

  prod:
    driver: pg
    host: db.internal          # as the bastion sees it
    database: shop
    tunnel:
      host: bastion.example
      username: deploy
      privateKeyPath: /home/deploy/.ssh/id_ed25519
      # port (22), privateKey, password, passphrase, localHost, localPort,
      # keepaliveInterval, readyTimeout (20000), options: {Key: value} -> -o

  Passwords and passphrases reach ssh through SSH_ASKPASS and the environment, never through the command line. Without one, ssh runs in batch mode and fails instead of waiting at a prompt. DBM_SSH names a different ssh binary.

• A project's own plugins go in plugins/*.c. The launcher compiles and opens them before it reads anything else, and build-app.sh compiles them in.

Drivers

A driver is a driver_t that dbmDriverNew has filled with the generic versions, the counterpart of db-migrate-base. The driver overrides only what its database does differently. To call the generic version (_super), it calls the generic function by name. The generic functions call each other only through self, so overriding columnDef changes what createTable writes. See include/db_migrate_driver.h.

Driver	database.json	Special features (header)	Notes
pg	host, port, user, password, database, sslmode	sequences, enums (db_migrate/pg.h)	DDL is transactional
cockroachdb	as pg	additionally changePrimaryKey, uuid keys, computed columns, row TTL (db_migrate/cockroachdb.h)	extends pg. A column added to an existing table cannot be used until its transaction commits, so adding a column and filling it are two migrations
mysql	host, port, user, password, database, socketPath	table options (engine, charset), unsigned, after, comment	MySQL commits before every DDL statement. A failed migration keeps the DDL it already ran, only its record is rolled back
sqlite3	filename, busyTimeout (ms, 10000)	–	foreign keys are written into the column (REFERENCES). Changing a column in place is not possible and is refused with a reason

cockroachdb extends pg the way db-migrate-cockroachdb extends db-migrate-pg: dbmPgConnect returns a fully configured pg driver, and Cockroach replaces individual slots in it (src/drivers/pg_driver.h).

Building and testing

./build.sh                    # libdbmigrate.a, a driver library each, meta-migrate
test/run.sh                   # against all four databases
test/plugins.sh               # yaml, plugins/, the ssh tunnel (own sshd)
test/options.sh               # --ignore-on-init, --log-level, dry runs
test/static.sh                # build-app --static: glibc only, on bare Debian/Fedora
test/embed.sh                 # dbmMigrateUp from a shared object with its own runtime
test/recovery.sh              # v2 runs that die or fail halfway, resumed and rolled back
test/compat.sh                # moving a project from node db-migrate and back

test/run.sh [driver...] runs the same cycle against every database: up, down, reset, a migration that fails partway, a dry run, and the launcher. test/migrations/common is shared, and each driver has its own folder. The databases (test/database.json) are started by the commands at the top of test/run.sh. SQLite only needs a file.

Building

build.sh asks meta what meta knows: where its runtime is (meta -print-config), and which libraries each driver needs (meta -print-flags over all of its sources, as pkg-config names). Those come from meta's own headers, or from the source itself where it includes a library's header directly: the yaml plugin says #pragma meta needs "yaml-0.1" beside #include <yaml.h>. build.sh itself knows none of it. This needs meta ed6f263 or newer (meta --version).

CI and releases

meta is not open source. The metalanguage repository publishes it, binary and headers, as the image wxone/meta, with everything under /opt/meta. This repository only uses it. meta bakes that path into itself, so it has to stay at /opt/meta:

docker create --name meta wxone/meta:f4e60f8
sudo docker cp meta:/opt/meta /opt/meta && docker rm meta
META_ROOT=/opt/meta ./build.sh

.github/workflows/ci.yml does exactly this (.github/actions/meta; the image is tagged with the meta commit this is tested with, META_IMAGE picks another) and runs every suite on Ubuntu 24.04, each in its own job: run.sh once per database (started with docker run, as at the top of test/run.sh), plus options.sh, plugins.sh, static.sh, embed.sh, recovery.sh, adopt.sh, scopes.sh, seeds.sh, dml.sh, releases.sh, work.sh and compat.sh. The last one moves a project from node db-migrate to this and back, against node-db-migrate and its pg driver as published (NODE_DB_MIGRATE_REF, NODE_PG_REF, default master).

A tag v* also builds a release with tools/release.sh. The release is a tarball that unpacks at / (tar -xzf <file> -C /) and contains:

• meta in /opt/meta, as the image has it: compiler, runtime headers and libmeta_runtime.a (1.5 MB). It has to be at that path, and overwrites an /opt/meta that is already there. The host only needs a C compiler on top. The launcher's drivers carry their client libraries inside them, except mysql, which needs libmysqlclient installed.
• in /opt/meta-db-migrate:
  • the launcher and bin/meta-migrate-build-app
  • the libraries, drivers and plugins, and the headers
  • the static libraries for --static in lib/deps
  • the license, THIRD_PARTY_NOTICES and a CycloneDX SBOM in share/doc/meta-db-migrate

The SBOM is also attached to the GitHub release as a separate file. It lists what the release brings and that no scanner can find in a binary: meta and its runtime, yyjson, and the static libraries, each with version, license and origin.

The name of the tarball says which glibc it needs: built on Ubuntu 24.04, it is glibc2.39. A release against an older glibc needs a meta that runs there too: today's needs glibc 2.34, and is bound to the compiler it was built with. Once meta is built on glibc 2.28 and asks the compiler at run time, a second release follows.

License

MIT, see LICENSE. A release also carries third-party code, which THIRD_PARTY_NOTICES lists. meta and its runtime are proprietary for now and are meant to become open source; the SBOM says so in an annotation next to the license that applies today. yyjson, libpq, OpenSSL, SQLite and libyaml are permissively licensed.

Status

• PostgreSQL, CockroachDB, MySQL, SQLite: createTable, dropTable, renameTable, add/remove/rename/changeColumn, indexes, foreign keys, insert, run/all/runSql, plus each database's own features (see table)
• up/down/sync with destinations, reset, check, create (code, SQL, v2, templates), scopes, db:create/db:drop, node's options, development launcher
• v2 migrations, node's state and lock, fix, in node's format; adopt and irreversible drops
• what node 1.1 to 1.8 added: scopes with their own database, rc files, defaults for variables, v1 migrations without a transaction, seeds, data migrations, background migrations and work, releases and deprecations, status
• plugins: YAML, ssh tunnel, config loaders, tunnels and templates of your own
• Missing: MongoDB