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. 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
@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:
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): row-level security applies |
the user of the address (usually postgres, who passes row-level security), or the current user with db.rls: user |
@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.urlis set:spring.datasource.urlis that address. The application lists thepostgresdriver package, as for any PostgreSQL data source.- It is not set, and the application has no
spring.datasource.urlof its own:spring.data.storeissupabase. - The application has a
spring.datasource.urlof its own and nodb.url: its default store stays its own. A repository goes to the REST API with@UseStore('supabase'):
@UseStore('supabase')
abstract interface class Todos implements ListCrudRepository<Todo, int> {}
At startup the path is logged once, without any password:
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:
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) |
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 isnullis left out, so the database generates it. A@Versionstarts at 1. - An existing entity is a
PATCHfiltered on its id. - Both ask for the row back (
Prefer: return=representation), sosavereturns what the database stored: its defaults and triggers included. @Versionworks without a transaction: the update is also filtered on the version it read, and raises it. No row updated is anOptimisticLockingFailureException: someone else changed or removed the row, or the caller may not update it.
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 anIncorrectUpdateSemanticsDataAccessException. - 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 conditionid=not.is.null: a project's database refuses aDELETEwithout a condition.
JSON columns
A property marked @Json (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. It is called as the current user, in one request, POST /rest/v1/rpc/<name> (rpc is the REST API's own path):
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:
fromIdisfrom_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 isspring.supabase.db.schema. - With
db.urlthe same method callsselect * from "transfer"(…)in the caller's transaction.
A scalar result is read from that one column:
@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.
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, so a repository has the two kinds of methods of the data starter's changes of rows:
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);
}
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…Byruns the query through the REST API when it is listened to, and again after every change Realtime reports for its conditions.changes…Bygives each change as aRowChangewith the entity.previousis there only when the table hasreplica identity full; without it a delete carries the id only.- Filtering. One condition with
eq,neq,lt,lte,gtorgteis 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.urlthe 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 DataAccessExceptions, 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 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 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), 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
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, @Lock, @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.urlis touched. - A signed-in user whose token has no claims is a
PermissionDeniedDataAccessException. - Through the transaction pooler (port 6543)
rls: userfails the start: a role set outside a transaction could be used by another client's statement.
It is the bean supabaseRlsSessionHook, a JdbcSessionHook.
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.