Grails - Use IntelliJ's Database Tool to connect to in-memory H2 instance

16 September 2016

H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode

When you use Grails for a new project, the default DBMS (DataBase Management System) is H2. A cool characteristic of this DBMS is its capability to handle in-memory tables: it is possible to create a database living in the RAM memory.

In Grails 3.x your database configuration might look as follows:


dataSource:
	  pooled: true
	  jmxExport: true
	  driverClassName: org.h2.Driver
	  username: sa
	  password:

environments:
    development:
	     dataSource:
	          dbCreate: create-drop
            url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    test:
       dataSource:
            dbCreate: create-drop
            url: jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

One nice feature of IntelliJ is the Database Tool Window.

Wouldn't be nice to connect to your H2 in memory database using IntelliJ? Well that's possible using a TCP Server for client/server connections.

You can accomplish this by simply doing the following in your BootStrap.groovy file


import org.h2.tools.Server

class BootStrap {
	  final String[] args = [
	        "-tcpPort", "8092",
	        "-tcpAllowOthers"]

	  Server server

	  def init = { servletContext ->
	        server = Server.createTcpServer(args).start()
	  }

	  def destroy = {
	        server.stop()
	  }
}

Doing this will start a TCP server on port 8092 that allow others to connect

IMPORTANT: This is an example for development/test environment, you should modify this behavior for production environment.

After we start our app, we can use the Database Tool Window and add a connection to a remote H2 database

I hope you find this helpful.