monetdbsolutions/mroll

db migration tool

Mroll migration tool

mroll has been designed to aid MonetDB users with managing database migrations. The functionality covers both roll forward and backward migration functionality. Although you can deploy mroll from any point in time onwards, it is advised to use it from day one, i.e. the creation of the database. mroll has been used internally to manage the continuous integration workflow of MonetDB.

Install

Install mroll from PyPi

$ pip install mroll

Synopsis

The command synopsis summarizes the functionality.

$ mroll --help
Usage: commands.py [OPTIONS] COMMAND1 [ARGS]... [COMMAND2 [ARGS]...]...

Options:
  --help  Show this message and exit.

Commands:
  config    Set up mroll configuration under $HOME/.config/mroll
  history   Shows applied revisions.
  init      Creates mroll_revisions tbl.
  revision  Creates new revision from a template.
  rollback  Downgrades to previous revision by default.
  setup     Set up work directory.
  show      Shows revisions information.
  upgrade   Applies all revisions not yet applied in work dir.
  version   Shows current version

Each command may come with some options, explained by the --help addition. For example, the location of the migration directory can be specified when you install mroll with an option --path option to specify location. For an example, --path "/tmp/migration" location.

To update/set mroll configuration use the config command. For example to update configuration setting for working directory path run.

mroll config -p <workdir_path>

Usage

To illustrate the functionality we walk you through the steps to get a MonetDB database, called demo, created and managed. We assume you have downloaded mroll (see below) and are all set to give it a try.

Setup

mroll needs a working directory for each database you want to manage. There is no restriction on its location, but you could keep the migration scripts in your application folder, e.g. .../app/migrations. From the .../app directory issue the command:

$ mroll setup
ok

A subdirectory migrations is being created to manage migrations versions.

Configuration

mroll needs information on the database whereabouts and credentials to initiate the migration steps. Make sure you have already created and released the demo database using the monetdb tools. Then complete the file migrations/mroll.ini to something like:

[db]
db_name=demo
user=monetdb
password=monetdb
port=50000

[mroll]
rev_history_tbl_name = mroll_revisions

The final step for managing the migrations is

$ mroll init

Define the first revision

The empty database will be populated with a database schema. For this we define a revision. Revision names are generated

$ mroll revision -m "Initialize the database"
ok
$ mroll show all_revisions
<Revision id=fe00de6bfa19 description=Initialize the database>

A new revison file was added under /tmp/migrations/versions. Open it and add the SQL commands under -- migration:upgrade and -- migration:downgrade sections.

vi tmp/migrations/versions/<rev_file>
-- identifiers used by mroll
-- id=fe00de6bfa19
-- description=create tbl foo
-- ts=2020-05-08T14:19:46.839773
-- migration:upgrade
	create table foo (a string, b string);
	alter table foo add constraint foo_pk primary key (a);
-- migration:downgrade
	drop table foo;

Then run "upgrade" command.

$ mroll upgrade
Done

Inspect what has being applied with "history" command

$ mroll history
<Revision id=fe00de6bfa19 description=create tbl foo>

For revisions overview use mroll show [all|pending|applied], mroll applied is equivalent to mroll history.

$mroll show applied
<Revision id=fe00de6bfa19 description=create tbl foo>

To revert last applied revision run the rollback command. That will run the sql under migration:downgrade section.

$ mroll rollback 
Rolling back id=fe00de6bfa19 description=create tbl foo ...
Done

Development

Developer notes

mroll is developed using Poetry, for dependency management and packaging.

Installation for development

In order to install mroll do the following:

  pip3 install --user poetry
  PYTHON_BIN_PATH="$(python3 -m site --user-base)/bin"
  export PATH="$PATH:$PYTHON_BIN_PATH"

  git clone [email protected]:MonetDBSolutions/mroll.git
  cd mroll
  poetry install
  poetry run mroll/commands.py --help

Install project dependencies with

poetry install

This will create virtual environment and install dependencies from the poetry.lock file. Any of the above commands then can be run with poetry

poetry run mroll/commands.py <command>

Testing

Run all unit tests

make test

mroll's Issues

Downgrade --preview

Downgrading in a lineage tree would walk all the way back to a specifically named revision-id.
With a --preview option to see what will happen if you apply it.

Report revision-id on errors

If mroll encounters a failing revision upgrade, then report its id. Otherwise it is hard to find what revision was wrong.
(PycharmProjects) bash-3.2$ mroll upgrade
3F000!CREATE SCHEMA: name ‘notary’ already in use
… raise exception(msg)
pymonetdb.exceptions.OperationalError: SELECT: no such table ‘mroll_revisions’ (edited)
9:33
Be more defensive. This sequence requires a better error message, not a stack trace.

