SCM - Schema Change Manager

Plain SQL Migrations for the Databases Everyone Else Ignores

12 min read

#tools, #scm, #databases

Overview

SCM (Schema Change Manager) is a database migration tool built in Python, included free in the WaSQL repo. It does what dbmate and golang-migrate do — plain SQL files, timestamped versions, up/down migrations — and then keeps going. Where those tools stop at four or five databases, SCM ships with built-in drivers for nine, including Oracle, SAP HANA, Snowflake, FairCom cTree, and Firebird. If your database has a Python DB-API 2.0 driver, SCM can be extended to support it in about 20 lines.

The entire tool is a single Python file. No compilation, no binary download, no package manager required beyond the driver for your specific database.

Why SCM Exists

dbmate is excellent — a single binary, zero dependencies, clean SQL files. But it covers PostgreSQL, MySQL, SQLite, and ClickHouse. golang-migrate covers more ground but drops the new command and .env support. Neither handles Oracle, SAP HANA, Snowflake, or the more specialized databases that enterprise and embedded systems rely on.

SCM was built to be the comprehensive option: all the ergonomics of dbmate (.env, new, init, single-file migrations) plus the database breadth that neither of those tools provides, in a single file you already have if you're using WaSQL.

Database Support

Database	URL Scheme	Driver Package
PostgreSQL	`postgres://`	`pip install "psycopg[binary]"` or `psycopg2-binary`
MySQL / MariaDB	`mysql://`	`pip install mysql-connector-python` or `pymysql`
SQL Server	`mssql://`	`pip install pymssql` or `pyodbc`
SQLite	`sqlite:///`	built-in — nothing to install
Oracle	`oracle://`	`pip install oracledb`
SAP HANA	`hana://`	`pip install hdbcli`
Snowflake	`snowflake://`	`pip install snowflake-connector-python`
FairCom cTree	`ctree://`	`pip install pyodbc` + Faircom ODBC Driver
Firebird	`firebird://`	`pip install fdb`

MySQL automatically falls back from mysql-connector-python to pymysql — install whichever you have. PostgreSQL tries psycopg3 first, then psycopg2.

Installation

No installation. SCM is already in the WaSQL repo at python/scm.py. Wrapper scripts handle the rest:

Windows — scm.bat is picked up by CMD and PowerShell automatically:

scm status
scm up

Linux / macOS — make scm.sh executable once:

chmod +x scm.sh

# Optionally put it on your PATH:
ln -s "$(pwd)/scm.sh" /usr/local/bin/scm

Both wrappers delegate to python3 scm.py — every flag and argument passes through unchanged.

Configuration

SCM reads a .env file in the current directory. The minimum required is a DATABASE_URL:

# .env
DATABASE_URL=postgres://user:pass@localhost:5432/mydb
MIGRATION_STYLE=one
# MIGRATIONS_DIR=./migrations
# MIGRATIONS_TABLE=schema_migrations

WaSQL projects get a shortcut. env-from-config reads connection details directly from config.xml and writes the .env for you:

# List available databases in config.xml
scm env-from-config

# Write .env and create migrations folder in one step
scm env-from-config mydb
# Created .env
#   DATABASE_URL=mysql://wasql_dbuser:***@localhost/mydb
#   created migrations/

Connection URL resolution order (first match wins): --url flag → DATABASE_URL environment variable → DATABASE_URL in .env.

Migration File Styles

SCM supports both popular file conventions. Set MIGRATION_STYLE in .env to choose.

Single-file style (dbmate-compatible) — one .sql file with markers:

-- migrate:up
CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    customer   VARCHAR(255) NOT NULL,
    total      DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrate:down
DROP TABLE IF EXISTS orders;

Two-file style (golang-migrate-compatible) — separate .up.sql and .down.sql files:

20240601120000_create_orders.up.sql
20240601120000_create_orders.down.sql

Both styles can coexist in the same migrations directory across different versions. SCM detects the style per file, not per project.

Daily Workflow

# First time setup
scm init                        # create migrations/ and .env stub

# Create and apply
scm new create_orders_table     # generates 20240601120000_create_orders_table.sql
scm up                          # apply all pending migrations
scm up 1                        # apply only the next pending migration
scm up --dry-run                # print SQL without executing it

# Check state
scm status                      # pending vs applied, color-coded
scm history                     # applied migrations with timestamps
scm show 20240601120000         # print SQL for a specific migration

# Roll back
scm down                        # roll back the last migration
scm down 3                      # roll back the last 3 migrations
scm down --dry-run              # print rollback SQL without executing

scm status output is color-coded in terminals: gray for applied, green for pending, yellow for orphaned (applied in the database but the file has since been deleted):

Version          Label                  Status      Down?
---------------------------------------------------------
20240601120000   create_orders_table    applied     yes
20240602094500   add_email_index        applied     yes
20240603110000   add_audit_log          pending     yes

2 applied  1 pending

Power Commands

These go beyond what dbmate and golang-migrate offer.

goto — migrate to a specific version, up or down:

scm goto 20240602094500    # applies or rolls back whatever it takes to land on that version
scm goto 20240602094500 --dry-run

baseline — mark migrations applied without running them. Essential for onboarding an existing database:

scm baseline                  # mark all migrations as applied
scm baseline 20240601120000   # mark up to a specific version

repair — remove orphaned tracking records (versions recorded in the database with no file on disk):

scm repair
# Found 1 orphaned version in schema_migrations with no file on disk:
#   20240530000000
# Remove these from the tracking table? Type 'yes' to confirm:

undo — interactively delete pending (unapplied) migration files:

