Required Knowledge
WORK IN PROGRESS: PR's welcome!
仍在完善中:欢迎提交 PR!
PostGraphile builds upon other technologies such as PostgreSQL and GraphQL, and the PostGraphile documentation doesn't cover these topics in depth as they are well documented elsewhere.
PostGraphile 构建于 PostgreSQL 和 GraphQL 等技术之上。由于这些技术本身已有完善的官方文档,PostGraphile 文档不再对其展开深入介绍。
This page aims to outline some of the things you might want to do with PostGraphile and what knowledge you may need (and where to get it) to do those things well.
本页旨在梳理使用 PostGraphile 时的常见开发任务,以及要出色完成这些任务所需掌握的相关知识(及获取资料的途径)。
Database数据库
Below outlines some of the SQL syntax and features you will probably want to be familiar with to make the most of your PostGraphile usage. PostGraphile is unopinionated in how you manage your database, so you may not need to be able to write this syntax depending on what technologies you use to manage your DB (for example, you might use a graphical tool), but if you can read it then it will help you to read the PostGraphile documentation.
下文列出了充分发挥 PostGraphile 能力时通常需要熟悉的 SQL 语法与特性。PostGraphile 对数据库管理方式不作强制限定——如果你使用图形化工具等其他方式管理数据库,未必需要亲手编写这些语句;不过,能够看懂这些 SQL 语法将大大有助于你阅读 PostGraphile 文档。
Case insensitive unless quoted未加引号时默认不区分大小写
SQL is case insensitive, both keywords and identifiers, so the following are all equivalent:
SQL 对关键字和标识符均不区分大小写,因此以下几种写法完全等价:
select * from usersSELECT * FROM USERSSeLeCt * FrOm UsErS
This means a column like userId will actually come out as userid. To make an
identifier (table/column/function/etc name) case sensitive, wrap it in double
quotes:
这意味着像 userId 这样的列名在数据库中实际会被解析为 userid。若要让标识符(表名、列名、函数名等)区分大小写,需使用双引号将其括起:
select * from "Users"
Benjie tends to prefer the lowercase syntax because it's easier to read and not as "shouty".
Benjie 更偏爱全小写写法,因为这样更易于阅读,也不像全大写那样显得“刺眼”。
It's common to use snake_case as the naming convention in SQL because of this case-insensitivity, to save having to escape all the identifiers.
正是由于这种大小写不敏感的特性,SQL 中通常会采用 snake_case(蛇形命名法),从而省去为所有标识符加引号转义的麻烦。
The word user is reserved, among othersuser 等词属于保留关键字
If you want to call your table user you need to escape it; e.g.
如果想将表命名为 user,必须对其进行引号转义,例如:
create table "user" ( ... );
Here's a full list of reserved keywords: https://www.postgresql.org/docs/current/sql-keywords-appendix.html
Most PostgreSQL keywords are singular, which is why Benjie prefers to use plurals when naming tables as it helps avoid potential keyword clashes.
大多数 PostgreSQL 关键字都是单数形式,这也是 Benjie 习惯用复数形式命名表的原因——有助于避免与关键字发生潜在冲突。
PostgreSQL doc versioningPostgreSQL 文档版本说明
PostgreSQL docs are found at https://postgresql.org/docs/VERSION/.... Often
when you google you will be taken to out of date docs, like those for version
9.1 or 8.3 or similar. At the top of every documentation page are links to jump
to the same page in the different PostgreSQL versions. Always make sure you are
referring to the docs in the version of PostgreSQL that you are using.
PostgreSQL 官方文档的地址格式为 https://postgresql.org/docs/VERSION/...。通过搜索引擎检索时,经常会跳转至 9.1 或 8.3 等过时版本的文档页面。每个文档页面的顶部都提供了指向不同 PostgreSQL 版本对应页面的跳转链接,请务必确保查阅的文档与你实际使用的 PostgreSQL 版本一致。
In our documentation, we always try and link to /docs/current/... which is a
special URL that always shows the latest stable version of PostgreSQL. If you
are using an older version you should make sure to switch to the older docs.
在本站文档中,我们会尽量链接到 /docs/current/...,这是一个特殊路径,始终指向最新稳定版的 PostgreSQL 文档。如果你使用的是较旧版本,请记得切换查看对应旧版本的文档。
Defining tables定义数据表
create table users (
id serial primary key,
username citext not null unique check(username ~ '^[a-z][a-z0-9]+$'),
first_name text,
last_name text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
-
create tablestatement: https://www.postgresql.org/docs/current/sql-createtable.htmlcreate table语句:https://www.postgresql.org/docs/current/sql-createtable.html -
serialdata type is effectively an "auto-increment" field (for primary key): https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIALserial数据类型实质上就是(用于主键的)“自增”字段:https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL -
uniqueis a shorthand for creating a unique constraint on the column: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS -
checkis a shorthand for creating a check constraint on the table: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS -
~is the case-sensitive POSIX regexp comparison operator: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP~是区分大小写的 POSIX 正则表达式匹配运算符:https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP -
timestamptzis short fortimestamp with time zonewhich reads the offset you provide and converts to UTC for storage. Note the timezone you save is LOST (despite the name of the type) so if you wish to store the timezone offset itself you should do so in a separate column. You should always use this date type: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29timestamptz是timestamp with time zone的缩写,它会读取你传入的时区偏移量并将其转换为 UTC 存储。需要注意(尽管类型名称中带有 with time zone),你传入的原始时区信息并不会被保留下来;如果确实需要保存时区偏移量本身,请单独建列存储。建议始终使用这种日期时间类型:https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29 -
defaultif the column is not specified, what should its value default to (only applies toINSERT)default:当未显式指定该列时应使用的默认值(仅在INSERT时生效)
Rather than using the serial type (as in id serial primary key), it is
typically better to use the int type directly and add a column generation
expression such as generated always as identity (as in
id int primary key generated always as identity). You may find a mixture of
these two techniques in this documentation, mostly because serial is older
and faster to type.
相比使用 serial 类型(如 id serial primary key),通常更推荐直接使用 int 类型并添加 generated always as identity 等列生成表达式(如 id int primary key generated always as identity)。本文档中可能会混用这两种写法,主要是因为 serial 出现得更早且书写更简短。
Creating roles创建角色
create role my_superuser with login password 'password here' superuser;
create role app_authenticator with login password 'password here' noinherit;
create role app_visitor;
grant app_visitor to app_authenticator;
-
create rolestatement: https://www.postgresql.org/docs/current/sql-createrole.htmlcreate role语句:https://www.postgresql.org/docs/current/sql-createrole.html -
login- enables loginlogin:允许该角色登录 -
password '...'- sets the password (note passwords are encrypted by default)password '...':设置密码(注意密码默认会经过加密处理) -
noinherit- if you are a role with noinherit then you do not inherit the permissions from the roles that you have been granted, though you may still switch into them (viaset [local] role other_role;) to use those privileges. This is useful for the PostGraphile user because it means that it cannot actually take any actions without first switching into another role (e.g.app_visitororapp_admin).noinherit:若角色带有noinherit属性,则不会自动继承其被授予的其他角色的权限,但仍可通过set [local] role other_role;切换至目标角色以使用相应权限。这对 PostGraphile 使用的数据库账号非常有用,因为它能确保连接角色在未先切换至其他角色(例如app_visitor或app_admin)之前无法执行任何操作。
Granting permissions授予权限
grant
select,
insert (column_1, column_2),
update (column_2, column_3),
delete
on my_table to app_visitor;
-
select: we don't recommend using column-level select grants (because it preventsselect *from working, and has knock-on consequences for mutations and functions)select:不建议使用列级select授权(因为这会导致select *无法执行,并对 mutation 和函数产生连锁影响) -
insert (col1, col2)/update (col1, col2): we recommend that you always whitelist the writable columns; do notgrant insert on my_tableas that will allow inserting into any column including primary key, timestamps, etc. Combine this with PostGraphile's--no-ignore-rbacto have a more carefully honed GraphQL schema.insert (col1, col2)/update (col1, col2):建议始终以白名单形式显式指定允许写入的列;不要直接执行grant insert on my_table,否则将允许向主键、时间戳等任意列插入数据。结合 PostGraphile 的--no-ignore-rbac选项,可以打造权限控制更精细的 GraphQL schema。 -
delete: column-level grants don't make sense on delete.delete:删除操作不存在列级授权的意义。
Creating security policies创建安全策略
Check out our Row-Level security infosheet here: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf
可在此查看我们整理的行级安全(RLS)速查表:https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf
-- Without this line, even if you define security policies, they will not be enforced
alter table my_table enable row level security;
create policy manage_own on my_table for all using ( author_id = current_user_id() );
create policy must_be_verified on my_table as restrictive for insert using ( current_user_is_verified() );
create policy admin_delete on my_table for delete using ( current_user_is_admin() );
create policy friend_insert on my_other_table for insert with check ( are_friends(author_id, user_id) );
-
create policystatement: https://www.postgresql.org/docs/current/sql-createpolicy.htmlcreate policy语句:https://www.postgresql.org/docs/current/sql-createpolicy.html -
as restrictive: by default policies are 'permissive' and only one must pass; restrictive policies require at least one permissive policy to pass and all restrictive policies to passas restrictive:策略默认属于“宽松型”(permissive),只需满足其中任意一条即可通过;而“限制型”(restrictive)策略要求至少有一条宽松型策略通过,并且所有限制型策略都必须全部通过 -
for all/for select/for insert/for update/for delete: what operations does this policy apply to (note: there's noFOR INSERT AND UPDATE- you either have to useALLor define multiple policies, one per operation)for all/for select/for insert/for update/for delete:指定该策略适用的操作类型(注意:PostgreSQL 中没有FOR INSERT AND UPDATE语法,要么使用ALL,要么为每种操作分别创建一条策略) -
using- check to apply against PRE-EXISTING ROW to see if you can do the action (doesn't make sense forINSERT)using:针对已有行执行的校验条件,用于判断是否允许执行该操作(对INSERT无意义) -
with check- check to apply against NEWLY-UPDATED ROW to see if you can do that action (doesn't make sense forSELECTorDELETE)with check:针对新写入或更新后的行执行的校验条件,用于判断是否允许完成该操作(对SELECT或DELETE无意义) -
NOTE: if
with checkis not specified then theusingclause will also be used forwith checkautomatically.注意:若未显式指定
with check,系统会自动将using子句同时用作with check条件。
Writing performant RLS policies编写高性能的 RLS 策略
One of the common mistakes that we see people make when taking their application programming experience and applying it to the database is thinking of functions as zero-cost. RLS policies are used to filter the rows that a user can see, and they run before some of the filters that you apply (for security reasons), so if your RLS policy passes row data to a function then that function might need to be called for every row in your table. This can be incredibly expensive, but is generally easy to solve by shifting your perspective.
许多开发者把应用层的编程习惯直接带到数据库中,常见误区之一就是以为调用函数几乎没有开销。RLS 策略用于过滤用户可见的数据行,并且出于安全原因,它会在你显式添加的部分查询过滤条件之前执行;因此,如果 RLS 策略将行数据传入函数,数据库就可能需要对表中的每一行都调用一次该函数。这种做法的开销极高,但只要换一种思路往往就能轻松解决。
For good performance in the database you want to ensure that the indexes/etc can be used, so you need to write your query in a way that can be easily optimized. Rather than asking "is the user a member of this organization" for each row, you can ask to "only include rows from the set of organizations the user is a member of". This sounds like the same question, but in database optimization terms it's a big shift.
为了在数据库中获得良好性能,你需要确保索引等机制能够被充分利用,这就要求查询语句本身易于被优化器优化。与其针对每一行去判断“当前用户是否属于该组织”,不如改写为“仅包含属于当前用户所在组织集合的数据行”。两种问法在逻辑上听起来差不多,但在数据库查询优化层面却有着天壤之别。
Here's an example of what not to do:
以下是一个反面示例(请勿使用):
-- Inefficient function, do not use!
create function current_user_is_member_of_organization(org_id int)
returns bool as $$
select exists(
select 1
from organization_members om
where om.organization_id = current_user_is_member_of_organization.org_id
and om.user_id = current_user_id()
);
$$ language sql stable;
-- Poorly performing RLS policy that passes row data to a function, do not use!
create policy select_my_organizations
for select on posts
using (current_user_is_member_of_organization(organization_id));
And here's the question rephrased in such a way that the database only needs to call a function once in total rather than once per row, and can then use indexes to filter the records:
下面是改写后的等价表达:数据库总共只需调用一次函数,而无需逐行调用,随后即可直接利用索引来过滤记录:
create function current_user_organization_ids()
returns setof int as $$
select organization_id
from organization_members om
where om.user_id = current_user_id()
$$ language sql stable;
create policy select_my_organizations
for select on posts
using (organization_id IN (select current_user_organization_ids()));
Recommended path推荐学习路径
A good way to tackle and make sense of PostGraphile is to go through these steps:
循序渐进掌握 PostGraphile 的推荐路径如下:
Describes why you would want to go with this approach in the first place and
what the benefits and features of using PostGraphile are. Do not be deterred if
you find this daunting and are not used to using this much SQL. Using the
database as the application layer is a fairly modern and unfamiliar concept, but
by adopting it there are many benefits you may reap by being close to the data,
as well as leveraging the declarative nature of SQL.
介绍采用这种架构方案的初衷,以及使用 PostGraphile 带来的优势与特性。如果你平时很少编写这么多 SQL,刚开始觉得门槛较高也无需退缩。将数据库直接作为应用层虽然是较新的理念、初看有些陌生,但贴近数据本身并充分发挥 SQL 的声明式特性,能为你带来诸多收益。
Walks you through installing the required software such as node and postgres
and creating of a database that PostGraphile will be connecting to.
带你完成 node、postgres 等必备软件的安装,并创建供 PostGraphile 连接的数据库。
Whether you have or have not built databases before, this section gives valuable advice about how to design a solid database. Topics include naming conventions, namespacing with schemas, managing access with roles and policies, handling migrations and much more. If these words are unfamiliar to you, do not worry, it will be clear once you have built a database following this guide.
无论你此前是否有过数据库设计经验,这一节都能为你打造稳健的数据库提供实用建议,涵盖命名规范、利用 schema 划分命名空间、通过角色与策略管理权限、数据库迁移处理等诸多主题。如果对这些概念还不熟悉也不用担心,跟着本指南亲手搭建完一个数据库后自然就会豁然开朗。
PostGraphile does not require Docker, and is very at home in a pure Node.js environment, so if you're not big on Docker you can skip this one!
PostGraphile 并不依赖 Docker,在纯 Node.js 环境中同样运行自如。如果你对 Docker 不太感兴趣,可以直接跳过这一篇!
If you develop using Docker, this will run through the building of a GraphQL API for a forum-like website. It goes through installation through to example queries and mutations to issue to PostGraphile.
如果你习惯使用 Docker 进行开发,该指南将带你从零为一个类论坛网站构建 GraphQL API,内容涵盖环境安装直至向 PostGraphile 发送查询(query)与变更(mutation)示例。