Database Schema Management#
In some situation, a change to the database schema is required. To perform these database schema migrations, we implemented a migration tool and associated testing framework. This page describes how to create a new version of the database schema and test the migration script.
New schema version definition#
The version number of the database schema evolves independently from any other versioned Inmanta element (product version, extension version, etc.). Each commit can introduce changes to the database schema. When that happens the commit creates a new database schema version. This means that multiple schema version can exist between two consecutive stable releases of the orchestrator.
A new version of the database schema is defined by adding a new Python module to the
inmanta.db.versions package. The
name of this module should have the format
v<timestamp><i>.py, where the timestamp is in the form
i is an index to allow more than one schema update per day (e.g.
Each of these Python modules should implement an asynchronous function
update that accepts a database connection object
as an argument. This function should execute all database queries required to update from the previous version of the
database schema to the new version of the database schema.
An example is given in the code snippet below:
# File: src/inmanta/db/versions/v202102220.py from asyncpg import Connection async def update(connection: Connection) -> None: schema = """ ALTER TABLE public.test ADD COLUMN new_column; """ await connection.execute(schema)
Executing schema updates#
Schema updates are applied automatically when the Inmanta server starts. The following algorithm is used to apply schema updates:
Retrieve the current version of the database schema from the
public.schemamanagertable of the database.
Check if the
inmanta.db.versionspackage contains any schema updates.
When schema updates are available, each
updatefunction between the current version and the latest version is executed in the right order.
When a schema update fails, the database schema is rolled-back to the state before the start of the Inmanta server. In that case the Inmanta server will fail to start.
Testing database migrations#
Each database migration script should be tested using an automated test case. The tests that verify the migration from
<new_version> are stored in a file named
In general, a database schema migration test has the following flow:
Load a database dump that uses the database schema version directly preceding the version being tested.
Perform assertions that verify the database schema before the migration.
Start the inmanta server to trigger the database migration scripts.
Perform assertions to verify that the migration was done correctly.
The example below shows a test for the above-mentioned database migration script.
1# File: tests/db/test_v202101010_to_v202102220.py email@example.com_restore_dump(os.path.join(os.path.dirname(__file__), "dumps", "v202101010.sql")) 3async def test_add_new_column_to_test_table( 4 migrate_db_from: abc.Callable[, abc.Awaitable[None]], 5 get_columns_in_db_table: abc.Callable[[str], list[str]], 6) -> None: 7 """ 8 Verify that the database migration script v202102220.py correctly adds the column new_column to the table test. 9 """ 10 # Assert state before migration 11 assert "new_column" not in await get_columns_in_db_table(table_name="test") 12 # Migrate DB schema 13 await migrate_db_from() 14 # Assert state after migration 15 assert "new_column" in await get_columns_in_db_table(table_name="test")
The most important elements of the test case are the following:
Line 2: The
db_restore_dumpannotation makes the
migrate_db_fromfixture load the database dump
tests/db/dumps/v202101010.sqlin the database used by the test case. This happens in the setup stage of the fixture. As such, the database contains the old version of the database schema at the beginning of the test case.
Line 11: Verifies that the column
new_columndoesn’t exist in the table test. The test case uses the fixture
get_columns_in_db_tableto obtain that information, but the
postgresql_clientfixture can be used as well to run arbitrary queries against the database.
Line 13: Invokes the callable returned by the
migrate_db_fromfixture. This function call starts an Inmanta server against the database used by the test case, which runs the migration script being tested.
Line 15: Verifies whether the migration script correctly added the column
new_columnto the table test.
Each commit that creates a new database version should also add a database dump for that new version to the
tests/db/dumps/ directory. Generating this dump can be done using the
tests/db/dump_tool.py script. This
script does the following:
Start an Inmanta server using the latest database schema available in
Execute some API calls against the server to populate the database tables with some dummy data.
Dump the content of the database to
The actions to be taken after generating a new dump file are described in the docstring of the
If a new table or column is added using a database migration script, the developer should make sure to adjust the
dump_tool.py script with the necessary API calls to populate the table or column if required.