Skip to main content

PostgreSQL Tables

PostGraphile automatically adds a number of elements to the generated GraphQL schema based on the tables and columns found in the inspected schema.

PostGraphile 会根据 introspection 扫描到的数据库 schema 中的表和列,自动向生成的 GraphQL schema 中添加一系列元素。

An example of a PostgreSQL table is:

下面是一个 PostgreSQL 数据表定义示例:

create table app_public.users (
id serial primary key,
username citext not null unique,
name text not null,
about text,
organization_id int not null
references app_public.organizations on delete cascade,
is_admin boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

For a table like this, PostGraphile will:

针对这样一张表,PostGraphile 会自动完成以下工作:

TODO: Are the inflectors up to date?

TODO:此处的 inflector 名称是否已更新至最新版本?

  • Create a GraphQL type, User, for the table, named in UpperCamelCase & singularized (inflector: tableType).

    为该表创建对应的 GraphQL 类型 User,采用大驼峰(UpperCamelCase)命名并转为单数形式(inflector:tableType)。

  • Add to related table types:

    向关联表的类型中添加:

  • Add CRUD Mutations to the root Mutation type.

    向根 Mutation 类型添加 CRUD Mutation 字段。

  • Add to the root Query type:

    向根 Query 类型添加:

    • An allUsers connection field with pagination, filtering, and ordering (inflector: allRows).

      支持分页、过滤和排序的 allUsers Connection 字段(inflector:allRows)。

    • A number of userByKey(key: ...) fields (e.g. userById, userByUsername), one for each of the unique constraints on the table (inflector: rowByUniqueKeys).

      若干个 userByKey(key: ...) 查询字段(如 userById、userByUsername),对应表上的每一个唯一约束(inflector:rowByUniqueKeys)。

    • A foo(nodeId: ID!) field to get the row by its nodeId.

      一个 foo(nodeId: ID!) 字段,用于通过 nodeId 获取对应数据行。

type Query implements Node {
allUsers(
first: Int
last: Int
offset: Int
before: Cursor
after: Cursor
orderBy: [UsersOrderBy!] = [PRIMARY_KEY_ASC]
condition: UserCondition
): UsersConnection

userById(id: Int!): User

userByUsername(username: String!): User

user(nodeId: ID!): User
}

* Remember these fields can be simplified by loading the @graphile/simplify-inflection plugin.

* 注意,加载 @graphile/simplify-inflection 插件可以简化这些关联字段的命名。

Permissions权限控制​

If you're using PgRBACPlugin (enabled by default if you're not using makeV4Preset()) then PostGraphile will only expose the tables/columns/fields you have access to. For example if you perform GRANT UPDATE (username, name) ON users TO graphql_visitor; then the updateUser mutations will only accept username and name fields - the other columns will not be present.

如果启用了 PgRBACPlugin(未使用 makeV4Preset() 时默认开启),PostGraphile 只会暴露当前数据库角色拥有访问权限的表、列和字段。例如,若执行了 GRANT UPDATE (username, name) ON users TO graphql_visitor;,则生成的 updateUser mutation 只会接收 username 和 name 字段,其他列不会出现在参数中。

Note that PgRBACPlugin inspects the RBAC (GRANT / REVOKE) privileges in the database and reflects these in your GraphQL schema. As is GraphQL best practices, this still only results in one GraphQL schema (not one per user), so it takes the user account you connect to PostgreSQL with (from your connection string) and walks all the roles that this user can become within the database, and uses the union of all these permissions. You can influence the settings used for this via the pgService.pgSettingsForIntrospection object. Using this plugin is recommended as it results in a much leaner schema that doesn't contain functionality that you can't actually use.

需要注意,PgRBACPlugin 会检查数据库中的 RBAC(GRANT / REVOKE)权限并将其反映到 GraphQL schema 中。遵循 GraphQL 的最佳实践,整个服务仍然只会生成一份统一的 GraphQL schema(而非按用户各生成一份):它会从连接字符串中用于连接 PostgreSQL 的账号出发,遍历该账号在数据库内可切换的所有角色,并取这些角色权限的并集。你可以通过 pgService.pgSettingsForIntrospection 对象调整这一检查过程使用的会话设置。建议保留启用该插件,它能剔除你实际无权使用的功能,使生成的 schema 更加精简。

note注意

We strongly advise against using column-based SELECT grants with PostGraphile. Instead, split your permission concerns into separate tables and join them with one-to-one relations.

我们强烈不建议在 PostGraphile 中使用列级别的 SELECT 授权。相反,请按权限边界拆分成多张独立的数据表,再通过一对一 relation 关联起来。

Unlogged tables非日志表(Unlogged tables)​

If you have an unlogged table (created via CREATE UNLOGGED TABLE), PostGraphile will not add it to the GraphQL schema by default. You can override this by explicitly giving the unlogged table the required behaviors via smart tags or similar.

如果你创建了非日志表(即通过 CREATE UNLOGGED TABLE 创建的表),PostGraphile 默认不会将其添加到 GraphQL schema 中。若需暴露此类表,可以通过 smart tag 等方式显式为其赋予所需的 behavior。