cmd db

nself db

Database operations: migrations, backups, restore, seed, shell.

Synopsis

nself db <subcommand> [flags]

Description

Database operations: migrations, backups, restore, seed, and shell.

---

db migrate

nself db migrate <subcommand> [flags]

Flags (persistent)

Flag	Description
--plugin <name>	Scope migration operations to a specific plugin

db migrate up

Apply all pending migrations in lexicographic order.

nself db migrate up [--dry-run] [--migration-dir <path>] [--plugin <name>]

Flag	Description
--dry-run	List pending migrations without applying them
--migration-dir <path>	Apply all .sql files in <path> in lexicographic order, skipping already-applied files

With --migration-dir and --dry-run, the command lists the pending files of that directory and applies nothing. It reads the ledger (np_common.schema_versions, plus the nself_ops.migrations checksums) with SELECT statements only: no table is created, no row is written, and nothing is run inside a rolled-back transaction. It fails, as the real run would, when an applied file was edited after apply or when a pending file is rejected by the SQL lint or the ALTER prerequisite check. With --env/--server, db migrate up forwards --migration-dir, --dry-run and --plugin to the remote host, so a plugin-scoped or directory-scoped run stays scoped there. (db migrate status forwards --plugin too, but only up uses it to scope the run.) A --dry-run on a remote target first probes the remote with nself version --json and proceeds only when the remote advertises the db-dry-run-safe capability (an older remote nself may ignore --dry-run and apply for real). --allow-version-drift never relaxes that probe: it only skips the version match check for ordinary, non-dry-run commands. An invalid --plugin name is refused before any SSH call.

Each file runs with statement_timeout = 60s and lock_timeout = 5s. A file that needs longer (a data backfill) raises or removes the statement timeout with a comment line anywhere in the file: -- nself:statement-timeout=600s (10 minutes) or -- nself:statement-timeout=0 (no limit). The directive works for up, down, and both with --migration-dir; lock_timeout stays at 5s.

up --migration-dir creates the two ledger tables in one exec and reads the ledger (applied names and stored checksums) once per run, then applies each pending file in its own transaction, so a run that has nothing to apply costs the same few database calls for 1 file or 500. Before each pending file it prints [n/total] <file> on stderr (total is the number of pending files); stdout is unchanged (Applied N migration(s) from <dir>. or No pending migrations in directory.).

In directory mode (up --migration-dir, down --migration-dir) the CLI runs each migration in one transaction with its ledger change, in two layers that never parse SQL:

1. Scan (over-refuses). Line endings are normalised, then only an exact outer wrapper is dropped: a first statement BEGIN;, BEGIN TRANSACTION; or START TRANSACTION; and a last statement COMMIT; or END; (case and whitespace tolerant, trailing comments allowed). If the rest of the file contains COMMIT, ROLLBACK, ABORT, SAVEPOINT, RELEASE or PREPARE as a word anywhere, in a string or a trailing comment too (whole lines that start with -- are skipped), up and down refuse the file. The error names the word and the line. For an up file, run it with nself db migrate apply --file <path>, which applies the file exactly as written. A down file is run by hand in nself db shell, then its two ledger rows are deleted. up refuses the whole batch before applying any file. BEGIN, END, DO, CALL, ATOMIC and CONCURRENTLY are not refused: Postgres itself rejects transaction control inside functions, procedures and DO blocks run in a transaction, and CREATE INDEX CONCURRENTLY errors inside one.
2. Server check. The transaction records its id, writes the ledger change first, runs the file, then asserts it is still the same open transaction before COMMIT. A top-level END (it acts as COMMIT) fails this check with a RECONCILE message: part of the file may be committed, and because the ledger was written first, ledger and schema agree on what was committed; compare them by hand before re-running. After the run the ledger row is read back (up: present, down: absent).

Examples:

# Apply all pending migrations
nself db migrate up

# Preview pending migrations
nself db migrate up --dry-run

# Apply external directory of SQL files (G-008)
nself db migrate up --migration-dir /path/to/plugin/migrations

# Preview what that directory would apply (writes nothing)
nself db migrate up --migration-dir /path/to/plugin/migrations --dry-run

db migrate apply

