
# Data sources

A data source is a database the application connects to, with a pool of connections. Set `spring.datasource.url` and the starter makes the primary `DataSource` and everything over it: a transaction manager, `JdbcTemplate`, `NamedParameterJdbcTemplate` and `JdbcClient`. Each `spring.datasources.<name>.url` adds another database with its own beans.

## Why

The database is configuration, not code: a file in development, a server in production, an in-memory database in tests. With properties you change the URL, not the application. The pool keeps a few connections open, so a request does not pay for a new connection each time, and the starter makes sure no code touches the database before its migrations ran.

## Example

```properties
# lib/resources/application.properties
spring.datasource.url=postgresql://db.example.com:5432/shop
spring.datasource.username=shop
spring.datasource.password=${SHOP_DB_PASSWORD}
spring.datasource.pool.max-size=10
spring.datasource.pool.connection-timeout=10s

# A second database: its own beans, qualifier and settings.
spring.datasources.stats.url=sqlite:file:stats.db
spring.datasources.stats.sql.migration.locations=resources:db/stats
```

```dart
@UseDataSource('stats')                       // a repository on the second database
abstract interface class VisitRepository implements ListCrudRepository<Visit, int> {}

@Service()
class Reports {
  Reports(this.jdbc, @Qualifier('stats') this.statsJdbc);

  final JdbcTemplate jdbc;                    // the primary database
  final JdbcTemplate statsJdbc;               // the stats database

  @Transactional(transactionManager: 'stats')
  Future<int> visits() => statsJdbc.queryForObject<int>('select count(*) from visits');
}

@SpringBootApplication()
Future<void> main(List<String> args) => runApplicationAsync(args);
```

## URLs and drivers

| URL | Database |
|---|---|
| `sqlite:file:books.db` | A SQLite file, relative to `spring.datasource.sqlite.base-directory` (the working directory by default). |
| `sqlite::memory:` | An in-memory SQLite database. Each connection has its own: use `pool.max-size=1`. |
| `postgresql://user:password@host:5432/shop?sslmode=require` | PostgreSQL (see [PostgreSQL](postgresql.md)). |
| `jdbc:postgresql://host/shop` | The same; a leading `jdbc:` is dropped, so URLs pasted from Spring Boot work. |

- The starter has no driver inside. List `sqlite3` or `postgres` in `pubspec.yaml` and run the generator: it writes a `JdbcDriver` bean for each driver package it finds (`SqliteBinding`, `PostgresBinding`).
- The URL's scheme chooses the driver. When two handle the scheme, `spring.datasource.driver` names one (`sqlite3`, `postgres`).
- A scheme no listed driver handles stops the start, naming the package to add.
- `username` and `password` are used when the URL does not carry them. A password is never logged: the pool's "Starting..." line and every message show `password=******`.

## The pool

| Key | Default | Meaning |
|---|---|---|
| `pool.max-size` | `10` | Most connections at once, at least 1. |
| `pool.min-idle` | `0` | Connections kept open when idle, from 0 to `max-size`. Hikari keeps as many as `max-size`. |
| `pool.connection-timeout` | `30s` | The longest wait for a connection, then `CannotGetJdbcConnectionException`. |
| `pool.idle-timeout` | `10m` | An idle connection above `min-idle` older than this is closed; `0` keeps them forever. |
| `pool.max-lifetime` | `30m` | A connection older than this is closed when it comes back to the pool; `0` for no limit. |
| `pool.validation` | `true` | A connection that waited in the pool is checked with `isValid` before it is handed out. |

Each key is under `spring.datasource.` for the primary database and `spring.datasources.<name>.` for a named one. The pool is closed when the application context closes.

## The beans

With `spring.datasource.url`, the starter makes these beans, each skipped when the application declares a bean of its type:

