Using Grails Postgresql Extensions for GORM in Micronaut

09 June 2019

Introduction

In Grails, there is a nice extension for GORM that enable us to use json types in the Postgres database. At my work in Cardlay, we had a situation where we would like to store JSON but still using the RDBMS features, and this would be nice for one of our microservices, but Micronaut is preferred for real micro-services.

Tip	It turned out to be rather easy to use in Micronaut!

Here, I’ll share the steps needed. First a micronaut app is created, with Groovy as the language and hibernate-gorm as a feature. Thus in the micronaut console do:

create-app --lang groovy --inplace --profile service --features hibernate-gorm,groovy,spock

Including Postgres Extensions

In build.gradle include in the dependencies block:

compile 'org.grails.plugins:postgresql-extensions:6.1.0'

The dialect of Hibernate must know the extensions, so in src/main/resources/application.yml add

hibernate:
  dialect: net.kaleidos.hibernate.PostgresqlExtensionsDialect

Domain Class

We can now add a domain class that make use of the extensions:

package postgresql.extensions.micronaut

import grails.gorm.annotation.Entity
import net.kaleidos.hibernate.usertype.JsonbMapType
import net.kaleidos.hibernate.usertype.ArrayType
import net.kaleidos.hibernate.usertype.HstoreMapType

@Entity
class GormStorageObject {

    String job
    Map history
    Integer[] favoriteNumbers = []
    Map testAttributes

    static mapping = {
        history type: JsonbMapType
        favoriteNumbers type:ArrayType, params: [type: Integer]
        testAttributes type: HstoreMapType
    }
}

To create a database for the application, we can use docker-compose.

docker-compose.yml

version: "2"
services:
  database:
    image: postgres:9.5-alpine
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - ./postgresdata/seed.sql:/docker-entrypoint-initdb.d/seed.sql

