Database functions

@Procedure('name') on a repository method calls a database function by name. The method's parameters are its named arguments, and what the function returns is read as the result of a @Query is.

Why

Some work belongs in the database: several writes that must happen together, a query the planner does better with than a client, a rule every client must follow. A function is also the way to do several writes atomically on a store without transactions, such as a REST API. Calling one by hand means its SQL, the argument order and a row mapper; @Procedure is the name and the method's signature.

Example

abstract interface class Accounts
    implements CrudRepository<Account, int> {
  // select * from "transfer"("from_id" => ?, "to_id" => ?, "amount" => ?)
  @Procedure('transfer')
  Future<void> transfer(int fromId, int toId, int amount);

  @Procedure('accounts_over')
  Future<List<Account>> over(int balance);
}
create function accounts_over(balance int) returns setof accounts
language sql stable as $$ select * from accounts where accounts.balance > $1 $$;

Arguments

Results

What the function returns is read as the rows of a @Query are:

The method returns From
Future<List<Account>>, Future<Set<Account>>, Stream<Account> The rows: the repository's entity, or a class of the application built from the columns by name. A Stream calls the function when it is listened to.
Future<List<int>>, Future<Set<String>> A single column of each row.
Future<Account?>, Future<int> One value. No row is null for a nullable type, else an error.
Future<List<Map<String, Object?>>> The rows as maps.
Future<void> Nothing: what the function returns is ignored.

The build checks the method, and a mistake is one of the repository's problems:

A method with @Procedure is never read as a derived query, whatever its name.

Stores

The store of the repository must have database functions (see Stores and their capabilities). On a store without them the repository fails when it is created, and at build time when the build knows the store.

Store The call
SQL on PostgreSQL select * from "name"("argument" => ?), in the current transaction of the caller: a rollback undoes what the function wrote.
SQL on SQLite no functions
in memory no functions
supabase (spring_boot_starter_supabase) POST /rest/v1/rpc/name (rpc is the REST API's own path), as the current user; one request, so atomic by itself.

Errors

Failures are DataAccessExceptions, as for every repository method:

What the generator writes

For each @Procedure method the generator writes a ProcedureMethod constant (the method's name, its annotation, its parameters, the shape and the element of its result) and a call of the repository's operations:

// For: @Procedure('accounts_over')
//      Future<List<Account>> over(int balance);
static const _over = ProcedureMethod(
  r'over',
  Procedure(r'accounts_over'),
  parameters: [r'balance'],
  element: ClassType<Account>(),
);

@override
Future<List<Account>> over(int balance) =>
    this.operations.procedure<List<Account>>(_over, [balance]);

The constants are handed to the repository's operations as functions:, and each method as a StoreRequirement of functions.

The run time

Under the generated code there are two layers a library can use directly.

The store: FunctionDataStore.callFunction(name, arguments) calls a function and gives its rows as maps of column names to values.

final rows = await store.callFunction('restock', {
  'sku': 'A-1',
  'quantity': 5,
});

The operations of a repository: a ProcedureMethod describes one @Procedure method (its name, its annotation, its parameters, the shape and the element of its result), and RepositoryOperations.procedure<R> and procedureStream<E> call it with the arguments in the order of the parameters:

const named = ProcedureMethod(
  'named',
  Procedure('authors_named'),
  parameters: ['prefix'],
  element: ClassType<Author>(),
);

final authors = RepositoryFactory(
  mappingContext: mappingContext,
  converter: converter,
  stores: [store],
).operations<Author, int>(
  'Authors',
  annotations: const [UseStore('jdbc')],
  functions: const [named],
);

final found = await authors.procedure<List<Author>>(named, ['He']);

In Spring (Java)

@Procedure("name") of Spring Data JPA; Spring Data JDBC has none. The store's call is SimpleJdbcCall.withFunctionName(name).execute(arguments).

See also