serverpod-database

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Serverpod Database

Serverpod 数据库

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>

Serverpod 会为

.spy.yaml

中带有

table

标识的模型生成ORM代码。PostgreSQL是默认的生产环境数据库；若在

config/<runMode>.yaml

中配置

database.filePath: <path>

，SQLite也可用于项目或测试环境。

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);

spy

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

如需了解更多模型及关系定义的内容，请查看Serverpod Models。

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).

通过

where

回调结合表描述符

实现流畅的筛选API：

dart

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)
```
、
```
notBetween
```
集合判断：
```
t.column.inSet(set)
```
、
```
notInSet
```
字符串匹配：
```
t.column.like('A%')
```
（区分大小写）、
```
ilike
```
（不区分大小写）；
```
%
```
匹配任意字符，
```
_
```
匹配单个字符
条件组合：
```
&
```
（且）、
```
|
```
（或）、
```
~
```
（非）；使用括号调整优先级
关联查询（一对一）：
```
t.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
```
），仅支持PostgreSQL。

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

排序：

orderBy: (t) => t.column

（默认升序）；使用

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

实现降序

多字段排序：

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

基于关联字段排序：

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

；基于数量排序：

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

分页：基于偏移量的分页使用
```
limit
```
+
```
offset
```
；基于游标分页：结合
```
where: (t) => t.id > lastId
```
、
```
orderBy: (t) => t.id
```
和
```
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).

使用

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()),
    ),
  ));

带有对象关联的模型还提供专用的附加/分离方法：

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);

被附加/分离的对象必须设置

id

（通常是之前从数据库中获取的）。

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.

dart

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

事务内的所有数据库操作都需使用

tx

。

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.

需要在事务中使用。在

find

findFirstRow

findById

中传入

lockMode

和

transaction

：

LockMode.forUpdate

（排他锁）、

forNoKeyUpdate

、

forShare

、

forKeyShare

```
LockBehavior.wait
```
（默认，等待）、
```
noWait
```
（抛出异常）、
```
skipLocked
```
（跳过行，适用于任务队列）

仅锁行不读取：

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

在SQLite中，尝试锁行会无操作，因为它一次仅支持一个写入事务。

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.

全局设置Postgres参数：在Serverpod初始化时使用

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

。事务级设置：

await tx.setRuntimeParameters(...)

。可用于设置搜索路径、向量索引选项或自定义

MapRuntimeParameters

。由于连接池的原因，无法在会话级别设置。

Raw SQL

原生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.

如需运行原生SQL查询，可使用以下方法之一：

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);

标准CRUD操作优先使用ORM，处理用户输入时请使用参数化查询，而非字符串拼接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.

当至少一个模型设置

database: client

或

database: all

时，生成的

Client

类会包含

createSession

方法，返回用于SQLite数据库文件的

ClientDatabaseSession

。在Flutter中，按以下方式打开数据库：

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);
}

注意，需在客户端包中添加

serverpod_database

作为依赖。