Database Management

Backup, restore, seed, mock, diff, shell, query, and type generation — the full nself db command reference.

Quick Start

# Take a backup
nself db backup

# Restore latest backup
nself db restore

# Open interactive shell
nself db shell

# Run a query
nself db query "SELECT count(*) FROM np_users WHERE deleted_at IS NULL;"

# Show schema status
nself db schema

Architecture Overview

nself db command suite:

  nself db migrate    -- Drizzle migration workflow
  nself db seed       -- Seed reference data
  nself db mock       -- Generate synthetic test data
  nself db backup     -- Dump PostgreSQL to file or S3
  nself db restore    -- Restore from dump or PITR
  nself db schema     -- Introspect live schema
  nself db types      -- Generate TypeScript types
  nself db shell      -- Interactive psql shell
  nself db query      -- One-off SQL query
  nself db hasura     -- Hasura metadata management

All commands operate on the environment set by nself env switch.
Default: dev. Use --env staging or --env prod to target other envs.

nself db migrate

See the Migrations page for the full Drizzle migration workflow. Quick reference:

nself db migrate generate --name <name>   # Generate SQL from schema diff
nself db migrate up                        # Apply all pending
nself db migrate up --to 0003             # Apply up to version
nself db migrate down                      # Roll back last
nself db migrate status                    # Applied vs pending
nself db migrate verify-checksums          # Tamper detection
nself db migrate diff                      # Drift detection
nself db migrate push                      # Dev-only: push without file
nself db migrate reset                     # Dev-only: drop + reapply all

nself db seed

# Apply all seed files in order
nself db seed

# Apply specific seed file
nself db seed --file seeds/001_roles.sql
nself db seed --file seeds/002_default_config.ts

# Apply Hasura-format seed (used by nself db hasura seed)
nself db hasura seed apply --file 008_owner_license.sql

# Seed with environment override
nself db seed --env staging

# List available seed files
nself db seed --list

Seed File Conventions

# Naming: NNN_description.{sql,ts}
# NNN is a 3-digit sequence number for ordering
seeds/
  001_roles.sql
  002_default_config.sql
  003_admin_user.ts          # TypeScript seeds supported
  008_owner_license.sql      # Hasura-format seed

-- seeds/001_roles.sql
INSERT INTO np_roles (id, name, permissions)
VALUES
  ('admin',  'Administrator', '["*"]'),
  ('user',   'User',          '["read","write"]'),
  ('viewer', 'Viewer',        '["read"]')
ON CONFLICT (id) DO NOTHING;

nself db mock

# Generate mock users
nself db mock --table np_users --count 100

# Generate mock documents linked to existing users
nself db mock --table np_documents --count 1000 --fk user_id:np_users

# Generate mock data with custom locale
nself db mock --table np_users --count 50 --locale ar

# Mock with fixed seed (reproducible data)
nself db mock --table np_users --count 100 --seed 42

# Clear mock data (only rows with mock=true flag)
nself db mock --table np_users --clear

nself db backup

# Default: timestamped dump in ./backups/
nself db backup

# Custom output path
nself db backup --output /var/backups/nself-$(date +%Y%m%d).dump

# Format options
nself db backup --format custom     # Default (pg_dump -Fc — compressed, fast)
nself db backup --format tar        # Tar format (parallel restore)
nself db backup --format plain      # Plain SQL (human-readable)

# Ship directly to S3 / MinIO
nself db backup --dest s3://my-bucket/backups/
nself db backup --dest minio://nself-backups/

# Backup specific tables only
nself db backup --tables np_users,np_documents

# Target non-default environment
nself db backup --env prod

# Verify backup integrity after creating
nself db backup --verify

Automated Backups

# Configure scheduled backups via nself
nself db backup schedule --cron "0 2 * * *" --dest s3://my-bucket/backups/
nself db backup schedule --list
nself db backup schedule --disable

# Or via pg_cron directly:
SELECT cron.schedule('daily-backup', '0 2 * * *',
  $$SELECT pg_notify('nself_backup', '{}')$$);

Backup Contents

# See what's in a backup before restoring
nself db backup --inspect backup-2026-05-07.dump

# Output:
# Backup: backup-2026-05-07T02:00:00Z.dump
# Size: 847 MB (compressed)
# Format: custom
# Tables: 42
# Rows: ~12M
# Created: 2026-05-07 02:00:05 UTC
# PostgreSQL version: 16.2

nself db restore

# Restore latest local backup
nself db restore

# Restore specific file
nself db restore --file backup-2026-05-07.dump

# Restore from S3
nself db restore --source s3://my-bucket/backups/backup-2026-05-07.dump

# Point-in-time recovery (requires WAL archiving enabled)
nself db restore --point-in-time "2026-05-07 14:30:00 UTC"

# Restore to a different database (for blue/green migrations)
nself db restore --file backup.dump --target-db nself_restore

# Restore only specific tables
nself db restore --file backup.dump --tables np_users,np_documents

# Dry run (shows restore plan without executing)
nself db restore --file backup.dump --dry-run

nself db schema

# Show current database schema
nself db schema

# Show schema for specific tables
nself db schema --table np_users
nself db schema --table np_users --include-indexes --include-triggers

# Show schema diff between code (Drizzle) and database
nself db schema diff

# Export schema as SQL
nself db schema --export schema.sql

# Schema in JSON format (for tooling)
nself db schema --format json > schema.json

