rdbmpy 1.1.3

CLI for plain-SQL, file-based database migrations (setup/create/exec/rollback) across PostgreSQL, MySQL, SQLite and SQL Server — no ORM

rdbmpy — plain-SQL database migrations CLI

rdbmpy is a command-line tool that applies and rolls back versioned, plain-SQL migration files against PostgreSQL, MySQL, SQLite, and SQL Server. There is no ORM, no schema DSL, and no auto-generated migrations — you write raw SQL, rdbmpy tracks which files were applied in a migrations table and runs the rest in order. It targets backend developers and CI pipelines that already write SQL by hand and want one consistent CLI across multiple database engines instead of a separate tool per driver.

Table of contents

• Installation
• Quickstart
• Use cases
• When to use
• When not to use
• Key features
• Conceptual API map
• More examples
• Limitations and trade-offs
• Troubleshooting
• Compatibility
• Local development
• Tests
• Roadmap
• License

Installation

pip install rdbmpy

SQL Server needs an extra and a system-level ODBC driver — see SQL Server ODBC setup below:

pip install "rdbmpy[sqlserver]"

Full details (extras, supported environments): docs/installation.md.

Quickstart

setup must run before any other command against a given database — it creates the migrations table. Skipping it fails with MIGRATIONS TABLE NOT FOUND.

# 1. Create the migrations table (one-time per database)
rdbmpy setup --driver pgsql --dbstring="user:password@localhost:5432/mydb"

# 2. Create a migration file (no DB connection needed)
rdbmpy create --driver pgsql --migration_name add_users_table
# -> writes migrations/<timestamp>_add_users_table.sql

# 3. Edit the file: UP section, then =====DOWN, then the rollback SQL

# 4. Apply all pending migrations (one transaction for the whole batch)
rdbmpy exec --driver pgsql --dbstring="user:password@localhost:5432/mydb"

# 5. Roll back a specific migration by its file stem
rdbmpy rollback --driver pgsql \
  --migration_name=20251208113351_add_users_table \
  --dbstring="user:password@localhost:5432/mydb"

Full walkthrough with expected output: docs/quickstart.md.

Use cases

• CI/CD pipeline applies pending SQL migrations to a staging/production database as a deploy step, using one CLI regardless of which of the four supported engines each environment runs.
• Backend project that writes raw SQL (no ORM) and needs simple, file-based, versioned schema changes shared across developer machines.
• Onboarding a database that already has a schema applied out-of-band (manual DBA change, restored dump): register the matching migration file as applied without re-running its SQL, via exec --fake.
• Local development against SQLite with the same workflow used in production against PostgreSQL/MySQL/SQL Server.

When to use

• You write SQL directly and want file-based, ordered migrations with UP/DOWN sections.
• You need the same CLI across PostgreSQL, MySQL, SQLite, and SQL Server.
• Your workflow is CLI/CI-driven (setup → create → exec → rollback), not embedded inside application code at runtime.
• You want atomic batch execution on PostgreSQL/SQL Server/SQLite: if one migration in a run fails, the entire batch — including schema changes (CREATE/DROP/ALTER TABLE) — rolls back. On MySQL, only the migrations tracking rows roll back; DDL does not (MySQL/InnoDB has no transactional DDL — see Limitations).

When not to use

• You need schema diffing or auto-generated migrations from ORM models (use Alembic, Django migrations, or similar).
• You need a dependency graph between migrations (branching, merging) — rdbmpy orders migrations by filename sort only.
• You need to call migrations programmatically as a stable library API from inside a running application — rdbmpy is built and tested as a CLI; the underlying Migrate class is importable but has no documented/stable public API contract (see Limitations).
• You need a status/dry-run preview of pending migrations before applying — not implemented (see Roadmap).
• You need distributed/multi-tenant locking beyond a single mutex row per database — rdbmpy's lock backs off a second concurrent run rather than queuing or coordinating across tenants.

Key features