Where the `seed.sql file adds the required hstore extension for the database:

postgresdata/seed.sql

CREATE EXTENSION hstore;

Running

docker-compose up

And starting the application

./gradlew run

And peeking into the database we see the table is created with the types as expected

postgres=# \d gorm_storage_object
                     Table "public.gorm_storage_object"
      Column      |          Type          | Collation | Nullable | Default
------------------+------------------------+-----------+----------+---------
 id               | bigint                 |           | not null |
 version          | bigint                 |           | not null |
 job              | character varying(255) |           | not null |
 test_attributes  | hstore                 |           | not null |
 history          | jsonb                  |           | not null |
 favorite_numbers | integer[]              |           | not null |
Indexes:
    "gorm_storage_object_pkey" PRIMARY KEY, btree (id)

Using the extensions

As the extensions is a Grails plugin, the usage is originally setup from the Grails plugin desctiptor. This does not happen in Micronaut, but we can wire this togeather easily at startup, listening for an event of type ServerStartupEvent. Add a class like this

package postgresql.extensions.micronaut

import groovy.util.logging.Slf4j
import io.micronaut.context.event.ApplicationEventListener
import io.micronaut.runtime.server.event.ServerStartupEvent
import net.kaleidos.hibernate.postgresql.criteria.ArrayCriterias
import net.kaleidos.hibernate.postgresql.criteria.HstoreCriterias
import net.kaleidos.hibernate.postgresql.criteria.JsonCriterias

import javax.inject.Singleton

@Slf4j
@Singleton
class GormExtensionInitializer implements ApplicationEventListener {

    @Override
    void onApplicationEvent(ServerStartupEvent event) {
        new ArrayCriterias()
        new HstoreCriterias()
        new JsonCriterias()
    }

}

Saving Our First Data Item

To be able to store objects, lets first create a service to save and a controller that can use it:

package postgresql.extensions.micronaut

import grails.gorm.services.Service

import javax.inject.Singleton

@Singleton
@Service(GormStorageObject)
interface GormStorageService {

    List findAll()

    GormStorageObject save(GormStorageObject gormStorageObject)
}

And a controller

package postgresql.extensions.micronaut

import groovy.util.logging.Slf4j
import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get
import io.micronaut.http.annotation.Post

@Slf4j
@Controller("/storage")
class StorageController {

    GormStorageService gormStorageService

    StorageController(GormStorageService gormStorageService) {
        this.gormStorageService = gormStorageService
    }

    @Get("/")
    List index() {
        log.info("List all")
        gormStorageService.findAll()
    }

    @Post("/")
    GormStorageObject save(GormStorageObject gormStorageObject) {
        log.info("Save: ${gormStorageObject}")
        gormStorageService.save(gormStorageObject)
    }
}

We can now do a first manual test of the application

curl localhost:8080/storage -X POST -H 'content-type: application/json' -d '{"job":"Job 1", "history": {"date":"2019-05-26", "activity":"Fun stuff", "price": 12}, "favoriteNumbers": [2, 3, 5, 7], "testAttributes": {"foo":"bar","baz":"rab"} }'

curl localhost:8080/storage

Peeking into the database, we can see all data is correct stored:

postgres=# select * from gorm_storage_object ;

 id | version |  job  |      test_attributes       |                           history                            | favorite_numbers
----+---------+-------+----------------------------+--------------------------------------------------------------+------------------
  1 |       0 | Job 1 | "baz"=>"rab", "foo"=>"bar" | {"date": "2019-05-26", "price": 12, "activity": "Fun stuff"} | {2,3,5,7}
(1 row)

Querying Using the Extensions

Arrays

Service to use the filtering function:

package postgresql.extensions.micronaut

import grails.gorm.transactions.Transactional
import javax.inject.Singleton

@Singleton
@Transactional
class GormExtensionsService {

    List filterByHasFavoriteNumbers(Integer[] numbers) {
        GormStorageObject.withCriteria {
            pgArrayContains 'favoriteNumbers', numbers
        }
    }
}

And adding the controller method:

@Get("/numbers")
List filterByNumbers(Integer[] numbers) {
    log.info("filterByNumbers: ${numbers}")
    gormExtensionsService.filterByHasFavoriteNumbers(numbers)
}

We can now run a simple curl test:

curl "localhost:8080/storage/numbers?numbers=2&numbers=5"
[{"job":"Job 1","history":{"date":"2019-05-26","price":12.0,"activity":"Fun stuff"},"favoriteNumbers":[2,3,5,7],"testAttributes":{"foo":"bar","baz":"rab"},"id":1}]

curl "localhost:8080/storage/numbers?numbers=2&numbers=4"
[]

Hstore

Service method

List filterByAttributes(String attribute) {
    GormStorageObject.withCriteria {
        pgHstoreContainsKey "testAttributes", attribute
    }
}

Controller method

@Get("/attribute")
List filterByTestAttributes(String attribute) {
    log.info("filterByTestAttributes: ${attribute}")
    gormExtensionsService.filterByAttributes(attribute)
}

Test

curl "localhost:8080/storage/attribute?attribute=foo"
[{"job":"Job 1","history":{"date":"2019-05-26","price":12.0,"activity":"Fun stuff"},"favoriteNumbers":[2,3,5,7],"testAttributes":{"foo":"bar","baz":"rab"},"id":1}]

curl "localhost:8080/storage/attribute?attribute=not"
[]

JSON

Service method

List filterByHistory(String date) {
    GormStorageObject.withCriteria {
        pgJson 'history', '->>', "date", '=', date
    }
}

Controller method

@Get("/history")
List filterByHistory(String date) {
    log.info("filterByHistory: ${date}")
    gormExtensionsService.filterByHistory(date)
}

Test

curl "localhost:8080/storage/history?date=2019-05-26"
[{"job":"Job 1","history":{"date":"2019-05-26","price":12.0,"activity":"Fun stuff"},"favoriteNumbers":[2,3,5,7],"testAttributes":{"foo":"bar","baz":"rab"},"id":1}]

curl "localhost:8080/storage/history?date=2019-05-27"
[]

Testing

We should of course still write a test for our service and controller. As this is very Postgres specific, we should use the postgres database. Testcontainers make this easy for us.

In build.gradle include in the dependencies

testCompile 'org.testcontainers:testcontainers:1.11.3'
testCompile 'org.testcontainers:spock:1.11.3'
testCompile 'org.testcontainers:postgresql:1.11.3'

And we will add an src/main/resources/application-test.yml file, to let the test environment also use the correct hibernate dialect and driver.

hibernate:
  dialect: net.kaleidos.hibernate.PostgresqlExtensionsDialect
dataSource:
  pooled: true
  jmxExport: true
  driverClassName: org.postgresql.Driver

One caveat, if we are using hstore, we cannot just use the build in PostgreSQLContainer, as it does not have the hstore extension. We can solve this by using the already created docker-compose file.

Then we can test the services using the following type of test:

package postgresql.extensions.micronaut

import io.micronaut.context.ApplicationContext
import io.micronaut.runtime.server.EmbeddedServer
import org.testcontainers.containers.DockerComposeContainer
import org.testcontainers.spock.Testcontainers
import spock.lang.AutoCleanup
import spock.lang.Shared
import spock.lang.Specification

@Testcontainers
class GormExtensionServiceSpec extends Specification {

    @Shared
    DockerComposeContainer dockerComposeContainer =
            new DockerComposeContainer(new File("docker-compose.yml"))
                    .withExposedService("database", 5432)

    @Shared @AutoCleanup
    EmbeddedServer embeddedServer

    @Shared
    GormStorageService gormStorageService

    @Shared
    GormExtensionsService gormExtensionsService

    void setupSpec() {
        String host = dockerComposeContainer.getServiceHost("database", 5432)
        Integer port = dockerComposeContainer.getServicePort("database", 5432)

        embeddedServer = ApplicationContext
                .build()
                .properties(
                        'dataSource.url':"jdbc:postgresql://${host}:${port}/postgres",
                        'dataSource.username':"postgres",
                        'dataSource.password':"postgres",
                )
                .packages(
                        'postgresql.extensions.micronaut'
                )
                .run(EmbeddedServer)
        gormExtensionsService = embeddedServer.applicationContext.getBean(GormExtensionsService)
        gormStorageService = embeddedServer.applicationContext.getBean(GormStorageService)
    }


    void "test save and filteringByAttributes"() {
        setup:
        gormStorageService.save(new GormStorageObject(
                job: 'irrelevant',
                history: [date: '2019-05-26', activity:'Speakers dinner'],
                favoriteNumbers: [1,2,3],
                testAttributes: [happy:'path',funny:'yes']
        ))

        when:
        List result = gormExtensionsService.filterByAttributes('happy')

        then:
        result.size() == 1
        result.first().testAttributes.happy == 'path'
    }
}

And the controller and full application using a low level client:

package postgresql.extensions.micronaut

import io.micronaut.context.ApplicationContext
import io.micronaut.http.HttpRequest
import io.micronaut.runtime.server.EmbeddedServer
import io.micronaut.http.client.RxHttpClient
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
import org.testcontainers.containers.DockerComposeContainer
import org.testcontainers.spock.Testcontainers
import spock.lang.AutoCleanup
import spock.lang.Shared
import spock.lang.Specification

@Testcontainers
class StorageControllerSpec extends Specification {

    @AutoCleanup
    @Shared
    RxHttpClient client

    @Shared
    DockerComposeContainer dockerComposeContainer =
            new DockerComposeContainer(new File("docker-compose.yml"))
                    .withExposedService("database", 5432)

    @Shared @AutoCleanup
    EmbeddedServer embeddedServer

    void setupSpec() {
        String host = dockerComposeContainer.getServiceHost("database", 5432)
        Integer port = dockerComposeContainer.getServicePort("database", 5432)

        embeddedServer = ApplicationContext
                .build()
                .properties(
                        'dataSource.url':"jdbc:postgresql://${host}:${port}/postgres",
                        'dataSource.username':"postgres",
                        'dataSource.password':"postgres",
                )
                .packages(
                        'postgresql.extensions.micronaut'
                )
                .run(EmbeddedServer)
        client = embeddedServer.applicationContext.createBean(RxHttpClient, embeddedServer.getURL())

        GormStorageObject object = new GormStorageObject(
                job: 'irrelevant',
                history: [date: '2019-05-26', activity:'Speakers dinner'],
                favoriteNumbers: [1,2,3],
                testAttributes: [happy:'path',funny:'yes']
        )

        HttpRequest request = HttpRequest.POST("/storage", object)
        HttpResponse response = client.toBlocking().exchange(request, Map)
    }

    void "test index"() {
        given:
        HttpResponse response = client.toBlocking().exchange("/storage")

        expect:
        response.status == HttpStatus.OK
    }

    void "test filterByNumbers"() {
        when:
        def result = client.toBlocking().retrieve(HttpRequest.GET('/storage/numbers?numbers=2&numbers=3'), List)

        println result

        then:
        result
        result.size() == 1
        result.first().favoriteNumbers == [1,2,3]
    }
}

Summary

It is quite easy to integrate the postgresql extensions for GORM from the Grails plugin. Both if you would like the array extension, the hstore or the JSON extension.

As an added bonus, we have tested the service using testcontainers.

The version of Micronaut used is 1.1.2, and the code can be found in this github repo: https://github.com/JacobAae/postgresql-extensions-micronaut^

Thanks

Thanks goes to Iván López for hints on issues that could arrise, and to Mathias Strange Hansen for doing the initial work at Cardlay A/S

Back