Loading...

June 11, 2016

Ratpacked: Using Multiple DataSources

Recently on our project where we use Ratpack we had to get data from different databases in our Ratpack application. We already used the HikariModule to get a DataSource to connect to one database. Then with the SqlModule we use this DataSource to create a Groovy Sql instance in the registry. In our code we use the Sql object to query for data. To use the second database we used the Guice feature binding annotations to annotate a second DataSource and Sql object. In this post we see how we can achieve this.

UPDATE: In a following blog post we learn another way to add multiple datasources to our Ratpack application.

Interestingly while I was writing this post there was a question on the Ratpack Slack channel on how to use multiple datasources. The solution in this post involves still a lot of code to have a second DataSource. In the channel Danny Hyun mentioned a more generic solution involving a Map with multiple datasources. In a follow-up blog post I will write an implementation like that, so we have a more generic solution, with hopefully less code to write. BTW the Ratpack Slack channel is also a great resource to learn more about Ratpack.

We first take a look at the solution where we actually follow the same module structure as the HikariModule and SqlModule. We also use binding annotations that are supported by Guice. With a binding annotation we can have multiple instances of the same type in our registry, but still can distinguish between them. We write a binding annotation for the specific DataSource and Sql objects we want to provide via the registry:

// File: src/main/groovy/mrhaki/ratpack/configuration/LocationDataSource.java
package mrhaki.ratpack.configuration;

import com.google.inject.BindingAnnotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Binding annotation for DataSource of location database.
 */
