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?

Thomas Mueller · Accepted Answer · 2013-07-09T08:02:49.777

210

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.

edited Jul 09 '13 at 08:02

answered Mar 08 '11 at 05:05

Thomas Mueller

48,905
14
116
132

Thank you very much; not sure how I missed that in the (excellent) documentation. – Laird Nelson Mar 08 '11 at 12:29
Thank you, it made the work as I was using generated changesets from liquibase that use the schema name for generated xml. – Jaime Hablutzel Jul 11 '11 at 22:21
2

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 `jdbc:h2:mem:test;INIT=RUNSCRIPT FROM 'script1.sql'\\\;RUNSCRIPT FROM script2.sql'` in your hibernate config. – Johnny Aug 11 '11 at 16:44
@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...` – Thomas Mueller Aug 11 '11 at 17:11
You are right, @Thomas. It works. But with triple slash it also works fine. – Johnny Aug 12 '11 at 06:52
is it possible to fetch script from classpath file? – pinkpanther Sep 30 '16 at 16:35
2

@pinkpanther yes, see http://stackoverflow.com/questions/4490138/problem-with-init-runscript-and-relative-paths – Thomas Mueller Oct 04 '16 at 06:50

score 34 · Answer 2 · edited Aug 19 '21 at 12:07

34

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

edited Aug 19 '21 at 12:07

Dherik

17,757
11
115
164

answered Dec 29 '17 at 09:28

Marquis Blount

7,585
8
43
67

It is also possible to create a schema this way in Grails 3 – Rhineb Jun 06 '18 at 13:50
2

And if you want to set schema as well: `INIT=CREATE SCHEMA IF NOT EXISTS calendar\;SET SCHEMA calendar` – Roar S. Jul 28 '22 at 13:15

Zeus · Answer 3 · 2018-04-06T15:14:37.897

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

trashgod · Answer 4 · 2011-03-08T08:28:01.483

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

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, Mar 08 '11 at 00:53

Dherik · Answer 5 · 2019-12-10T12:43:03.213

7

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;

edited Dec 10 '19 at 12:43

answered Dec 09 '19 at 20:07

Dherik

17,757
11
115
164

score 1 · Answer 6 · answered Jun 28 '21 at 10:10

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=""/>

score 0 · Answer 7 · answered Jul 23 '23 at 08:42

I had a problem that causes confusing message! I had been added

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

to the configuration for handling keyword names. but it keeps saying category "menu" does not exists. I tried to verify if INIT script is running by tampring it to something wrong, and h2 causes an error (which is a true behaviour). So I was sure that menu schema will be generated.

Finally I found that h2 database says "menu" schema does not exists not menu schema!

Then I removed globally_quoted_identifiers and everything goes fine!

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

7 Answers7

Linked