The previous lesson ended with a very specific error message: Failed to configure a DataSource. Spring Boot detected the JPA starter, tried to build the persistence unit and found itself without the most basic ingredient, a connection to a database. This lesson solves exactly that, and it goes well beyond pasting four properties: a badly sized DataSource is the number one cause of Spring Boot applications falling over in production, well ahead of any logic error.

We are going to set up two environments for CicloUrbana: in-memory H2 for developing fast, and PostgreSQL 16 in Docker as the real database. We will understand what a connection pool is and tune HikariCP parameter by parameter with sizing criteria you can defend in front of a colleague. We will settle the Hibernate properties that govern the ORM's behaviour, including the important and rarely discussed decision to switch open-in-view off. And we will leave logging ready to show the SQL that actually travels towards Ribalta.

1. What a DataSource is and why connections are pooled
2. In-memory H2 for development
3. The H2 console and its security risk
4. PostgreSQL 16 with Docker Compose
5. The essential spring.datasource properties
6. HikariCP in depth
7. Sizing the pool with judgement
8. The JPA and Hibernate properties
9. open-in-view: why it gets switched off
10. Reading the generated SQL comfortably
11. Multiple data sources
12. Credentials outside the repository
13. Checking the connection at startup
14. Common Mistakes and Tips
15. Exercises

javax.sql.DataSource is a Java interface with one essential method: getConnection(). It is the standard factory of database connections, and it is what Hibernate asks for when it needs to talk to PostgreSQL.

The interesting question is what sits behind that method. The naive implementation would open a fresh TCP connection every time. And opening a database connection is expensive: TCP handshake, authentication, TLS negotiation, creation of a server process or thread, allocation of session memory. In PostgreSQL, between 20 and 100 milliseconds. If GET /api/v1/stations takes 5 ms for its query and 40 ms to open the connection, 89% of the time goes into plumbing.

A connection pool solves this by keeping a set of already-open connections and lending them out:

The detail that throws people the first time: when your code calls connection.close(), the connection is not closed. The pool hands back a wrapper whose close() means "return it to the pool". That is why try-with-resources around connections is still correct and necessary.

Consequences to keep in mind throughout the module:

• The number of simultaneous connections is bounded by the pool size, not by the number of requests.
• If all of them are lent out, the next request waits. If it waits too long, it fails with a timeout.
• A connection that is lent and never returned is a leak that eventually drains the pool and brings the application down.

Spring Boot includes HikariCP through spring-boot-starter-jdbc, which the JPA starter pulls in. There is nothing to add.

H2 is a relational database written in Java that can live inside your own process. For developing CicloUrbana it is ideal: it starts in milliseconds, requires no installation and comes back clean on every run.

The runtime scope is deliberate: the driver is needed at run time, never at compile time. Your code must not import a single H2 class. If you ever need compile, it is a sign that something has become coupled to the engine.

The configuration in application.yml:

Let's break down the URL, which is where the interesting part lives:

DB_CLOSE_DELAY=-1 is not optional. Without it, as soon as the pool closes its last active connection H2 wipes the whole database, and the data loaded by DemoStationLoader vanishes mid-run.

MODE=PostgreSQL is a strategic decision of this course: it makes H2 behave like PostgreSQL in types, functions and syntax, so that what works in development is far more likely to work in production. It is not full equivalence —which is why in 06-05 we will use Testcontainers with a real PostgreSQL for the tests—, but it narrows the gap a lot.

If you would rather the data survived between runs, H2 can also write to a file with jdbc:h2:file:./data/ciclourbana;MODE=PostgreSQL; remember to add data/ to .gitignore in that case.

With spring.h2.console.enabled: true, once the application starts you have a web SQL client at http://localhost:8080/h2-console. The connection details to type in are the ones from the YAML:

It is extremely handy for seeing which tables Hibernate created from your entities (04-03) and checking that the data is where you think it is.

And it is, at the same time, the biggest security hole you can leave open. The H2 console lets you run arbitrary SQL with no real authentication. Worse still: H2 allows Java code to be executed from SQL through aliases, which turns an exposed console into remote code execution. There have been serious CVEs for precisely this.

