CLI Database Operations

Overview

lexigram db manages the full database lifecycle — migrations, seeding, schema inspection, and backup/restore — backed by lexigram-sql's migration manager. All commands accept --env/-e to select the environment.

Migration Lifecycle

lexigram db init                    # Create migrations/ directory (arg: dir name)
lexigram db migrate add_users_table # Generate a new migration file
lexigram db create add_users_table  # New empty migration (-m for description)
lexigram db upgrade                 # Apply pending migrations
lexigram db downgrade               # Roll back the most recent
lexigram db downgrade 0003_seed     # Roll back to a specific version (positional arg)
lexigram db status                  # Current version + pending
lexigram db history --limit 20      # Last N applied migrations (-n)
lexigram db validate                # Check applied migrations have files

Seeding & Reset

lexigram db seed                    # Run seeds/*.py scripts (arg: single file)
lexigram db reset --seed            # Drop + recreate + seed
lexigram db reset --force           # Skip confirmation prompt

Seed scripts live in seeds/*.py.

Inspection & Administration

lexigram db inspect                 # List tables
lexigram db inspect --table users   # One table's columns + types (-t)
lexigram db shell                   # Open a DB client REPL
lexigram db setup                   # Per-package schema setup

Backup & Restore

lexigram db backup --output dump.sql
lexigram db restore dump.sql --force

Config

The SQL config section is sql: (env prefix LEX_SQL__):

sql:
  backend:
    url: postgresql+asyncpg://user:pass@localhost:5432/mydb
  pool:
    min_size: 2
    max_size: 10

Default URL is sqlite:///data.db. Valid URL schemes: sqlite, postgresql/postgres, mysql, mariadb, oracle, mssql. Insecure default credentials are blocked in production.

Common Mistakes

• Running upgrade before db init — the migrations directory must exist
• Running downgrade without a version argument — reverts only the most recent
• Editing applied migration files — write a new migration instead
• reset --force without backup — irreversible data loss
• Using sync driver (psycopg2) in async context — use +asyncpg in the URL