Database Connections

Spawn requires a database connection to apply migrations and run tests. Database connections are configured in your spawn.toml file under the [targets] section. See the configuration reference for the full list of target fields.

Basic Configuration

Each target configuration requires:

• engine: The database engine type (currently only "postgres-psql")
• spawn_database: The database where spawn stores migration tracking (defaults to using whichever database your command connects to by default). This database must already exist.
• spawn_schema: The schema where spawn stores migration tracking (default: "_spawn"). This schema will be created if it does not exist.
• environment: Environment name (e.g., "dev", "prod")
• command: How to execute SQL commands (see below)

Command Configuration

The command field specifies how spawn should execute SQL against your database. There are two modes:

1. Direct Commands

Use a direct command when you have a straightforward way to connect to your database.

[targets.local]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "dev"
command = { kind = "direct", direct = ["psql", "-U", "postgres", "myapp"] }

Docker Example

For databases running in Docker:

[targets.docker_local]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "dev"
command = {
    kind = "direct",
    direct = [
        "docker", "exec", "-i", "myapp-db",
        "psql", "-U", "postgres", "myapp"
    ]
}

2. Provider Commands (Dynamic)

Provider commands are useful when the connection details need to be resolved dynamically, such as with cloud providers where connection setup is slow but the underlying connection is fast.

[targets.staging]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "staging"
command = {
    kind = "provider",
    provider = [
        "gcloud", "compute", "ssh",
        "--zone", "us-central1-a",
        "my-sql-proxy-vm",
        "--project", "my-project",
        "--dry-run"
    ],
    append = ["-T", "sudo", "-u", "postgres", "psql", "myapp"]
}

The provider array specifies a command that outputs a shell command string to run. The append array contains additional arguments to append to the resolved command.

Google Cloud SQL via SSH

The Problem with Direct gcloud SSH

When connecting to Google Cloud SQL instances via SSH, using gcloud compute ssh directly works but is significantly slower because gcloud must.

This overhead happens every time spawn executes SQL, making migrations and tests much slower.

Solution: Using the Provider Pattern

The provider pattern resolves the SSH command once using gcloud compute ssh --dry-run, then reuses the underlying SSH command directly for all subsequent operations.

Method 1: Test the Direct Approach (Slow)

You can use the gcloud command directly, but this will be called multiple times during a single spawn migration apply command.

[targets.staging_slow]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "staging"
command = {
    kind = "direct",
    direct = [
        "gcloud", "compute", "ssh",
        "--zone", "us-central1-a",
        "my-sql-proxy-vm",
        "--project", "my-project",
        "--",
        "-T",
        "sudo", "-u", "postgres",
        "psql", "myapp"
    ]
}

Method 2: Use the Provider Pattern (Fast)

You can also use gcloud to provide the underlying ssh command needed to connect, which will be resolved just once, and then every connection to the database will use the provided ssh command directly.

[targets.staging]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "staging"
command = {
    kind = "provider",
    provider = [
        "gcloud", "compute", "ssh",
        "--zone", "us-central1-a",
        "my-sql-proxy-vm",
        "--project", "my-project",
        "--dry-run"
    ],
    append = ["-T", "sudo", "-u", "postgres", "psql", "myapp"]
}

How It Works

1. Provider Resolution: When spawn initializes, it runs the provider command:

   gcloud compute ssh --zone us-central1-a my-sql-proxy-vm --project my-project --dry-run

2. Parse Output: Spawn parses the command output (gcloud outputs the underlying SSH command):

   ssh -t -i /path/to/key -o StrictHostKeyChecking=no user@ip

3. Append Extra Arguments: Spawn combines the resolved SSH command with the append args:

   ssh -t -i /path/to/key -o StrictHostKeyChecking=no user@ip -T sudo -u postgres psql myapp

4. Reuse: This final command is cached and reused for all SQL operations in that spawn session.

Avoiding Repeated SSH Passphrase Prompts

When using SSH keys with a passphrase, each connection to the database will prompt for the passphrase. Since spawn opens multiple SSH sessions during a single apply run, this gets tedious quickly.

To avoid this, add your key to the SSH agent before running spawn:

# macOS
ssh-add ~/.ssh/your_key

# Linux
eval "$(ssh-agent -s)"
ssh-add ~/.ssh/your_key

Complete Example: Multiple Environments

spawn.toml

spawn_folder = "spawn"
target = "local"  # Default target

# Local development (Docker)
[targets.local]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "dev"
command = {
    kind = "direct",
    direct = ["docker", "exec", "-i", "myapp-db", "psql", "-U", "postgres", "myapp"]
}

# Staging (Google Cloud via provider - fast!)
[targets.staging]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "staging"
command = {
    kind = "provider",
    provider = [
        "gcloud", "compute", "ssh",
        "--zone", "us-central1-a",
        "myapp-staging-proxy",
        "--project", "myapp-staging",
        "--dry-run"
    ],
    append = ["-T", "sudo", "-u", "postgres", "psql", "myapp"]
}

# Production (Google Cloud via provider - fast!)
[targets.production]
engine = "postgres-psql"
spawn_database = "myapp"
spawn_schema = "_spawn"
environment = "prod"
command = {
    kind = "provider",
    provider = [
        "gcloud", "compute", "ssh",
        "--zone", "us-east1-b",
        "myapp-prod-proxy",
        "--project", "myapp-production",
        "--dry-run"
    ],
    append = ["-T", "sudo", "-u", "postgres", "psql", "myapp"]
}

Usage:

# Use local target (default)
spawn migration apply <migration name>

# Use staging target
spawn --target staging migration apply <migration name>

# Use production target
spawn --target production migration apply <migration name>

Advanced Configuration

Multiple Targets

You can configure multiple targets and switch between them:

# Use specific target
spawn --target staging migration build my-migration

# Override in environment variable
export SPAWN_TARGET=production
spawn migration apply

Environment-Specific Templating

The environment field is available in your migration templates:

BEGIN;

{% if env == "dev" %}
-- Insert test data only in dev
INSERT INTO users (email) VALUES ('test@example.com');
{% endif %}

COMMIT;

Security Considerations

• Never commit sensitive credentials to your spawn.toml
• Use environment variables or cloud authentication (like gcloud) instead of passwords in connection strings
• For production databases, consider using read-only connections for migration builds/tests
• The spawn_schema should have appropriate permissions for the user executing migrations