Perf spanner migrations

Performance Dashboard (perf) Cloud Spanner Schema Migrations

Use this skill when modifying the database schema, adding new database tables, writing SQL migrations, or regenerating Go/JSON schema target files in perf/go/sql.

Workflow for Schema Changes

When modifying the database schema, follow these sequential steps:

1. Create a New Migration File

Add a new sequence-numbered SQL file inside perf/go/sql/expectedschema/migrations/:

• Format: 000X_description.sql (e.g., 0004_add_metadata_column.sql).
• Sequence numbers must be strictly sequential (no gaps or duplicate versions).
• Cloud Spanner has strict schema modification constraints (e.g., you cannot change a column's type to ARRAY or to incompatible types directly; add a new column instead).

> [!IMPORTANT] > Write Idempotent DDL Queries

> In Spanner, DDL queries are non-transactional. This means DDL statements (like CREATE TABLE or ALTER TABLE) and updating the schema_migrations tracking table are not atomic. If the program crashes or is interrupted after the DDL completes but before the version is recorded, the maintenance runner will try to rerun the DDL on the next execution, resulting in errors (e.g., "Duplicate name in schema").

>

> Always write DDL queries in an idempotent way so they are safe to rerun:

>

> `sql

> -- Example: Create Table

> CREATE TABLE IF NOT EXISTS dummytable (

> id TEXT PRIMARY KEY,

> dummy_value TEXT

> );

>

> -- Example: Add Column

> ALTER TABLE dummytable ADD COLUMN IF NOT EXISTS extra_value TEXT;

> `

2. Update Table Structs

Modify the Go structs representing the table layouts in tables.go to match the new schema layout.

3. Regenerate the Go Schema File

Regenerate schema_spanner.go from the structs:

cd perf/go/sql
go generate

_(This executes //perf/go/sql/tosql under the hood to write the target SQL declarations)._

4. Regenerate the expected JSON Catalog Description

Regenerate schema_spanner.json:

> [!IMPORTANT]

> Because Bazel runs commands inside sandboxed output directories, passing a relative path to --out will write the file into Bazel's output cache instead of your actual workspace.

> Always use an absolute path (e.g. $(pwd)/... or $(git rev-parse --show-toplevel)/...) for the output file:

cd perf
bazelisk run --config=mayberemote //perf/go/sql/exportschema -- \
    --out $(pwd)/go/sql/expectedschema/schema_spanner.json \
    --databaseType spanner

5. Verify and Run Unit Tests

To run database tests locally against the emulator:

• Launch a clean Spanner Emulator instance:

   make -C perf run-spanner-emulator

• Export the connection environment variables:

   export PGADAPTER_HOST=localhost:5432
   export SPANNER_EMULATOR_HOST=localhost:9010

• Run the SQL package tests:

   bazelisk test //perf/go/sql/...