DOCSEARCHbyAlgolia
Submit Docs

pg-schema-diff

repository·main·Indexed 21 days ago

https://github.com/stripe/pg-schema-diff

A tool for computing differences between Postgres database schemas and generating SQL migration plans. It focuses on minimal downtime and locks by utilizing native Postgres features like concurrent index builds and invalid constraint validation. The CLI provides commands to plan migrations, apply changes to databases, and dump schemas to SQL DDL statements. It can be used as a standalone CLI tool or as a Go library.

Tokens
4.5K
Snippets
16
Records
23
Agent score
75%

What's inside pg-schema-diff

  1. Control Go return types using sqlc query annotations

    main

    The sqlc tool determines the return type of the generated Go methods based on the suffix annotation used in your SQL statements within queries.sql. Use these suffixes to control what the generated method returns:

    AnnotationReturn Type (Go)Description
    :execerrorIndicates only whether the query succeeded.
    :execrows(int64, error)Returns the number of rows affected and an error.
    :one(Struct, error)Returns a single struct instance and an error.
    :many([]Struct, error)Returns a slice of structs and an error.

    Example of how annotations affect the generated signature:

    • :one $\rightarrow$ (Author, error)
    • :many $\rightarrow$ ([]Author, error)
    -- Example usage in queries.sql
-- Get a single author
-- name: GetAuthor :one
SELECT * FROM authors WHERE id = $1 LIMIT 1;

-- Get many authors
-- name: ListAuthors :many
SELECT * FROM authors;

  2. How pg-schema-diff achieves online migrations

    main

    The tool uses native Postgres operations to minimize downtime and locking:

    • Online Index Replacement: When an index is modified, the new version is built (using CREATE INDEX CONCURRENTLY) before the old version is dropped. This ensures queries always have an index backing them.
    • Online Constraint Builds: Constraints (check, foreign key) are added as INVALID first, then validated. This avoids long-lived access-exclusive locks.
    • Online NOT NULL Creation: Uses check constraints to eliminate the need for access-exclusive locks on the table.
    • Prioritized Index Builds: New indexes are always built before old ones are deleted.

  3. Generate Go code from SQL queries using sqlc

    main

    To add new SQL queries to the project and generate the corresponding Go methods, follow these steps:

    1. Add your SQL query or statement to the queries.sql file, following the existing pattern in the file.
    2. Run the following command to trigger code generation:
      make sqlc

    Note: Ensure you use the same version of sqlc specified in build/Dockerfile.codegen to maintain consistency in the generated code.

    make sqlc

  4. Install pg-schema-diff

    main

    You can install the pg-schema-diff CLI using Homebrew or the Go toolchain. For use as a Go library, use go get.

    # Via Brew
brew install pg-schema-diff

# Via Go toolchain
go install github.com/stripe/pg-schema-diff/cmd/pg-schema-diff@latest

# As a Go library
go get -u github.com/stripe/pg-schema-diff@latest

  5. Update an existing database schema using the CLI

    main

    To migrate an existing database to a new schema state, update your local SQL files and run the apply command.

    If the generated migration plan contains hazardous operations (like concurrent index builds), you must explicitly approve them using the --allow-hazards flag followed by the hazard type (e.g., INDEX_BUILD).

    # 1. Update your schema file
echo "CREATE INDEX message_idx ON bar(message)" >> schema/bar.sql

# 2. Apply with hazard approval
pg-schema-diff apply --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir schema --allow-hazards INDEX_BUILD

  6. Apply a schema to a fresh database using the CLI

    main

    To initialize a new database with a schema defined in a directory of SQL files, use the apply command. You must provide a --from-dsn (the connection string for the target database) and a --to-dir (the directory containing your DDL files).

    Note: Setting the PGPASSWORD environment variable is recommended to avoid putting passwords in the connection string.

    # 1. Prepare schema files
mkdir schema
echo "CREATE TABLE foobar (id int);" > schema/foobar.sql

