Using Flyway with Paper - Database migrations

Why database migrations?

Database migrations are all about safely changing your database over time as requirements change and your codebase changes. Migrations allow you to run SQL statements to upgrade your database for new versions of your software, whether it’s one change, or years of changes.

Why Flyway?

There are a lot of packages which help to manage database migrations. Flyway seems the most suitable for Java development, as it has a Java library which can run migrations which live inside your plugin jar. This means running a new version of your plugin will run all the new migrations automatically since the version of the plugin they are upgrading from.

Dependencies

In this example, I’m using Kotlin and PostgreSQL. You should be able to adapt the examples to your database and programming language pretty easily.

I am using HikariCP to handle connection pooling, which I would highly recommend for any database work in Java. PgJDBC is the database driver I am using, you should swap this out for a driver for your chosen database. You can find more information about database drivers under “Supported Databases” in the Flyway documentation.

Finally, you will need Flyway core to run your migrations inside your plugin.

implementation("com.zaxxer:HikariCP:5.0.0")
implementation("com.impossibl.pgjdbc-ng:pgjdbc-ng:0.8.9")
implementation("org.flywaydb:flyway-core:8.0.1")

You will need to shade these libraries into your plugin jar using shadow, and don’t forget to relocate them to prevent conflicts in case other plugins are using these libraries as well.

Create your migrations

Migrations can be added inside your plugin’s resources folder, the default path Flyway uses is db/migration, so I have stuck with this.

  1. Create folders db/migration inside your resources folder
  2. Create a migration, which must conform to the Flyway style, you can read more in the documentation linked further up

As an example, I have created V1__create_players_table.sql, containing:

CREATE TABLE IF NOT EXISTS players
(
    id    uuid PRIMARY KEY,
    money integer DEFAULT 0 NOT NULL
);

Set up your database and run your migrations

Here is a basic example in Kotlin creating a data source using HikariCP and running the migrations using Flyway:

fun initializeDatabase(plugin: MyPlugin): HikariDataSource {
    val config = HikariConfig().apply {
        dataSourceClassName = "com.impossibl.postgres.jdbc.PGDataSource"
        addDataSourceProperty("serverName", "localhost")
        addDataSourceProperty("databaseName", "dbname")
        username = "postgres"
        password = "dbpassword"
    }

    val dataSource = HikariDataSource(config)

    val flyway = Flyway.configure(plugin.javaClass.classLoader).dataSource(dataSource).load()
    flyway.migrate()

    return dataSource
}

The important bit specific to Paper development here is passing plugin.javaClass.classLoader into the Flyway#configure method. If you do not do this, Flyway will be unable to find your migrations.

You can use your data source however you wish from here, I am planning on trying out JetBrain’s Exposed.