How do I set up AWS X-Ray SQL Instrumentation for Hikari based projects without using Tomcat JDBC?

Question

The AWS Documentation for AWS X-Ray currently doesn't give any solutions for Java projects that do not use Tomcat JDBC.

In order to instrument database queries using spring-boot-data-jpa, you need to also include Tomcat JDBC as a dependency, and set up a Tomcat DataSource object along with your Hikari one, and include the XRay interceptor as a JDBC interceptor by either:

Adding it in your config using:

dataSource.setJdbcInterceptors("com.amazonaws.xray.sql.postgres.TracingInterceptor;");

As a property:

spring.datasource.jdbc-interceptors=com.amazonaws.xray.sql.postgres.TracingInterceptor

Gradle:

dependencies {
    ...
    implementation 'org.springframework.boot:spring-boot-starter'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

    implementation "com.amazonaws:aws-java-sdk-core"
    implementation "com.amazonaws:aws-xray-recorder-sdk-core" // Required for core xray features
    implementation "com.amazonaws:aws-xray-recorder-sdk-spring" // Required for spring annotations
    implementation "com.amazonaws:aws-xray-recorder-sdk-sql-postgres" // required for db callouts

    implementation 'org.apache.tomcat:tomcat-jdbc:9.0.31'
    ...
}

Database Configuration (Spring):

    @Bean(name = "dataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        final org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
        dataSource.setUsername(getUsername());
        dataSource.setPassword(getPassword());
        dataSource.setUrl(POSTGRES_URL_PREFIX
                + getHost()
                + ":" + getPort()
                + "/" + getName()
                + "?stringtype=unspecified");
        dataSource.setDriverClassName(getDriver());
        dataSource.setJdbcInterceptors("com.amazonaws.xray.sql.postgres.TracingInterceptor;");

        final HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDataSource(dataSource);

        return hikariDataSource;
    }

I find this quite clunky, and I would rather not have to have Tomcat JDBC as an additional dependency if possible.

Is there no way around this without using Tomcat?

Other notes:

Spring Boot 2.1.7
Gradle 6.0.1
AWS SDK for Java 2.4.0

score 5 · Accepted Answer · answered Mar 13 '20 at 15:04

It turns out that someone else had this question. The AWS developers have been working on this in a feature branch that got merged in Novemeber 2019 for SDK version 2.3.0.

I am yet to find any documentation for this new feature, so after some digging into the PR code, I managed to find that it's even more simple than the previous implementation.

Simply add the following dependency instead of the postgres specific one to your build.gradle file (or similar):

    implementation "com.amazonaws:aws-java-sdk-core:2.4.0"
    implementation "com.amazonaws:aws-xray-recorder-sdk-core:2.4.0"
    implementation "com.amazonaws:aws-xray-recorder-sdk-sql:2.4.0"

Then simply create a TracingDataSource object in your DataSource configuration, and pass it your original javax.sql.DataSource object. This should be done in whatever @Configuration annotated class you use to create your DataSource bean.

    @Bean(name = "dataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        final DataSource dataSource = DataSourceBuilder
                .create()
                .username(getUsername())
                .password(getPassword())
                .url(POSTGRES_URL_PREFIX
                        + getHost()
                        + ":" + getPort()
                        + "/" + getName()
                        + "?stringtype=unspecified")
                .driverClassName(getDriver())
                .build();

        final TracingDataSource tracingDataSource = new TracingDataSource(dataSource);
        return tracingDataSource;
    }

That's all there is to it. I really hope this helps someone as I spent many hours trying to get this to work without Tomcat, plus the AWS documentation doesn't help here either.

Thanks for this! We will follow-up to update our docs accordingly. Sorry for the issues. — AWSSandra, Mar 13 '20 at 17:13

score 2 · Answer 2 · answered May 22 '20 at 12:28

J Hamm's answer definitely provided a good starting point but I didn't want to manually set DataSourceBuilder properties. Additionally, Hikari specific properties were not being set for me. I ended up with following:

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    //DataSourceProperties will convert `spring.datasource.url` property to hikari's jdbcUrl property
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "hikariDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.hikari")//set hikari specific properties
    public HikariDataSource hikariDataSource(DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource(HikariDataSource hikariDataSource) {
        //wrap the spring ds into xray tracing ds
        var tracingDataSource = new TracingDataSource(hikariDataSource);
        return tracingDataSource;
    }

Unfortunately all this ended up being wasted effort because x-ray doesn't record executed SQL statements making it almost useless. See https://github.com/aws/aws-xray-sdk-java/issues/28

Yep this works too! I kept the properties being explicitly set there just to make it clear what was happening here. It's worth noting that they have finally released the implementation of this in 2.5.0 but I haven't had a chance to look in to it. — J Hamm, Jun 08 '20 at 12:17

score 0 · Answer 3 · answered Jun 02 '21 at 09:47

Resolved the same using TracingDataSource, while still using HikariCP as the connection pool

Reference https://github.com/aws/aws-xray-sdk-java/issues/88#issuecomment-570328275

Code: (Note, I am using AWS Secrets Manager JDBC Library aws-secretsmanager-jdbc to connect to the database using secrets stored in AWS Secrets Manager)

import com.amazonaws.xray.sql.TracingDataSource;

...
...
@Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {

        return TracingDataSource
                .decorate(DataSourceBuilder.create()
                        .driverClassName("com.amazonaws.secretsmanager.sql.AWSSecretsManagerPostgreSQLDriver")
                        .url("jdbc-secretsmanager:postgresql://" + System.getenv("PGHOST") + ":"
                                + System.getenv("PGPORT") + "/" + System.getenv("PGDATABASE"))
                        .username(System.getenv("SECRET_NAME")).build());

    }

Dependency:

<dependency>
    <groupId>com.amazonaws</groupId>
    <artifactId>aws-xray-recorder-sdk-sql</artifactId>
</dependency>

How do I set up AWS X-Ray SQL Instrumentation for Hikari based projects without using Tomcat JDBC?

3 Answers3