PostGraphile Best PracticesPostGraphile 最佳实践
This guide is a work in progress. If you have ideas for best practices, please use the “Suggest improvements to this page” link to submit them, or discuss them in #documentation on the Graphile Discord chat.
本指南仍在持续完善中。如果你有值得分享的最佳实践建议,可以通过“Suggest improvements to this page”链接提交改进,也可以在 Graphile Discord 聊天室 的 #documentation 频道中交流讨论。
Foreign Key Indexes为外键添加索引
PostgreSQL does NOT add indexes to foreign keys by default. This isn’t an issue for the forward relation (getting the record that your record belongs to), but for the reverse relation (getting all the records that belong to your record) it can make the lookup very expensive. Always add indexes to your foreign keys.
PostgreSQL 默认不会为外键自动创建索引。对于正向 relation(根据当前记录查找其所属记录)而言这通常不是问题,但对于反向 relation(查找归属于当前记录的所有关联记录),缺少索引会导致查询开销急剧上升。因此,务必为所有外键字段建立索引。
create table users (id serial primary key);
create table things (id serial primary key, user_id int not null references users);
create index on things (user_id);
Out of the box, if you don’t do this then the “reverse relation” will not
appear in your GraphQL schema. You can force it to appear by giving the
foreign key constraint the +select behavior, or you can disable this behavior
by adding disablePlugins: ['PgIndexBehaviorsPlugin'] to your configuration.
默认情况下,如果不为外键建索引,对应的“反向 relation”就不会出现在 GraphQL schema 中。你可以为该外键约束添加 +select behavior 来强制生成它,也可以在配置中添加 disablePlugins: ['PgIndexBehaviorsPlugin'] 来禁用这一限制。
Row Level Security行级安全策略(RLS)
If you’re using RLS, it’s best to enable it on every table in your database.
You should at least enable it on every table in your exposed schemas. It’s
better to enable RLS and create a policy with using (true) to say “anything
goes” than to not enable RLS; this helps your team mates understand intent:
when you enable RLS you’re being explicit about what access is allowed, whereas
if you don’t you’re just implicitly allowing all access, which could have been
an oversight.
如果项目中使用了 RLS,建议在数据库的每张表上都开启它——至少也要为所有对外暴露的 schema 中的每张表开启 RLS。即使某张表允许“全量放行”,也建议开启 RLS 并创建一条 using (true) 策略,而不是干脆不启用 RLS。这样能向团队成员明确传达设计意图:开启 RLS 并配置放行策略属于显式授权,而不开启则只是隐式放行所有访问,很容易让人误以为是一时疏漏。
Use Table GRANT for SELECT/DELETE and Column GRANT for INSERT/UPDATE对 SELECT/DELETE 使用表级 GRANT,对 INSERT/UPDATE 使用列级 GRANT
The following are fine:
推荐以下写法:
-- ✅ Fine
grant select on users to graphql_role;
-- ✅ Fine
grant insert (column_1, column_2, ...) on users to graphql_role;
-- ✅ Fine
grant update (column_a, column_b, ...) on users to graphql_role;
-- ✅ Fine
grant delete on users to graphql_role;
The following should be avoided:
应避免以下写法:
-- 🛑 DO NOT USE
grant select (column_a, column_b) on users to graphql_role;
-- 🛑 DO NOT USE
grant insert on users to graphql_role;
-- 🛑 DO NOT USE
grant update on users to graphql_role;
Column-level SELECT grants cause a lot of issues not just for PostGraphile:
列级 SELECT 授权不仅会给 PostGraphile 带来诸多问题,在纯 PostgreSQL 层面同样如此:
-
Cannot
select * from无法执行
select * from -
Cannot use
returning *on mutations无法在 mutation 中使用
returning * -
Cannot pass a row type into a function (like we do for computed columns)
无法将行类型(row type)作为参数传入函数(例如计算列常用的做法)
Table-level INSERT/UPDATE grants are not advisable because they lack the explicitness that should come from such operations.
表级的 INSERT/UPDATE 授权同样不推荐使用,因为写入操作应当具备更明确的字段级权限控制。
Simplify Your GraphQL Field Names简化 GraphQL 字段命名
You can get a leg up on this
using @graphile/simplify-inflection.
The long names PostGraphile uses by default are to try and avoid people getting
naming conflicts when they run PostGraphile for the first time. Once you’re more
comfortable you should move to using shorter names as it’s a GraphQL best
practice.
你可以借助 @graphile/simplify-inflection 快速上手字段名简化。PostGraphile 默认生成的长名称主要是为了防止初次接入时发生命名冲突;当你熟悉之后,建议切换为更精简的命名风格,这也是 GraphQL 的最佳实践。
Protect Your API保护你的 API
请参阅生产环境注意事项。
Use language sql Over language plpgsql Where Possible优先使用 language sql 而非 language plpgsql
Performance reasons. Specifically, under many circumstances, functions in SQL
(but not plpgsql) can be inlined into the call-site and are thereby
transparent to the query planner. The PostgreSQL docs
have a lot more details.
主要出于性能考虑。具体而言,在许多场景下,SQL 语言编写的函数(plpgsql 则不行)能够被内联到调用处,从而对查询规划器完全透明。更多细节可参阅 PostgreSQL 官方 Wiki 说明。
Name Triggers With A Numeric Prefix使用数字前缀命名触发器
e.g. _200_do_a_thing / _800_do_something_else
Reason: triggers in PostgreSQL run in lexicographical order.
原因在于:PostgreSQL 中的触发器是按名称的字典序依次执行的。