# Sample nself db schema output:
Table: np_users
  id                UUID         PK NOT NULL  DEFAULT gen_random_uuid()
  source_account_id TEXT         NOT NULL     DEFAULT 'primary'
  tenant_id         UUID         NULLABLE
  email             TEXT         NOT NULL
  created_at        TIMESTAMPTZ  NOT NULL     DEFAULT NOW()
  updated_at        TIMESTAMPTZ  NOT NULL     DEFAULT NOW()
  deleted_at        TIMESTAMPTZ  NULLABLE

Indexes:
  np_users_pkey          PRIMARY KEY (id)
  idx_np_users_email     UNIQUE (email, source_account_id)
  idx_np_users_active    (id) WHERE deleted_at IS NULL

Triggers:
  np_users_updated_at    BEFORE UPDATE

RLS:
  Enabled: YES
  Policies: source_account_isolation, hide_deleted

nself db types

Generate TypeScript types from the live database schema. Keeps your types in sync with the database.

# Generate types to default location (src/db/types.ts)
nself db types

# Custom output
nself db types --output src/generated/db-types.ts

# Generate only for specific tables
nself db types --tables np_users,np_documents

# Watch mode (regenerate on schema change)
nself db types --watch

// Generated: src/db/types.ts
export type NpUser = {
  id: string
  sourceAccountId: string
  tenantId: string | null
  email: string
  createdAt: Date
  updatedAt: Date
  deletedAt: Date | null
}

export type NpDocument = {
  id: string
  sourceAccountId: string
  tenantId: string | null
  userId: string
  title: string
  body: string | null
  createdAt: Date
  updatedAt: Date
  deletedAt: Date | null
}

nself db shell

# Open interactive psql shell (dev env)
nself db shell

# Target specific environment
nself db shell --env staging
nself db shell --env prod

# Run as specific Postgres role
nself db shell --role admin

# Non-interactive: pipe SQL
echo "SELECT count(*) FROM np_users;" | nself db shell

# Connect to a specific database
nself db shell --database nself_restore

Useful psql Commands

# Inside the psql shell:
\dt np_*           -- list np_* tables
\d np_users        -- describe table
\di np_*           -- list indexes
\dp np_users       -- show permissions
\timing            -- show query timing
\x                 -- toggle expanded output
\copy (SELECT ...) TO '/tmp/export.csv' WITH CSV HEADER
\q                 -- quit

nself db query

# Run a query (returns JSON by default)
nself db query "SELECT id, email FROM np_users LIMIT 5;"

# Table format
nself db query "SELECT * FROM np_users LIMIT 5;" --format table

# CSV format
nself db query "SELECT * FROM np_users;" --format csv --output users.csv

# Count rows
nself db query "SELECT count(*) FROM np_users WHERE deleted_at IS NULL;"

# Against staging
nself db query --env staging "SELECT count(*) FROM np_users;"

# Pass parameters safely
nself db query "SELECT * FROM np_users WHERE id = $1" --params '["uuid-here"]'

nself db hasura

# Export current Hasura metadata to disk
nself db hasura metadata export

# Apply metadata from disk (after manual edits)
nself db hasura metadata import

# Check metadata consistency
nself db hasura metadata status

# Apply a seed in Hasura SQL format
nself db hasura seed apply --file 008_owner_license.sql

# Reload Hasura metadata (after direct DB changes)
nself db hasura metadata reload

# Console (opens Hasura admin UI)
nself db hasura console

Health Checks and Diagnostics

# Full DB health check
nself doctor --check DB

# Checks:
# - PostgreSQL reachable
# - Hasura reachable
# - Migration checksums valid
# - No schema drift
# - RLS enabled on np_* tables
# - FK indexes present
# - Connection pool not exhausted

# Check connection pool
nself db status --pool

# Show long-running queries (>5s)
nself db query --env prod   "SELECT pid, now() - query_start AS duration, query
   FROM pg_stat_activity
   WHERE state = 'active' AND now() - query_start > interval '5s'
   ORDER BY duration DESC;"

# Kill a long-running query
nself db query --env prod "SELECT pg_cancel_backend(PID_HERE);"
# Or hard kill:
nself db query --env prod "SELECT pg_terminate_backend(PID_HERE);"  

Environment Targeting

# Switch active environment
nself env switch dev
nself env switch staging
nself env switch prod

# Or use --env flag on any command
nself db backup --env prod
nself db migrate status --env staging
nself db query --env prod "SELECT count(*) FROM np_users;"

# Show current environment
nself env show

Best Practices

• Automate backups — schedule daily backups with nself db backup schedule and ship to S3. Never rely on manual backups.
• Verify backup integrity weekly — run nself db restore --file latest.dump --target-db nself_verify --dry-run to confirm restores work.
• Run nself doctor --check DB before every deploy — catches drift, missing RLS policies, and checksum violations.
• Use --dry-run before production restores — shows the exact tables that will be dropped and recreated.
• Never run raw SQL mutations in production without a transaction — wrap multi-step changes in BEGIN; ... COMMIT;.
• Generate types after every migration — keeps TypeScript in sync with the database automatically.
• Use ON CONFLICT DO NOTHING in seed files — makes seeds idempotent and safe to re-run.

Related

• Migrations — Full Drizzle migration workflow
• PostgreSQL — Extensions, RLS, PITR, replication
• Schema Design — np_* conventions, soft-delete, audit log
• Hasura — Metadata management, permissions