The rules are non-negotiable:

• Never enable the console in an environment reachable from outside.
• Turn it on only in the development profile (profiles are covered in 07-02):

• In application.yml (the base file), leave it off: enabled: false.
• When we add Spring Security in module 5, the console will need an explicit rule and, even then, will remain restricted to development.

web-allow-others: false is the default value and limits access to localhost. Do not change it.

H2 is fine for developing, but CicloUrbana will run in production on PostgreSQL 16. Bringing it up with Docker avoids installing anything on the machine and guarantees the whole team uses the same version. Create docker-compose.yml at the root of the project:

Point by point:

Common commands:

Careful with down -v: it deletes the volume and all the data with it. In 07-04 we will pick this file up again to add the container of the application itself.

The PostgreSQL driver:

And the matching configuration:

You can keep both drivers in the pom.xml at once: which one gets used is decided by the URL. In 07-02 we will see how to separate both configurations cleanly into profiles.

Why driver-class-name is not needed. Spring Boot uses DatabaseDriver, an enum that maps URL prefixes to driver classes: jdbc:postgresql: → org.postgresql.Driver, jdbc:h2: → org.h2.Driver, jdbc:mysql: → com.mysql.cj.jdbc.Driver. With the URL and the driver on the classpath, the inference is automatic. You only have to declare it when you use an alternative driver or a URL with an unrecognised prefix.

And if you configure nothing while having H2 on the classpath, Spring Boot creates an embedded DataSource with a random URL along the lines of jdbc:h2:mem:2a5f.... It is convenient for a quick start, but since the name changes on every run you cannot connect with the console. For CicloUrbana we always declare it explicitly.

HikariCP has been Spring Boot's default pool since version 2.0, and with good reason: it is the fastest in the Java ecosystem and the one that needs the least configuration to be right. Its philosophy is to have few options and sensible defaults.

All its parameters go under spring.datasource.hikari:

Details that really matter:

max-lifetime must be lower than whatever lifetime the database or the firewall imposes. It is the most common cause of intermittent Connection is closed errors in production: a firewall cuts idle connections after 30 minutes and the pool goes on believing they are valid. The official recommendation is to set it several seconds below that limit; 30 minutes is a prudent value almost always.

minimum-idle equal to maximum-pool-size is the recommendation of HikariCP's authors for steady loads: a fixed-size pool avoids paying the cost of opening connections at precisely the worst moment, the traffic spike.

leak-detection-threshold deserves to be enabled in development and pre-production. When a connection has been lent out for longer than the threshold, HikariCP writes the stack trace of whoever asked for it. It is the most direct way to find a leak:

auto-commit: false leaves transaction management to Spring, which is what we want with @Transactional (04-07). Spring Boot already sets it correctly when using JPA.

Intuition says "more connections, more performance". It is false, and understanding that separates those who configure from those who copy.

A database runs queries on a limited number of cores and disks. With more active connections than resources, the server spends its time context-switching and competing for locks instead of working. HikariCP's documentation shows a classic case: a server with 10,000 users performing better with a pool of 10 than with one of 100.

PostgreSQL's reference formula:

For a PostgreSQL in a container with 4 vCPUs and SSD storage (where the disk term is around 1-2):

Ten. That is HikariCP's default value and it rarely needs raising. Practical rules for CicloUrbana:

• Start at 10. Only raise it with metrics that justify it (Actuator and Micrometer expose hikaricp.connections.*; we will see this in 09-03).
• Add up all the instances. If you deploy 4 replicas with a pool of 10, the database sees 40 connections. PostgreSQL's max_connections (100 by default) is a global ceiling that runs out sooner than people think.
• If there are waits, the answer is almost never more connections: it is usually a slow query, a missing index or a transaction that is too long.
• Never do slow work inside a transaction. Calling the payment gateway with a borrowed connection holds a scarce resource for seconds. In CicloUrbana, charging for the rental must happen outside the transaction; in 04-07 we will solve it with @TransactionalEventListener.