| Bean | What |
|---|---|
| `dataSource` | The primary `DataSource` (a pool, `@Primary`), its schema set up before its first connection. |
| `transactionManager` | A `DataSourceTransactionManager` over it. Without any data source, an in-memory transaction manager instead. |
| `jdbcTemplate`, `namedParameterJdbcTemplate`, `jdbcClient` | Over the primary data source. |
| `transactionTemplate` | Over the only transaction manager. |
| `<name>DataSource`, `<name>TransactionManager`, `<name>JdbcTemplate`, `<name>NamedParameterJdbcTemplate`, `<name>JdbcClient` | For each `spring.datasources.<name>.url`, qualified `<name>`. They are fallbacks, so injection by type still finds the primary. |
| `jdbcLogSettings` | The `spring.data.jdbc.*` log switches. In the build by `spring.data.jdbc.show-sql`: see [Logging SQL](logging.md#in-a-release-build). |
| `jdbcSlowQueryLogSettings` | `spring.data.jdbc.slow-query-threshold` alone, in a build without `jdbcLogSettings`. |
| `dataFailureAnalyzer` | Explains a URL no driver handles, a failed migration, a schema that does not fit. A startup check: in the build by `spring.main.startup-checks.enabled`, in debug and profile builds only unless it is `true`. |
| `dbHealthContributor` | The actuator's `db` health, one indicator per data source. |

Repositories without `@UseStore` use the `jdbc` store when a primary data source exists, the `memory` store otherwise.

## Named databases

`spring.datasources.<name>.*` takes the same keys as `spring.datasource.*` (URL, credentials, driver, pool, `sqlite.*`, `postgresql.*`), plus its own schema settings: `sql.init.*`, `sql.migration.*` and `data.jdbc.schema`.

- A repository uses it with `@UseDataSource('<name>')`, a transaction with `@Transactional(transactionManager: '<name>')`, an injection with `@Qualifier('<name>')`.
- The names are found in property sources that list their keys (files, the command line), not in an environment variable alone. Declare the URL in a file and override it with a variable.

## Startup order

**The schema is ready before any use.** At startup the context applies the migrations and checks the entities against the schema, and a failure stops the start with its message. The data source itself holds back every connection until that is done, so a repository, a `JdbcTemplate`, a runner or a synchronous `@PostConstruct` that starts a query never sees an unmigrated database.

The entities checked or created are those of the repositories on that data source, with their children. Entities no repository uses are the application's own tables.

A `DataSource` bean the application declares itself gets the same setup at startup, but does not hold back early queries.

**Start asynchronously.** The schema is set up on a connection, which Dart cannot wait for without `await`. With a SQL data source, start the application with `await runApplicationAsync(args)` (`SpringApplication.runAsync`; tests use `ApplicationContextRunner.runAsync`). The synchronous `runApplication(args)` fails at start with a report that says so.

## Driver settings from the environment

Every driver key (`sqlite.*`, `postgresql.*`) is read through the environment, so it is also an environment variable, a command line argument or a file entry:

```bash
SPRING_DATASOURCE_POSTGRESQL_SSLMODE=verify-full
SPRING_DATASOURCES_SHOP_SQLITE_BUSYTIMEOUT=2000
```

A key the driver does not know is refused. A driver's keys are read under the URL's scheme and under the driver's name, the driver's name winning: `spring.datasource.sqlite3.busy-timeout` is `sqlite.busy-timeout`, `spring.datasource.postgres.sslmode` is `postgresql.sslmode`.

## Health

With the actuator, the `db` health contributor checks each data source: `UP` when a connection can be taken and is valid, `DOWN` with the error otherwise.

```json
{"status": "UP", "details": {"database": "PostgreSQL", "validationQuery": "isValid()"}}
```

`management.health.db.enabled=false` switches it off.

## In Spring (Java)

```properties
spring.datasource.url=jdbc:postgresql://db.example.com:5432/shop
spring.datasource.hikari.maximum-pool-size=10
```

The pool is the starter's own, configured under `spring.datasource.pool.*` instead of `spring.datasource.hikari.*`. A second database is properties (`spring.datasources.<name>`), not a second `@Configuration` with its own `DataSource`, `JdbcTemplate` and `@EnableJdbcRepositories`.

## See also

- [SQLite](sqlite.md)
- [PostgreSQL](postgresql.md)
- [Schema and migrations](schema.md)
- [All properties](properties.md)
