
# SQLite databases

With `spring_boot_starter_data` and a SQL database in the properties (`spring.datasource.url`, or `spring.datasources.<name>.url`), a relative `sqlite:file:books.db` is a file in the app's own directory, and SQLite's synchronous calls run on an isolate of their own, so a long query never holds the UI. Nothing to write.

## Why

A Flutter app has no working directory to keep a database in, and SQLite's calls are synchronous: on the UI isolate a slow query drops frames. The starter sets the two properties the data starter needs, so the same repository code runs on a server and in an app.

## Example

```yaml
# lib/resources/application.yml
spring:
  datasource:
    url: sqlite:file:books.db
    sqlite:
      location: support     # support (the default), cache or documents
```

```dart
// The repository is the data starter's; the file is in the app's directory.
abstract interface class BookRepository
    implements ListCrudRepository<Book, int> {
  Future<List<Book>> findByAuthor(String author);
}
```

## What the starter sets

Before the application starts, the starter reads the app's directories from `path_provider`. Once the configuration files are read, for the primary data source and for each named one, it sets:

| Key | Value | Meaning |
|---|---|---|
| `spring.datasource.sqlite.base-directory` | the directory of `location` | Relative file names are relative to it; the directory is created when it is missing |
| `spring.datasource.sqlite.isolate` | `true` | Each connection runs on an isolate of its own |

They have the lowest precedence of all: a property of your own (a file, `--dart-define`, `defaultProperties`) wins. Without a data source, nothing is set. The starters do not depend on each other: this one only sets properties the other reads.

## The property

| Key | Type | Default | Meaning |
|---|---|---|---|
| `spring.datasource.sqlite.location` | `support`, `cache`, `documents` | `support` | Which directory of the app holds the databases; any other value stops the start, naming these three |
| `spring.datasources.<name>.sqlite.location` | the same | `support` | The same for a named data source |

`support` is the app's private data, which the system never deletes and backs up with the device; `cache` can be deleted when space runs out, so it is for a database that can be built again; `documents` is the user's documents folder.

| Platform | `support` directory |
|---|---|
| Android | the app's `databases/` directory, as `Context.getDatabasePath` |
| iOS | `Library/Application Support` |
| macOS | the container's `Application Support/<bundle id>` |
| Windows | `%LOCALAPPDATA%\<company>\<product>` (not the roaming directory); `cache` is its `cache` folder, so the two never share a folder |
| Linux | `$XDG_DATA_HOME/<app id>` |

## Limits

- **The web** has no such directory yet: nothing is set there.
- **Widget tests** have no `path_provider` plugin: nothing is set, and the database stays where its URL says. Install a `PathProviderPlatform` fake to test the directories.
- **No readable directory.** When the directory cannot be read and none was asked for, only `isolate` is set, and a warning says that relative file names stay relative to the working directory.
- **Loader overrides.** A loader override of the `sqlite3` package (`open.overrideFor(...)`) is per isolate: the one you make in `main` does not reach the worker isolate. If your platform needs an override, set `spring.datasource.sqlite.isolate=false` (a property of your own wins) to keep the calls on the UI isolate.

## See also

- [Files of the app](files.md)
- [Data in the generator](../spring-core-dart-generator/data.md)
- [Configuration in a Flutter app](configuration.md)
