Views
Views are a great solution for abstraction. PostGraphile supports reading from and writing to views; however PostgreSQL lacks the powerful introspection capabilities on views that it has on tables, so we cannot easily automatically infer the primary key, relations, etc.
视图(View)是实现数据抽象的绝佳手段。PostGraphile 同时支持对视图进行读取和写入操作;不过,PostgreSQL 针对视图提供的 introspection 信息远不如表那么完备,因此 PostGraphile 无法自动推断出视图的主键、relation 等元数据。
It is recommended that you use "smart tags" to add "virtual" constraints to
views. This will make them behave a lot more
table-like - you can add a @primaryKey or @foreignKey constraints between
views and other views/tables.
建议你使用 smart tags 为视图添加“虚拟”约束。这样一来,视图的行为会更加贴近普通表——你可以利用 @primaryKey 或 @foreignKey 在视图之间、以及视图与表之间建立约束关系。
PostgreSQL introspection misses quite a lot of detail on views compared to tables:
与表相比,PostgreSQL 在视图上的 introspection 会缺失相当多细节信息:
-
No type modifiers (e.g. no
varchar(8); onlyvarchar)没有类型修饰符(例如只能识别为
varchar,无法保留varchar(8)的长度限制) -
No
NOT NULLmodifiers (everything is nullable) - fix with@notNull没有
NOT NULL修饰符(所有列默认均为可空)——可通过@notNull修正 -
No contraints:
没有任何约束:
-
No primary key - fix with
@primaryKey col1,col2没有主键——可通过
@primaryKey col1,col2补充 -
No unique constraints - fix with
@unique col1,col2没有唯一约束——可通过
@unique col1,col2补充 -
No foreign keys - fix with
@foreignKey (col1,col2) refeences other_table (other_col_1,other_col_2)没有外键——可通过
@foreignKey (col1,col2) refeences other_table (other_col_1,other_col_2)补充
-
You might think that PostGraphile should read the underlying SQL used to implement your view and infer relations from that; it does not do so due to the complexity this would involve (and the ambiguity of what is "right").
你可能会觉得 PostGraphile 应该直接读取定义视图的底层 SQL,并从中自动推断出各种 relation;但 PostGraphile 并未这样做,因为这不仅实现复杂度极高,而且“怎样的推断才算正确”本身往往也存在歧义。
If you'd like to attempt this, we encourage you to do so by building a plugin — we'd love to see it!
如果你有兴趣尝试这种做法,非常欢迎通过编写插件来实现——我们也很期待看到你的成果!
Abstract Business Logic抽象业务逻辑
We can prepare certain queries in advance and expose the results through
GraphQL. For example, say we want just the Comedy films from our films
table, we can create a view that contains this specific film type.
你可以提前编排好特定查询,再通过 GraphQL 将结果暴露出去。例如,假设我们只希望从 films 表中筛选出 Comedy 类型的电影,就可以创建一个专门包含该类型电影的视图:
create table app_public.films (
id serial primary key,
name text,
release_year int,
kind text
);
create view comedies as
select *
from app_public.films
where kind = 'Comedy';
And query this view as if it were a normal table:
之后便可以像查询普通表一样查询这个视图:
{
comedies(first: 20) {
name
releaseYear
}
}
Flatten joined tables扁平化关联表
Views enable you to expose a simple "flattened" object built from multiple tables.
借助视图,你可以将多张表的数据整合成一个简洁的“扁平化”对象对外暴露。
create table app_public.person (
id serial primary key
);
create table app_public.address (
person_id int primary key references app_public.person,
country text,
street text,
);
create view person_view as
select person.id, address.country, address.street
from app_public.person person
inner join app_public.address
on person.id = address.person_id;
The GraphQL query using this view is flatter than the query using the underlying tables:
使用该视图后,GraphQL 查询结构会比直接查询底层多张表更加扁平:
query Before {
person {
id
address {
country
street
}
}
}
query After {
personView {
id
country
street
}
}
You can use smart tags to change the GraphQL field name.
你可以通过 smart tags 来修改生成的 GraphQL 字段名称。
Selecting record-typed columns选取 record 类型的列
If you select a table row directly in a view, rather than it's columns, PostgreSQL reports that as a column of a composite type. PostGraphile exposes it as a nested object using the same fields and relationships as the underlying table type:
如果在视图中直接选取整行表记录(而不是逐一列出各个列),PostgreSQL 会将其报告为复合类型(composite type)的列。PostGraphile 会将其暴露为嵌套对象,并沿用底层表类型相同的字段与 relation:
create view person_with_address as
select person.id, person.name, person, address
from app_public.person person
inner join app_public.address address
on person.id = address.person_id;
{
personWithAddress {
id
name
person {
id
}
address {
country
}
}
}
This is not recommended since it selects the entire row for each record-typed
column, even if the client only requests a subset of the fields. Instead, expose
just the primary key of the related table and use the @foreignKey smart tag to
create a relation to it.
并不建议这样做,因为即便客户端只请求了其中少数几个字段,数据库也会为每个 record 类型的列查出整行数据。更好的做法是:在视图中只暴露关联表的主键列,再通过 @foreignKey smart tag 为其建立 relation。
Authorization权限控制
Authorization can be enforced using views as well, for example, exposing some
data only to authenticated users:
视图(views)也可以用于实施权限控制,例如仅向已认证用户暴露特定数据:
create table app_public.person (
id serial primary key
);
create table app_public.personal_data (
id serial primary key,
secret1 text,
secret2 int,
person_id references app_public.person (id)
);
create view personal_data_view
with (security_barrier, check_option = 'cascaded')
as
select personal_data.*
from app_public.personal_data personal_data
where person_id = current_user_id()
(current_user_id() here is a function that might return something like
nullif(current_setting('jwt.claims.user_id', true), '')::int)
(这里的 current_user_id() 是一个自定义函数,其返回值类似于 nullif(current_setting('jwt.claims.user_id', true), '')::int。)
API Layer作为 API 抽象层
Using views, one can create an access layer that will remain consistent even while making changes to the underlying tables — for example when splitting tables or combining them. Note that simple name changes can be solved using smart tags without the need for views. Much of what can be achieved with views can also be achieved with functions or plugins, so consider those options too.
借助视图,你可以构建一层稳定的数据访问抽象——即使未来需要对底层表进行拆分或合并,对外暴露的接口也能保持一致。需要注意的是,如果只是简单的重命名需求,直接使用 smart tag 即可解决,无需引入视图。此外,视图能实现的大多数功能同样可以通过数据库函数或插件来完成,在设计方案时不妨一并纳入考量。