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
- Each parameter of the method is one argument of the function, passed by name.
- The name is the parameter's name in snake case, as columns are named:
accountIdisaccount_id. The naming strategy of the mapping names them. - Values are converted as column values are: an enum is its
name. - A list is one array argument.
@Procedure('schema.name')calls a function outside the search path.
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
Pageor aSlicecannot be returned: a function is not a query to count; - there is no
PageableorSortparameter: every parameter of the method is an argument of the function; @Proceduredoes not go together with@Query,@Modifyingor@Lock;- the name of the function is not empty.
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:
- a function that does not exist is a
BadSqlGrammarExceptionon PostgreSQL; - a privilege the caller lacks, or an exception the function raises with the state
42501, is aPermissionDeniedDataAccessException; - the wrong number of arguments is an
InvalidDataAccessApiUsageException.
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).