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
# pubspec.yaml
dependencies:
spring_boot_starter_data: ^0.9.0
sqlite3: ^3.8.0
# 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
SqlErrorwhoseconnectionLostistrue. - The generated
SqliteBindingis sent to the isolate, so a binding of your own must hold no native resource. - A loader override of the
sqlite3package (open.overrideFor(...)) belongs to the isolate that runs it: the one made inmaindoes not reach the worker, and the starter has no hook to run code in the worker. When a platform needs an override, keepisolate=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.defaultLevelandIsolation.serializableare accepted, any other is anInvalidIsolationLevelException. - There are no row locks. A transaction with a
@Lockquery takes the write lock of the whole database when it begins (BEGIN IMMEDIATE); thetimeoutandskipLockedof@Lockare 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.
IgnoreCaseuses alowerfunction the driver registers on each connection (Dart'stoLowerCase), because SQLite's own folds ASCII only.- A derived
Inbinds 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.