
# Data

The data of a Supabase project is reached through Spring Data: the entities, repositories, derived queries, `Pageable` and `Sort` of [spring_boot_starter_data](../spring-boot-starter-data/index.md). There are two paths to the database, and the configuration decides which one the repositories take.

## Why

A Supabase project offers its database twice: as a REST API (PostgREST) that works from anywhere with the user's token, and as plain PostgreSQL for a server that has the password. A Flutter app can only use the first; a server may want transactions and locks, which only the second has. The repository interfaces are the same on both, so the choice is one property, and an application says once at startup which path it is on.

## Example

```dart
@Table('todos')
class Todo {
  Todo({
    required this.title,
    required this.listId,
    this.done = false,
    this.id,
    this.createdAt,
    this.version,
  });

  @Id()
  int? id;

  String title;
  String listId;
  bool done;
  DateTime? createdAt;

  @Version()
  int? version;
}

abstract interface class Todos implements ListCrudRepository<Todo, int> {
  Future<List<Todo>> findByDoneOrderByCreatedAtDesc(bool done);

  Future<Page<Todo>> findByListId(String listId, Pageable pageable);

  Future<int> countByDone(bool done);

  Future<int> deleteByDone(bool done);
}
```

With only `spring.supabase.url` and `anon-key` set, `findByDoneOrderByCreatedAtDesc(false)` is one request:

```text
GET /rest/v1/todos?done=eq.false&order=created_at.desc
apikey: <anon key>
Authorization: Bearer <the user's token, or the anon key>
```

## The two paths

