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):

@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

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}

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);
}

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}

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

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.

See also