Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.)

I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting to it. If it helps, I'm only interested in the in-memory case.

H2 supports various semicolon-separated modifiers at the end of the URL, but I didn't find one for automatically creating a schema. Is there such a feature?

Yes, H2 supports executing SQL statements when connecting. You could run a script, or just a statement or two:

String url = "jdbc:h2:mem:test;" + 
             "INIT=CREATE SCHEMA IF NOT EXISTS TEST"
String url = "jdbc:h2:mem:test;" + 
             "INIT=CREATE SCHEMA IF NOT EXISTS TEST\\;" + 
                  "SET SCHEMA TEST";
String url = "jdbc:h2:mem;" + 
             "INIT=RUNSCRIPT FROM '~/create.sql'\\;" + 
                  "RUNSCRIPT FROM '~/populate.sql'";

Please note the double backslash (\\) is only required within Java. The backslash(es) before ; within the INIT is required.

If you are using spring with application.yml, the following will work for you:

spring:
  datasource:
    url: jdbc:h2:mem:mydb;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;INIT=CREATE SCHEMA IF NOT EXISTS calendar

What Thomas has written is correct, in addition to that, if you want to initialize multiple schemas you can use the following. Note there is a \\; separating the two create statements.

    EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
                    .setType(EmbeddedDatabaseType.H2)
                    .setName("testDb;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;INIT=create " +
                            "schema if not exists " +
                            "schema_a\\;create schema if not exists schema_b;" +
                            "DB_CLOSE_DELAY=-1;")
                    .addScript("sql/provPlan/createTable.sql")
                    .addScript("sql/provPlan/insertData.sql")
                    .addScript("sql/provPlan/insertSpecRel.sql")
                    .build();

ref : http://www.h2database.com/html/features.html#execute_sql_on_connection

"By default, when an application calls DriverManager.getConnection(url, ...) and the database specified in the URL does not yet exist, a new (empty) database is created."—H2 Database.

Addendum: @Thomas Mueller shows how to Execute SQL on Connection, but I sometimes just create and populate in the code, as suggested below.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/** @see http://stackoverflow.com/questions/5225700 */
public class H2MemTest {

    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
        Statement st = conn.createStatement();
        st.execute("create table customer(id integer, name varchar(10))");
        st.execute("insert into customer values (1, 'Thomas')");
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select name from customer");
        while (rset.next()) {
            String name = rset.getString(1);
            System.out.println(name);
        }
    }
}

If you are using Spring Framework with application.yml and having trouble to make the test find the SQL file on the INIT property, you can use the classpath: notation.

For example, if you have a init.sql SQL file on the src/test/resources, just use:

url=jdbc:h2:~/test;INIT=RUNSCRIPT FROM 'classpath:init.sql';DB_CLOSE_DELAY=-1;

If you are using spring and xml configuration, here is an example how this should be done:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" depends-on="h2Server">
<property name="driverClassName" value="org.h2.Driver"/>
<property name="url"
          value="jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM 'classpath:db/create_tables.sql'\;RUNSCRIPT FROM 'classpath:db/insert.sql';TRACE_LEVEL_FILE=4;TRACE_LEVEL_SYSTEM_OUT=3;"/>
<property name="username" value="sa"/>
<property name="password" value=""/>