Running H2 in SQL Server Mode With Custom Schema Name on Connection

These days I am working on a multi-tenant SaaS application where we are using SQL Server as our main transactional database. Using SQL Server is fine when you run the application but for unit and integration tests you want to use an in-memory database for quick turn around, better isolation, free from any external service to be available. Last thing you want is developers to not write tests because they take too much time to run. I love the experience of running a build on a clean machine and it works without any setup or configuration.

The problem that you face with in-memory database is that when you start using native SQL queries then compatibility becomes an issue. JPA/hibernate cause performance issues and many times you have to use native SQL queries.

I want to use in-memory database but at the same time I don’t want my tests to fail as soon as I use native SQL queries.

I figured out that H2 supports a mode option that H2 uses to emulate behaviour of specific database.

To run H2 in SQL Server mode you can use following JDBC URL.

jdbc:h2:mem:testdb;MODE=MSSQLServer

If you are using Spring Boot then you can specify using the following property.

spring.datasource.url=jdbc:h2:mem:testdb;MODE=MSSQLServer

As you can see we have specify mode as MSSQLServer . With this mode, H2 emulates following features:

• For aliased columns, ResultSetMetaData.getColumnName() returns the alias name and getTableName() returns null .
• Identifiers may be quoted using square brackets as in [Test] .
• For unique indexes, NULL is distinct. That means only one row with NULL in one of the columns is allowed.
• Concatenating NULL with another value results in the other value.
• Text can be concatenated using ‘+’.
• Arguments of LOG() function are swapped.
• MONEY data type is treated like NUMERIC(19, 4) data type. SMALLMONEY data type is treated like NUMERIC(10, 4) data type.
• IDENTITY can be used for automatic id generation on column level.
• Table hints are discarded. Example: SELECT * FROM table WITH (NOLOCK) .
• Datetime value functions return the same value within a command.
• 0x literals are parsed as binary string literals.
• TRUNCATE TABLE restarts next values of generated columns.

One more requirement that we had was to use specific schema names. Our entity classes and repository interface are using schema name like shown below.

@Table(schema = "[masters]", name = "[users]")
public class UserEntity

public interface UserRepository extends JpaRepository<UserEntity, Long> {

    @Override
    @Query(value = "select * from [masters].[users] where active = 1", nativeQuery = true)
    List<UserEntity> findAll();

So, we had to make tell H2 to use specify schema.

I was able to achieve that using following

jdbc:h2:mem:testdb;MODE=MSSQLServer;INIT=CREATE SCHEMA IF NOT EXISTS [masters]\\;SET SCHEMA [masters]

So, we used INIT option to specific two SQL queries — first create schema and second set the schema that H2 will use.

Conclusion

It took me couple of hours to get all of this running and make my tests pass. I hope it saves your time if you end up using this setup.