The worst database tooling I have used was not bad because the commands were wrong, it was bad because nobody decided which command belonged to which stage of a pipeline, so the same binary ended up formatting a laptop database in the morning, running inside CI on every pull request at noon, and getting invoked from a deploy script at midnight, and the failure modes of those three contexts are completely different even though the binary is identical. A command that mutates a schema is fine in a deploy script and catastrophic in CI, a command that only reads is a waste of a deploy step and exactly what CI wants, and a command that talks to a human belongs in a terminal with a pager, not in a YAML file. When we laid out the mongreldb-kit CLI the subcommands landed in three piles almost immediately, read-only, one-way door, and operator, and this post is the argument for keeping them there, because the CLI surface is only half the product and the other half is the discipline about when each verb runs.

The read-only verbs belong in CI

CI has one job, which is to say no before the merge, and everything it runs should be free to run a thousand times a day without touching anything that matters, which rules out anything that writes. Four subcommands fit that contract cleanly. schema validate checks a schema JSON file without opening a database at all, so it runs before the build even has a database to open:

mongreldb-kit schema validate schema/schema.json

migrate plan opens the database, prints which migrations would be applied, and writes nothing, and migrate dry-run is the same thing under a second name for the people who grep their own history for “dry-run” instead of “plan”, because a plan that nobody looks at is not a plan. diff compares the schema in the repo against a stored catalog and prints the delta as + table, - column, and friends, with one honest caveat you should know before wiring it up: diff always exits zero, because a diff is a report and not a verdict, so the CI gate is a text match and not an exit code. The natural objection is that CI has no live database to diff against, which is fair, so the job builds one on the spot, initializing a scratch database, applying the full migration set into it, and diffing the schema file against the result, which catches the schema file and the migrations disagreeing with each other before a deploy finds out the hard way:

mongreldb-kit init ./ci-scratch.kitdb
mongreldb-kit migrate apply ./ci-scratch.kitdb migrations.json
mongreldb-kit diff schema/schema.json ./ci-scratch.kitdb | grep -qx 'no drift'

The same command doubles as the deploy gate against the real database, where no drift is the last line you want to read before traffic flips, and the only difference is which path you point it at.

The fourth CI verb is generate types, which emits TypeScript, Rust, or Python row definitions from the schema, and the pattern that makes it worth anything is regenerate-and-diff, where CI reruns the generator and then fails if the checked-in file differs:

mongreldb-kit generate types schema/schema.json --lang ts > src/db-types.ts
git diff --exit-code src/db-types.ts

That second line is doing the actual work, because the generator is deterministic and the only way the file drifts is if someone edited the schema without regenerating, which is exactly the class of mistake CI exists to catch, and it is the same trick people used with protoc output in 2010, just pointed at a schema catalog instead of a .proto file.

The one-way doors belong in deploy

Deploy is where mutation is supposed to happen, but only the deliberate kind, and the pile here is smaller than people expect. migrate apply is the main one, and it is deliberately boring: it applies pending migrations from a JSON file, in order, and migrate status afterward prints the applied versions so the deploy log has a receipt. Notice what is not in that subcommand list, because there is no migrate rollback, and that is not an oversight; rollback in a schema migration is usually a lie, since the down-migration that drops a column cannot un-drop the data that was in it, so the honest answer is that you fix forward with another migration and the CLI declines to pretend otherwise. If your pipeline needs a safety net, the net is migrate plan in CI plus a database backup before deploy, not a fictional undo button.

The other deploy-stage verbs are the credential ones, user create, role allow, and the one that deserves real ceremony, auth enable, which turns on require_auth for an existing credentialless database and creates the first admin user in the same step. This is a one-way door in spirit even where it is not in code, because enabling auth on a database that your running application connects to without credentials will take the application down at deploy speed, so the sequencing matters: create the application user, grant its roles, roll the app with credentials configured, and only then enable enforcement. The deploy script for that looks like the kind of runbook step you read twice before executing, which is the correct amount of ceremony for a permission system:

mongreldb-kit user create ./store.kitdb app_user "$APP_DB_PASSWORD"
mongreldb-kit role create ./store.kitdb app
mongreldb-kit role allow ./store.kitdb app select:orders
mongreldb-kit role allow ./store.kitdb app insert:orders
mongreldb-kit role grant ./store.kitdb app_user app
# after the app is rolling with credentials configured:
MONGREL_PASSWORD="$ADMIN_DB_PASSWORD" mongreldb-kit auth enable ./store.kitdb --admin-user admin

Two details here are worth stealing for your own scripts. Your own connection password, the one that authenticates you to a database that already has require_auth on, comes from MONGREL_PASSWORD or --password-stdin and never from a positional argument, because positional arguments land in shell history while environment variables and stdin do not. The new user’s password on user create is a positional argument, though, so feed it from an expanded variable like "$APP_DB_PASSWORD" instead of pasting a literal, which keeps the secret in your deploy environment rather than in the runbook, and the same applies to user passwd later when you rotate it; the honest limitation is that a positional argument is also visible in the process list for the moment the command runs, so on a shared deploy box you would rather have the stdin treatment here too, and it is the kind of gap we will close rather than defend. The role allow permission string, something like select:orders or ddl, is enforced by the engine at the storage layer, so the grant you write in the deploy script is the grant the transaction engine checks, with no middleware translation in between.

The operator verbs stay in the terminal

The third pile is the verbs you run at 2 AM with one eye open, and their defining trait is that a human is reading the output and deciding what to do next, so they optimize for being legible rather than scriptable. doctor runs an integrity check over the whole database, check is the faster version that only verifies the internal catalog tables exist, and the difference matters at 2 AM because check answers “is this thing openable at all” in milliseconds while doctor answers “is every page where it should be” in however long that takes. compact merges the sorted runs in every table down to one, which flattens query latency after a heavy ingest window, analyze rebuilds the index statistics the query planner feeds on, and vacuum is the two of those followed by garbage collection of dead pages, so the mental model is compact for speed, analyze for the planner, vacuum for disk.

The verb that sees the most terminal use is sql, because sometimes the fastest path to an answer is the statement itself:

mongreldb-kit sql ./store.kitdb \
  "SELECT region, count(*) FROM orders GROUP BY region" \
  --timeout-ms 5000

The --timeout-ms flag is the part to copy into your muscle memory, because an ad-hoc query against a production-adjacent copy of the data should always carry a deadline, and the companion --query-id flag lets you tag the statement with a 32-hex identifier so the engine logs can tell your 2 AM exploration apart from the application’s traffic. The data verbs, get, query, insert, update, delete, upsert, count, round out the pile, and they exist for the same reason sqlite3 has dot-commands, which is that the fastest debugging session is the one where you never leave the tool you already have open.

The sorting is the feature

The tradeoff in this design is that nothing stops you from running migrate apply in CI or schema validate in a deploy script, because the binary does not enforce the piles, and that is deliberate; enforcement would mean flags and guards and a bigger CLI, and the cost of discipline-by-convention is one document like this one that says which pile is which. The payoff is that when something goes wrong you know which pile the blame belongs to, a CI failure means a read-only verb found drift or an invalid schema before anything mutated, a deploy failure means a one-way door was approached in the wrong order, and an operator failure means a human ran a verb and the database answered honestly. Twenty years of watching teams improvise this sorting on top of mysqladmin and psql taught me that the improvisation always happens eventually, in somebody’s runbook wiki page that goes stale the week it is written, so we wrote the sorting into the subcommand tree instead, and the CLI you get is the one where mongreldb-kit --help reads like a pipeline diagram if you know how to squint at it.