For Developers: Database Migrations

Database Migrations

When working with an SQL database, there are a couple of points one must keep in mind:

1. A database contains definitions for all the different types of data it contains. These are usually called tables, and they are defined using "Data Definition Language", or DDL. This is a subset of SQL, which includes keywords and actions such as CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, etc.
2. The definitions of tables are rather inflexible. It is not possible to store more data than is otherwise defined. Any new data points ( columns ) must be defined as part of the database schema ( which includes all of the DDL for the database ).

This presents a problem. When modifying Java code for entities which are meant to be stored in already-existing tables, we must also account for the change in the definition of the Java classes by modifying the database schema. AtherysCore utilizes Hibernate for all Atherys Plugins' persistance needs, and part of that is the hbm2ddl.auto ability, which reads the state of the database, reads the state of the corresponding java classes, and attempts to generate new DDL based on the differences.

However, this is imperfect, and when more complex modifications are required, even fails to modify the schema to properly match the Java class definitions, thus leading to runtime errors when attempting to read/write data from/to the database.

The solution to this is to return to a simpler way of doing things. Database migrations are not a new or novel idea by any stretch, they have been used to solve the above-mentioned problem for decades, and to this day they remain the most reliable way to ensure data definition coherency between software version upgrades.

Thus, AtherysCore 2.0.0 and above includes a library called FlywayDB, which is used to reliably execute database migrations when necessary.

Implementation

The way AtherysCore provides FlywayDB functionality to plugins, without requiring each plugin to use the library directly, is by leveraging Sponge's event system:

The AtherysDatabaseMigrationEvent is triggered before the initialization of Hibernate. At this point in the lifecycle, it would not be possible to access an EntityManagerFactory, thus making it impossible to read/write any data from the database, and for good reason. The migration event is intended only so plugins may register themselves for participating in the database migration lifecycle stage by doing the following:

    @Listener
    public void onDatabaseMigration(AtherysDatabaseMigrationEvent event) {
        event.registerForMigration(PLUGIN_ID); // PLUGIN_ID refers to the id of the plugin as defined in the @Plugin annotation of the main plugin class
    }

It is of utmost importance that each plugin use its own database schema, as migrations are done per-plugin. This is done by using the schema property of the @Table, @JoinTable and @CollectionTable ( @Table( schema = "plugin_id" ), etc. ).

⚠️ Plugin schemas must correspond exactly to the plugin id

When migrations begin, AtherysCore will start flywaydb's migration process by iterating through each plugin's provided id, using it to construct a classpath directory where the plugin's database migration scripts are meant to be stored, as so: "classpath:db/migration/{plugin_id}/{db_provider}". db_provider refers to the database provider ( h2, postgresql, mysql, oracle, etc. ), which is determined by AtherysCore automatically based on the jdbc connection string provided.

This is what a plugin's database migration directory layout should look like:

• resources
  • db
    • migration
      • {plugin_id}
        • {db_provider_1}
          • V1__Migration_Schema_Provider_1.sql
        • {db_provider_2}
          • V1__Migration_Schema_Provider_2.sql

Currently, the only officially supported databases are h2 and postgresql. These are the only ones one may expect to find migrations for in A'therys plugins.

Script Naming

FlywayDB is very precise with the way migration scripts are named. The full documentation for this can be found here: https://flywaydb.org/documentation/concepts/migrations.html.

Depending on the name, flyway will know to do one of several things with the script in question, though this article will not go into detail on this. Instead, here is the explanation of versioned migration script naming, which is most commonly expected to be used:

V{plugin_version}__{description_with_underscores}.sql

The V means that this is a versioned script. The plugin version provides the ordering of scripts, so flyway knows which scripts to execute first and which ones after that. For simplicity, this should match the plugin version. The description can be anything, though it is preferred that each script's name is descriptive enough so one can understand its contents better.

Writing Migration Scripts

So what goes into a migration script? Well, any DDL really. When a new column is added, or a new table, or anything that Hibernate may query the database for, it must be defined in a migration script.

Here is an example from the AtherysRPG plugin:

resources/db/migration/atherysrpg/h2/V2.0.0__Create_RPG_Tables.sql

-- Note the creation of the plugin schema before creating the remaining objects
-- Note the `if not exists` clause, just in case this script is run multiple times for whatever reason
create schema if not exists atherysrpg;

create table if not exists atherysrpg.PlayerCharacter (
    id binary not null,
    experience double not null,
    spentAttributeExperience double not null,
    spentExperience double not null,
    spentSkillExperience double not null,
    primary key (id)
);

create table if not exists atherysrpg.PlayerCharacter_attributes (
    PlayerCharacter_id binary not null,
    value double,
    attribute_type varchar(255) not null,
    primary key (PlayerCharacter_id, attribute_type)
);

create table if not exists atherysrpg.PlayerCharacter_skills (
    PlayerCharacter_id binary not null,
    skills varchar(255)
);