DB migrations in legacy without ORM: structure and practice

Database Migration System for Legacy Projects Without ORM

In legacy systems without ORM, managing DDL/DML script migrations demands strict control. This approach organizes a repository and state tables for consistent application of changes across test and production environments. It ensures traceability, data integrity, and predictable deployments on relational databases with ACID compliance.

The method cuts down on boilerplate code in scripts, automates validation and logging. Scripts fall into four types: initialization, baseline, versioned, and repeatable. Each type follows specific rules to avoid duplicates and maintain version monotonicity.

Git Repository Structure

Scripts are grouped by type and applied in a fixed order:

• Environment initialization — run once to create users, roles, databases, and schemas. Environment-specific (test/prod), executed via psql.
• Baseline — run once on an empty schema to establish the initial production version and control tables.
• Versioned — applied sequentially by ascending integer versions, only once, within a transaction that logs timestamp and user.
• Repeatable — applied if SHA256 changes or no record exists, after versioned scripts, for views/procedures/triggers without data modifications.

Object types:

• Initialization: users, roles, databases, schemas.
• Baseline: tables, data, views, procedures, triggers.
• Versioned: schema changes, data migrations.
• Repeatable: views, procedures, triggers.

This structure draws inspiration from Flyway but is tailored for manual processes without external tools.

State Tables in the Schema

Each schema gets service tables for tracking:

• dbmigration_versions (version_id, is_baseline, created_at, created_by, created_from) — tracks baseline and versioned script history.
• dbmigration_repeatable (sha256sum, relative_path, created_at, created_by, created_from) — tracks repeatable scripts, enabling reapplication on changes.

These tables enforce checks before running scripts: a script executes only if no record exists or the checksum has changed. Logs capture user and timestamp for full audit trails.

Development Deployments

In CI/CD (GitLab pipelines), clone the branch with changes, then for each schema:

$ git clone <url with database schema changes>
$ USER_PASSWORD=topsecret123 dbmigration.py update --host <host> --port <port> --dbname <db> --user <user_name> schema1 ./db/schema1
$ USER_PASSWORD=topsecret123 dbmigration.py update --host <host> --port <port> --dbname <db> --user <user_name> schema2 ./db/schema2

This automatically applies pending scripts while preserving data.

Production Process

Supports hot deployments with zero downtime. Pre-tested in UAT with smoke tests.

Steps:

• Extract the archive: tar xzvf system-x.y.z.tar.gz.
• For the schema: cd system-x.y.z/schema1.
• Generate migrate script: USER_PASSWORD=topsecret123 dbmigration.py verify --host <host> --port <port> --dbname <db> --user <user_name> --build-update-script ./schema1_migrate.sql schema1 ./db/schema1.
• Review schema1_migrate.sql for breaking changes.
• Apply: PGPASSWORD=topsecret123 psql -h <host> -p <port> -U <user> -d <database> -v schema_name=<schema1> -f "schema1_migrate.sql".

Repeat for each schema. Verify mode previews changes without applying them.

Key Practices

• Fix forward: Use new migrations to correct errors instead of rollbacks, preserving data.
• Non-breaking changes: Add tables/columns first, remove post-release when unused. Enables multi-version coexistence on the same DB.
• Schema-level tracking: Versions tracked per schema, not per database.
• Pre-checks: Generate scripts for review.
• Data preservation: Incremental updates avoid drop/recreate.

Key Takeaways

• Automated tracking eliminates manual boilerplate and script errors.
• Monotonic versions with transactions ensure consistent states.
• Hotfix support across versions (prod/UAT/dev) via versioned scripts.
• Full audit trails from tables: who/when/what was applied.
• Works with any ACID-compliant RDBMS.

— Editorial Team