# Serverpod Database

> Serverpod ORM with PostgreSQL or SQLite — CRUD, filters, sorting, pagination, includes, transactions, raw SQL, client-side database. Use when reading or writing data at runtime. To declare tables and relations instead, use serverpod-models.

- Skill: `serverpod/serverpod-database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add serverpod/serverpod-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/serverpod/serverpod-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: serverpod (https://skillmd.com/u/serverpod)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/serverpod/serverpod-database

---


# Serverpod Database

Serverpod generates ORM code for models with `table` in `.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.yaml
class: Company
table: company
fields:
  name: String
  foundedDate: DateTime
```

See [Serverpod Models](../serverpod-models/SKILL.md) 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 `t`:

```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` to `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` or `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.