Serverpod ORM with PostgreSQL — CRUD, filters, sorting, pagination, relations, migrations, transactions, raw SQL. Use when querying the database, writing migrations, or working with relations. Also include serverpod-models skill if you need to change models.
PostgreSQL via ORM. Models with table in .spy.yaml get generated ORM code.
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);
Fluent filter API via where callback with table descriptor t:
var activeCompanies = await Company.db.find(session,
where: (t) => t.name.ilike('a%') & (t.foundedDate > DateTime(2020)));
t.column.equals(value), t.column.notEquals(value)>, >=, <, <=t.column.between(a, b), notBetweent.column.inSet(set), notInSett.column.like('A%') (case-sensitive), ilike (case-insensitive); % = any chars, _ = one char& (and), | (or), ~ (not); use parentheses for precedencet.address.street.like('%road%')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) => ...)distanceCosine, distanceL2) for similarity searchorderBy: (t) => t.column (ascending default); orderDescending: true for descendingorderByList: (t) => [t.name.desc(), t.id.asc()]orderBy: (t) => t.ceo.name; on count: orderBy: (t) => t.employees.count()limit + offset for offset-based; cursor-based: where: (t) => t.id > lastId with orderBy: (t) => t.id and limitvar 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()),
),
));
Attach/detach: Company.db.attachRow.employees(session, company, employee), Company.db.attach.employees(session, company, [e1, e2]), Company.db.detachRow.employees(session, employee). Objects must have id set.
await session.transaction((tx) async {
await Company.db.insertRow(tx, company);
await OtherModel.db.updateRow(tx, other);
});
Use tx (not the outer session) for all DB calls inside.
Requires a transaction. Pass lockMode and transaction to find/findFirstRow/findById:
LockMode.forUpdate (exclusive), forNoKeyUpdate, forShare, forKeyShareLockBehavior.wait (default), noWait (throw), skipLocked (skip, good for job queues)Company.db.lockRows(session, where: ..., lockMode: ..., transaction: tx)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.
After changing models with table/indexes: serverpod create-migration, then dart run bin/main.dart --apply-migrations. Options: --force (override safeguards), --tag "v1" (name tag). For scripted apply: --role maintenance --apply-migrations (exits with 0 on success). Opt out per table: managedMigration: false.
Repair migrations: If DB was changed outside migrations: serverpod create-repair-migration (options: --mode production, --version <name>, --force, --tag). Apply: --apply-repair-migration. Runs before normal migrations.
Use raw SQL via the session/database API for queries beyond ORM. Prefer ORM for standard CRUD.