SQL databases: Database

A pooled JDBC DataSource (HikariCP) whose connections log in with the cloud's identity instead of a stored password, on Amazon RDS and Aurora, Azure Database for PostgreSQL and MySQL and Azure SQL, and Google Cloud SQL. Passwords work everywhere too, including any self-hosted PostgreSQL, MySQL or SQL Server.

A pool with IAM login#

Database orders = halyx.provider("aws").database(SqlConfig.builder(SqlEngine.POSTGRESQL)
        .host("orders.cluster-abc.eu-west-1.rds.amazonaws.com")
        .database("orders")
        .auth(SqlAuth.iam("orders_app"))
        .pool(new PoolSettings(20, 5, Duration.ofSeconds(30), Duration.ofMinutes(10), Duration.ofMinutes(30)))
        .build());

DataSource dataSource = orders.dataSource();   // to JDBC, JPA, jOOQ, Flyway, Liquibase
boolean ok = orders.isHealthy(2);               // borrows a connection and checks it within 2 seconds

database(config) opens a new pool on every call, so create the Database once and keep it. Closing it, or closing Halyx, closes the pool. The pool starts on first use, so the application can start while the database is unreachable.

PoolSettings(maxSize, minIdle, connectionTimeout, idleTimeout, maxLifetime) sizes the pool. Without one, PoolSettings.DEFAULTS applies: 10 connections, all kept idle, a 30-second connection timeout, a 10-minute idle timeout and a 30-minute maximum lifetime.

How token login works#

Tokens are fetched when a new physical connection opens, and cached until shortly before they expire. Established connections stay logged in, so token lifetimes never interrupt queries. IAM logins turn on TLS (sslmode=require, sslMode=REQUIRED or encrypt=true).

Passwords and rotated secrets#

SqlAuth.password(user, supplier) asks the supplier for every new connection, so a rotated secret takes effect as connections are replaced (maxLifetime, 30 minutes by default) without a restart:

ParameterStore secrets = halyx.provider("aws").parameterStore(
        ParameterStoreConfig.secrets().cacheTtl(Duration.ofMinutes(5)).build());

Database reporting = halyx.provider("aws").database(SqlConfig.builder(SqlEngine.MYSQL)
        .host("reporting.abc.eu-west-1.rds.amazonaws.com")
        .database("reporting")
        .auth(SqlAuth.password("app", () -> secrets.require("reporting/db", "password")))
        .build());

Cloud specifics#

RDS Proxy works as is: use the proxy endpoint as host, with SqlAuth.iam(...) if the proxy requires IAM authentication.

Cloud SQL Java connector. On Google Cloud, set the instance connection name instead of a host. The connector handles TLS and IAM itself and needs no authorized networks:

Database db = halyx.provider("gcp").database(SqlConfig.builder(SqlEngine.POSTGRESQL)
        .property(GcpProperties.SQL_INSTANCE, "acme-prod:europe-west2:orders")
        .database("orders")
        .auth(SqlAuth.iam())
        .build());

Add com.google.cloud.sql:postgres-socket-factory or com.google.cloud.sql:mysql-socket-factory-connector-j-8 to use it.

TLS verification. To verify the server certificate instead of only encrypting, override the TLS property with jdbcProperty(...), for example jdbcProperty("sslmode", "verify-full") with the cloud's CA bundle.

Driver. The JDBC driver is your dependency: org.postgresql:postgresql, com.mysql:mysql-connector-j or com.microsoft.sqlserver:mssql-jdbc.

Login latency#

A token login costs more than a password login, because the database checks every token. Measured from the same client:

Pooled connections pay this only when they are opened. To keep it off the first request, set minIdle above zero so the pool opens connections in the background, and keep maxLifetime long. RDS also limits how fast new IAM connections can be opened, so pool rather than connect per request. The full measurements are in Cloud testing.

Reactive: R2DBC#

halyx-r2dbc gives reactive code the same logins through R2DBC: a pooled ConnectionFactory (r2dbc-pool) whose new connections log in with a password, looked up each time, or with the cloud's IAM token. The token is fetched on a scheduler for blocking work, never on the event loop, and reused until shortly before it expires; established connections stay logged in.

ConnectionPool orders = R2dbcDatabases.connectionFactory(halyx.provider("aws"),
        SqlConfig.builder(SqlEngine.POSTGRESQL)
                .host("orders.cluster-abc.eu-west-1.rds.amazonaws.com")
                .database("orders")
                .auth(SqlAuth.iam("orders_app"))
                .build());
DatabaseClient client = DatabaseClient.create(orders);     // Spring's, or any R2DBC client

The pool's size and timeouts come from the config's PoolSettings. Close it with dispose(); it doesn't close with the Halyx context. R2DBC connects to a host, so on Google Cloud use the instance's IP address, not the Cloud SQL connector (gcp.sql.instance). Other clients can get the same endpoint and login from CloudContext.sqlEndpoint(config).

Reactor Netty versions. The R2DBC drivers depend on reactor-netty-core, and the Azure SDK on reactor-netty-http. The two must be the same version, or the Azure SDK fails with IncompatibleClassChangeError. Spring Boot manages both. Without Boot, import io.projectreactor:reactor-bom in your build, or pin both modules to one version.

Spring Boot#

Every halyx.databases.<name> entry becomes a DataSource bean, covered by Spring Boot's JDBC health check and Hikari metrics. Spring Data JPA and JDBC, JdbcTemplate, Flyway and Liquibase use the primary Halyx DataSource unchanged. See Spring Boot.