scm undo
# Pending migrations:
#   1. 20240603110000_add_audit_log
#   2. 20240604083000_drop_legacy_table
#
# Enter number(s) to undo (e.g. 1  or  1,3  or  1-3), blank to cancel: 1
#   Deleted  20240603110000_add_audit_log.sql

learn — print a quick-start reference without needing a database connection:

scm learn

Multi-Database Projects

The --db flag scopes every command to a specific database, automatically resolving the .env file and migrations directory:

scm --db analytics up      # uses .env.analytics and migrations/analytics/
scm --db reporting status  # uses .env.reporting and migrations/reporting/

Set each database up once with env-from-config:

scm env-from-config analytics
scm env-from-config reporting

This produces a clean layout that keeps every database's migration history isolated:

.env.analytics
.env.reporting
migrations/
  analytics/
  reporting/

Adding a Custom Driver

If your database isn't in the built-in list, subclass BaseDriver, implement five methods, and register with a decorator. The new scheme is active immediately — no config changes needed:

@register_driver(['mydb'])
class MyDBDriver(BaseDriver):

    def connect(self):
        import mydb_driver
        p = urlparse(self.url)
        self.conn = mydb_driver.connect(
            host=p.hostname, port=p.port or 5000,
            user=p.username, password=p.password,
            database=p.path.lstrip('/'),
        )

    def ensure_migrations_table(self): ...
    def applied_versions(self): ...
    def record_migration(self, version): ...
    def remove_migration(self, version): ...

Use self.table instead of hardcoding schema_migrations so the MIGRATIONS_TABLE setting is respected.

Command Reference

Command	Purpose
`scm init`	Create migrations directory and `.env` stub
`scm env-from-config [name]`	Pull connection from WaSQL `config.xml`
`scm new <name>`	Create timestamped migration file(s)
`scm up [N]`	Apply all (or N) pending migrations
`scm down [N]`	Roll back N migrations (default: 1)
`scm goto <version>`	Migrate to a specific version
`scm status`	Show applied vs pending
`scm history`	Show applied migrations with timestamps
`scm show <version>`	Print SQL for a specific migration
`scm baseline [version]`	Mark migrations applied without running SQL
`scm repair`	Remove orphaned tracking records
`scm undo`	Interactively delete pending migration files
`scm reset [--force]`	Wipe history and delete all migration files
`scm learn`	Print quick-start reference
`scm version`	Print version and exit

SCM vs. dbmate vs. golang-migrate

Feature	SCM	dbmate	golang-migrate
PostgreSQL / MySQL / SQLite / MSSQL	yes	yes	yes
Oracle	yes	no	no
SAP HANA	yes	no	no
Snowflake	yes	no	no
FairCom cTree	yes	no	no
Firebird	yes	no	no
Single-file migrations	yes	yes	no
Two-file migrations	yes	no	yes
`new` command	yes	yes	no
`.env` support	yes	yes	no
`goto` version	yes	no	yes
`baseline` command	yes	no	no
`repair` command	yes	no	no
`undo` command	yes	no	no
WaSQL `config.xml` integration	yes	no	no
Extensible drivers	yes	no	limited
Runtime	Python + pip driver	single binary	single binary
Cost	free (WaSQL repo)	free	free

The tradeoff is honest: dbmate and golang-migrate ship as a single binary with zero runtime dependencies. SCM requires Python 3.8+ and a pip package for your database driver. If you're already running Python — and in a WaSQL environment you are — that's a non-issue.

AI-Assisted Migrations

SCM pairs exceptionally well with AI coding assistants like Claude, Cursor, or GitHub Copilot — and the combination becomes even more powerful when the AI also has access to a database MCP server.

With an AI assistant alone

The scm.md documentation is detailed enough to serve as full context for an AI. Hand it the file and ask it to write a migration, and it knows:

The correct file naming convention and timestamp format
Which SQL syntax is safe for your target database
How to write a proper down migration
When to use IF EXISTS / IF NOT EXISTS guards
How to handle destructive operations safely

In practice this means you describe what you want in plain English and the AI writes the SQL, names the file correctly, and tells you exactly which scm commands to run. Most routine migrations — add a column, create an index, rename a table, backfill data — require no hand-written SQL at all.

With a database MCP server

A database MCP (Model Context Protocol) server gives an AI agent live read access to your database — schema, tables, columns, indexes, constraints, row counts. Combined with SCM, the AI can close the entire migration loop autonomously:

Inspect — query the live schema to understand the current state
Plan — determine exactly what SQL is needed to reach the desired state
Write — generate the migration with correct up and down SQL for your specific database
Create — run scm new <name> and write the SQL into the generated file
Preview — run scm up --dry-run to confirm what will execute
Apply — run scm up
Verify — query the live schema again to confirm the change landed correctly

If something goes wrong the AI can read scm status, identify orphaned entries, run scm repair, and retry — without human intervention.

WaSQL tightens this further. The env-from-config command already reads config.xml for connection details, the same config file a WaSQL database MCP server would use. The AI, the MCP, and SCM are all working from the same source of truth.

The result is a workflow where a developer describes a schema change in a sentence, and the entire migration — file creation, SQL generation, application, and verification — happens automatically. SCM's command structure and plain SQL files make it straightforward for an AI to drive; there's no DSL to interpret, no hidden state, just files and a tracking table.

The Bottom Line

SCM is the migration tool for teams that need to move schema changes across databases that the popular tools don't cover. Oracle, HANA, Snowflake, cTree, Firebird — they all get the same workflow: new, up, down, status. The WaSQL env-from-config integration means setup for any database in your config.xml takes one command.

It ships free inside the WaSQL repo. If you're already there, you already have it.

scm learn    # start here