SADU Updater

SADU provides a database updater which deploys patch to your database. Which scripts are deployed is defined by a database version in the database itself and in your project.

The patches are semi failsave. If one of the queries fails the upgrade will be aborted. This avoids that the database ends up in a partially migrated state.

dependencies {
    implementation("de.chojo.sadu", "sadu-updater", "<version>")
}

Picking a SQL Type

In order to create a updater you will need a sql type. These types are provided by our database implementations. Make sure to import the one you need.

Once you imported it you can use the type class which has the same name like your database.

Setup

Setting up the updater is quite straight forward.

Base Setup

Create a structure like this in your resources. We will configure our updater to update a postgres database.

resources
└── database                # The database directory holds all information
    ├── postgresql          # All script for postgresql are stored here
    │   └── 1               # All scripts for 1.x are contained in this directory
    │       └── setup.sql   # The initial setup of the database 
    └── version             # The current database version. Currently 1.0

Of course you can add scripts for multiple databases. All database directories need all setup and other scripts.

Setting the version

To set the version simply enter a major and patch version separated by a dot in your version file.

1.0

Adding the setup script

Enter all your queries for the initial database setup into the setup.sql file.

For our example we add a simple table we want to deploy:

CREATE TABLE dev_schema.test
(
    id   SERIAL,
    name TEXT,
    age  INTEGER
); -- Make sure to end your statement with a semicolon

Executing the Updater

So execute the updater, we need to create a instance of it, pass a datasource and execute it after some minimal configuration.

class Updater {
    private final DataSource dataSource;

    Updater(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void update() throws IOException, SQLException {
        // We create our updater for the postgres database.
        // By setting the type our updater knows which directory it needs to search for our scripts.
        SqlUpdater.builder(dataSource, PostgreSql.get())
                // For easier development on our dev system we use a dev_schema in our database
                // During deployment we want to change the name to live_schema
                .setReplacements(new QueryReplacement("dev_schema.", "live_schema."))
                // Our version should be stored in this table. Changing the table later can cause trouble.
                // By default the table name will be only "version"
                .setVersionTable("my_app_version")
                // We want to deploy to the live_schema.
                // By defining it here, the updater will create this schema if it doesnt exist
                .setSchemas("live_schema")
                // In the end we execute out update.
                .execute();
    }
}

What happens now?

On the first startup the updater will do the following: 0. Check if the schema exists and creates it if missing. (This only works on databases which actually support schemas)

1. Create a version table
2. Execute the setup.sql file
3. Set the database version to 1.0

Adding a patch

If you want to deploy changes on an existing database you need to add a patch. At some point you will have multiple patches for your database. After some time your directory will look like this:

resources
  database
    postgresql
      1
        setup.sql       # Setup for 1.0
        patch_1.sql     # Patch for 1.1
        patch_2.sql     # Patch for 1.2
        patch_3.sql     # Patch for 1.3
    version             # The version is now 1.3

What happens now if we execute the updater on the database with version 1.0 we created previously?

1. Compare the version of the version table and our version file.
2. Load all patches after 1.0 (Basically all patch_x.sql) files
3. Execute them one after another.

Migrating to a major version

At some point you probably want a fresh start and not deploy 20 patches everytime your setup your database again.

Thats where the migration files comes in handy. Add a new version directory /2/. Add a migrate.sql script to your old version. Add a /2/setup.sql script which represents the state of the database after a successful migration.

resources
  database
    postgresql
      1
        setup.sql
        patch_1.sql
        patch_2.sql
        patch_3.sql
        patch_4.sql     # Patch for 1.4
        migrate.sql     # Migration from 1.4 to 2.0
      2
        setup.sql       # Setup for 2.0
        patch_1.sql     # Patch for 2.1
    version             # The version is now 2.1

This is what the updater will to when encountering a database with version 1.3.

1. Compare the versions
2. Load all patches after the 1.3 patch (/1/patch_4.sql)
3. Execute the patches
4. Load the /1/migrate.sql script
5. Execute the migration
6. Load all patches after 2.0 (/2/patch_1.sql)
7. Execute all patches

Note 1: The /1/migrate.sql needs to create the same state as the /2/setup.sql

Note 2: When a basic setup would be performed now, the updater would skip everything in /1/ and directly deploy /2/setup.sql.