Setting up a database from scratch at run time

Question

I'm currently working on a Java database application that I want to be entirely self-contained (i.e. single .jar file and a config file, nothing else). This is a really small project and I don't have fancy build pipelines and extra application data like I would for a commercial codebase - hence the desire for a simple single-jar solution.

Part of achieving this involves embedding H2 in the application and setting up the database at runtime. However, I'm not sure how to go about applying my desired schema in this use case. Let's use this table as an example.

+-------------+------------+----------+---------+
| Column name | Data type  | Nullable | Default |
+-------------+------------+----------+---------+
| id          | BIGINT     | FALSE    | -       |
| prefix      | VARCHAR(3) | FALSE    | 'g!'    |
+-------------+------------+----------+---------+

Here are my two solutions so far.

Programmatically laying out tables with jOOQ:

try (DSLContext ctx = DSL.using(dataSource, SQLDialect.MYSQL)) {
    ctx.createTableIfNotExists("guilds")
        .column("id", SQLDataType.BIGINT.nullable(false))
        .column("prefix", SQLDataType.VARCHAR(3).nullable(false).defaultValue("g!"))
        .constraint(constraint("PK_GUILDS").primaryKey(field("id")))
        .execute();
}

Naturally, in this use case, I can't use jOOQ's code generation since there's nothing to generate classes for. This works and is entirely programmatic, but it's really ugly and clogs up the class with static imports. Expanding upon it to add more tables and/or columns is painful and makes the code even less readable.

The other thing I tried was Flyway, which is pretty simple and not as horrible. All I need is a small .sql file that sets up my schema, like so:

create table guilds(
    id BIGINT NOT NULL,
    prefix VARCHAR(3) NOT NULL,
    PRIMARY KEY(id)
);

Then I can run a Gradle task to generate an empty H2 database with all the tables already set up and ready to go. Gradle even lets me specify this task to be run right before build. I can also use jOOQ code generation in this case, which makes a lot of things way easier. This all happens at compile-time though, so as far as I understand, I'd have to package the application with an empty database file. This, of course, doesn't fulfill the single-jar desire I intially wanted.

Is there a clean, "conventional" way to do this, or is building an application in this manner just not something that anybody does when a database is involved?

user_11657446 · Answer

You should use Spring–Data and let it do the work for you. In application properties you setup your datasource. You create your entities (annotated classes) and your repositories. Once you are done and pretending you are using hibernate you could just put a property like "hibernate.hbm2ddl.auto", "create-drop" in your application properties. This would automatically create and also drop the database for you. No extra code needed. There are more config options like this. You will find them in the manual.

user_521799 · Answer

From a jOOQ perspective, we strongly recommend you choose the Flyway approach, or something similar. The reason for this is that when your project grows, you will be very happy to have chosen a path where you're in full control of your schema evolution, instead of letting third party products govern that for you, and the jOOQ integration works effortlessly.

The jOOQ DDL API, which you've tried before, could work as well, of course, e.g. when you use this API with Flyway or something similar as a programmatic schema evolution API. You can still produce a single jar as output even if you have multiple modules as input. For example, you could use the maven-assembly-plugin to produce this single jar. This particular build constraint should not be a driving factor in your decision of how to manage your database schema.

Setting up a database from scratch at run time

2 Answers