{"id":16365,"library":"google-spanner-migrations-runner","title":"Google Cloud Spanner Migrations Runner","description":"The `google-spanner-migrations-runner` package provides a robust engine for managing schema migrations in Google Cloud Spanner databases, supporting both Google-managed instances and the Spanner emulator. Currently at version 1.13.0, the library receives frequent updates, with minor versions and bug fixes typically released every few months. Unlike ORM-based migration tools, this runner operates directly with `.sql` files, emphasizing a 'migrations-as-code' approach where developers write and test their SQL scripts. It does not generate schema from application code. Key functionalities include applying migrations from a designated directory, validating SQL files, and maintaining a ledger of applied migrations to prevent re-execution. Migrations are applied transactionally and in a specific order, making file naming conventions crucial for successful database evolution. It also features optional annotations for environment-specific migration execution.","status":"active","version":"1.13.0","language":"javascript","source_language":"en","source_url":"https://github.com/mdovhopo/google-spanner-migrations-runner","tags":["javascript","spanner","sql","google","google-cloud","database","migration","migrations","typescript"],"install":[{"cmd":"npm install google-spanner-migrations-runner","lang":"bash","label":"npm"},{"cmd":"yarn add google-spanner-migrations-runner","lang":"bash","label":"yarn"},{"cmd":"pnpm add google-spanner-migrations-runner","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Runtime dependency for interacting with Google Cloud Spanner database instances.","package":"@google-cloud/spanner","optional":false}],"imports":[{"note":"Primarily designed for ESM usage with Node.js 14+, though CommonJS require may work with transpilation.","wrong":"const SpannerMigration = require('google-spanner-migrations-runner');","symbol":"SpannerMigration","correct":"import { SpannerMigration } from 'google-spanner-migrations-runner';"},{"note":"Importing the type definition for configuration options when using TypeScript.","symbol":"MigrationConfig","correct":"import type { MigrationConfig } from 'google-spanner-migrations-runner';"},{"note":"The core method to execute the migrations. It's a method of the SpannerMigration class, not a direct export.","symbol":"runMigrations","correct":"import { SpannerMigration } from 'google-spanner-migrations-runner';\nconst runner = new SpannerMigration(config);\nawait runner.runMigrations();"}],"quickstart":{"code":"import { SpannerMigration } from 'google-spanner-migrations-runner';\n\n// A minimal example demonstrating how to programmatically run migrations.\n// Ensure you have a 'migrations' directory with .sql files in your project root.\nasync function main() {\n  // Configure Spanner connection and optional environment for annotations.\n  const config = {\n    projectId: process.env.SPANNER_PROJECT_ID ?? 'your-gcp-project-id',\n    instanceId: process.env.SPANNER_INSTANCE_ID ?? 'your-spanner-instance-id',\n    databaseId: process.env.SPANNER_DATABASE_ID ?? 'your-spanner-database-id',\n    migrationsDir: './migrations', // Path to your SQL migration files\n    env: process.env.NODE_ENV ?? 'development' // Optional, for @only-in-env annotations\n  };\n\n  // Basic validation for demonstration. In a real app, use proper error handling.\n  if (!config.projectId || !config.instanceId || !config.databaseId) {\n    console.error('Missing Spanner configuration. Set SPANNER_PROJECT_ID, SPANNER_INSTANCE_ID, SPANNER_DATABASE_ID environment variables.');\n    process.exit(1);\n  }\n\n  try {\n    const runner = new SpannerMigration(config);\n    console.log('Starting Spanner migrations...');\n    await runner.runMigrations();\n    console.log('Spanner migrations completed successfully.');\n  } catch (error) {\n    console.error('Error running Spanner migrations:', error);\n    process.exit(1);\n  }\n}\n\nmain();\n\n// Example content for 'migrations/00001_initial_schema.sql':\n// CREATE TABLE users (\n//   id INT64 NOT NULL,\n//   name STRING(MAX)\n// ) PRIMARY KEY (id);\n","lang":"typescript","description":"This quickstart demonstrates how to programmatically initialize and execute Spanner database migrations using the `SpannerMigration` class, setting up the necessary configuration for connection and specifying the migrations directory."},"warnings":[{"fix":"Ensure all SQL statements in migration files are terminated with a semicolon, even if your SQL client typically infers them.","message":"Each SQL statement within a migration file must be explicitly separated by a semicolon (`;`). The runner relies on this delimiter to parse and execute individual queries.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Split migrations that combine different statement types (like `CREATE TABLE` and `INSERT`) into separate `.sql` files, each dedicated to a single statement type.","message":"Each migration `.sql` file MUST contain only one type of SQL statements (e.g., all DDL or all DML). Mixing statement types can lead to parsing errors or unexpected behavior.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Plan migration names carefully. If a change is absolutely necessary for an applied migration, create a new migration to reverse or correct the previous one, rather than altering an existing, applied file.","message":"Once a migration is applied, its file name MUST NOT be changed, as the engine tracks applied migrations by name. Similarly, do not modify already applied migration files.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Be aware of Spanner emulator limitations. Test migrations involving advanced features against a real Spanner instance or acknowledge that certain operations might be skipped locally.","message":"Certain Spanner features (e.g., specific index types, some DDL statements) are not fully supported by the Spanner emulator. The runner may ignore these in an emulator environment, logging a warning.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Always provide the correct `--env <env-name>` CLI flag or `config.env` programmatic option when running migrations, especially for environment-specific migrations, to avoid false 'failed' records.","message":"Migrations using the `-- @only-in-env <env>` annotation will result in a 'failed' entry in the migrations table if the runner's `--env` option (or programmatic `config.env`) does not match the specified environment or is omitted, rather than simply being skipped.","severity":"gotcha","affected_versions":">=1.13.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"fix":"Rename your migration `.sql` files to follow the pattern, for example: `00001_create_users_table.sql`.","cause":"The name of a `.sql` migration file does not conform to the required `[0-9]{5}[a-z\\-]{0,256}.sql` format.","error":"Error: Migration file '<filename>' does not match expected pattern."},{"fix":"Separate your migration logic into distinct `.sql` files, ensuring each file contains only one type of SQL operation. For instance, put all schema changes in one file and data inserts in another.","cause":"A single `.sql` migration file includes a mix of different SQL statement types, such as DDL (e.g., CREATE TABLE) and DML (e.g., INSERT INTO).","error":"Error: Migration '<filename>' contains multiple types of statements. Each migration must contain only one type."},{"fix":"This usually indicates a successful prior run. If you intend to run new migrations, ensure new `.sql` files are added or that existing applied migrations were not altered.","cause":"An attempt was made to apply a migration that has already been recorded as applied in the internal migrations tracking table.","error":"Error running Spanner migrations: RpcError: 9 FAILED_PRECONDITION: Row '...' in table '...' was already applied."},{"fix":"This is often expected in emulator environments. Review Spanner emulator documentation for unsupported features or consider testing these migrations against a live Spanner instance. The migration will still apply on a real Spanner database.","cause":"The SQL within the migration file contains statements or features (e.g., specific index types, certain DDL) that are not supported by the Google Cloud Spanner emulator.","error":"WARNING: Migration '<filename>' ignored in emulator due to unsupported feature."}],"ecosystem":"npm"}