dbmate for coding agents

When a coding agent changes code that uses a database, it needs to track changes to the database structure, or schema, alongside the code. dbmate is a command-line tool that manages database migrations: plain SQL files that describe changes to a database and, when provided, how to reverse them. The dbmate README describes it as a standalone tool that works with any language or framework, so the migration files do not depend on a particular programming language or ORM.

An agent can use dbmate to create a migration and check which migrations are pending. It can then apply them to a development database. Applying a migration changes the database. Let the agent prepare and inspect the migration, then run dbmate up only after you approve the change.

Install and connect

On macOS, install dbmate with Homebrew:

brew install dbmate

The installation section of the official repository covers other systems too. dbmate connects to the database using a URL supplied in the DATABASE_URL environment variable. The URL contains connection details, including credentials; provide it through your shell or a secret manager rather than placing a real password in an agent prompt. dbmate also reads a .env file in the current directory by default, so check what that file contains before an agent works in the project.

For a local PostgreSQL database, set a URL like this. Use the username and password from your development setup, along with its database name:

export DATABASE_URL='postgres://dbmate:[email protected]:5432/app_development?sslmode=disable'

This example assumes PostgreSQL is running locally and the named database already exists. The connection goes to that local server. The sslmode=disable part matters for a local server, because dbmate requires a TLS connection to Postgres by default. Use a development database for an agent’s migration work, not a production URL.

Create and check a migration

Suppose an agent needs to add a widgets table. From the repository root, ask dbmate to create a migration file:

dbmate --migrations-dir db/migrations new create_widgets

--migrations-dir points dbmate to the project’s migration directory. db/migrations is also the default, and global options like this one go before the command name. The new command creates a SQL file named with a timestamp. The file has a -- migrate:up section for the forward change and a -- migrate:down section for the rollback. It does not apply SQL to the database. The agent should write the table definition in the up section and a deliberate reversal in the down section.

After reviewing the file, check its status against the configured database:

dbmate --migrations-dir db/migrations status

The status command lists applied and pending migrations. With --exit-code, it also returns exit code 1 when migrations are pending, which a script can check. Treat that as a check, not as a substitute for reviewing the SQL: it tells you whether dbmate has recorded a migration as run, not whether the schema change fits the application’s needs.

Applying the pending migration is a separate database write:

dbmate --migrations-dir db/migrations up

up runs pending migrations against the database in DATABASE_URL. It also creates that database if it does not exist yet. If you only want to run the migrations, the README documents dbmate migrate for that. Run either command only after reviewing the migration and confirming that the URL points to the intended development database.

After up, migrate or rollback, dbmate writes the full schema to db/schema.sql by default. That dump needs pg_dump, mysqldump or sqlite3 on the PATH. If that tool is missing, dbmate writes no schema file and shows no error. Running dbmate dump shows the problem. The --no-dump-schema option turns the dump off.

Give the agent a bounded task

A useful prompt names the files the agent may change and stops before the database write:

Add a widgets table using dbmate. Create a migration in db/migrations, include a migrate:down section, and inspect the diff and dbmate status output. Do not run dbmate up or dbmate migrate. Use the local development database URL from the shell environment, and do not print or copy its credentials.

The agent creates and edits a local SQL file, then connects to the configured database when it runs status. It does not change the database schema until someone runs up or migrate. Keep that distinction clear when granting shell access.

Know what dbmate leaves to you

dbmate runs SQL migrations. It does not design the schema, and it doesn't know how to roll back a migration until someone writes the migrate:down section. The migration author must write both directions, and SQL syntax can depend on the database engine. Review the forward and rollback statements for the database the project uses before applying them.

dbmate keeps migration files independent of frameworks and lets you prepare migrations before applying them, which fits a coding agent that should stop before a database write. The README lists every command and option.