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

Question

(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?

Solution 1

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.

Solution 2

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

Solution 3

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

Solution 4

"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);
        }
    }
}

Solution 5

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;

View more solutions

Comments

[Laird Nelson]

Yes, and that is the catalog or database, not a schema within it. So you might open a connection to jdbc:h2:mem:test, for example, but by default you are placed in the PUBLIC schema, and no other schemata exist.

[Laird Nelson]

Thank you very much; not sure how I missed that in the (excellent) documentation.

[Jaime Hablutzel]

Thank you, it made the work as I was using generated changesets from liquibase that use the schema name for generated xml.

[Johnny]

Note that if you use H2 with hibernate and want to run multiple scripts by calling RUNSCRIPT, you should type triple backslash (\\\). For example, you should set up <property name="hibernate.connection.url">jdbc:h2:mem:test;INIT=RUNSCR‌​IPT FROM 'script1.sql'\\\;RUNSCRIPT FROM script2.sql'</property> in your hibernate config.

[Thomas Mueller]

@Johnny Are you sure? It looks like the ; doesn't need to be escaped (there is an unescaped ; before the INIT). Could you try if using only one backslash works? 'script1.sql'\;RUNSCRIPT...

[Johnny]

You are right, @Thomas. It works. But with triple slash it also works fine.

[pinkpanther]

is it possible to fetch script from classpath file?

[Thomas Mueller]

@pinkpanther yes, see stackoverflow.com/questions/4490138/…

[Rhineb]

It is also possible to create a schema this way in Grails 3

[Deepboy]

Thankyou very much. I used this tip to fix an issue that was causing my code not to work for 4 days.

[carlos palma]

works like a charm