• Four drivers, one CLI: pgsql (postgresql+psycopg://), mysql (mysql+pymysql://), sqlite, sqlserver (mssql+pyodbc://).
• Plain SQL files: UP section, optional =====DOWN separator, DOWN section. No =====DOWN means exec runs the whole file and rollback also runs the whole file (no separate rollback SQL).
• Atomic batch apply: exec wraps the pending-migration SELECT and every UP statement in a single engine.begin() transaction — any failure rolls back the entire batch, not just the failing file. Full DDL+DML rollback on PostgreSQL, SQL Server, and SQLite; DML-only on MySQL (see Limitations).
• Fake apply: exec --fake --migration_name=<name> records a migration as applied without running its SQL — for migrations already applied outside rdbmpy.
• Migration name validation: --migration_name is restricted to [a-zA-Z0-9_-], rejecting path traversal (../, /) before it reaches the filesystem.
• Config via env or .env: DATABASE_MIGRATION_URL / DATABASE_DRIVER, overridable per-invocation with --dbstring / --driver.

Conceptual API map

rdbmpy is a CLI first; see docs/api.md for the full method-by-method map. Summary:

Command	Needs DB connection	Touches migrations table	--migration_name
setup	yes	creates	no
create	no	no	required
exec / execute	yes	reads + writes	no
exec --fake	yes	writes (no SQL run)	required
rollback	yes	deletes one row	required

• rdbmpy/migrate.py — Migrate class (one instance per invocation, dispatches on command) + main() CLI entrypoint.
• rdbmpy/constants.py — per-driver protocol string and CREATE TABLE migrations DDL.

Import path matches the distribution name (import rdbmpy.migrate), but only the CLI is tested/documented as a public interface — see Limitations.

More examples

Runnable scripts for all four drivers: docs/examples.md and examples/.

• examples/01_minimal_sqlite.sh — smallest possible working flow, SQLite only, no external DB.
• examples/02_fake_apply.sh — realistic --fake usage: a migration applied by hand gets registered without re-running its SQL.
• examples/03_error_handling.sh — a broken migration in a batch, showing the atomic-rollback behavior and the exit codes involved.

Per-driver manual runbooks (PostgreSQL, MySQL, SQL Server, SQLite) via Docker Compose: docs/tests_execution.md.

Limitations and trade-offs

• MySQL does not roll back schema changes (DDL) in a failed batch. MySQL/InnoDB has no transactional DDL — a CREATE/DROP/ALTER TABLE that ran before a later migration failed stays applied, even though its migrations tracking row rolls back with the rest of the transaction. This can leave a MySQL database with schema changes that don't match the tracking table, requiring manual cleanup. Verified directly; not the case on PostgreSQL, SQL Server, or SQLite (all roll back DDL correctly). See concepts.md.
• No library API contract. Only a CLI is documented and tested; importing Migrate directly is possible but not covered by tests as a public interface.
• No dependency graph. Migration order is a plain filename sort (timestamp prefix from create keeps this monotonic if you always use create; hand-added files can break ordering).
• No status/dry-run command. You cannot preview which migrations are pending without applying them.
• Multi-statement .sql files don't work on every driver. Verified directly against all four: PostgreSQL (psycopg) and SQL Server (pyodbc) run ;-separated statements in one file fine; MySQL (pymysql) and SQLite (stdlib sqlite3) both reject it (You have an error in your SQL syntax... / You can only execute one statement at a time). See concepts.md — write one statement per migration file if you need MySQL or SQLite portability.
• Application-level lock, not a distributed lock service. exec/exec --fake/rollback acquire a single-row migrations_lock mutex before writing and release it when done (see concepts.md). A second concurrent run backs off with an error instead of double-applying. If a run is killed (SIGKILL) before releasing, the row is orphaned and must be cleared manually — there is no auto-expiry.
• --fake requires the migration file to exist locally at the exact relative path exec would use — it does not accept an arbitrary/free-form name.

Fixed since the first cut of this list: migration files are read/written with explicit UTF-8 encoding, and --connect-timeout (or DATABASE_CONNECT_TIMEOUT) sets a DB connection timeout per driver.

Troubleshooting

Common errors and fixes: docs/troubleshooting.md.

• MIGRATIONS TABLE NOT FOUND — RUN "rdbmpy setup" FIRST — run rdbmpy setup once per database before exec/rollback.
• INVALID MIGRATION NAME — --migration_name must match ^[a-zA-Z0-9_-]+$; no /, no ...
• INVALID DRIVER — --driver must be one of pgsql, mysql, sqlite, sqlserver.
• SQL Server pyodbc/ODBC errors — see SQL Server ODBC setup.

Compatibility

• Python: >=3.10; CI matrix tests 3.10, 3.11, 3.12 (.github/workflows/python-package.yml). Full test suite (25 tests) verified passing on 3.10, 3.11, and 3.12.
• OS: CI matrix runs on Linux, macOS, and Windows (ubuntu-latest/macos-latest/windows-latest) — SQL Server's pyodbc extra still needs a system ODBC driver on any OS, see below.
• Databases: PostgreSQL (psycopg[binary]>=3.1), MySQL (pymysql>=1.0.0), SQLite (stdlib), SQL Server (pyodbc>=4.0.39 extra + system ODBC Driver 18).
• Core dependency: SQLAlchemy>=2.0.

SQL Server ODBC setup (Ubuntu/Debian)

curl https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | sudo tee /usr/share/keyrings/microsoft.gpg > /dev/null
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo sed -i 's|deb |deb [signed-by=/usr/share/keyrings/microsoft.gpg] |' /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc-dev

pip install "rdbmpy[sqlserver]"

No host installation needed if you use the Dockerized tester service in docker-compose.yaml — see docs/installation.md.

Local development

git clone https://github.com/gdakuzak/rdbmpy
cd rdbmpy
pip install -r requirements.txt
pip install -e .

Real database engines for manual/integration testing (PostgreSQL, MySQL, SQL Server, SQLite) run via Docker Compose:

docker compose up -d postgres mysql sqlite
MSSQL_SA_PASSWORD=<strong_password> docker compose up -d sqlserver

Per-driver commands: docs/tests_execution.md.

Tests

Unit tests use SQLite in a temp directory — no external database required:

pip install -r requirements.txt
python -m unittest discover -s tests -v

Roadmap

Tracked in docs/roadmap.md (completed P0–P3 hardening cycle) and docs/roadmap-v2.md (open items: status command, --dry-run, CI/packaging cleanup, type hints).

License

Apache License 2.0 — see LICENSE.txt.

Forked from py-migrate-db.