Database CLI
The kick db commands — migrations, schema diffs, introspection — ship with @forinda/kickjs-db. Run them through the kick CLI as a plugin, or through the standalone kickjs-db binary, which needs no @forinda/kickjs-cli.
Commands
| Command | Does | Needs a database |
|---|---|---|
generate <name> | Diff the schema against the last migration and write a new one | no ¹ |
check | Fail if the schema, the migrations and their review state are out of step | no |
migrate latest | Apply every pending migration as one batch | yes |
migrate up | Apply the next pending migration, or up to --to | yes |
migrate down | Reverse the most recent migration, or back to --to | yes |
migrate rollback | Reverse the whole last batch, or --all | yes |
migrate status | List applied and pending migrations | yes |
migrate review <id> | Mark a migration reviewed | no |
migrate unlock | Release a migration lock a killed run left behind | yes |
push | Make a prototyping database match the schema, with no migration file | yes |
seed [names...] | Run the seed files in seedsDir | yes |
introspect | Write a schema file from a live database | yes |
¹ On Postgres with a connectionString (and no adapter factory), generate connects to check enum changes against composite types; otherwise it reads only files.
Every command exits 0 on success and 1 on failure, printing the error's message. Troubleshooting lists the messages and what to do about each.
Setup
As a kick plugin
Add dbCliPlugin to kick.config.ts. The commands read the same file's db block:
// kick.config.ts
import { defineConfig } from '@forinda/kickjs-cli'
import { dbCliPlugin } from '@forinda/kickjs-db/cli'
export default defineConfig({
plugins: [dbCliPlugin],
db: {
schemaPath: 'src/db/schema.ts',
migrationsDir: 'db/migrations',
dialect: 'sqlite',
adapter: async () => {
const Database = (await import('better-sqlite3')).default
const { sqliteAdapter } = await import('@forinda/kickjs-db/sqlite')
return sqliteAdapter({ database: new Database('app.db') })
},
},
})If your app already opens the database in a file that exports a migration adapter, return that one instead and keep a single connection: adapter: async () => (await import('./src/db/client')).migrationAdapter (Getting started).
The plugin is opt-in: without it kick db is an unknown command. It also adds the schema types to kick typegen (see kick typegen). kick loads .env before running a command, so NODE_ENV=development there lets unreviewed migrations apply locally.
Standalone kickjs-db
The same commands without @forinda/kickjs-cli:
pnpm exec kickjs-db migrate statusIt reads the db block of kick.config.ts and a kickjs-db.config.ts, if either exists — the standalone file wins where both set a field:
// kickjs-db.config.ts
import { defineKickDbConfig } from '@forinda/kickjs-db/cli'
export default defineKickDbConfig({
dialect: 'sqlite',
schemaPath: 'src/db/schema.ts',
migrationsDir: 'db/migrations',
adapter: async () => {
const Database = (await import('better-sqlite3')).default
const { sqliteAdapter } = await import('@forinda/kickjs-db/sqlite')
return sqliteAdapter({ database: new Database('app.db') })
},
})A .ts / .mts config loads through jiti, which kick/db ships with; .js, .mjs and .json work too. For a .ts / .mts config, both kick db and the standalone binary resolve the project's tsconfig.json path aliases in what the config loads — so a db.adapter() that imports ./src/db/client, which imports @/config, works outside Vite. A .js / .mjs config is imported natively, without alias resolution. The standalone binary doesn't load .env — set NODE_ENV and DATABASE_URL in the environment.
Config fields
The kick.config.ts db block and kickjs-db.config.ts share one shape:
| Field | Type | Default | Description |
|---|---|---|---|
schemaPath | string | 'src/db/schema.ts' | The schema module generate and check read. |
migrationsDir | string | 'db/migrations' | Where migrations and _journal.json live. |
seedsDir | string | 'db/seeds' | Where kick db seed finds seed files. |
dialect | 'postgres' | 'sqlite' | 'mysql' | 'postgres' | The SQL generate writes. |
connectionString | string | DATABASE_URL | Postgres only — the built-in adapter connects with it when there's no adapter. |
adapter | () => MigrationAdapter | Promise<> | — | Builds the connection the commands use. Required for SQLite and MySQL; wins over connectionString. |
driftCheck | 'error' | 'warn' | 'ignore' | 'error' | What migrate does when the live database has schema no migration recorded — see Drift. |
An adapter factory that opens a pool — MySQL, or your own pg.Pool — should pass endPoolOnClose: true, so the command can exit when it's done (Troubleshooting).
Config helpers
| Helper | Purpose |
|---|---|
defineKickDbConfig(cfg) | Typed identity helper for kickjs-db.config.ts. |
mergeKickDbConfig(...cfgs) | Shallow-merge config layers; later wins. |
resolveKickDbConfig(block) | Apply the defaults above to a config block. |
registerDbCommands(parent, getConfig) | Attach the command tree to any commander command — for building your own binary. |
The examples below run against SQLite.
generate
kick db generate <name> [-e, --empty] [--rename-table <old=new>] [--rename-column <table.old=new>] [--no-interactive] [--ts]Diffs schemaPath against the last migration's snapshot.json and writes db/migrations/<YYYYMMDD_HHMMSS>_<name>/ — up.sql, down.sql, snapshot.json, meta.json — plus an entry in _journal.json. The new migration starts unreviewed.
pnpm exec kick db generate create_notesCreated migration /app/db/migrations/20261002_180007_create_notes (1 change).With nothing to do:
No schema changes detected.| Flag | Effect |
|---|---|
-e, --empty | Skip the diff and write an empty migration for SQL you write yourself — a backfill, a seed. The snapshot is the previous one. |
--rename-table <old=new> | Treat a dropped table as renamed to a new one. Repeatable. |
--rename-column <table.old=new> | Treat a dropped column as renamed; table is the new table name. Repeatable. |
--no-interactive | Don't ask which drops are renames, even in a terminal. |
--ts | Write the migration in TypeScript: a migration.ts with up(db) / down(db). The snapshot is carried over, as with --empty — TypeScript migrations. |
In a terminal, a dropped table or column that a new one could replace is asked about — Renames.
Created empty migration /app/db/migrations/20261002_180018_seed_notes (author up.sql + down.sql).When a reverse can't be generated exactly — a dropped column, a dropped table, a type change — down.sql starts with -- DRAFT: ambiguous reverses present …; read it before you rely on it. Migrations covers the output in detail.
Write your SQL into the migration before you review it: kick db migrate review records a hash of the files as reviewed, and an edit after that is refused until it's reviewed again (Troubleshooting).
check
kick db checkEverything migrate latest would refuse, found from the files alone — no database. Run it in CI (CI and deploy) so a missing, unreviewed or edited migration fails the build, not the deploy. It fails when:
- the schema has changes no migration covers;
- a migration isn't reviewed;
- a reviewed migration's files changed after review.
Migrations are in step with the schema.The schema has 1 change no migration covers — run `kick db generate <name>`.
20261002_180017_add_pinned is not reviewed — read it, then `kick db migrate review 20261002_180017_add_pinned`.
20261002_180017_add_pinned was edited after it was reviewed — review it again, or revert the edit.Exit code 1 when anything is listed. The same check is exported as checkMigrations({ config, cwd }).
push
kick db push [--rename-table old=new] [--rename-column table.old=new] [--accept-data-loss] [--no-interactive] [-y, --yes]Makes the database match the schema straight away, with no migration file — for trying out a schema before committing to it. Run it again after each edit:
Pushed 3 changes.
The database already matches the schema.- Data loss is asked about. Dropping a table or column, changing a column's type or removing an enum value asks first in a terminal, and fails otherwise —
--accept-data-lossagrees in advance. A rename asks likegeneratedoes, or take--rename-table/--rename-column. - It remembers what it pushed. The schema it pushed is kept in a
kick_migrations_pushtable (named after your migrations table) and the next push diffs against it. If the database no longer matches it, push stops:the database differs from what was last pushed. That's a change made some other way, or a MySQL push that failed partway — MySQL commits each DDL statement on its own, so a failure can leave earlier ones applied. Make the database match the schema you last pushed, or start from an empty one. - It starts from an empty database. On one with tables push didn't create, it stops.
- It's refused where migrations run: on a database with any migration applied, and whenever
NODE_ENV=production— either one is enough. - A remote database is asked about. When the connection string points at a host that isn't this machine or a neighbouring container (
localhost,127.*,::1,*.local,*.localhost, a compose service name such asdb), push names the host and asks in a terminal, and refuses otherwise.--yesagrees in advance.NODE_ENVis often unset in a shell, so it can't be the only guard. Adb.adapter()factory with no connection string gives push nothing to check, so only the other guards apply.
When the schema settles, write it down as a migration: kick db generate init, then apply it to a fresh database (or drop the prototyping one) — the pushed tables already exist, so migrate latest on the same database would fail. The same function is exported as pushSchema({ adapter, schema }).
migrate latest
kick db migrate latest [--confirm-enum-drop]Applies every pending migration in one new batch. Before it applies anything it takes the migration lock, checks each pending migration's hash and — outside NODE_ENV=development — that it's reviewed, and compares the live database with the last applied snapshot (drift). Each migration runs in its own transaction where the dialect allows; MySQL commits DDL as it goes (Recovering from a failed migration).
Applied batch 2: 20261002_180017_add_pinned, 20261002_180018_seed_notesNo pending migrations.| Flag | Effect |
|---|---|
--confirm-enum-drop | Allow a migration that removes values from a Postgres enum (it carries a -- KICK ENUM REMOVE header). Read its USING casts first. |
migrate up
kick db migrate up [--to <migration>] [--confirm-enum-drop]Applies only the next pending migration, as its own batch. With --to, applies every pending migration up to and including that one, named by its id (20261002_180007_create_notes) or its name (create_notes). Same checks and flag as migrate latest.
Applied 20261002_180007_create_notes (batch 1)migrate down
kick db migrate down [--to <migration>]Runs the most recently applied migration's down.sql and marks it pending again. With --to, reverses every migration applied after that one, newest first; the one named stays applied.
Reversed 20261002_180018_seed_notes.Nothing to reverse.migrate rollback
kick db migrate rollback [--all]Reverses the whole last batch — everything one migrate latest applied — newest first. --all reverses every applied migration, back to an empty history.
Rolled back batch 2: 20261002_180017_add_pinnedNothing to roll back.migrate status
kick db migrate statusEvery migration in the journal, with its state, batch and review flag:
┌─────────┬────────────────────────────────┬───────────┬───────┬──────────┬───────────────────────┐
│ (index) │ id │ state │ batch │ reviewed │ applied │
├─────────┼────────────────────────────────┼───────────┼───────┼──────────┼───────────────────────┤
│ 0 │ '20261002_180007_create_notes' │ 'applied' │ 1 │ true │ '2026-10-02 18:00:09' │
│ 1 │ '20261002_180017_add_pinned' │ 'pending' │ '-' │ true │ '-' │
└─────────┴────────────────────────────────┴───────────┴───────┴──────────┴───────────────────────┘It exits 0 whether or not anything is pending; use kick db check and the app's boot policy to gate on that.
migrate review
kick db migrate review <id>Marks a migration reviewed by setting reviewed: true in its meta.json — the step that says someone read the SQL. Outside NODE_ENV=development the runner applies only reviewed migrations. Touches no database.
Reviewed 20261002_180017_add_pinned — it can now be applied.20261002_180017_add_pinned was already reviewed.migrate unlock
kick db migrate unlockReleases the migration lock. The runner holds it while it migrates and releases it when it finishes — even on failure — but a process that was killed (out of memory, a deploy timeout) never gets the chance, and every later run stops with Another process holds the migration lock. Run this only when you're sure no migration is running.
Released the migration lock.seed
kick db seed [names...]Runs the seed files in seedsDir (db/seeds) in name order — prefix them to order them (01_roles.ts, 02_admin.ts) — or only the ones named, with or without the extension. Each file default-exports an async function and imports what it needs, usually the app's own client:
// db/seeds/01_admin.ts
import { db } from '@/db/client'
import { hashPassword } from '@/auth/password'
export default async function seed() {
await db.findOrCreate('users', {
where: { email: 'admin@example.com' },
create: { name: 'Admin', passwordHash: await hashPassword(process.env.ADMIN_PASSWORD!) },
})
}Ran 1 seed(s): 01_admin.tsNothing records which seeds ran — they aren't migrations, and every run runs them all. Write them to be run again: db.upsert() and db.findOrCreate() make that one line. A seed that throws stops the run with Seed <file> failed: <message> and exit code 1; kick db seed <name> that matches no file fails before anything runs. The command exits when the seeds finish, even though the client they imported still holds its pool.
Seed files load the way the app's code does — TypeScript, extensionless relative imports, and the tsconfig.json path aliases (@/db/client). They aren't type-checked when they run, so keep their folder in tsconfig.json's include for kick typecheck to catch a seed that misses a column added since. New projects include db; with a custom seedsDir or migrationsDir, add that folder. For schema changes, data fixes that must run exactly once, or anything a deploy depends on, write a migration (kick db generate <name> --empty) instead.
Generated sample data
For data to develop against, seedFake() fills tables with rows that fit the schema — and are the same every run for the same seed:
// db/seeds/02_sample.ts
import { seedFake } from '@forinda/kickjs-db'
import { db } from '@/db/client'
import * as schema from '@/db/schema'
export default async function seed() {
// The same rows every run would collide on unique columns — fill once.
if (await db.selectFrom('tasks').select('id').limit(1).executeTakeFirst()) return
await seedFake(db, schema, {
counts: { users: 5, projects: 3, project_members: 6, tasks: 20 },
overrides: { project_members: { role: 'member' } },
})
}- Values fit the columns — their type, a
varchar's length, an enum's values; a unique column gets the row's number so no two clash, and a column namedemail,title,slug,url… gets text that looks like one. - Relations hold. Tables are filled parents first; a foreign key points at the parent rows made in the same call, or at rows already in the table when it isn't in
counts. A table with several foreign keys (a junction) gets distinct combinations. - The database keeps its say. Defaults,
$defaultFn, serial and identity keys, generated and managed columns are left to it — a uuid primary key is the exception, given a value so the rows are reproducible. - Overrides per table and column: a value, or
({ index, random }) => value. A column it can't fill — a custom type, a vector, a CHECK it can't know about — isnullwhen nullable, otherwise an error asking for one.
fakeRows(table, { count, refs }) makes the rows without a database, for tests or your own inserts.
introspect
kick db introspect [--out <path>] [--json]Reads the live database and writes a schema file for it — the first step of adopting kick/db on an existing database.
| Flag | Effect |
|---|---|
--out <path> | Where to write the file. Default: schemaPath — which overwrites your schema. |
--json | Print the raw snapshot JSON instead of writing a file. |
pnpm exec kick db introspect --out src/db/introspected.tsWrote src/db/introspected.ts (1 table).import { table, text } from '@forinda/kickjs-db'
export const notes = table('notes', {
id: text().primaryKey().default('lower(hex(randomblob(4)) || …)'),
title: text().notNull(),
body: text(),
createdAt: text().notNull().default("strftime('%Y-%m-%d %H:%M:%f', 'now')"),
})SQLite and MySQL keep less type information than the schema had — uuid() comes back as text, varchar(200) as text, defaults as raw SQL — so read the result and put the types back before you use it.
kick typegen
With dbCliPlugin mounted, kick typegen (which kick dev runs on every save) also types the rows of an injected KickDbClient and the relation names db.query … { with } accepts. Run it after changing relations() if the editor doesn't see the new relation — Schema Types.
Related
- Migrations — how generating, reviewing and applying fit together
- CI and deploy — where each command runs in a pipeline
- Recovering from a failed migration
- Troubleshooting