Apply a single SQL migration file by path (G-008).

nself db migrate apply --file <path>

Flag	Required	Description
--file <path>	yes	Path to the .sql migration file to apply

Double-apply protection: if the file's filename is already recorded in schema_versions, the command prints already applied: <filename> (skipped) and exits cleanly with code 0. It does not re-execute the SQL.

Checksum tracking: the SHA-256 checksum of the file is stored in nself_ops.migrations for audit purposes.

Non-transactional detection: SQL files containing CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY or ALTER TYPE ... ADD VALUE (found anywhere in the text) are run outside a transaction automatically, as written, in up --migration-dir and apply --file; the scan above does not apply to them. down --migration-dir always uses the down transaction, so a down file with such a statement fails in Postgres and nothing is reverted.

Examples:

# Apply a specific external RLS migration
nself db migrate apply --file /path/to/plugin/rls_policy.sql

# Apply a migration from a plugin's migration directory
nself db migrate apply --file ~/.nself/plugins/claw/migrations/20240115_rls.sql

Use case: plugin-claw external RLS migrations can be applied via CLI without requiring nself db shell as a workaround.

db migrate down

Revert the most recently applied migration.

nself db migrate down [--migration-dir <path> [--steps N]] [--yes]

Without --migration-dir, looks for a corresponding .down.sql file next to the original migration.

Reverting drops what the migration created, so it needs consent. In v1.4 it warns when called without --yes. In v1.5 (NSELF_V15=1) it refuses with [E403] unless you pass --yes or answer y on a terminal.

Flag	Description
--migration-dir <path>	Revert directory migrations: each reverted file <name>.sql needs <name>_down.sql or <name>.down.sql in <path>
--steps N	With --migration-dir: number of most recent migrations to revert (default 1)
--yes	Confirm the revert without a prompt (required from v1.5 unless you answer a terminal prompt)

With --migration-dir, every step runs the down file and removes the migration's rows from both ledgers in one transaction. The newest applied migration must be a file of <path>; otherwise nothing is reverted and the error names it. A missing down file is an error naming both expected paths, and every step is checked before the first one runs. The ledger keys on the file name only, so down also requires the file in <path> to match the checksum recorded for that name in nself_ops.migrations; a same-named file from another directory (or an edited one) is refused. --steps without --migration-dir is an error.

db migrate status

Show applied and pending migrations.

nself db migrate status

Reports: migration name, status (applied/pending), and timestamp for applied migrations.

status is a safe read: it issues SELECT statements only. It creates no table and rewrites no ledger row, and a missing ledger just means nothing is applied. When the ledger still holds legacy rows (old prefix ids in nself_ops.migrations, or a nested up.sql row), status resolves them in memory and prints one warning line on stderr: nself db migrate up (or down) performs the ledger upgrade, with the same --migration-dir if you used one. With --json the same fact is data.ledger_needs_upgrade (boolean); the nself_db_migrate_status MCP tool returns the same ledger_needs_upgrade field. Scripts and agents read the flag instead of parsing stderr.

db migrate create

Create a new migration file pair (up + down).

nself db migrate create <name>

Creates migrations/<timestamp>_<name>.sql and migrations/<timestamp>_<name>.down.sql.

Name must be lowercase alphanumeric with underscores or hyphens only.

---

Replacing raw psql migration scripts

