H2: how to set default schema and database?
database, h2, liquibase, spring
Solution
Default schema is `PUBLIC`
For the record, the `Commands` page of the H2 Database site for the `SET SCHEMA` command says:
The default schema for new connections is `PUBLIC`.
That documentation also notes that you can specify the default schema when connecting:
This setting can be appended to the database URL: jdbc:h2:test;SCHEMA=ABC
Only one database
As for accessing various databases, H2 does not support the SQL Standard concepts of `CLUSTER` or `CATALOG`. You connect to one specific database (catalog) as part of your JDBC URL. Connections to that database are limited to that one single database. See the Question, Can you create multiple catalogs in H2? with an Answer by Thomas Mueller.
You could open another connection to another database but it would be entirely separate.
So talking about a “default database” has no meaning with H2 Database.
Problem
I am using `Liquibase` for my database updates and testing it against `H2`. I am using `Spring` to configure the properties. I use ``` dataSource.setUrl("jdbc:h2:mem:test_common"); ``` to connect to `test_common` database, but it did not work out. I realized that in `H2` `database != Schema`, so I tried to put a default schema to `test_common` as ``` dataSource.setUrl("jdbc:h2:mem:test_common;INIT=CREATE SCHEMA test_common\\; SET SCHEMA test_common"); ``` but this didn't work out, I see logs as ``` INFO 5/26/14 2:24 PM:liquibase: Dropping Database Objects in schema: TEST_COMMON.PUBLIC INFO 5/26/14 2:24 PM:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: Successfully released change log lock INFO 5/26/14 2:24 PM:liquibase: Successfully acquired change log lock INFO 5/26/14 2:24 PM:liquibase: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Table network created INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: ChangeSet liquibase/2014/1-1.xml::05192014.1525::h2 ran successfully in 5ms INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-1.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: New row inserted into network INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: ChangeSet liquibase/2014/1-2.xml::05192014.1525::h2 ran successfully in 5ms INFO 5/26/14 2:24 PM:liquibase: liquibase/changelog.xml: liquibase/2014/1-2.xml::05192014.1525::h2: Reading from PUBLIC.DATABASECHANGELOG ``` How do I set default schema and database name in H2?