# 2. Apply to database
pg-schema-diff apply --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir schema

  7. How hazards work in `apply`

    main

    A hazard is a potentially dangerous operation identified during the plan generation phase (e.g., DELETES_DATA or INDEX_BUILD).

    To prevent accidental destructive changes, pg-schema-diff will block the migration if any statement in the plan contains a hazard type that has not been explicitly permitted. You must pass these types to the --allow-hazards flag as a comma-separated list. If a hazard is detected that is not in your allowed list, the command will exit with an error listing the prohibited hazards and the specific statements where they occur.

  8. Apply a migration plan using the Go library

    main

    The pg-schema-diff library does not automatically execute the migration plan; it leaves application to the user. This allows you to implement custom safety measures like session-level advisory locks or manual approval steps.

    Each statement in the plan includes Timeout and LockTimeout values that should be applied to the session before executing the SQL statement.

    for _, stmt := range plan.Statements {
	// Apply statement-level timeouts
	if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION statement_timeout = %d", stmt.Timeout.Milliseconds())); err != nil {
		panic(fmt.Sprintf("setting statement timeout: %s", err))
	}
	if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION lock_timeout = %d", stmt.LockTimeout.Milliseconds())); err != nil {
		panic(fmt.Sprintf("setting lock timeout: %s", err))
	}

	// Execute the migration SQL
	if _, err := conn.ExecContext(ctx, stmt.ToSQL()); err != nil {
		panic(fmt.Sprintf("executing migration statement. the database maybe be in a dirty state: %s: %s", stmt, err))
	}
}

  9. Generate a migration plan using the Go library

    main

    To use pg-schema-diff within a Go application, use the diff.Generate function. This requires a tempdb.Factory (to validate the plan against a temporary database) and sources for both the current database schema and the desired DDL schema.

    Example workflow:

    1. Create a tempdb.OnInstanceFactory to handle temporary database creation for validation.
    2. Call diff.Generate with your connection pool and DDL source.
    3. The returned plan contains the sequence of statements required for the migration.
    // 1. Setup TempDbFactory for validation
tempDbFactory, err := tempdb.NewOnInstanceFactory(ctx, func(ctx context.Context, dbName string) (*sql.DB, error) {
	copiedConfig := connConfig.Copy()
	copiedConfig.Database = dbName
	return openDbWithPgxConfig(copiedConfig)
})
if err != nil {
	panic("Generating the TempDbFactory failed")
}
defer tempDbFactory.Close()

// 2. Generate the migration plan
plan, err := diff.Generate(ctx, diff.DBSchemaSource(connPool), diff.DDLSchemaSource(ddl),
	diff.WithTempDbFactory(tempDbFactory),
	diff.WithDataPackNewTables(),
)
if err != nil {
	panic("Generating the plan failed")
}

  10. Supported Postgres versions and limitations

    main

    Supported Versions

    • Supported: 14, 15, 16, 17
    • Unsupported: <= 13 (use at your own risk)

    Migration Limitations

    • Types: Only enums are currently supported. Other type changes are unsupported.
    • Renaming: The library identifies objects by name. If you rename a table, index, or other object, the tool will treat it as a DROP of the old name and an ADD of the new name rather than a rename operation.

  11. Dump an existing database schema

    main

    If you have an existing database and want to generate the initial set of DDL files to manage it declaratively, use the dump command.

    mkdir -p schema && pg-schema-diff dump --dsn "postgres://postgres:postgres@localhost:5432/postgres" > schema/schema.sql

  12. Configure database connection via CLI flags

    main

    When using the pg-schema-diff CLI, you must provide connection information for the target database. You can do this in two ways:

    1. Using a DSN: Provide a standard PostgreSQL connection string using the --<prefix>dsn flag. You can specify the database password via the PGPASSWORD environment variable.
    2. Using environment variables: Use the --<prefix>empty-dsn flag to connect using an empty DSN, which instructs the tool to rely on standard pq environment variables and defaults.

    Note: The <prefix> depends on the specific command being executed (e.g., plan or apply).

    # Example using a DSN
pg-schema-diff <command> --dsn "postgres://user:pass@localhost:5432/dbname"

# Example using environment variables (empty DSN)
PGPASSWORD=mypassword pg-schema-diff <command> --empty-dsn