A loop such as for f in migrations/*.sql; do psql -f "$f"; done applies files with no ledger, no checksum and no rollback, and re-running it applies everything again. Use the CLI:

nself db migrate status                       # what is applied, what is pending
nself db migrate up --dry-run                 # preview (writes nothing)
nself db migrate up                           # apply, recorded and checksummed
nself db migrate up --migration-dir ./sql     # an existing directory of SQL files

Files that were already applied by hand are listed as pending once; read status and the dry run before the first up. Take a backup first (nself backup stream, see cmd-backup). Never edit an applied migration: its checksum no longer matches. An advisory nself doctor hint for scripts that feed migrations/ to raw psql is written but not yet wired (P7-SURF-11).

---

db seed

Run seed data for the current environment.

nself db seed [file]
nself db seed run [--env <env>] [--fixture <name>] [--reset]
nself db seed list
nself db seed verify [--fixture <name>]
nself db seed graph

---

db backup

nself db backup [file]
nself db backup list [--format table|json]

db backup writes a local dump. For an encrypted off-box backup use nself backup stream (a project with no backup configuration gets its first encrypted backup with NSELF_V15=1; see cmd-backup for where the key lives and what losing it means).

---

db restore

nself db restore <file> [--overwrite] [--yes]

---

db shell

Open an interactive psql shell inside the project's Postgres container.

nself db shell

---

db drop

Drop the project database. This is destructive and irreversible.

nself db drop [--yes]

---

db reset

Drop and recreate the project database from scratch.

nself db reset [--force] [--yes]

---

db lint

Audit database schema and RLS policies.

nself db lint [--format table|json] [--rls] [--metric] [--matrix] [--remediate]

---

db hasura

Hasura metadata operations.

nself db hasura console
nself db hasura schema --json
nself db hasura permissions --json
nself db hasura metadata apply
nself db hasura metadata export
nself db hasura metadata reload
nself db hasura diff
nself db hasura validate

metadata export --archive - writes a tar archive of hasura/metadata to stdout. metadata apply --archive - reads that archive from stdin and applies it without changing the checkout. Invalid paths, links, and archives above 64 MiB are rejected before metadata is written.

db hasura sync --env staging exports metadata from the selected remote environment into the local hasura/metadata and commits it locally. db hasura apply-ref --env prod <ref> compares the ref with the remote export and prints a Hasura change plan. A nonempty plan for prod or staging requires --yes in a noninteractive session; otherwise it returns E403 (exit 4). Both commands use the configured db remote target and require a remote nself CLI that supports --archive -.

---

db pitr restore

nself db pitr restore --target 2026-01-15T14:30:00Z [--yes]

Restores the database to a point in time and overwrites what is there now. In v1.4 it warns when called without --yes. In v1.5 (NSELF_V15=1) it refuses with [E403] unless you pass --yes or answer y on a terminal. The same rule applies to nself backup pitr restore.

Flags

Name	Shorthand	Type	Default	Env	Escalates to	JSON override	Description
--help	-h	bool	false				Show help

Subcommands

Name	Description
backup	Create pg_dump backup
backup-sync	Sync local backups to remote storage (cross-region)
backup-sync-status	Show cross-region backup sync status
drift	Schema drift detection: scan np_* tables for Theme 25 column compliance
drop	Drop the project database (DESTRUCTIVE)
export	Export a portable bundle from a started project
fk-index	Audit and create indexes for foreign key columns
generate	Generate type-safe client SDK types from the live Hasura schema
hasura	Hasura metadata operations
image	Manage the Postgres image of a project
import	Import data from another platform
lint	Check RLS policies on tenant-scoped tables
list	List databases in the project Postgres instance
migrate	Manage database migrations
pgbouncer	PgBouncer connection pooler operations
pitr	Point-in-time recovery: status, enable, test, restore
reconcile	Push repo-declared Hasura metadata (permissions, relationships, table tracking) to a live instance
reset	Drop and recreate database (DESTRUCTIVE)
reset-checksum	Reset stored checksum for a migration (dangerous)
restore	Restore from backup
restore-drill	Run a non-destructive restore drill
restore-drill-list	List past restore drill results
rls	Row-Level Security management: audit, apply, rollback
seed	Database seeding: run, list, verify, graph
shell	Open psql interactive shell
soft-delete	Manage soft-delete (deleted_at) patterns across tables
verify	Verify what a Hasura role can actually reach via role-scoped introspection
verify-checksums	Verify migration file checksums against stored values

Contract

• Side effect: read
• Output: document
• JSON: envelope (data schema)
• Exit codes: none declared

Examples

# Apply all pending migrations
nself db migrate up

# Open an interactive psql shell inside the Postgres container
nself db shell

# Audit RLS policies and schema for tenant-scoped tables
nself db lint

# Take a local backup, then check migration status
nself db backup
nself db migrate status

See Also

• Architecture — stack overview including migration engine
• cmd-plugin-dev — plugin development, including plugin-specific migrations
• cmd-backup — full backup subcommand reference

Home

← Commands | Home →