Running Versioned SQL Database Migrations in Winter Boot

Winter Boot includes a migration runner that executes plain .sql files against your configured datasources and records each applied file in a winter_migrations table. A file is identified by its relative path: once recorded, it is skipped on later runs. Deploys are repeatable and idempotent, with no migration DSL to learn.

Tip

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:

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

2. Create the migrations directory

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

   mkdir -p /migrations/defaultdb

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

4. Create a migration entry point

   Add a small runner script to your project. It uses WinterMigrationApplication, which loads your config, runs the migrations, and exits:

   bin/migrate.php

   <?php
   declare(strict_types=1);
   
   use dev\winterframework\core\app\WinterMigrationApplication;
   use dev\winterframework\stereotype\WinterBootApplication;
   
   require_once dirname(__DIR__) . '/vendor/autoload.php';
   
   #[WinterBootApplication(
       configDirectory: [__DIR__ . '/../config'],
       scanNamespaces: [['com\\example\\myapp', __DIR__ . '/../src']]
   )]
   class MigrationApplication {
       public static function main(): void {
           (new WinterMigrationApplication())->run(self::class);
       }
   }
   
   MigrationApplication::main();

   Scanning your own namespace matters for multi-tenant setups, where the runner needs your TenantDataSourceProvider class.

5. Run the migrations

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

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

Each tool is run so that the first SQL error stops the file and fails the migration: psql -v ON_ERROR_STOP=1, sqlcmd -b, and WHENEVER SQLERROR EXIT for sqlplus. Without these, the tools exit successfully after errors and a broken file would be recorded as applied.

Caution

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. The suvera/winter-boot Docker image ships psql; suvera/winter-boot-mysql ships mysql.

---

Directory Structure

Organize migration files under a root directory with one sub-folder per datasource name. Files are executed in natural order of their relative path, so version folders run as 1.9.0/ before 1.10.0/ — 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

Note

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 organization 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 \
  --configDir /path/to/config \
  --sqlPath /path/to/migrations

Flag	Description
--configDir, -c	Directory containing application.yml (required)
--sqlPath	Root of the migrations directory tree (required)
--migrationType, -m	sql (default) or opensearch — see OpenSearch Migrations
--version, -v	Print the application version and exit

Packaging as a PHAR

To ship the runner in a Docker image or CI pipeline, package bin/migrate.php as a PHAR with Box, exactly as described in Build & Deploy (set "main": "bin/migrate.php" and a different output, e.g. target/migrations.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: ["php", "/app/target/migrations.phar"]
      args:
        - "--configDir"
        - "$(CONFIG_DIR)"
        - "--sqlPath"
        - "$(SQL_MIGRATION_PATH)"
      env:
        - name: SQL_MIGRATION_PATH
          value: /migrations
        - name: CONFIG_DIR
          value: /app/config

Tip

Baking the migrations PHAR into your application image means migrations and the application always ship as the same versioned artifact, so schema and code cannot drift apart.

---

Migration Execution Flow

Knowing the exact sequence helps you predict behavior and debug failures:

1. Load configuration

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

2. Locate SQL folder

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

3. Scan and sort

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

4. Enumerate tenants (multi-tenant only)

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

5. Check tracking table

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

6. Execute new migrations

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

7. Record success

   Insert a row into winter_migrations on successful execution.

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