Metadata-Version: 2.4
Name: rdbmpy
Version: 1.1.3
Summary: CLI for plain-SQL, file-based database migrations (setup/create/exec/rollback) across PostgreSQL, MySQL, SQLite and SQL Server — no ORM
Author-email: Guilherme Makoto Sacoman Dakuzaku <makoto@rocketti.com.br>
License: Apache-2.0
Project-URL: Homepage, https://github.com/gdakuzak/rdbmpy
Project-URL: Repository, https://github.com/gdakuzak/rdbmpy
Project-URL: Documentation, https://github.com/gdakuzak/rdbmpy/blob/main/docs/overview.md
Project-URL: Issues, https://github.com/gdakuzak/rdbmpy/issues
Keywords: database,migration,migrations,sql,cli,postgresql,mysql,sqlite,sqlserver,sqlalchemy
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Build Tools
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE.txt
Requires-Dist: greenlet>=1.1.0
Requires-Dist: psycopg[binary]>=3.1
Requires-Dist: python-dotenv>=1.2.2
Requires-Dist: SQLAlchemy>=2.0
Requires-Dist: pymysql>=1.0.0
Provides-Extra: sqlserver
Requires-Dist: pyodbc>=4.0.39; extra == "sqlserver"
Dynamic: license-file

# rdbmpy — plain-SQL database migrations CLI

[![PyPI version](https://img.shields.io/pypi/v/rdbmpy)](https://pypi.org/project/rdbmpy/)
[![Python versions](https://img.shields.io/pypi/pyversions/rdbmpy)](https://pypi.org/project/rdbmpy/)
[![License: Apache-2.0](https://img.shields.io/badge/license-Apache--2.0-blue)](./LICENSE.txt)
[![CI](https://github.com/gdakuzak/rdbmpy/actions/workflows/python-package.yml/badge.svg)](https://github.com/gdakuzak/rdbmpy/actions/workflows/python-package.yml)

`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](#installation)
- [Quickstart](#quickstart)
- [Use cases](#use-cases)
- [When to use](#when-to-use)
- [When not to use](#when-not-to-use)
- [Key features](#key-features)
- [Conceptual API map](#conceptual-api-map)
- [More examples](#more-examples)
- [Limitations and trade-offs](#limitations-and-trade-offs)
- [Troubleshooting](#troubleshooting)
- [Compatibility](#compatibility)
- [Local development](#local-development)
- [Tests](#tests)
- [Roadmap](#roadmap)
- [License](#license)

## Installation

```sh
pip install rdbmpy
```

SQL Server needs an extra and a system-level ODBC driver — see [SQL Server ODBC setup](#sql-server-odbc-setup-ubuntudebian) below:

```sh
pip install "rdbmpy[sqlserver]"
```

Full details (extras, supported environments): [docs/installation.md](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`.

```sh
# 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](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](#limitations-and-trade-offs)).

## 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](#limitations-and-trade-offs)).
- You need a `status`/dry-run preview of pending migrations before applying — not implemented (see [Roadmap](#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](#limitations-and-trade-offs)).
- **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](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](#limitations-and-trade-offs).

## More examples

Runnable scripts for all four drivers: [docs/examples.md](docs/examples.md) and [`examples/`](examples/).

- [`examples/01_minimal_sqlite.sh`](examples/01_minimal_sqlite.sh) — smallest possible working flow, SQLite only, no external DB.
- [`examples/02_fake_apply.sh`](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`](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](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](docs/concepts.md#atomic-batch-apply).
- **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](docs/concepts.md#multi-statement-sql-files) — 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](docs/concepts.md#concurrency-lock)). 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](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](#sql-server-odbc-setup-ubuntudebian).

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

```sh
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](docs/installation.md#sql-server-without-host-install).

## Local development

```sh
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:

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

Per-driver commands: [docs/tests_execution.md](docs/tests_execution.md).

## Tests

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

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

## Roadmap

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

## License

Apache License 2.0 — see [LICENSE.txt](LICENSE.txt).

Forked from [py-migrate-db](https://github.com/indicoinnovation/py-migrate-db).
