Skip to content

CLI

SQLSpec installs a sqlspec command-line interface (powered by rich-click) for inspecting adapter configurations, managing schema migrations, and maintaining Google ADK memory tables.

See CLI Reference for the complete generated option and argument tables across every command.

Configuration Discovery

The sqlspec CLI resolves database configurations in priority order:

  1. Explicit --config flag: uv run sqlspec --config app.db:get_configs show-config
  2. Environment variable: SQLSPEC_CONFIG="app.db:get_configs"
  3. pyproject.toml [tool.sqlspec] config entry:
[tool.sqlspec]
config = "app.db:database_config"

Comma-separated dotted paths (--config app.oltp:config,app.analytics:config) are loaded from the current working directory and deduplicated by bind_key. Pass --validate-config to verify config loading and print whether each target runs synchronously or asynchronously.

Global CLI Flags

Flag Environment Variable Purpose
--config TEXT SQLSPEC_CONFIG Dotted import path to a config attribute, sequence of configs, or zero-argument config factory callable.
--validate-config — Load and validate resolved configurations before executing the requested subcommand.
--bind-key TEXT — Restrict command execution to the single configuration matching bind_key.
--include TEXT — Repeatable allowlist of bind_key targets when managing multiple databases.
--exclude TEXT — Repeatable denylist of bind_key targets to skip during multi-database runs.
--dry-run — Preview pending SQL or revision operations without mutating schema state.
--no-prompt — Skip interactive confirmation prompts in CI/CD pipelines and automated scripts.
--use-logger — Emit structured Python logging records instead of Rich interactive console output.

Core Migration Workflow

The example below exercises the end-to-end CLI lifecycle (show-config, init, create-migration, upgrade, show-current-revision) programmatically with Click's CliRunner:

docs/examples/quickstart_migrations.py
"""Quickstart example demonstrating the SQLSpec migration CLI workflow."""

import os
import sys
import tempfile
from pathlib import Path

from click.testing import CliRunner

from sqlspec.cli import add_migration_commands

__all__ = ("main",)


def main() -> None:
    """Execute the standard migration workflow commands against a temporary SQLite database."""
    config_example = Path(__file__).with_name("migration_quickstart_config.py")
    runner = CliRunner()
    with tempfile.TemporaryDirectory() as temp_dir:
        temp_path = Path(temp_dir)
        (temp_path / "database.py").write_text(config_example.read_text(encoding="utf-8"), encoding="utf-8")
        old_cwd = Path.cwd()
        sys.path.insert(0, str(temp_path))
        os.chdir(temp_path)
        try:
            config_path = "database:database_config"
            commands = (
                ["show-config"],
                ["init", "--no-prompt"],
                ["create-migration", "-m", "create users table", "--no-prompt"],
                ["upgrade", "--no-prompt"],
                ["show-current-revision"],
            )
            for command in commands:
                result = runner.invoke(add_migration_commands(), ["--config", config_path, *command])
                print(result.output, end="")
                if result.exit_code:
                    message = f"Migration command failed: {' '.join(command)}"
                    raise RuntimeError(message) from result.exception
        finally:
            os.chdir(old_cwd)
            if str(temp_path) in sys.path:
                sys.path.remove(str(temp_path))
            sys.modules.pop("database", None)


if __name__ == "__main__":
    main()

From your terminal, run the equivalent commands directly:

uv run sqlspec show-config
uv run sqlspec init --no-prompt
uv run sqlspec create-migration -m "create users table"
uv run sqlspec upgrade --no-prompt
uv run sqlspec show-current-revision

Multi-Database Targeting and Dry Runs

When [tool.sqlspec] returns multiple configs, filter execution by bind_key using --bind-key, --include, or --exclude, and preview pending operations safely with --dry-run:

uv run sqlspec upgrade --bind-key primary --dry-run
uv run sqlspec upgrade --include primary --include analytics --no-prompt
uv run sqlspec downgrade -1 --bind-key analytics --use-logger

Pass --use-logger in CI/CD pipelines to emit structured log records instead of interactive Rich terminal tables.

Revision Maintenance (stamp, fix, and squash)

Command Typical Use Case
sqlspec stamp <revision> Mark an already-provisioned database at <revision> without executing SQL scripts.
sqlspec fix Convert sequential numeric revision filenames to canonical timestamp revisions (or normalize ordering).
sqlspec squash Combine a contiguous revision range into one consolidated migration file (--update-database updates tracker state).

Google ADK Memory Commands

When using sqlspec[adk] (extension_config={"adk": ...}), the sqlspec adk memory command group provides operational checks and retention cleanup:

uv run sqlspec adk memory verify --bind-key agent_db
uv run sqlspec adk memory cleanup --days 30 --bind-key agent_db
  • adk memory verify: Confirms the configured ADK memory table exists and accepts queries.
  • adk memory cleanup --days N: Deletes persisted conversation memory entries older than N days using the adapter's sync or async ADK memory store.

Recap

  • Configure [tool.sqlspec] in pyproject.toml once so developers and CI jobs run uv run sqlspec ... without repeating module paths.
  • Always pass --no-prompt and --use-logger in non-interactive deployment jobs.
  • Consult CLI Reference for every flag, environment variable, and subcommand signature.