@BindingAnnotation
@Target({ElementType.FIELD, ElementType.PARAMETER, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface LocationDataSource {
}
// File: src/main/groovy/mrhaki/ratpack/configuration/LocationSql.java
package mrhaki.ratpack.configuration;

import com.google.inject.BindingAnnotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Binding annotation for Groovy Sql object for location database.
 */
@BindingAnnotation
@Target({ElementType.FIELD, ElementType.PARAMETER, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface LocationSql { }

Instead of writing a specific binding annotation we can use the @Named binding annotation and use a name as the value. We will see an example of this in the LocationHikariModule we write.

We are going to write a new class that extends ConfigurableModule to configure a DataSource with Hikari. We use the same structure as the default HikariModule that is supplied by Ratpack:

// File: src/main/groovy/mrhaki/ratpack/configuration/LocationHikariModule.groovy
package mrhaki.ratpack.configuration

import com.google.inject.Provides
import com.google.inject.Singleton
import com.google.inject.name.Named
import com.zaxxer.hikari.HikariDataSource
import ratpack.guice.ConfigurableModule
import ratpack.hikari.HikariService

import javax.sql.DataSource

class LocationHikariModule extends ConfigurableModule<LocationHikariConfig> {
    
    @Override
    protected void configure() {
        // Add HikariService and DataSource
        // via @Provides annotation in this class.
    }

    /**
     * Create a HikariService instance. This object closes
     * the DataSource when Ratpack stops. The LocationHikariConfig
     * object is created by a @Provides method in the 
     * ConfigurableModule class (which we extend from).
     * 
     * @param config Configuration object with properties for creating a HikariDataSource.
     * @return HikariService object with a binding annotation name locationHikariService.
     */
    @Provides
    @Singleton
    @Named('locationHikariService')
    HikariService locationHikariService(final LocationHikariConfig config) {
        return new HikariService(new HikariDataSource(config))
    }

    /**
     * Create a DataSource object with a binding annotation LocationDataSource.
     * 
     * @param hikariService HikariService with binding 
     *        annotation name locationHikariService to get DataSource.
     * @return New DataSource with binding annotation LocationDataSource.a
     */
    @Provides
    @Singleton
    @LocationDataSource
    DataSource locationDataSource(@Named('locationHikariService') HikariService hikariService) {
        return hikariService.dataSource
    }
    
}

And the configuration class we need:

// File: src/main/groovy/mrhaki/ratpack/configuration/LocationHikariConfig.groovy
package mrhaki.ratpack.configuration

import com.zaxxer.hikari.HikariConfig

/**
 * We need a separate class for the configuration
 * of Hikari to get a specific DataSource.
 * If we would re-use HikariConfig to configure
 * the LocationHikariModule we would only have
 * one instance of HikariConfig, because a
 * ConfigurableModule adds a instance of
 * HikariConfig to the registry. And by type
 * is this instance used again. 
 */
class LocationHikariConfig extends HikariConfig {
}

With the LocationHikiriModule class we provided a HikariService, DataSource and LocationHikariConfig instance for the registry. Now we want to use the DataSource and create a Sql instance. We create a LocationSqlModule class and a LocationSqlProvider to create a Sql instance with the binding annotation LocationSql:

// File: src/main/groovy/mrhaki/ratpack/configuration/LocationSqlModule.groovy
package mrhaki.ratpack.configuration

import com.google.inject.AbstractModule
import com.google.inject.Scopes
import groovy.sql.Sql

class LocationSqlModule extends AbstractModule {
    @Override
    protected void configure() {
        // Bind Groovy Sql to registry,
        // but annotated as LocationSql,
        // so we can have two Sql instances in
        // the registry.
        bind(Sql)
            .annotatedWith(LocationSql)
            .toProvider(LocationSqlProvider)
            .in(Scopes.SINGLETON)
    }
}
// File: src/main/groovy/mrhaki/ratpack/LocationSqlProvider.groovy
package mrhaki.ratpack.configuration

import com.google.inject.Provider
import groovy.sql.Sql

import javax.inject.Inject
import javax.sql.DataSource

class LocationSqlProvider implements Provider<Sql> {
    
    private final DataSource dataSource

    /**
     * Assign DataSource when creating object for this class.
     * 
     * @param dataSource Specific DataSource specified by 
     *        the LocationDataSource binding annotation. 
     */
    @Inject
    LocationSqlProvider(@LocationDataSource final DataSource dataSource) {
        this.dataSource = dataSource
    }

    /**
     * Create new Groovy Sql object with the DataSource set 
     * in the constructor.
     * 
     * @return Groovy Sql instance.
     */
    @Override
    Sql get() {
        return new Sql(dataSource)
    }
    
}

We are ready to write a class that uses two Sql instances. We have a CustomerRepository interface to get a Customer that contains properties that come from two databases:

// File: src/main/groovy/mrhaki/ratpack/CustomerRepository.groovy
package mrhaki.ratpack

import ratpack.exec.Promise

interface CustomerRepository {
    Promise<Customer> getCustomer(final String id)
}
// File: src/main/groovy/mrhaki/ratpack/configuration/CustomerSql.groovy
package mrhaki.ratpack

import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import mrhaki.ratpack.configuration.LocationSql
import ratpack.exec.Blocking
import ratpack.exec.Promise

import javax.inject.Inject

class CustomerSql implements CustomerRepository {

    private final Sql customerSql
    private final Sql locationSql

    /**
     * We are using constructor injection to 
     * get both Sql instances.
     *
     * @param customerSql Sql to access the customer database.
     * @param locationSql Sql to access the location database. Sql instance
     * is indicated by binding annotation LocationSql.
     */
    @Inject
    CustomerSql(final Sql customerSql, @LocationSql final Sql locationSql) {
        this.customerSql = customerSql
        this.locationSql = locationSql
    }

    /**
     * Get customer information with address. We use
     * both databases to find the information 
     * for a customer with the given id.
     * 
     * @param id Identifier of the customer we are looking for.
     * @return Customer with filled properties.
     */
    @Override
    Promise<Customer> getCustomer(final String id) {
        Blocking.get {
            final String findCustomerQuery = '''\
                SELECT ID, NAME, POSTALCODE, HOUSENUMBER
                FROM CUSTOMER
                WHERE ID = :customerId
                '''

            customerSql.firstRow(findCustomerQuery, customerId: id)
        }.map { customerRow ->
            new Customer(
                    id: customerRow.id,
                    name: customerRow.name,
                    address: new Address(
                            postalCode: customerRow.postalcode,
                            houseNumber: customerRow.housenumber))
        }.blockingMap { customer ->
            final String findAddressQuery = '''\
                SELECT STREET, CITY
                FROM address
                WHERE POSTALCODE = :postalCode
                  AND HOUSENUMBER = :houseNumber
                '''

            final GroovyRowResult addressRow =
                    locationSql.firstRow(
                            findAddressQuery,
                            postalCode: customer.address.postalCode,
                            houseNumber: customer.address.houseNumber)

            customer.address.street = addressRow.street
            customer.address.city = addressRow.city
            customer
        }
    }
}

We also write a handler that uses the CustomerSql class:

// File: src/main/groovy/mrhaki/ratpack/configuration/CustomerSql.groovy
package mrhaki.ratpack

import ratpack.handling.Context
import ratpack.handling.InjectionHandler

import static groovy.json.JsonOutput.toJson

class CustomerHandler extends InjectionHandler {

    void handle(
            final Context context,
            final CustomerRepository customerRepository) {

        final String customerId = context.pathTokens.customerId

        customerRepository
                .getCustomer(customerId)
                .then { customer -> context.render(toJson(customer)) }
    }

}

We have created all these new classes it is time to get everything together in our ratpack.groovy file:

// File: src/ratpack/ratpack.groovy
import com.zaxxer.hikari.HikariConfig
import mrhaki.ratpack.CustomerHandler
import mrhaki.ratpack.CustomerRepository
import mrhaki.ratpack.CustomerSql
import mrhaki.ratpack.configuration.LocationHikariConfig
import mrhaki.ratpack.configuration.LocationHikariModule
import mrhaki.ratpack.configuration.LocationSqlModule
import ratpack.groovy.sql.SqlModule
import ratpack.hikari.HikariModule

import static ratpack.groovy.Groovy.ratpack

ratpack {
    serverConfig {
        // HikariConfig properties for customer database.
        props 'customer.jdbcUrl': 
              'jdbc:postgresql://192.168.99.100:5432/customer'
        props 'customer.username': 'postgres'
        props 'customer.password': 'secret'

        // LocationHikariConfig properties for location database.
        props 'location.jdbcUrl': 
              'jdbc:mysql://192.168.99.100:3306/location?serverTimezone=UTC&useSSL=false'
        props 'location.username': 'root'
        props 'location.password': 'secret'
    }

    bindings {
        // Default usage of HikariModule.
        // This module will add a DataSource to the registry.
        // The HikariModule is a ConfigurableModule
        // and this means the configuration class
        // HikariConfig is also added to the registry.
        // Finally a HikariService type is added to the
        // registry, which is a Ratpack service that
        // closes the DataSource when the application stops.
        // We use the configuration properties that start with customer. to
        // fill the HikariConfig object.
        moduleConfig(HikariModule, serverConfig.get('/customer', HikariConfig))
        
        // The default SqlModule will find
        // the DataSource type in the registry
        // and creates a new Groovy Sql object.
        module(SqlModule)
        
        // Custom module to add a second DataSource instance
        // to the registry identified by @LocationDataSource binding annotation.
        // We use the configuration properties that start with location. to
        // fill the LocationHikariConfig object.
        moduleConfig(LocationHikariModule, serverConfig.get('/location', LocationHikariConfig))

        // We create a second Sql instance in the registry,
        // using the DataSource with @LocationDataSource binding annotation.
        // The Sql instance is annotated as @LocationSql.
        module(LocationSqlModule)
        
        // CustomerSql uses both Sql objects.
        bind(CustomerRepository, CustomerSql)
    }

    handlers {
        get('customer/:customerId', new CustomerHandler())
    }
}

Written with Ratpack 1.3.3.