H2 (MySQL) + Flyway + Sql20

AbstractSql2oTestBase.java

package co.vgw.pok.customer.persistence.sql20;

import java.sql.SQLException;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.configuration.FluentConfiguration;
import org.h2.tools.Server;
import org.sql2o.Sql2o;

public abstract class AbstractSql2oTestBase {

    private static final String JDBC_URL = "jdbc:h2:mem:db_name;MODE=MySQL;DB_CLOSE_DELAY=-1";
    private static Server server;

    protected static Sql2o sql2o;

    public static void setup() throws SQLException {
        server = Server.createTcpServer();

        FluentConfiguration flyWayConfiguration = new FluentConfiguration()
            .locations("classpath:/sql")
            .dataSource(JDBC_URL, "sa", "");

        Flyway flyway = new Flyway(flyWayConfiguration);
        flyway.migrate();

        org.h2.jdbcx.JdbcDataSource datasource = new org.h2.jdbcx.JdbcDataSource();
        datasource.setURL(JDBC_URL);
        datasource.setUser("sa");
        datasource.setPassword("");

        sql2o = new Sql2o(datasource);
    }
}

Highlights

"jdbc:h2:mem:db_name;MODE=MySQL;DB_CLOSE_DELAY=-1"; JDBC_URL

• MODE=MySQL make h2 use/check MySQL syntax
• DB_CLOSE_DELAY=-1 make h2 stay up after last client disconnects (vs closing)

.locations("classpath:/sql") locations tell Flyway where to look for migration files. Use "normal" maven/gradle magic to get the fils into test class path

new Sql2o(datasource) Sql2o Sql2o does not understand H2 URL for some reason... but luckily you can create Sql2o object with datasource.