| | `spring.supabase.db.url` not set (default) | `spring.supabase.db.url` set |
|---|---|---|
| The store | `supabase` (`PostgrestDataStore`): one HTTP request per call to `/rest/v1` | `jdbc`: SQL over PostgreSQL |
| Made as | the current user ([`SupabaseCredentials`](credentials.md)): row-level security applies | the user of the address (usually `postgres`, who passes row-level security), or the current user with [`db.rls: user`](#row-level-security-with-sql) |
| `@Transactional`, `@Lock`, SQL `@Query`, `JdbcClient` | no | yes |
| `@MappedCollection` | no | yes |
| Where | a server, a Flutter app, the web | a server (in a Flutter app the password ships in the app, and the starter warns) |

How the starter chooses, with defaults of the lowest precedence (what the application sets itself wins):

- `spring.supabase.db.url` is set: `spring.datasource.url` is that address. The application lists the `postgres` driver package, as for any [PostgreSQL](../spring-boot-starter-data/postgresql.md) data source.
- It is not set, and the application has no `spring.datasource.url` of its own: `spring.data.store` is `supabase`.
- The application has a `spring.datasource.url` of its own and no `db.url`: its default store stays its own. A repository goes to the REST API with `@UseStore('supabase')`:

```dart
@UseStore('supabase')
abstract interface class Todos implements ListCrudRepository<Todo, int> {}
```

At startup the path is logged once, without any password:

```text
Supabase: the repositories use the REST API of abcd.supabase.co, as the current user (row-level security applies); no transactions, locks or SQL (spring.supabase.db.url for those)
Supabase: the repositories use SQL over db.abcd.supabase.co:5432 (direct connection), as the user of the address (row-level security is passed; spring.supabase.db.rls: user to apply it)
```

## Filters of the REST store

A derived query becomes query parameters of PostgREST. A value is always data: text that the API would read as syntax is quoted or escaped.

| In the method name | Sent as |
|---|---|
| `findByDone(false)` | `done=eq.false` |
| `findByTitle(null)` | `title=is.null` |
| `Not` | `neq`; with `null`, `not.is.null` |
| `IsNull`, `IsNotNull`, `True`, `False` | `is.null`, `not.is.null`, `is.true`, `is.false` |
| `LessThan`, `LessThanEqual`, `GreaterThan`, `GreaterThanEqual` | `lt`, `lte`, `gt`, `gte` |
| `Between` | `gte` and `lte` on the column |
| `In`, `NotIn` | `in.(1,2)`, `not.in.(…)`; text values in double quotes |
| `Like`, `NotLike` | `match` with the pattern as an anchored regular expression (`%` is `.*`, `_` is `.`) |
| `StartingWith`, `EndingWith`, `Containing`, `NotContaining` | `match` with the text escaped: `title=match.^Buy` |
| `IgnoreCase` on an equality or a text pattern | `imatch` |
| `And` | one parameter per condition |
| `Or` | one `or=(…)` parameter; a group of several conditions is `and(…)` |

The text patterns are sent as regular expressions because the API's own `like` reads every `*` as a wildcard and cannot say a literal one. So `findByTitleStartingWithIgnoreCase('a*b(c')` sends `title=imatch.^a\*b\(c`.

`findByListIdAndDoneOrPriority('a,b', true, Priority.high)` sends one parameter:

```text
or=(and(list_id.eq."a,b",done.eq.true),priority.eq."high")
```

Dates are sent in UTC as ISO 8601, an enum as its name.

## Sorting and pages

| In the method | Sent as |
|---|---|
| `OrderByCreatedAtDesc`, a `Sort` | `order=created_at.desc`; `nullsfirst` and `nullslast` are added when the `Sort` says so |
| `First`, `Top3` | `limit` |
| `Pageable` | `offset` and `limit`; ordered by the id when no order is given, so a page is the same twice |
| a `Page` result | the rows, then a count for the total: a `GET` of no rows (`limit=0`) with `Prefer: count=exact` |
| `countBy…` | the same count: a `GET` of no rows with `Prefer: count=exact` |
| `existsBy…` | the id of one row (`limit=1`) |

```dart
final page = await todos.findByListId('l1', PageRequest.of(1, 2));
// GET /rest/v1/todos?list_id=eq.l1&order=id&offset=2&limit=2
// GET /rest/v1/todos?list_id=eq.l1&select=id&limit=0        Prefer: count=exact
```

The total is read from the `Content-Range` header of the answer (`*/0`, or `0-24/3573`). It is not a `HEAD`: a `HEAD` answer can make a server or a proxy close the connection under the next call.

## Saving and @Version

- **A new entity** is a `POST`. An id that is `null` is left out, so the database generates it. A `@Version` starts at 1.
- **An existing entity** is a `PATCH` filtered on its id.
- Both ask for the row back (`Prefer: return=representation`), so `save` returns what the database stored: its defaults and triggers included.
- **`@Version`** works without a transaction: the update is also filtered on the version it read, and raises it. No row updated is an `OptimisticLockingFailureException`: someone else changed or removed the row, or the caller may not update it.

```dart
await todos.save(Todo(title: 'Changed', listId: 'l1', id: 9, version: 2));
// PATCH /rest/v1/todos?id=eq.9&version=eq.2     {"title": "Changed", ..., "version": 3}
```

- Without a `@Version`, no row updated is an `IncorrectUpdateSemanticsDataAccessException`.
- A row that was inserted but may not be read back (the policies allow the insert and not the select) is a `PermissionDeniedDataAccessException`.
- `deleteAll()` is sent with the condition `id=not.is.null`: a project's database refuses a `DELETE` without a condition.

## JSON columns

A property marked [`@Json`](../spring-boot-starter-data/json-columns.md) (a class of the application, a list or a map) goes into a `jsonb` or `json` column: the REST store sends it as nested JSON and reads the answer back into the class. Without `@Json`, the REST store sends a plain `Map` or `List` as JSON as it is. With `db.url` the store is the data starter's SQL store, which refuses a `Map`, `List` or `Set` without `@Json` when the repository is created, with the fix; a `List` of simple values in an array column (`tags text[]`) stays.

## Database functions

For work that must be atomic without `db.url`, write a database function and declare it on the repository with [`@Procedure`](../spring-boot-starter-data/database-functions.md). It is called as the current user, in one request, `POST /rest/v1/rpc/<name>` (`rpc` is the REST API's own path):

```dart
abstract interface class Accounts implements CrudRepository<Account, int> {
  // POST /rest/v1/rpc/transfer   {"from_id": 1, "to_id": 2, "amount": 50}
  @Procedure('transfer')
  Future<void> transfer(int fromId, int toId, int amount);

  @Procedure('accounts_over')
  Future<List<Account>> over(int balance);
}
```

- **The arguments** are the method's parameters by name, in snake case: `fromId` is `from_id`.
- **The result** is read as the data starter reads it: entities or other classes from the rows, one value from a scalar, nothing for `Future<void>`.
- **The name** is a plain identifier (letters, digits, `_`): `@Procedure('schema.name')` is refused on this store. The function's schema is `spring.supabase.db.schema`.
- With `db.url` the same method calls `select * from "transfer"(…)` in the caller's transaction.

A scalar result is read from that one column:

```dart
@UseStore('supabase')
abstract interface class Todos implements ListCrudRepository<Todo, int> {
  @Procedure('close_all')
  Future<int> closeAll(String listId);
}

final closed = await todos.closeAll('l1');
// POST /rest/v1/rpc/close_all   {"list_id": "l1"}   the answer 2: closed is 2
```

A function the API refuses (`permission denied for function close_all`) is a data access failure, as any other refused request.

Under the annotation, the store calls a function by name (`FunctionDataStore.callFunction`): a function that returns rows gives them, a scalar gives one row with one column named after the function, nothing gives no rows.

```dart
final store = context.getBean<PostgrestDataStore>();
final rows = await store.callFunction('transfer', {
  'from_id': 1,
  'to_id': 2,
  'amount': 50,
});
// POST /rest/v1/rpc/transfer   {"from_id": 1, "to_id": 2, "amount": 50}
```

## Changes of rows

On the REST path the store reports the changes of its rows from [Realtime](realtime.md), so a repository has the two kinds of methods of the data starter's [changes of rows](../spring-boot-starter-data/changes.md):

```dart
abstract interface class Todos implements ListCrudRepository<Todo, int> {
  // The open todos of a list, now and after every change.
  Stream<List<Todo>> watchByListIdAndDoneOrderByIdAsc(String listId, bool done);

  // Each insert, update and delete of the todos of a list.
  Stream<RowChange<Todo>> changesByListId(String listId);
}
```

```dart
final subscription = todos.changesByListId('l1').listen((change) {
  // change.kind is RowChangeKind.insert, update or delete; change.row is the entity
  // (null for a delete), change.previous the old one (null without replica identity full).
});
// Realtime join: {event: *, schema: public, table: todos, filter: list_id=eq.l1}
```

- **`watch…By`** runs the query through the REST API when it is listened to, and again after every change Realtime reports for its conditions.
- **`changes…By`** gives each change as a `RowChange` with the entity. `previous` is there only when the table has `replica identity full`; without it a delete carries the id only.
- **Filtering.** One condition with `eq`, `neq`, `lt`, `lte`, `gt` or `gte` is filtered by Realtime on the server; any other conditions are tested on this side.
- The table must be in the publication `supabase_realtime`, and the user must be allowed to read the rows.
- With `db.url` the repositories are on the SQL store, which reports no changes: such a method fails when the application starts. Use `@UseStore('supabase')` on the repository that watches.

## Schema

The tables are those of `spring.supabase.db.schema` (the API's default, `public`, when not set), sent as `Accept-Profile` on reads and `Content-Profile` on writes. An entity with a schema of its own in `@Table` uses that one. The schema must be among the project's exposed schemas.

## Errors

Errors are the data starter's [`DataAccessException`s](../spring-boot-starter-data/errors.md), the same on both paths. The database's own code is translated as on the SQL path:

| Answer of the REST API | Exception |
|---|---|
| a PostgreSQL code: `23505` (a duplicate key) | `DuplicateKeyException` |
| `23503` (a foreign key) | `DataIntegrityViolationException` |
| `42501` (a privilege, a row-level security policy) | `PermissionDeniedDataAccessException` |
| any other PostgreSQL code | what the [PostgreSQL](../spring-boot-starter-data/postgresql.md) dialect gives for it |
| `PGRST301`, `PGRST302`, `PGRST303`, or status 401 / 403 (the token: expired, not accepted) | `PermissionDeniedDataAccessException` |
| `PGRST200`, `PGRST202`, `PGRST204`, `PGRST205`, or status 404 (a table, a column or a function the API does not know) | `InvalidDataAccessResourceUsageException` |
| status 409 | `DataIntegrityViolationException` |
| status 500 and above | `DataAccessResourceFailureException` |
| anything else (a filter, an order or a body the API cannot read) | `InvalidDataAccessApiUsageException` |
| no answer | `DataAccessResourceFailureException`; a timeout is a `QueryTimeoutException` |

The message is what the API said (`message`, `details`, `hint`) with its code.

## What the REST store cannot do

Each is refused with the reason and the fix:

| Not supported | What happens | Instead |
|---|---|---|
| `@Transactional`, `@Lock`, the SQL of `@Query` | The store's [capabilities](../spring-boot-starter-data/stores.md) lack them (it has `functions`, and `changes` with Realtime): a repository on this store that uses them **fails the build**, and so does a `@Transactional` bean when the build's default store is `supabase`. The message names the fix | a database function, or `db.url` |
| An entity with `@MappedCollection` | `InvalidDataAccessApiUsageException`: the rows of an aggregate are written together, in a transaction | `db.url`, or the children as an entity with a repository of its own |
| `Distinct` | `InvalidDataAccessApiUsageException` | a database function, or `db.url` |
| An order that ignores case | `InvalidDataAccessApiUsageException` | a column that holds the lower-case text, or `db.url` |
| `IgnoreCase` on `LessThan`, `Between`, `In`… | `InvalidDataAccessApiUsageException` | `db.url` |

A reference to another aggregate is a plain column and works. Embedding of related rows and upsert are not used.

**What the build can tell.** The starter describes its store to the build (`@DataStoreDeclaration`, see [Stores](../spring-boot-starter-data/stores.md)), and the build reads `spring.supabase.url` from `application.yml` or `application.properties` (no profile). With `spring.supabase.url` there and no `spring.supabase.db.url`, `spring.datasource.url` or `spring.data.store`, the default store is `supabase` and the check is made when the code is generated. With only `spring.supabase.db.url` set the build cannot tell the default store, and the check happens when the application starts.

## SQL: db.url and poolers

```yaml
spring:
  supabase:
    db:
      url: postgresql://postgres:${DB_PASSWORD}@db.abcd.supabase.co:5432/postgres
```

With it everything of the data starter's SQL store works: [transactions](../spring-boot-starter-data/transactions.md), [`@Lock`](../spring-boot-starter-data/locks.md), `@Query`, `JdbcClient`, migrations. The kind of connection is read from the address:

| Address | Kind | Notes |
|---|---|---|
| `db.<ref>.supabase.co:5432` | direct connection | |
| `….pooler.supabase.com:5432` | session pooler | everything works |
| `….pooler.supabase.com:6543` | transaction pooler | a warning at startup: it keeps no session between transactions, so prepared statements, advisory locks and `LISTEN` do not work through it |

An address that is not a database's fails the start.

## Row-level security with SQL

SQL over `db.url` runs as the user of that address, usually `postgres`, who passes row-level security. `spring.supabase.db.rls: user` makes every transaction, and every statement outside one, run as the caller, the way the REST API does:

| Caller | The database session |
|---|---|
| a signed-in user | the role `authenticated`, and the claims of their token in `request.jwt.claims`: `auth.uid()`, `auth.jwt()` and the policies see them |
| nobody | the role `anon` |
| inside `asServiceRole` | nothing is changed: the statements run as the address's user |

- In a transaction the role and the claims are set for that transaction only (`set local`).
- Outside one they are set on the connection and taken off again before it goes back to the pool. A connection whose reset fails is closed, never reused.
- The role is one of two fixed names, and the claims are passed as a value, never written into the SQL text.
- Only the data source of `db.url` is touched.
- A signed-in user whose token has no claims is a `PermissionDeniedDataAccessException`.
- Through the transaction pooler (port 6543) `rls: user` fails the start: a role set outside a transaction could be used by another client's statement.

It is the bean `supabaseRlsSessionHook`, a [`JdbcSessionHook`](../spring-boot-starter-data/session-hooks.md).

## The database protects the data

Neither path checks what a user may do. The database does: row-level security, column privileges (`grant update (name) …`), `CHECK` constraints. A request sent straight to the REST API with the anon key gets the same answer as one from the application, so the rules must be there.

## In Spring (Java)

Spring Data has a module per store; here one repository interface runs on either path. The REST store is a `DataStore` of this project's Spring Data, as the in-memory and the SQL stores are.

## See also

- [Repositories](../spring-boot-starter-data/repositories.md)
- [Derived queries](../spring-boot-starter-data/derived-queries.md)
- [Stores and their capabilities](../spring-boot-starter-data/stores.md)
- [Errors](../spring-boot-starter-data/errors.md)
- [Who a call is made as](credentials.md)
- [Realtime](realtime.md)
