
# SQLite

SQLite is a database in a file, with no server. List the `sqlite3` package, give a `sqlite:` URL, and the starter opens it through the generated `SqliteBinding` and the `sqliteJdbcDriver`. It suits a Flutter app, a command-line tool, a small server, and tests (`@DataTest` uses an in-memory SQLite database).

## Why

Many applications need a real SQL database but no database server: an app on a phone, a desktop tool, a single-instance service. SQLite gives transactions, indexes and SQL in one file. With the starter, the same repositories and migrations run on SQLite in a Flutter app and on PostgreSQL on a server.

## Example

```yaml
# pubspec.yaml
dependencies:
  spring_boot_starter_data: ^0.9.0
  sqlite3: ^3.8.0
```

```properties
# lib/resources/application.properties
spring.datasource.url=sqlite:file:library.db
spring.datasource.sqlite.base-directory=/var/lib/library
spring.datasource.sqlite.busy-timeout=2000
spring.datasource.sqlite.isolate=true
spring.datasource.pool.max-size=4
```

Run the generator once after adding `sqlite3`: it writes the binding and the driver bean.

## URLs

| URL | Database |
|---|---|
| `sqlite:file:library.db` | A file, relative to `base-directory`. |
| `sqlite:file:/var/lib/library/library.db` | An absolute path. |
| `sqlite::memory:` | In memory. Each connection has its own database, and the bundled SQLite has no shared cache: use `pool.max-size=1`, `pool.min-idle=0`, `pool.idle-timeout=0`, `pool.max-lifetime=0`, or the data is lost when the pool closes the connection. |

## Settings

| Key | Default | Meaning |
|---|---|---|
| `spring.datasource.sqlite.busy-timeout` | `5000` | Milliseconds a connection waits for SQLite's lock before `CannotAcquireLockException`. |
| `spring.datasource.sqlite.base-directory` | the working directory | Where relative `sqlite:file:` names are looked for. Beats `SqliteJdbcDriver.baseDirectory`. |
| `spring.datasource.sqlite.isolate` | `false` | `true` opens each connection inside an isolate of its own. Beats `SqliteJdbcDriver(isolate:)`. |
| `spring.datasource.sqlite.location` | `support` | Read by the Flutter starter only: `support`, `cache` or `documents`, the app directory it sets `base-directory` to. |

The same keys exist under `spring.datasources.<name>.sqlite.*`, and as `spring.datasource.sqlite3.*` (the driver's name wins).

## An isolate of its own

SQLite's calls are synchronous. On Flutter's UI isolate or a server's event loop, a long query or a wait for a lock holds everything else. With `spring.datasource.sqlite.isolate=true`, each connection opens its database inside a worker isolate, and every statement is a message to it.

Nothing else changes: transactions, savepoints, read-only mode, streams (rows come in batches, and the cursor is closed when the stream is cancelled) and errors, with their types, work as before. Things to know:

- Values are copied between the isolates, so a very large result costs a copy.
- A connection must be closed, which ends its isolate. The pool does it when the application closes.
- If the isolate dies, the pending and later statements fail with a `SqlError` whose `connectionLost` is `true`.
- The generated `SqliteBinding` is sent to the isolate, so a binding of your own must hold no native resource.
- A loader override of the `sqlite3` package (`open.overrideFor(...)`) belongs to the isolate that runs it: the one made in `main` does not reach the worker, and the starter has no hook to run code in the worker. When a platform needs an override, keep `isolate=false`.

## In a Flutter app

`spring_boot_starter_flutter` sets two properties when the app configures a SQL database: `base-directory` to the app's own directory (chosen by `spring.datasource.sqlite.location`), and `isolate=true`. They have the lowest precedence, so a property of yours wins. The starters do not depend on each other; the Flutter starter only sets properties.

| `location` | Directory |
|---|---|
| `support` (default) | The app's private data, never deleted by the system and backed up with the device. On Android, the app's `databases/` directory. |
| `cache` | May be deleted when space runs out: for a database that can be built again. |
| `documents` | The user's documents folder. |

Any other value stops the start, naming these three. The web has no such directory: nothing is set there.

## How values are stored

| Dart | SQLite |
|---|---|
| `int`, `bool` (0 or 1) | `INTEGER` |
| `double`, `num` | `REAL` |
| `String`, enums | `TEXT` |
| `DateTime` | ISO 8601 `TEXT` in UTC (`2026-03-01T10:00:00.000Z`), which sorts like the instant; an integer read back is taken as milliseconds since the epoch |
| `Uint8List`, `List<int>` | `BLOB` |
| `BigInt` | `TEXT`, because an `INTEGER` holds 64 bits |

A generated id is `INTEGER PRIMARY KEY`.

## Transactions and locks

- The only isolation level is serializable: `Isolation.defaultLevel` and `Isolation.serializable` are accepted, any other is an `InvalidIsolationLevelException`.
- There are no row locks. A transaction with a `@Lock` query takes the write lock of the whole database when it begins (`BEGIN IMMEDIATE`); the `timeout` and `skipLocked` of `@Lock` are ignored, and the busy timeout applies.
- Savepoints give `Propagation.nested`.
- Foreign keys are on (`foreign_keys = ON`), so references are enforced, and the in-memory store behaves the same way.

## Text

- Text compares by code units, as in the in-memory store, not by a collation.
- `IgnoreCase` uses a `lower` function the driver registers on each connection (Dart's `toLowerCase`), because SQLite's own folds ASCII only.
- A derived `In` binds one value per element; SQLite allows 32 766 values per statement, and a longer list is refused with a clear message.

## In Spring (Java)

Spring Boot has no SQLite support of its own: you add a JDBC driver and a Hibernate dialect or a Spring Data JDBC dialect yourself. Here the dialect, the driver glue and the type mapping come with the starter.

## See also

- [Data sources](data-sources.md)
- [PostgreSQL](postgresql.md)
- [Testing](testing.md)
