Running Versioned SQL Database Migrations in Winter Boot

Winter Boot includes a standalone SQL migration tool that executes plain .sql files against your configured datasources and tracks each applied migration in a winter_migrations table. Version state is determined by the relative file path — if a path has already been recorded in the tracking table, the file is skipped on subsequent runs. This gives you repeatable, idempotent deploys without requiring any migration-specific DSL.

The same runner also applies JSON-based OpenSearch index templates, ISM policies, and index schemas. See OpenSearch Migrations.

Quick Start

Follow these steps to apply your first migration:

Enable migrations in application.yml

Add migrations.enabled: true to the datasource you want to migrate.

application.yml

datasource:
    -   name: defaultdb
        isPrimary: true
        url: "mysql:host=localhost;dbname=myapp"
        username: myuser
        password: mypassword
        migrations:
            enabled: true

Create the migrations directory

Create a sub-folder named after your datasource under your migrations root.

mkdir -p /migrations/defaultdb

Add a SQL migration file

Create /migrations/defaultdb/001-init-schema.sql with your schema statements:

001-init-schema.sql

# Initial schema
CREATE TABLE users (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(100) NOT NULL UNIQUE,
    email      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add search index
CREATE INDEX idx_users_email ON users(email);

Run the migrations

Execute the migration tool from the CLI or using the pre-built PHAR.

php bin/migrate.php -c /path/to/config --sqlPath /path/to/migrations

./winter-migrations-app.phar -c /path/to/config --sqlPath /path/to/migrations

Verify the migration was applied

Query the winter_migrations tracking table to confirm the file was recorded:

SELECT migration_path, executed_at FROM winter_migrations;
-- defaultdb/001-init-schema.sql  |  2026-09-01 11:14:49

Configuration

Standalone Datasource

Enable migrations on any datasource by setting migrations.enabled: true. You can enable it on multiple datasources simultaneously — each datasource’s folder is migrated independently.

application.yml

datasource:
    -   name: defaultdb
        url: "mysql:host=localhost;dbname=myapp"
        username: myuser
        password: mypassword
        migrations:
            enabled: true

    -   name: admindb
        url: "pgsql:host=localhost;dbname=adminapp"
        username: pguser
        password: pgpassword
        migrations:
            enabled: true

Multi-Tenant Datasource

Migrations work with multi-tenant datasources too. The tool runs each SQL file against every tenant returned by TenantDataSourceProvider::getAllTenantIds().

application.yml

multitenant-datasource:
    -   name: tenantdb
        url: "mysql:host=localhost;port=3306"
        providerClass: "App\\Config\\MyTenantDataSourceProvider"
        migrations:
            enabled: true

Native CLI Mode (`useCli`)

By default, the migration tool parses SQL files in PHP and executes each statement individually. For complex SQL files that contain transactions, PL/SQL or T-SQL blocks, stored procedures, triggers, or mixed DDL/DML, set useCli: true to hand the entire file to the database’s native command-line client instead.

application.yml

datasource:
    -   name: defaultdb
        url: "pgsql:host=localhost;dbname=myapp"
        username: postgres
        password: secretpassword
        migrations:
            enabled: true
            useCli: true

When useCli: true is set, Winter Boot selects the appropriate CLI tool based on the DSN scheme:

DSN Scheme	CLI Tool
`pgsql`	`psql`
`mysql`	`mysql`
`sqlite`	`sqlite3`
`oci`	`sqlplus`
`sqlsrv` / `dblib`	`sqlcmd`

When using useCli: true, make sure the corresponding CLI binary (e.g. psql, mysql) is installed on the machine running migrations and is available on the system PATH.

Directory Structure

Organise migration files under a root directory with one sub-folder per datasource name. Files are executed in alphabetical order — use numeric or date-based prefixes to enforce a deterministic sequence.

/migrations/
├── defaultdb/                    # matches datasource name "defaultdb"
│   ├── 001-init-schema.sql
│   ├── 002-add-indexes.sql
│   └── release-1.1/              # optional release sub-folders
│       └── 003-add-audit-cols.sql
├── admindb/                      # matches datasource name "admindb"
│   └── 001-init-admin-schema.sql
└── tenantdb/                     # matches multi-tenant datasource name "tenantdb"
    ├── 001-tenant-schema.sql
    └── 002-tenant-seed-data.sql

Each top-level sub-folder must exactly match the datasource name in application.yml. The match is case-sensitive on Linux. SQL files must use the .sql extension. Sub-folders for release-based organisation are supported and scanned recursively.

The winter_migrations Tracking Table

The framework automatically creates a winter_migrations table the first time it runs against a datasource. Each successfully executed migration is recorded by its relative path from the migrations root (e.g. defaultdb/001-init-schema.sql).

-- Generic / SQLite
CREATE TABLE winter_migrations (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    migration_path VARCHAR(512) NOT NULL UNIQUE,
    executed_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    executed_by    VARCHAR(100)
);

On subsequent runs, the tool queries COUNT(*) WHERE migration_path = ?. Any file that already has a row is skipped entirely, making every run idempotent.

SQL File Format

Each file may contain one or more SQL statements. The parser supports both # and -- style line comments and ignores blank lines. Every statement must be terminated with a semicolon (;).

orders.sql

# Create the orders table
CREATE TABLE orders (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer   VARCHAR(200) NOT NULL,
    amount     DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    status     VARCHAR(50)  NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add a composite index for reporting queries
CREATE INDEX idx_orders_status_created
    ON orders(status, created_at);

CLI Reference

php bin/migrate.php \
  -c /path/to/config \
  --sqlPath /path/to/migrations

Flag	Description
`-c`	Path to the directory containing `application.yml`
`--sqlPath`	Root path of the migrations directory tree

Building the PHAR

Build a self-contained PHAR for use in Docker images or CI pipelines:

cd build/sqlmigrator
./build.sh
# Output: build/sqlmigrator/target/winter-migrations-app.phar

Kubernetes Init Container

Run migrations as an init container so your schema is always up-to-date before the main application pod starts:

pod.yaml

apiVersion: v1
kind: Pod
metadata:
  name: my-app
spec:
  initContainers:
    - name: sql-migrations
      image: my-app:latest
      command: ["/winter-migrations-app.phar"]
      args:
        - "-c"
        - "$(CONFIG_DIR)"
        - "--sqlPath"
        - "$(SQL_MIGRATION_PATH)"
      env:
        - name: SQL_MIGRATION_PATH
          value: /migrations
        - name: CONFIG_DIR
          value: /app/config

Baking winter-migrations-app.phar directly into your application image means migrations and the application always share the same versioned artifact, eliminating drift between schema and code.

Migration Execution Flow

Understanding the exact sequence helps you predict behaviour and debug failures:

Load configuration

Read application.yml and collect all datasources with migrations.enabled: true.

Locate SQL folder

For each datasource, resolve the SQL folder at {sqlBasePath}/{datasource-name}/.

Scan and sort

Recursively scan for .sql files; sort alphabetically within each directory level.

Enumerate tenants (multi-tenant only)

For multi-tenant datasources, retrieve all tenant IDs from TenantDataSourceProvider::getAllTenantIds().

Check tracking table

For each file (and each tenant, if multi-tenant): query winter_migrations to check whether the file has already been applied.

Execute new migrations

If not yet recorded, execute the file using PHP parser mode or native CLI mode (useCli: true).

Record success

Insert a row into winter_migrations on successful execution.

Halt on failure

Stop immediately on the first failure. No automatic rollback is performed — you must fix the failing statement and re-run.

Troubleshooting

"SQL folder not found" error

The directory under --sqlPath does not contain a sub-folder that exactly matches the datasource name. Verify your folder structure is {sqlPath}/{datasource-name}/ and confirm the datasource name in application.yml exactly matches the folder name — the comparison is case-sensitive on Linux.

Migration not executing (file silently skipped)

Check that migrations.enabled: true is present in application.yml for the target datasource, and that the file has a .sql extension. Then query the tracking table:

SELECT * FROM winter_migrations WHERE migration_path LIKE '%your-file%';

If the file has already been recorded and you need to re-run it, delete its row from winter_migrations. Use this with care in production environments.

Native CLI tool not found (useCli: true)

Install the appropriate client package and confirm the binary is on the system PATH:

which psql    # PostgreSQL
which mysql   # MySQL / MariaDB
which sqlite3 # SQLite

Migration fails mid-way

Fix the failing SQL statement and manually roll back or compensate any partial changes. Re-run the migration tool — files already recorded in winter_migrations are skipped, so only the failed (and unrecorded) file will be re-attempted.

Getting Started

Core Concepts

Web & REST

Data

Async & Concurrency

Operations

Building Applications

Running Versioned SQL Database Migrations in Winter Boot

Quick Start

Configuration

Standalone Datasource

Multi-Tenant Datasource

Native CLI Mode (`useCli`)

Directory Structure

The winter_migrations Tracking Table

SQL File Format

CLI Reference

Building the PHAR

Kubernetes Init Container

Migration Execution Flow

Troubleshooting

​Quick Start

​Configuration

​Standalone Datasource

​Multi-Tenant Datasource

​Native CLI Mode (useCli)

​Directory Structure

​The winter_migrations Tracking Table

​SQL File Format

​CLI Reference

​Building the PHAR

​Kubernetes Init Container

​Migration Execution Flow

​Troubleshooting

Quick Start

Configuration

Standalone Datasource

Multi-Tenant Datasource

Native CLI Mode (`useCli`)

Directory Structure

The winter_migrations Tracking Table

SQL File Format

CLI Reference

Building the PHAR

Kubernetes Init Container

Migration Execution Flow

Troubleshooting