Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 38 ยท Data Access

Data Sources and Connection Pools

Connect a Micronaut app to a database: datasource configuration, HikariCP pool sizing, environments and H2. Runnable JDBC and connection-pool stand-ins on Java.

Almost every service stores data. Before repositories, queries and transactions, your application needs one thing: a way to get a database connection quickly and safely. In Micronaut that means a data source (the connection settings) backed by a connection pool (a small set of reusable, already-open connections).

The Micronaut framework is not installed on this site, so the framework code and configuration below are read-only. The runnable programs use plain JDBC against the H2 in-memory database that is available in the Java runner, plus a hand-written pool that mimics how HikariCP shares connections.

Add the pieces

A Micronaut JDBC setup has three parts: the JDBC support module, a pool, and a database driver. In a Gradle build:

GROOVY
dependencies {
    implementation("io.micronaut.sql:micronaut-jdbc-hikari")
    runtimeOnly("org.postgresql:postgresql")
    runtimeOnly("com.h2database:h2")     // handy for local work and tests
}

The micronaut-jdbc-hikari module uses HikariCP, a fast and widely trusted pool. Other modules exist (micronaut-jdbc-tomcat, micronaut-jdbc-dbcp), but Hikari is the default choice. If you use Micronaut Data, it also needs a pool underneath, so you configure this first.

Configure a datasource

Datasources live under datasources in application.yml. The first one is called default:

YAML
datasources:
  default:
    url: jdbc:postgresql://localhost:5432/shop
    username: shop
    password: ${DB_PASSWORD}
    driver-class-name: org.postgresql.Driver
    maximum-pool-size: 10
    minimum-idle: 2
    connection-timeout: 3000

Things to notice:

  • The password comes from an environment variable through ${DB_PASSWORD}. Never commit real passwords; see configuration properties.
  • dialect is not required here. The dialect (for generating SQL) is chosen on the repository with @JdbcRepository(dialect = Dialect.POSTGRES) or in configuration when you use Micronaut Data.
  • You can define more than one datasource (datasources.reports, for example) and inject the one you want with @Named("reports") DataSource.

For local work you can skip installing a database. With H2 the whole datasource can be:

YAML
datasources:
  default:
    url: jdbc:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    driver-class-name: org.h2.Driver
    username: sa
    password: ""

Use per-environment files, such as application-test.yml, to switch databases per environment. Micronaut also supports Test Resources, which start a real PostgreSQL container for tests automatically (covered in mocking and test resources).

Using a connection directly

You rarely write raw JDBC in a Micronaut app, but a DataSource is a normal bean and you can inject it:

Java
@Singleton
public class HealthDao {
    private final DataSource dataSource;

    public HealthDao(DataSource dataSource) { this.dataSource = dataSource; }

    public boolean canQuery() {
        try (Connection c = dataSource.getConnection();
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery("SELECT 1")) {
            return rs.next();
        } catch (SQLException e) {
            return false;
        }
    }
}

The try with resources is essential. Closing a pooled connection does not close the real database connection; it returns it to the pool. Forgetting to close it is a connection leak, and after enough leaks the pool is empty and every request waits.

Stand-in 1: JDBC against in-memory H2

This program is real JDBC code. It creates a table, inserts rows with a PreparedStatement and queries them. The same code works with any datasource that Micronaut hands you. Parameters use ? placeholders instead of string concatenation, which prevents SQL injection.

Java Plain JDBC with H2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

How a pool works

Opening a database connection is slow: TCP handshake, authentication, session setup, maybe TLS. A pool pays that cost once, keeps connections open, and lends them out. Three numbers control it:

Setting Meaning Typical value
maximum-pool-size Most connections that may exist 10 to 20 per instance
minimum-idle Connections kept ready when quiet Same as max, or slightly lower
connection-timeout How long a caller waits for a free connection 2 to 5 seconds

More connections is not better. A database serves a fixed number of concurrent queries well, and extra connections just queue inside the database. A good starting point is a pool of about 10 per application instance; multiply by the number of instances and make sure the database's own limit (max_connections in PostgreSQL) is higher than the total.

flowchart LR
    R1["Request A"] --> P["Pool: 3 connections"]
    R2["Request B"] --> P
    R3["Request C"] --> P
    R4["Request D waits"] -.-> P
    P --> D[("Database")]

Request D waits until a connection is returned or the timeout expires.

Stand-in 2: a tiny connection pool

The program below builds a pool of two "connections" with a blocking queue. A caller borrows one, uses it and returns it. When the pool is empty, a caller waits up to a timeout and then fails, which is exactly the symptom of a leak in a real service. Time is simulated with short sleeps, but the printed lines do not depend on timing.

Java Borrow, return and exhaust a pool

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

In a real service this shows up as requests that hang for the connection-timeout and then fail with an exception from Hikari. The fix is always the same: close connections in a try-with-resources block, or let Micronaut Data and @Transactional manage them for you.

Health, metrics and common mistakes

  • Health. Adding the management module exposes /health, which includes a datasource check (SELECT 1 style). Orchestrators can use it to restart unhealthy pods.
  • Metrics. Hikari publishes pool size, active and pending connections to Micrometer, so you can watch for pools that stay full (see metrics, health and tracing).
  • Hard-coded credentials. Always use environment variables or a secret store.
  • A giant pool. If you think you need 200 connections, you probably need a faster query or a cache.
  • Long transactions. A transaction holds its connection until it ends, so slow remote calls inside a transaction starve the pool.
  • Pool per request. Never create a DataSource yourself in a controller; inject the shared one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens when you call close() on a connection borrowed from a pool?

    A pooled connection is a wrapper. Closing it hands the real connection back to the pool.
  2. 2. Where should a database password normally come from?

    Use ${DB_PASSWORD}-style placeholders so secrets stay out of source control.
  3. 3. Requests hang for several seconds and then fail with a timeout waiting for a connection. What is a likely cause?

    When all pooled connections are in use, new callers wait until connection-timeout expires.
  4. 4. Why not set maximum-pool-size to 500?

    Pool size should match what the database can run concurrently, multiplied across all application instances.

Summary

  • A datasource holds connection settings; a pool such as HikariCP reuses open connections.
  • Configure datasources under datasources.default and keep secrets in environment variables.
  • Closing a pooled connection returns it to the pool; always use try-with-resources.
  • Size pools modestly and keep total connections under the database limit.
  • Prepared statements with ? parameters prevent SQL injection.
  • Leaks and long transactions exhaust the pool and show up as connection timeouts.

Next: Micronaut Data and Repositories