Skip to content

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 ​

CommandDoesNeeds a database
generate <name>Diff the schema against the last migration and write a new oneno ¹
checkFail if the schema, the migrations and their review state are out of stepno
migrate latestApply every pending migration as one batchyes
migrate upApply the next pending migration, or up to --toyes
migrate downReverse the most recent migration, or back to --toyes
migrate rollbackReverse the whole last batch, or --allyes
migrate statusList applied and pending migrationsyes
migrate review <id>Mark a migration reviewedno
migrate unlockRelease a migration lock a killed run left behindyes
pushMake a prototyping database match the schema, with no migration fileyes
seed [names...]Run the seed files in seedsDiryes
introspectWrite a schema file from a live databaseyes

¹ 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:

ts
// 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 status

It 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:

ts
// 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:

FieldTypeDefaultDescription
schemaPathstring'src/db/schema.ts'The schema module generate and check read.
migrationsDirstring'db/migrations'Where migrations and _journal.json live.
seedsDirstring'db/seeds'Where kick db seed finds seed files.
dialect'postgres' | 'sqlite' | 'mysql''postgres'The SQL generate writes.
connectionStringstringDATABASE_URLPostgres 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 ​

HelperPurpose
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 ​

text
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_notes
text
Created migration /app/db/migrations/20261002_180007_create_notes (1 change).

With nothing to do:

text
No schema changes detected.
FlagEffect
-e, --emptySkip 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-interactiveDon't ask which drops are renames, even in a terminal.
--tsWrite 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.

text
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 ​

text
kick db check

Everything 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.
text
Migrations are in step with the schema.
text
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 ​

text
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:

text
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-loss agrees in advance. A rename asks like generate does, or take --rename-table / --rename-column.
  • It remembers what it pushed. The schema it pushed is kept in a kick_migrations_push table (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 as db), push names the host and asks in a terminal, and refuses otherwise. --yes agrees in advance. NODE_ENV is often unset in a shell, so it can't be the only guard. A db.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 ​

text
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).

text
Applied batch 2: 20261002_180017_add_pinned, 20261002_180018_seed_notes
text
No pending migrations.
FlagEffect
--confirm-enum-dropAllow a migration that removes values from a Postgres enum (it carries a -- KICK ENUM REMOVE header). Read its USING casts first.

migrate up ​

text
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.

text
Applied 20261002_180007_create_notes (batch 1)

migrate down ​

text
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.

text
Reversed 20261002_180018_seed_notes.
text
Nothing to reverse.

migrate rollback ​

text
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.

text
Rolled back batch 2: 20261002_180017_add_pinned
text
Nothing to roll back.

migrate status ​

text
kick db migrate status

Every migration in the journal, with its state, batch and review flag:

text
┌─────────┬────────────────────────────────┬───────────┬───────┬──────────┬───────────────────────┐
│ (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 ​

text
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.

text
Reviewed 20261002_180017_add_pinned — it can now be applied.
text
20261002_180017_add_pinned was already reviewed.

migrate unlock ​

text
kick db migrate unlock

Releases 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.

text
Released the migration lock.

seed ​

text
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:

ts
// 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!) },
  })
}
text
Ran 1 seed(s): 01_admin.ts

Nothing 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:

ts
// 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 named email, 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 — is null when nullable, otherwise an error asking for one.

fakeRows(table, { count, refs }) makes the rows without a database, for tests or your own inserts.

introspect ​

text
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.

FlagEffect
--out <path>Where to write the file. Default: schemaPath — which overwrites your schema.
--jsonPrint the raw snapshot JSON instead of writing a file.
pnpm exec kick db introspect --out src/db/introspected.ts
text
Wrote src/db/introspected.ts (1 table).
ts
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.

Released under the MIT License. Built with TypeScript — runs on Express, Fastify, or h3.