Recently, I’ve been experimenting with doing database migrations directly with JOOQ instead of using some tool like Liquibase or Flyway. And even though it’s just a proof-of-concept for now and the project is mostly on hold, I want to write down what I did so far. Also, I would love to hear your feedback: Pink me on Twitter or via Email.

I started creating a portal for marmota.app. For now, the app has only a static web site for now, but some time in the future, it will need some dynamic features. And I started with a few proof-of-concepts for that portal. I created a spring boot application and decided to use JOOQ to access the database.

First, I started writing the database migrations using Flyway, but then I had an idea: With a small amount of glue code, I could use JOOQ directly to migrate my database. There’s one caveat, though: I don’t care about hashing the change sets. You changed a migration that already ran? Yeah, that won’t work, but my code will not protect you against it. I am planning to write integration tests to catch this.

Runnin Migrations

The code that runs my migrations basically looks like this:

@Component
class Migrations(
	private val dsl: DefaultDSLContext
) {
	@PostConstruct
	fun runMigrations() {
		createMigrationsTable()
		runMigration("v2022.21.0", ::createUserTables)
		runMigration("v2022.22.0", ::createUserPendingUserVerificationsTable)
	}
}

The class Migrations basically manages the life cycle of the migrations. When the app starts, it first creates the migrations table (if it does not exist yet). Then, it runs all migrations.

A migration has an ID and a function that can change the database accordingly.

A Migration

A single migration, in this case createUserPendingUserVerificationsTable, is a function that runs some DDL:

fun createUserPendingUserVerificationsTable(dsl: DSLContext) {
	dsl.createTable(PendingUserVerification.table)
		.column(PendingUserVerification.userId)
		.column(PendingUserVerification.emailValidationCode)
		.constraints(
			DSL.primaryKey(PendingUserVerification.emailValidationCode),
			DSL.foreignKey(PendingUserVerification.userId).references(User.table, User.id)
		)
		.execute()
}

The fields, etc… of the database table are defined in another object:

class PendingUserVerification {
	companion object DB {
		val table = DSL.table("pending_user_verifications")
		val emailValidationCode = DSL.field("email_validation_code", SQLDataType.VARCHAR(40))
		val userId = DSL.field("user_id", SQLDataType.VARCHAR(40))
	}
}

Type Safety

The code that acesses the database later can now use the same static fields, like PendingUserVerification.table etc. So, this code automatically makes sure that the code that accesses the database uses the same assumptions about the structure of the database as the code that creates it. No generators needed.

Here’s another example, where the data class that is used by the application code also contains the database definitions for the same entity:

data class User(
	val id: String,
	val name: String,
	val email: String,
	val passwordHash: String,
	val hashAlgorithm: String,
	val emailValidated: Boolean,
	val emailValidationCode: String,
) {
	companion object DB {
		val table = DSL.table("users")
		val id = DSL.field("id", VARCHAR(40))
		val name = DSL.field("name", VARCHAR(64))
		val email = DSL.field("email", VARCHAR(256))
		val passwordHash = DSL.field("password_hash", VARCHAR(256))
		val hashAlgorithm = DSL.field("hash_algorithm", VARCHAR(12))
		val emailValidated = DSL.field("email_validated", BOOLEAN)
		val emailValidationCode = DSL.field("email_validation_code", VARCHAR(40))
	}
}

Renaming a Field

Renaming a field or changing the type of an existing field is not exactly straight-foward, but doable. The problem you’d be facing is that the companion object can only hold one definition for emailValidationCode. When I want to change the type of this field from VARCHAR(40) to VARCHAR(128), I’d have to do something like this:

1. Refactor: Rename the original field to something like emailValidationCode_before_v22022_34_0. Use this in all existing migrations, but NOT in the rest of the code.
2. Create the new field emailValidationCode with the new definition
3. Create the migration that changes the column type

Now, the class with the definitions would look like this:

class PendingUserVerification {
	companion object DB {
		val table = DSL.table("pending_user_verifications")
		val emailValidationCode_before_v22022_34_0 = DSL.field("email_validation_code", SQLDataType.VARCHAR(40))
		val emailValidationCode = DSL.field("email_validation_code", SQLDataType.VARCHAR(128))
		val userId = DSL.field("user_id", SQLDataType.VARCHAR(40))
	}
}

The Full Picture

Two pieces are still missing from this puzzle: How to create the migrations table and how to run a migration.

The migrations table can be created by executing a single DDL statement:

@Component
class Migrations(
	private val dsl: DefaultDSLContext
) {
	private val migrationsTable = object {
		val table = table("migrations")
		val id = field("migration_id", VARCHAR(20).nullable(false))
		val migrated = field("migrated_at", TIMESTAMP)
	}
	private val logger = LoggerFactory.getLogger(Migrations::class.java)

	private fun createMigrationsTable() {
		dsl.transaction { config ->
			val context = using(config)

			logger.info("DB migrations: Creating table (if not exists)")
			context.createTableIfNotExists(migrationsTable.table)
				.column(migrationsTable.id)
				.column(migrationsTable.migrated)
				.constraints(primaryKey(migrationsTable.id))
				.execute()

		}
	}
}

And the migration require checking whether the migration should be executed and then running the migration and then remembering that it ran:

@Component
class Migrations(
	private val dsl: DefaultDSLContext
) {
	private fun runMigration(id: String, migration: (d: DSLContext)->Unit) {
		dsl.transaction { config ->
			val context = using(config)

			val alreadyMigratedEntry = context.select(migrationsTable.id)
				.from(migrationsTable.table)
				.where(migrationsTable.id.eq(id))
				.fetch()

			if(alreadyMigratedEntry.size == 0) {
				logger.info("DB migrations: RUNNING migration $id")
				migration(context)

				context.insertInto(migrationsTable.table,
					migrationsTable.id, migrationsTable.migrated)
					.values(id, Timestamp.valueOf(LocalDateTime.now()))
					.execute()
			} else {
				logger.info("DB migrations: Migration $id already applied, nothing to be done")
			}
		}
	}
}

To Recap…

I wanted to run database migrations directly in my code, using JOOQ. For me, this has two advantages: More information about how my application works can be found direclty in the source code, and the migrations and production code use the same database definition.

What I created so far is not a full replacement of more sophisticated tools like Flyway. But I am confident that I can make it work well enough for my use-case.