Serverpod Database

Serverpod generates ORM code for models with

table

.spy.yaml

. PostgreSQL is the default production database; SQLite is also supported for projects/tests that configure the database on

config/<runMode>.yaml

with

database.filePath: <path>

CRUD

spy

class: Company
table: company
fields:
  name: String
  foundedDate: DateTime

See Serverpod Models for more on defining models and relations.

dart

var company = Company(name: 'Serverpod Inc.', foundedDate: DateTime.now());
company = await Company.db.insertRow(session, company);
var stored = await Company.db.findById(session, company.id);
company = company.copyWith(name: 'New Name');
await Company.db.updateRow(session, company);
await Company.db.deleteRow(session, company);

Filters

Fluent filter API via

where

callback with table descriptor

dart

var activeCompanies = await Company.db.find(session,
  where: (t) => t.name.ilike('a%') & (t.foundedDate > DateTime(2020)));

Equality:

t.column.equals(value)

t.column.notEquals(value)

Comparison (int/double/Duration/DateTime):
```
>
```
,
```
>=
```
,
```
<
```
,
```
<=
```
Range:
```
t.column.between(a, b)
```
,
```
notBetween
```
Set:
```
t.column.inSet(set)
```
,
```
notInSet
```
String:
```
t.column.like('A%')
```
(case-sensitive),
```
ilike
```
(case-insensitive);
```
%
```
= any chars,
```
_
```
= one char
Combine:
```
&
```
(and),
```
|
```
(or),
```
~
```
(not); use parentheses for precedence
Related (one-to-one):
```
t.address.street.like('%road%')
```

Related (one-to-many):

t.orders.count() > 3

t.orders.count((o) => o.itemType.equals('book')) > 3

t.orders.none()

t.orders.any()

t.orders.any((o) => ...)

t.orders.every((o) => ...)

Vector: distance operators (
```
distanceCosine
```
,
```
distanceL2
```
) for similarity search (PostgreSQL only).

Sorting and pagination

Sort:

orderBy: (t) => t.column

(ascending default);

orderBy: (t) => t.column.desc()

for descending

Multiple:

orderByList: (t) => [t.name.desc(), t.id.asc()]

Sort on relation:

orderBy: (t) => t.ceo.name

; on count:

orderBy: (t) => t.employees.count()

Pagination:

limit

offset

for offset-based; cursor-based:

where: (t) => t.id > lastId

with

orderBy: (t) => t.id

and

limit

Relations (include, attach, detach)

Fetch related objects with

include

dart

var employee = await Employee.db.findById(session, id,
  include: Employee.include(address: Address.include()));

var company = await Company.db.findById(session, id,
  include: Company.include(
    employees: Employee.includeList(
      where: (t) => t.name.ilike('a%'),
      orderBy: (t) => t.name,
      limit: 10,
      includes: Employee.include(address: Address.include()),
    ),
  ));

Models with object relations also have dedicated methods for attach/detach:

dart

await Company.db.attachRow.employees(session, company, employee);
await Company.db.attach.employees(session, company, [e1, e2]);
await Company.db.detachRow.employees(session, employee);

Objects being attached/detached must have

id

set (typically fetched previously from the database).

Transactions

dart

await session.db.transaction((tx) async {
  await Company.db.insertRow(session, company, transaction: tx);
  await OtherModel.db.updateRow(session, other, transaction: tx);
});

Use

tx

for all DB calls inside the transaction.

Row locking

Requires a transaction. Pass

lockMode

and

transaction

find

findFirstRow

findById

LockMode.forUpdate

(exclusive),

forNoKeyUpdate

forShare

forKeyShare

```
LockBehavior.wait
```
(default),
```
noWait
```
(throw),
```
skipLocked
```
(skip, good for job queues)

Lock without reading:

Company.db.lockRows(session, where: ..., lockMode: ..., transaction: tx)

On SQLite, trying to lock rows will be a no-op, since it only supports one write transaction at a time.

Runtime parameters

Set Postgres params globally:

runtimeParametersBuilder: (params) => [params.searchPaths(['my_schema', 'public'])]

at Serverpod init. Per-transaction:

await tx.setRuntimeParameters(...)

. Use for search path, vector index options, or custom

MapRuntimeParameters

. Cannot set at session level due to connection pooling.

Raw SQL

For running raw SQL queries, use one of the following methods:

dart

late DatabaseResult result;
result = await session.db.unsafeQuery(query, parameters: parameters);
result = await session.db.unsafeSimpleQuery(query);

late int rowsAffected;
rowsAffected = await session.db.unsafeExecute(query, parameters: parameters);
rowsAffected = await session.db.unsafeSimpleExecute(query);

Prefer ORM for standard CRUD, and parameterize user input instead of string-concatenating SQL.

Client-side database

When at least one model has

database: client

database: all

, the generated

Client

class will have a

createSession

method that returns a

ClientDatabaseSession

for the SQLite database file. On Flutter, open the database doing:

dart

import 'package:flutter/foundation.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:my_project_client/my_project_client.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();

  // Set the client URL to the server URL
  final client = Client(clientUrl);

  // Resolve the database path
  final path = await resolveDatabasePath('app.db');

  // Store the session in your state manager to later use on database operations.
  final session = await client.createSession(path, isDebugMode: kDebugMode);
}

Future<String> resolveDatabasePath(String fileName) async {
  if (kIsWeb) return fileName;
  final dir = await getApplicationSupportDirectory();
  return p.join(dir.path, fileName);
}

Note that the

serverpod_database

package will have to be added as dependency on the client package.

serverpod-database

NPX Install

Tags

SKILL.md Content

Serverpod Database

CRUD

Filters

Sorting and pagination

Relations (include, attach, detach)

Transactions

Row locking

Runtime parameters

Raw SQL

Client-side database