Database lifecycle management: schema migrations, seeding, backup and restore, Hasura metadata sync, row-level security audits, point-in-time recovery, pgBouncer pooling, audit logging, soft-delete scaffolding, foreign-key index audits, and test fixtures — all without touching Postgres directly.
# Apply pending migrations
nself db migrate up
# Open a Postgres shell (read-only by default)
nself db shell
# Take a backup
nself db backup
# Restore the latest backup
nself db restore
# Run all seeds for the current environment
nself db seed run
# Open the Hasura console
nself db hasura consolenself db <SUBCOMMAND> [FLAGS]nself db is the single interface for every database operation in an nSelf stack. It communicates with Postgres through the running Docker network — you never need connection strings or direct psql access. Every operation respects the active environment cascade and validates against the Hasura schema before writing.
Operations are divided into groups based on scope. Each group has its own subcommand hierarchy documented below.
Schema migration management using Hasura Migrations. All migrations live in migrations/ and are version-ordered. The CLI tracks applied state in thehdb_catalog.schema_migrations table.
nself db migrate <ACTION> [FLAGS]Apply all pending migrations to the current environment.
# Apply all pending
nself db migrate up
# Apply exactly N migrations
nself db migrate up --count 3
# Target a specific environment
nself db migrate up --env staging
# Preview SQL without executing
nself db migrate up --dry-run| Flag | Default | Description |
|---|---|---|
--count N | all | Apply at most N pending migrations |
--env NAME | active | Target environment: dev, staging, prod |
--dry-run | false | Print SQL that would be executed, then exit |
--skip-metadata | false | Skip Hasura metadata reload after migration |
Roll back the last N applied migrations.
# Roll back the last migration
nself db migrate down
# Roll back 3 migrations
nself db migrate down --count 3| Flag | Description |
|---|---|
--count N | Number of migrations to roll back (default: 1) |
--env NAME | Target environment |
--dry-run | Print rollback SQL without executing |
Show the applied / pending state of every migration file. Useful before a deploy to confirm no drift between environments.
nself db migrate status
# Compare two environments
nself db migrate status --env prod
# JSON output for CI
nself db migrate status --jsonScaffold a new migration file with the current timestamp prefix.
# Create an up+down migration pair
nself db migrate create add_user_preferences
# Create with SQL stub
nself db migrate create add_user_preferences --sql "ALTER TABLE users ADD COLUMN preferences JSONB;"Verify that previously applied migration files have not been edited since they were applied. Detects accidental or malicious modification of migration history.
nself db migrate verify-checksums
# Fix: overwrite stored checksums with current file hashes
nself db migrate reset-checksum --migration 20240501120000Reset the stored checksum for a specific migration version. Use only after deliberately editing a migration file with full awareness of the consequences.
nself db migrate reset-checksum --migration 20240501120000Seed the database with reference or test data. Seeds are SQL or TypeScript files inseeds/ and are tagged by environment (dev, staging, all). Seeds are idempotent by convention — running them twice should not duplicate data.
# Run all seeds for the active environment
nself db seed run
# Run a specific seed file
nself db seed run --file seeds/users.sql
# Target environment
nself db seed run --env staging
# Dry run
nself db seed run --dry-runList all available seed files with their environment tags and applied status.
nself db seed list
nself db seed list --jsonVerify that expected seed data is present in the database. Useful as a post-deploy sanity check.
nself db seed verify
nself db seed verify --env prodPrint the dependency graph of seed files — which seeds must run before which others. Helps diagnose foreign-key constraint failures during seeding.
nself db seed graph
nself db seed graph --jsonTake a logical backup of the Postgres database using pg_dump. Backups are stored in .nself-backups/ with a timestamp filename. The --remote flag uploads to the configured S3/MinIO bucket.
# Local backup (stored in .nself-backups/)
nself db backup
# Label the backup
nself db backup --label pre-migration-v2
# Upload to remote storage
nself db backup --remote
# Backup a specific environment
nself db backup --env prod| Flag | Default | Description |
|---|---|---|
--label TEXT | timestamp | Human-readable label appended to the filename |
--remote | false | Upload to S3/MinIO bucket configured in .env |
--format | custom | pg_dump format: custom (default), plain, directory |
--env NAME | active | Target environment |
--compress N | 6 | Compression level 0–9 |
Restore a database backup created by nself db backup. By default restores the most recent local backup. Prompts for confirmation before overwriting data.
# Restore the latest local backup
nself db restore
# Restore a specific file
nself db restore --file .nself-backups/2024-05-01T12:00:00_pre-migration-v2.dump
# Restore from remote storage
nself db restore --remote
# Skip confirmation prompt (CI/scripts)
nself db restore --force| Flag | Description |
|---|---|
--file PATH | Path to a specific backup file |
--remote | Pull backup from remote S3/MinIO, then restore |
--force | Skip the "this will overwrite data" confirmation prompt |
--env NAME | Target environment |
Open an interactive psql shell connected to the running Postgres instance. Credentials and connection parameters are read from the environment cascade.
# Interactive psql shell
nself db shell
# Run a single query and exit
nself db shell --command "SELECT count(*) FROM users;"
# Read-write mode (default is read-only)
nself db shell --rw
# Connect to a specific environment
nself db shell --env staging| Flag | Default | Description |
|---|---|---|
--command SQL | — | Execute a single SQL statement and exit (non-interactive) |
--rw | false | Open in read-write mode (default: read-only for safety) |
--env NAME | active | Target environment |
Drop all nSelf-managed tables (those with the project's table prefix) from the database. This is destructive and requires explicit confirmation. Does not drop the Postgres database itself — only tables.
# Drop all project tables (prompts for confirmation)
nself db drop
# Force without prompt (use with extreme care)
nself db drop --force
# Preview what would be dropped
nself db drop --dry-runDestructive
nself db drop permanently removes all project data. Run nself db backup first. For a full wipe including volumes, use nself reset.
Drop all project tables, then re-run all migrations and seeds from scratch. Equivalent to nself db drop && nself db migrate up && nself db seed run. Useful for resetting a local dev environment to a clean state.
# Reset and reseed (local dev only)
nself db reset
# Reset without seeding
nself db reset --no-seedList all tables, views, and sequences managed by nSelf in the current database.
# List all tables
nself db list
# Include row counts
nself db list --counts
# Filter by prefix
nself db list --filter np_auth
# JSON output
nself db list --jsonManage Hasura GraphQL Engine metadata and console. Hasura metadata tracks which tables are tracked, which relationships are configured, and which permissions apply. Metadata lives in hasura/metadata/ and is version-controlled.
Open the Hasura console in your browser (proxied through the CLI for auth).
nself db hasura console
# Specify port
nself db hasura console --port 9695Apply the local metadata directory to the running Hasura instance.
nself db hasura metadata apply
# Dry run — show what would change
nself db hasura metadata apply --dry-runExport the current Hasura metadata to the local hasura/metadata/ directory.
nself db hasura metadata exportReload Hasura's in-memory metadata from the database. Useful after direct DB changes.
nself db hasura metadata reloadShow the diff between the local metadata directory and the live Hasura instance.
nself db hasura metadata diffValidate metadata files for consistency and missing table references.
nself db hasura metadata validateLint metadata YAML files for style issues, duplicate relationships, and deprecated fields.
nself db hasura metadata lintShow metadata files that have changed relative to the last committed state.
nself db hasura git statusPull metadata from the live Hasura instance into the local directory (overwrites local).
nself db hasura git pullPush local metadata to the live Hasura instance (equivalent to metadata apply).
nself db hasura git pushRow-Level Security management. RLS policies restrict which rows each Postgres role can read or write. nSelf requires RLS on all tables accessed by Hasura to ensure tenant and user data isolation.
Audit all tables for missing or incorrect RLS policies. Outputs a report of tables that are tracked by Hasura but lack required RLS.
nself db rls audit
# Fail with exit code 1 if any violations found (CI-safe)
nself db rls audit --strict
# JSON output
nself db rls audit --jsonApply the nSelf standard RLS policies to all tables that are missing them. Generates and executes ALTER TABLE ... ENABLE ROW LEVEL SECURITY and the standard Hasura role policies.
nself db rls apply
# Preview SQL without executing
nself db rls apply --dry-run
# Apply only to a specific table
nself db rls apply-table --table usersApply RLS policies to a single named table.
nself db rls apply-table --table ordersRemove RLS policies from a table (disables row-level security). Use with extreme care in production.
nself db rls rollback --table orders --env devPoint-In-Time Recovery management. PITR continuously archives WAL segments to remote storage so you can restore the database to any second in the past. Requires theSTORAGE_* env vars to be configured (S3/MinIO).
Show the current PITR configuration: whether it is enabled, the archive lag, and the earliest restore point.
nself db pitr statusEnable WAL archiving and configure the retention window.
# Enable with 7-day retention
nself db pitr enable --retention 7d
# Enable with 30-day retention
nself db pitr enable --retention 30dPerform a non-destructive PITR validation: archives a checkpoint, attempts to restore it to a temporary schema, verifies table counts match, then tears down the temp schema.
nself db pitr testRestore the database to a specific point in time. Destructive — requires confirmation.
# Restore to a specific timestamp
nself db pitr restore --to "2024-05-01T12:30:00Z"
# Restore to 10 minutes ago
nself db pitr restore --to "10 minutes ago"
# Skip confirmation (scripts)
nself db pitr restore --to "2024-05-01T12:30:00Z" --forceForce an immediate WAL archive flush. Useful before a planned maintenance window.
nself db pitr backup-syncpgBouncer connection pooling management. pgBouncer sits between your application and Postgres, multiplexing many app connections into a smaller set of real DB connections. Required for production workloads with high connection counts.
Show pgBouncer pool status: active connections, waiting clients, pool mode, and stats.
nself db pgbouncer status
# JSON for monitoring
nself db pgbouncer status --jsonGenerate the pgbouncer.ini and userlist.txt config files from your .env. These are included in the docker-compose.yml when pgBouncer is enabled.
nself db pgbouncer generate
# Preview without writing
nself db pgbouncer generate --dry-runPrint the pgBouncer connection URL for use in application config.
nself db pgbouncer connection-url
# → postgresql://app_user:***@localhost:6432/mydbAudit log management. The nSelf audit system records every row write to an append-onlynp_audit_log table via Postgres triggers. Enables compliance reporting and change history.
Apply all audit triggers idempotently — safe to run multiple times, will not create duplicate triggers or modify existing ones unless the schema has changed.
nself db audit idempotent
# Preview trigger SQL
nself db audit idempotent --dry-runScan for tables that should have audit triggers (based on Hasura tracking) but do not. Reports drift between expected and actual trigger state.
nself db audit drift scan
# Exit 1 if drift found (CI)
nself db audit drift scan --strictApply missing audit triggers discovered by audit drift scan.
nself db audit drift fix
# Preview without executing
nself db audit drift fix --dry-runSoft-delete scaffolding. Adds a deleted_at TIMESTAMPTZ column to tables that should support logical deletion instead of hard deletes, along with the required Hasura permission filters and Postgres views.
Audit which tracked tables have soft-delete configured and which are missing it.
nself db soft-delete audit
nself db soft-delete audit --jsonAdd the deleted_at column, create a _deleted view, and apply Hasura permission filters to one or all tables.
# Apply to all tables missing soft-delete
nself db soft-delete apply
# Apply to a specific table
nself db soft-delete apply --table posts
# Preview
nself db soft-delete apply --dry-runGenerate a migration file that adds soft-delete to a table. Useful when you want to review and commit the migration before running it.
nself db soft-delete generate --table posts
# → migrations/20240501120000_add_soft_delete_posts.sqlForeign key index audit. Every foreign key column should have an index to avoid slow full-table scans on JOIN and ON DELETE CASCADE operations. This group audits and repairs missing FK indexes.
List all foreign key columns that are missing a supporting index.
nself db fk-index audit
# Exit 1 if any missing (CI)
nself db fk-index audit --strict
# JSON
nself db fk-index audit --jsonCreate the missing indexes identified by fk-index audit. Indexes are created concurrently to avoid locking.
nself db fk-index apply
# Preview index SQL
nself db fk-index apply --dry-runTest fixture management. Fixtures are named, reproducible data sets used for automated tests. Unlike seeds, fixtures are environment-agnostic and are always loaded into an isolated transaction that is rolled back after the test.
List all available fixture sets with their table dependencies.
nself db fixtures list
nself db fixtures list --jsonLoad a fixture set into the database (rolls back automatically in test mode).
# Load a named fixture set
nself db fixtures run --fixture basic-users
# Load and commit (for manual inspection)
nself db fixtures run --fixture basic-users --commitVerify that a fixture set can be loaded without errors — useful in CI to catch stale fixtures after schema changes.
# Verify all fixtures
nself db fixtures verify
# Verify a specific fixture
nself db fixtures verify --fixture basic-usersAll nself db subcommands accept these flags:
| Flag | Default | Description |
|---|---|---|
--env NAME | active | Target a specific environment: dev, staging, prod |
--dry-run | false | Print the SQL or operations that would run, then exit without executing |
--json | false | Output results as JSON (CI-friendly) |
--verbose, -v | false | Print each SQL statement as it executes |
--quiet, -q | false | Suppress all output except errors and final status |
nSelf uses a single Postgres instance per project. All tables carry a project-level prefix (e.g. np_ for the core nSelf tables, flo_ for Flock). App isolation within a single deploy uses a source_account_id TEXT NOT NULL DEFAULT 'primary' column. Cloud multi-tenancy uses a separate tenant_id UUID column with row-level security. Never use the wrong column for the wrong isolation level — see the Multi-Tenant Convention Wall in the architecture docs.