dbmigrator_cli

Powerful SQL migration toolkit for Rust.

Usage

• Add dbmigrator to your Cargo.toml dependencies with the selected driver as feature eg: dbmigrator = { version = "0.8", features = ["tokio_postgres"]}
• Migrations can be defined in .sql files.

Intoduction

DBMigrator is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production.

Recipes

Recipe is the basic unit of change in DBMigrator. It is a simple SQL script executed in a transaction.

We distinguish four kinds of recipes:

• baseline - A consolidated SQL script independently creating a specific version of the database.
• upgrade - The smallest unit of change, migrating the DB schema one version forward.
• revert - A special fix script to roll back erroneous upgrade recipes that need to be and can be reverted.
• fixup - Also a fix script that can attempt to repair an erroneously issued upgrade recipe. Sometimes it is not possible to reverse the action of an erroneously issued migration. This kind of recipe can, however, introduce some corrective actions.

We do not foresee creating pairs of do and undo scripts because it is usually not possible to roll back applied migrations. Undo scripts are typically not eagerly maintained by developers, are usually untested, and are just a source of additional problems.

Each recipe is identified by a version and a name.

We must choose a version sorting algorithm because recipes need to be arranged in an unambiguous order. Two version comparators are available:

• Simple compare assumes that the version should be fixed length to avoid problems with comparing numeric parts.
• Version compare allows naming files according to semver, but most file tools will not be able to present them in the correct order.

If not necessary, it is better to use simple_compare and try to use versions with a uniform number of characters.

Recipes have the following metadata:

All metadata can be stored in the SQL file as first comments:

-- old_checksum: ab3c3ade
-- maximum_version: 20241106-2359

DROP TABLE IF EXISTS customer;

Changelog

Changes are stored in a changelog table. It is a simple table with the following columns:

log_id is plain integer, not database serial. DBMigrator automatically increments it from 1.

DBMigrator determines the effective migration state by reviewing subsequent changelog entries in the dbmigrator_log table. For each version, the last row is considered the current one. Rows with a checksum equal to NULL remove (revert) the effective state for a given version.

Historical changelog row is never deleted and modified. Only revert_ts is updated when the recipe is reverted or amended. revert_ts is only informative and does not affect the effective state.

First row in the dbmigrator_log table is always the baseline. It is created automatically when the database is initialized.

The basic strategy assumes that we create an empty baseline file for the first version (e.g., 0.0.0_baseline.sql, 000000_baseline.sql, etc.). All following database changes are further upgrade migrations. When initializing a new database, we execute all recipes sequentially.

This can be optimized by periodically issuing a baseline recipe for release versions with a consolidated SQL script. When initializing a new database, the latest baseline version will be used.

DDL

DDL (Data Definition Language) is a subset of SQL commands used to define the structure of a database. DDL allows you to create, modify, and delete database objects such as tables, indexes, views, functions and schemas. Example DDL commands include: CREATE, ALTER and DROP.

Zawartość skryptów migracyjnych (receptur) to zazwyczaj komendy DDL. Poza kolejnymi recepturami (migracjami) w repozytorium kodu (np. GIT) warto przechowywać aktualny stan bazy w uporządkowanej strukturze skryptów DDL.

DBMigrator pozwala na automatyczne wygenerowanie takich skryptów. Wykorzystuje do tego wewnętrznie narzędzie pg_dump z PostgreSQL. Daje to gwarancje, że skrypty są zgodne z aktualnym stanem bazy danych i uwzględniają wszystkie świeżo dodane funkcjonalności do PostgreSQL. Skrypty DDL są grupowane wg zestawu reguł. Standardowy zestaw tworzy następującą strukturę:

DDL (Data Definition Language) is a subset of SQL commands used to define the structure of a database. DDL allows you to create, modify, and delete database objects such as tables, indexes, views, functions and schemas. Example DDL commands include: CREATE, ALTER and DROP.

The content of migration scripts (recipes) is usually DDL commands. Besides subsequent recipes (migrations), it is worth keeping the current state of the database in an organized structure of DDL scripts in the code repository (e.g., GIT).

DBMigrator can automatically generate such scripts. DDL scripts are grouped according to a set of rules. The standard set creates the following structure:

• database.sql
• extensions.sql
• public/
  • schema.sql
  • fts.sql
  • index.sql
  • sequence.sql
  • ...
  • functions/
    • proc1.sql
    • proc2.sql
    • func1.sql
    • func2.sql
    • ...
  • types/
    • table1.sql
    • table2.sql
    • view1.sql
    • view2.sql
    • type1.sql
    • ...
• nextschema/
  • schema.sql
  • ...
• casts.sql
• ...
• unclassified.sql

DBMigrator internally uses the pg_dump tool from PostgreSQL. This ensures that the DDL scripts are consistent with the current state of the database and include all newly added functionalities to PostgreSQL.

Custom DDL ruleset

The above structure is suitable for our habits and our projects. It will not always be the best solution, so you can develop your own set of rules.

You need to create a pgsql_ddl_ruleset.yaml file, which will be an array of rules:

Elements that do not match any rule will be placed in the unclassified.sql file.

When creating rules for ACL and COMMENT, analyzing PostgreSQL code may be helpful:

• parseAclItem in postgres/src/bin/pg_dump/dumputils.c
• _getObjectDescription in postgres/src/bin/pg_dump/pg_backup_archiver.c

License

This project is licensed under the MIT license and Apache license.

Contribution

Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in dbmigrator by you, shall be licensed as MIT, without any additional terms or conditions.

Inspiration and ideas

• Katharina Fey (Refinery) - kookie@spacekookie.de
• João Oliveira (Refinery) - hello@jxs.pt