Migrations and code generation#

The schema comes first, the code second. You write SQL and apply it, and the table definition classes are built from that.

Migrations#

Put your SQL in conf/migration/<schema name>/.

# --- !Ups

CREATE TABLE `note` (
	`id`         BIGINT UNSIGNED AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
	`title`      VARCHAR(200) NOT NULL COMMENT 'Title',
	`created_at` DATETIME     NOT NULL COMMENT 'Created at'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Note';

# --- !Downs

DROP TABLE `note`;

./gradlew migrate

• File names are up to you. They are applied in the natural order of the names (a 001_ prefix makes it obvious)
• <schema name> is scheme from the configuration (or the data source name if that is missing)
• They are looked up from the classpath. Add conf/ to your resources (Configuration)

Splitting into single statements#

One file can hold several statements. They are split on ;, but never on a ; inside a string, an identifier or a comment.

Where those start and end differs by product, so they are read with the rules of the product you are connected to.

A fragment that is nothing but a comment is dropped (MySQL fails it with "Query was empty"). MySQL's and MariaDB's /*!40101 ... */ and /*M!100301 ... */ are executable comments, so they are kept.

The record, and failures#

A failure stops there, and the application does not start. That is so it never takes requests in a half-migrated state. The failure stays in migration as up_error, and the next run redoes down → up.

Do not rewrite SQL that has already been applied#

The hash is checked, so rewriting it after it has been applied fails.

適用済みのマイグレーションが書き換えられています: 003_xxx.sql
（down が無効なため巻き戻せません。新しい SQL ファイルを追加してください）

In English: "a migration that has already been applied was rewritten: 003_xxx.sql (down is off, so it cannot be rolled back — add a new SQL file instead)."

Set migration.down = true (the default is false) and it redoes down → the new up. There is no command to roll back by hand. down runs in exactly two cases: the file is gone, or the hash changed.

Splitting SQL per product#

The same DDL will not do for both MySQL and PostgreSQL (AUTO_INCREMENT versus bigserial, ENGINE=InnoDB, how column comments are written). Split them by filename suffix.

conf/migration/blog_example/
	001_create_post.mysql.sql        <- mysql / mariadb only
	001_create_post.postgresql.sql   <- postgresql only
	002_add_status.sql               <- no suffix = both

• The suffix is the same name you can write in db.<name>.product (mariadb counts as mysql)
• A file with no suffix is applied on every product. SQL that works on both does not need splitting
• Files that were skipped are named in the startup log

マイグレーション: 他の製品向けを飛ばしました（いまは postgresql）: 001_create_post.mysql.sql

In English: "migration: skipped files for other products (currently postgresql): ...".

What has been applied is remembered by filename, so 001_create_post.mysql.sql and 001_create_post.postgresql.sql are separate records. A file that is present is never treated as "gone", not even a file for the other product, so it is never downed.

Two files for the same version that both run on the current product is an error (001_x.mariadb.sql and 001_x.mysql.sql both run on MySQL).

マイグレーション SQL の名前が変わったようです（いまは mysql）:
  001_create_post.sql → 001_create_post.mysql.sql
履歴の名前も変えてください。
  UPDATE migration SET name = '001_create_post.mysql.sql' WHERE name = '001_create_post.sql';
  UPDATE migration_history SET name = '001_create_post.mysql.sql' WHERE name = '001_create_post.sql';

When several servers start at once#

The row in db_lock is taken with FOR UPDATE before anything is applied, so only one machine runs. The one that could not wait long enough fails to start (migration.lock_timeout_seconds, 60 seconds by default).

Migrations written in Java#

A migration you cannot express in SQL (repacking existing data, for instance) goes in a class.

CodeMigration.add(new V20260901FillUserKana());
Migration.install();

They run once each, in versionYyyyMmDd() order, and the result is kept in migration_code. The classpath is not scanned (principle 2). Write one, then add it.

Code generation#

./gradlew codegen

From the DB schema, three kinds of file are generated per data source.

public class Post extends Table {

	/* Post ID */
	public static final Column id = new Column(instance(), "id", long.class, false, null, true);

	private static final List<Column> COLUMNS = List.of(id, title, body, published, created_at);
}

The column list is fixed at generation time (nothing is reflected on at runtime).

/*
 * このファイルは jimble が作りました（codegen）。手で直さないでください。
 * 直しても次の codegen で消えます。
 */

In English: "jimble wrote this file (codegen). Do not edit it by hand — an edit is gone at the next codegen."

Type mapping#

On PostgreSQL these are treated the same way.

On PostgreSQL#

Write db.xxx.product = postgresql and the same ./gradlew codegen works (Using the DB). Only the reading side changes; the shape of the generated classes is the same.

There are a few places where it does not line up with MySQL.

Tables that are not generated#

The tables jimble creates for itself (migration / db_cache / session / batch_* and so on) are excluded automatically. The application names its own MQ queue tables, so list those yourself.

codegen {
	package        = "db"
	exclude_tables = ["mq_blog", "mq_scheduler"]
}

Configuration#

migration {
	on_startup            = "auto"   # auto | true | false. auto applies them everywhere but locally
	down                  = false    # whether to roll back
	lock_timeout_seconds  = 60
	resource_dir          = "migration"
}

codegen {
	package        = "db"
	exclude_tables = []
}

From the CLI#

Where Gradle is not available (CI, production), the CLI does the same things.

java -cp app.jar io.jimble.db.cli.JimbleDbCli migrate
java -cp app.jar io.jimble.db.cli.JimbleDbCli codegen src/main/java

Both the configuration and the SQL are looked up from the classpath. The Gradle tasks are listed in the Gradle plugins.