20

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?

Markus W Mahlberg
  • 19,711
  • 6
  • 65
  • 89
daydreamer
  • 87,243
  • 191
  • 450
  • 722
  • Why don't connecting to `test_common` db work? Does your code do programmatic lookup of the db/schema name? – gerrytan May 26 '14 at 22:35
  • Yes, it does, using `@Configuration` class in `Spring Framework`. See http://stackoverflow.com/questions/23874693/liquibase-does-not-honor-schema-name-for-h2-but-mysql-is-fine – daydreamer May 26 '14 at 22:38

2 Answers2

21

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.

leaqui
  • 533
  • 6
  • 22
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 1
    You can also create the schema if it doesn't already exist as part of the connection URL - https://stackoverflow.com/questions/5225700/can-i-have-h2-autocreate-a-schema-in-an-in-memory-database – Alexander Taylor Sep 22 '22 at 06:08
1

It looks like you are interacting with Liquibase through the Java APIs. There is a setDefaultSchemaName() method on the Database object that you can use to set a different default schema.

Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64