The ORM's behaviour is configured under spring.jpa.

The five values of ddl-auto, the most dangerous property in the framework:

update is not safe in production, and the reason is usually misunderstood. It is not just that it "might delete data" —in fact it does not drop columns—: it is that it cannot modify what already exists. If you change a varchar(80) to varchar(40), or add a NOT NULL column to a table with rows, update either ignores it silently or fails halfway, leaving the schema in a state nobody has reviewed or can reproduce. On top of that there is no record of what was applied and no way to undo it.

The module's plan is explicit: we use update while we design the entities in 04-03 and 04-04, and in 04-08 we replace it with Flyway and validate. By the time you reach that lesson, update disappears from the project for good.

The dialect is not configured. Hibernate 6 detects it by interrogating the driver and tailors SQL generation to the engine's specific version. Pinning it by hand only serves to leave you stuck on an old version without noticing.

Spring Boot enables spring.jpa.open-in-view=true by default, and warns about it with a message in the log:

What it does: a filter (OpenEntityManagerInViewInterceptor) keeps the persistence context open for the whole HTTP request, not just for the service's transaction.

It sounds convenient, and that is the trap. The three problems it causes:

1. It hides N+1 until production. If the controller serialises an entity with a lazy collection, with open-in-view on the collection is loaded without complaint, firing queries during serialisation. With it off, LazyInitializationException is raised in development, which is where you want to find out.
2. It holds the connection longer than necessary. The JDBC connection can stay bound to the entire request, JSON serialisation included. With a pool of 10, this drastically reduces the number of concurrent requests.
3. It blurs the boundaries. The presentation layer ends up running queries, the exact opposite of the layer separation we built in 03-05 with DTOs.

For CicloUrbana the decision is made as of this lesson:

The trade-off is that the service must return fully mapped DTOs, with everything needed already loaded. That is exactly what we have been doing since 03-05 with StationMapper and StationDetailResponse, so the cost is zero. We will come back to this in 04-04 and 04-07.

Working with an ORM without seeing the SQL is programming blind. There are two ways to see it and one is clearly better.

The quick way (seriously, avoid it): spring.jpa.show-sql: true. It writes to System.out, unformatted, with no level, no timestamp and without going through the logging system. It is good for a glance and little more.

The right way: Hibernate's log.

With org.hibernate.SQL at DEBUG you will see every statement; with org.hibernate.orm.jdbc.bind at TRACE, the values that replace the ?. Watch the name: in Hibernate 5 it was org.hibernate.type.descriptor.sql; in Hibernate 6, the one shipped with Spring Boot 3, it is org.hibernate.orm.jdbc.bind. Copying the old configuration is a frequent reason for "the parameters don't show up".

The result on the console:

generate_statistics: true adds, at the end of each session, a summary that is invaluable for spotting N+1:

Forty-seven entities with two statements is fine. Forty-seven entities with forty-eight statements is a textbook N+1 (04-04).

Security warning: the TRACE level for parameters prints every value sent to the log, including emails, hashed passwords or personal data of Ribalta's citizens. It is a development setting. In production, never.

Occasionally an application needs to talk to two databases: CicloUrbana could have its operational database and a read-only replica for reports. As soon as you declare two DataSource beans, the autoconfiguration backs off and you have to build them by hand.

@Primary (which you already know from 02-02) resolves the ambiguity: without it, any injection of DataSource would fail with NoUniqueBeanDefinitionException. On top of that, with two sources you have to declare EntityManagerFactory and TransactionManager manually for each one, and separate the repositories into packages with @EnableJpaRepositories(basePackages = ...).

That is quite a lot of work, and this is why the recommendation is clear: do not do it unless it is unavoidable. Very often what people are after (isolating reports) is better solved with materialised views, caching (09-02) or splitting the service out (07-05).

The PostgreSQL password cannot live in application.yml, because application.yml is in Git and Git has an eternal memory: deleting it in a later commit does not remove it from history.

We already saw the configuration precedence in 02-04; here we apply it. The most portable form is an environment variable with a placeholder:

The first two carry a default value after the :; the third does not, deliberately: if the variable is not defined, the application fails to start with a clear message instead of trying to connect with an empty password.

Remember, too, the automatic name translation: Spring Boot turns spring.datasource.password into SPRING_DATASOURCE_PASSWORD, so defining that environment variable works without writing anything in the YAML.

And in .gitignore, at a minimum: .env, *.env.local and data/.

With everything configured, an explicit verification is worthwhile. A CommandLineRunner like the ones from 01-05, restricted to development:

The try-with-resources matters: without it, the connection never returns to the pool and you have just created a leak in the very first line of code that touches the DataSource.

Expected output with PostgreSQL:

In module 7 we will see that Actuator offers this same information permanently at /actuator/health, with a db indicator that runs a validation query.

Leaving ddl-auto: update when deploying. The most expensive mistake in the module. It works in development, it appears to work in production, and one day it leaves the schema in a state nobody knows how to rebuild. Lesson 04-08 exists to get rid of it.

Raising maximum-pool-size to fix slowness. It almost always makes things worse: the database saturates and every query slows down. Before touching the pool, look at the SQL, the indexes and the duration of the transactions.

Leaving open-in-view at its default. The log warning is systematically ignored. Set it to false on the project's first line of JPA configuration; doing it later brings dozens of LazyInitializationException to light all at once.

Forgetting DB_CLOSE_DELAY=-1 in H2. It produces the baffling "my data disappears mid-run" with no error whatsoever.

Using org.hibernate.type.descriptor.sql to see the parameters. That is the Hibernate 5 name. In Spring Boot 3 you have to use org.hibernate.orm.jdbc.bind.

Leaving the H2 console reachable. Arbitrary SQL execution without authentication. Development only, localhost only.

Tip: set pool-name. With CicloUrbanaPool the log messages and the metrics are identifiable at a glance, especially if one day there are two pools.

Tip: enable leak-detection-threshold in development. Sixty seconds is a good threshold. Finding a leak from its stack trace takes minutes; finding it in production through pool exhaustion takes an afternoon.

Tip: pin the Docker image version. postgres:16-alpine, never postgres:latest. Having the whole team on the same engine avoids the hardest class of failure to reproduce.

The council deploys CicloUrbana across 3 replicas. The PostgreSQL server has 8 vCPUs, SSD storage and max_connections = 100. There is also a nightly reporting process that opens up to 5 connections and an administration dashboard with 5 more.

1. Calculate maximum-pool-size per replica using PostgreSQL's formula.
2. Check that the total does not exhaust max_connections.
3. Write the complete spring.datasource.hikari block, justifying each value.

Write CicloUrbana's configuration split across application.yml (common), application-dev.yml (H2 + console + SQL logs) and application-prod.yml (PostgreSQL + credentials from environment variables + validate). State which property must never appear in the production file and why.

In production this error appears intermittently at peak hours:

List four possible causes ordered from most to least likely, and for each one state how to confirm it and how to fix it. Explain why raising maximum-pool-size is not the first answer.

Solution 1.

1. Formula: (cores * 2) + effective_spindles = (8 * 2) + 2 = 18 connections for the whole database, not per replica. Split across 3 replicas: 18 / 3 = 6 per replica. A value of 6 is defensible; 8 is too, leaving some headroom. What is not defensible is 20 per replica.
2. Total: 3 replicas × 6 = 18, plus 5 for the nightly process and 5 for the dashboard = 28 connections. Against max_connections = 100 that leaves plenty of headroom, including what PostgreSQL reserves for the superuser and maintenance. With 20 per replica it would be 70, already uncomfortable and far above what 8 vCPUs can serve in parallel.
3. Configuration:

The most debatable value is connection-timeout: 3000. Lowering it from the default 30 s is deliberate: if the pool is exhausted, waiting 30 seconds only piles up threads and makes the problem worse. Failing in 3 seconds returns a quick 503, keeps the application alive and leaves the symptom visible in the metrics.

Solution 2.

What must never appear in production, in order of severity:

• org.hibernate.orm.jdbc.bind: TRACE: it would dump into the log all the personal data of Ribalta's citizens that passes through a query. That is a privacy breach, on top of a noticeable performance cost.
• ddl-auto: update or create: unreviewed schema modifications, or total data loss.
• spring.h2.console.enabled: true: arbitrary SQL execution without authentication.
• The literal password: always through an environment variable, and with no default so that the failure is loud.

Profiles are activated with --spring.profiles.active=prod or SPRING_PROFILES_ACTIVE=prod, and are studied in depth in 07-02.

Solution 3. Causes ordered by real likelihood:

1. Transactions that are too long (the most likely). A @Transactional method that calls an external service —CicloUrbana's payment gateway— holds the connection for the whole network call. With 6 connections and 2-second calls, the pool drains under very little traffic. Confirm: enable leak-detection-threshold in pre-production and review the @Transactional methods looking for I/O. Fix: move the external call outside the transaction, with @TransactionalEventListener(AFTER_COMMIT) (04-07).
2. A connection leak. Some code obtains a connection from the DataSource without try-with-resources. It is rare with Spring Data, but it shows up in hand-written utilities. Confirm: leak-detection-threshold: 60000 and look for the "Apparent connection leak detected" traces. Fix: always close with try-with-resources.
3. Slow queries from a missing index. A query that takes 5 seconds occupies its connection for 5 seconds. Confirm: pg_stat_statements in PostgreSQL or log_min_duration_statement = 1000. Fix: indexes and rewriting the query (09-01).
4. The N+1 problem. An endpoint firing hundreds of queries per request multiplies the holding time. Confirm: generate_statistics: true and count the statements per session. Fix: JOIN FETCH or @EntityGraph (04-04 and 04-06).

Why raising maximum-pool-size is not the first answer: exhaustion is a symptom, not the disease. If the cause is a 2-second transaction, doubling the pool doubles the load on the database without fixing anything; the problem reappears at twice the traffic, now with a more saturated server. What is more, more active connections competing for CPU and locks slow down every query, including the ones that were fine. Raising the pool is the last measure, after you have measured and ruled out the four causes above.

CicloUrbana now has plumbing. You know what a DataSource is and why opening connections is expensive, which justifies the existence of a pool and the fact that close() closes nothing. You have in-memory H2 configured for development with DB_CLOSE_DELAY=-1 and MODE=PostgreSQL, its web console available on localhost only and the security warning engraved. You have a docker-compose.yml with PostgreSQL 16, a named volume, a healthcheck and the password from an environment variable, ready to be picked up again in 07-04. You know the four essential spring.datasource properties and why the driver is inferred on its own. You have walked through HikariCP parameter by parameter, you know that max-lifetime must sit below the firewall's cut-off, that minimum-idle equal to the maximum avoids the worst possible moment to open connections and that leak-detection-threshold is the fastest way to find a leak. And, above all, you have a defensible criterion for sizing the pool: (cores × 2) + disks, split across replicas, with the certainty that more connections do not mean more performance.

On the JPA side you have settled the decisions that govern the rest of the module: ddl-auto at update only while we design the entities, with the explicit commitment to replace it with Flyway and validate in 04-08; open-in-view: false from now on, accepting that the service returns complete DTOs in exchange for N+1 and lazy loading showing their face in development; and Hibernate's log configured with org.hibernate.SQL and org.hibernate.orm.jdbc.bind so you can read the real SQL with its parameters. You also know how several data sources are declared with @Primary and why it is worth avoiding, how to keep credentials out of Git and how to verify the connection at startup without leaving a leak behind on the way.

But the database is empty: there is not a single table, because there is not a single entity. Station is still an immutable record in memory. Lesson 04-03, Creating JPA Entities, changes that: we will see why a record cannot be an entity, turn Station, Bike and Rental into entities with @Entity, @Table and indexes, choose the right identifier generation strategy for PostgreSQL, map each data type carefully —including why amounts are BigDecimal and never double—, embed Location with @Embeddable, add automatic auditing and debut @Version, the optimistic locking that finally retires the ShallowEtagHeaderFilter from 03-03.