Google Cloud Spanner Migrations Runner

Common errors

• Error: Migration file '<filename>' does not match expected pattern.

  cause The name of a `.sql` migration file does not conform to the required `[0-9]{5}[a-z\-]{0,256}.sql` format.
  fix Rename your migration `.sql` files to follow the pattern, for example: `00001_create_users_table.sql`.

• Error: Migration '<filename>' contains multiple types of statements. Each migration must contain only one type.

  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).
  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.

• Error running Spanner migrations: RpcError: 9 FAILED_PRECONDITION: Row '...' in table '...' was already applied.

  cause An attempt was made to apply a migration that has already been recorded as applied in the internal migrations tracking table.
  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.

• WARNING: Migration '<filename>' ignored in emulator due to unsupported feature.

  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.
  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.

Warnings

• gotcha 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.
  Fix: Ensure all SQL statements in migration files are terminated with a semicolon, even if your SQL client typically infers them.
• gotcha 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.
  Fix: Split migrations that combine different statement types (like `CREATE TABLE` and `INSERT`) into separate `.sql` files, each dedicated to a single statement type.
• gotcha 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.
  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.
• gotcha 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.
  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.
• gotcha 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.
  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.

Install

• npm install google-spanner-migrations-runner npm
• yarn add google-spanner-migrations-runner yarn
• pnpm add google-spanner-migrations-runner pnpm

Imports

• SpannerMigration
  wrong:

  const SpannerMigration = require('google-spanner-migrations-runner');

  correct:

  import { SpannerMigration } from 'google-spanner-migrations-runner';

  Primarily designed for ESM usage with Node.js 14+, though CommonJS require may work with transpilation.
• MigrationConfig

  import type { MigrationConfig } from 'google-spanner-migrations-runner';

  Importing the type definition for configuration options when using TypeScript.
• runMigrations

  import { SpannerMigration } from 'google-spanner-migrations-runner';
  const runner = new SpannerMigration(config);
  await runner.runMigrations();

  The core method to execute the migrations. It's a method of the SpannerMigration class, not a direct export.

Quickstart

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.

import { SpannerMigration } from 'google-spanner-migrations-runner';

// A minimal example demonstrating how to programmatically run migrations.
// Ensure you have a 'migrations' directory with .sql files in your project root.
async function main() {
  // Configure Spanner connection and optional environment for annotations.
  const config = {
    projectId: process.env.SPANNER_PROJECT_ID ?? 'your-gcp-project-id',
    instanceId: process.env.SPANNER_INSTANCE_ID ?? 'your-spanner-instance-id',
    databaseId: process.env.SPANNER_DATABASE_ID ?? 'your-spanner-database-id',
    migrationsDir: './migrations', // Path to your SQL migration files
    env: process.env.NODE_ENV ?? 'development' // Optional, for @only-in-env annotations
  };

  // Basic validation for demonstration. In a real app, use proper error handling.
  if (!config.projectId || !config.instanceId || !config.databaseId) {
    console.error('Missing Spanner configuration. Set SPANNER_PROJECT_ID, SPANNER_INSTANCE_ID, SPANNER_DATABASE_ID environment variables.');
    process.exit(1);
  }

  try {
    const runner = new SpannerMigration(config);
    console.log('Starting Spanner migrations...');
    await runner.runMigrations();
    console.log('Spanner migrations completed successfully.');
  } catch (error) {
    console.error('Error running Spanner migrations:', error);
    process.exit(1);
  }
}

main();

// Example content for 'migrations/00001_initial_schema.sql':
// CREATE TABLE users (
//   id INT64 NOT NULL,
//   name STRING(MAX)
// ) PRIMARY KEY (id);