Testing with revisions

Consider a series of revisions A-B-C-D. This can of course nicely capture the database structure, but between each step, there are tests perform. They typically consist of rolling the database forward to a testing state first and discarding temporary test stuff.
This does not look great.

• a separate testing infrastructure should understand the revision scheme
• a separate infrastructure has to implement its own test-revision scheme

The ideal with be a double-stranded scheme
A ------- B ------C ------- D
| | | |
Ta------- Tb---- |Tc ------Td
After a revision, e.g C, has been applied the testing state should be rolled forward to Tc.
Conversely, the test downgrade should also work.

The revision scheme can be 'misused' for this as follows:
A Ta-- B Tb ---- C Tc ----DTc
But then you can not easily extract the original, production-related revision line.

A hack is to create two migration directories with shared revisionids.

dummy

test tets

Explicit revision dependencies

The dependencies should not depend on the time stamp. Instead, they form a lineage structure that should be explicit. In general, the migrations my form a tree, such that one can accommodate a .../test and a ../production line of migrations in parallel.

Much like branches in mercurial/git.

Better context in error message

Error message are reported currently as :
Error: revision id=b9fdaaed8931
alter table results."memory_metrics" rename vm_total to total;
===========
('OperationalError("ALTER TABLE: no such schema 'results'\n")',)

it will be useful to add the migration text/description text as well as to avoid to back track just by id

Clarify command and options

all_revisions, new_revisions and history looks overlapping queries, perhaps it is leaner do us properties, as filters to be applied.

mroll revisions --all
mroll revisions --pending
mroll revisions --done

One database restriction

Because mroll init writes a file in a global location ($HOME/.config/mroll/config.ini) a single user cannot have two different databases under mroll at the same time.

mroll failed to read correctly the applied migrations on testweb staging

monetdb destroy -f workbench
monetdb create -p monetdb workbench
bzcat dump.sql.bz2 | mclient -d workbench -

[fedora@mtest service]$ mclient -d workbench -s 'select * from sys.mroll_revisions'
+--------------+------------------------------------------------------------------+----------------------------+
| id           | description                                                      | ts                         |
+==============+==================================================================+============================+
| b9fdaaed8931 | rename memory metrics attr                                       | 2020-05-12 14:04:53.700639 |
| 4e88e648891d | create socket connections table                                  | 2020-05-17 23:37:17.673998 |
| a9f1c1274c0a | benchmark sf as varchar                                          | 2020-06-16 12:58:03.536655 |
| 856e260c104a | Add thread_count and cpu_socket_count columns in the hosts table | 2020-07-24 14:11:09.107083 |
| e2987c4c1983 | Drop changeset and branch from  experiments table                | 2020-08-14 07:13:35.542932 |
| 079d43f62b6b | put back forgotten unique constraint on experiments              | 2020-08-20 15:42:01.565026 |
| e1a0b2dfdfc8 | software name not null                                           | 2021-01-26 14:43:18.120314 |
| 4e25419dac57 | unique software name version constraint                          | 2021-02-03 15:59:52.281635 |
+--------------+------------------------------------------------------------------+----------------------------+
8 tuples
[fedora@mtest service]$ ~/src/venv/bin/mroll show applied
<Revision id=b9fdaaed8931 description=rename memory metrics attr>
<Revision id=4e88e648891d description=create socket connections table>
<Revision id=a9f1c1274c0a description=benchmark sf as varchar>
<Revision id=856e260c104a description=Add thread_count and cpu_socket_count columns in the hosts table>
<Revision id=b71514c56ae9 description=test web schema extension>
<Revision id=e2987c4c1983 description=Drop changeset and branch from  experiments table>
<Revision id=079d43f62b6b description=put back forgotten unique constraint on experiments>
<Revision id=e1a0b2dfdfc8 description=software name not null>
<Revision id=4e25419dac57 description=unique software name version constraint>

Viewing revision patches (and their result)

The history gives only the revision ideas. Using a -p should also show their content, and result applied to the database (errors?)

mroll history

mroll history -p
-- identifiers used by mroll
-- id=fe00de6bfa19
-- description=create tbl foo
-- ts=2020-05-08T14:19:46.839773
-- migration:upgrade
create table foo (a string, b string);
alter table foo add constraint foo_pk primary key (a);
-- migration:downgrade
drop table foo;

History should report on init

The history table should be populated with the initial event to initialize the migration table.