Skip to main content

Custom Mutations

PostGraphile automatically generates CRUD Mutations for you; but it’s rare that these will cover all your needs — and many people just disable them outright. Custom mutations enable you to write exactly the business logic you need with access to all of your data all wrapped up in a PostgreSQL function. You can even bypass the RLS and GRANT checks, should you so choose, by tagging your function as SECURITY DEFINER — but be very careful when you do so!

PostGraphile 会自动为你生成 CRUD Mutation,但它们往往难以满足所有业务需求——事实上,许多开发者会直接将它们全部禁用。自定义 mutation 允许你将所需的业务逻辑完整封装在 PostgreSQL 函数中,并自由访问全部数据。如有需要,你还可以通过为函数加上 SECURITY DEFINER 来绕过 RLS 和 GRANT 权限检查——但使用该选项时务必格外谨慎!

Prefer JavaScript?更倾向于用 JavaScript?

If you want to script your mutation in JavaScript/TypeScript, you can model it directly with extendSchema. This lets you build the exact GraphQL schema you want using ergonomic GraphQL SDL, and lets you control exactly what mutation logic will be executed via Grafast plans.

如果你想使用 JavaScript 或 TypeScript 编写 mutation 逻辑,可以直接通过 extendSchema 来定义。它能让你用直观顺手的 GraphQL SDL 构建精确符合预期的 GraphQL schema,并借助 Grafast 执行计划精细控制 mutation 的具体执行逻辑。

Rules规则​

To create a function that PostGraphile will recognise as a custom mutation, it must obey the following rules:

要想让 PostgreSQL 函数被 PostGraphile 识别为自定义 mutation,它必须满足以下规则:

Functions matching these requirements will be represented in GraphQL in a way that is compatible with the Relay Input Object Mutations Specification. For example the function

满足上述条件的函数,将在 GraphQL 中以兼容 Relay Input Object Mutations Specification 的形式暴露出来。例如下面这个函数:

create function my_function(a int, b int) returns text as $$ … $$ language sql volatile;

could be called from GraphQL like this:

在 GraphQL 中可以这样调用:

mutation {
myFunction(input: { a: 1, b: 2 }) {
text
}
}

Look at the documentation in Ruru/GraphiQL to find the parameters you may use.

可在 Ruru/GraphiQL 的文档面板中查看所有可用的参数。

Example示例​

Here’s an example of a custom mutation, which will generate the GraphQL acceptTeamInvite mutation:

下面这个自定义 mutation 示例会在 GraphQL 中生成 acceptTeamInvite mutation:

create function app_public.accept_team_invite(team_id integer)
returns app_public.team_members
as $$
update app_public.team_members
set accepted_at = now()
where accepted_at is null
and team_members.team_id = accept_team_invite.team_id
and member_id = app_public.current_user_id()
returning *;
$$ language sql volatile strict security definer;

Notes on the above function:

上述函数需要注意以下几点:

  • STRICT is optional, it means that if any of the arguments are null then the mutation will not be called (and will thus return null with no error) - this allows us to mark teamId as a required argument.

    STRICT 为可选项,表示只要任意参数为 null,该函数就不会被执行(而是直接返回 null 且不报错)——借此 PostGraphile 可以将 teamId 标记为必填参数。

  • SECURITY INVOKER is the default, it means the function will run with the security of the person who invoked the function

    SECURITY INVOKER 是默认行为,表示函数将以调用者的权限身份运行

  • SECURITY DEFINER means that the function will run with the security of the person who defined the function, typically the database owner - this means that the function may bypass RLS, RBAC and other security concerns. Be careful when using SECURITY DEFINER - think of it like sudo!

    SECURITY DEFINER 表示函数将以定义者(通常是数据库所有者)的权限身份运行——这意味着函数可能会绕过 RLS、RBAC 及其他安全限制。使用 SECURITY DEFINER 时务必慎重,不妨把它当成 sudo 对待!

  • we use LANGUAGE sql here, but you can use LANGUAGE plpgsql if you need variables or looping or if blocks or similar concerns; or if you want to write in a more familiar language you can use LANGUAGE plv8 (JavaScript, requires extension), or one of the built in LANGUAGE options such as Python, Perl or Tcl

    这里使用的是 LANGUAGE sql,但如果你需要局部变量、循环或条件分支等流程控制,可以改用 LANGUAGE plpgsql;如果想用更熟悉的语言编写,也可以选择 LANGUAGE plv8(JavaScript,需安装相应扩展)或 PostgreSQL 内置支持的其他 LANGUAGE(如 Python、Perl 或 Tcl)

pgStrictFunctionspgStrictFunctions​

If you’d like PostGraphile to treat all function arguments as required (non-null) unless they have a default then you can use the preset.gather.pgStrictFunctions setting:

如果你希望 PostGraphile 将所有未设置默认值的函数参数都视为必填(非空),可以启用 preset.gather.pgStrictFunctions 选项:

graphile.config.mjs
export default {
// ...
gather: {
pgStrictFunctions: true,
},
};

This is similar to marking the function as STRICT but with the subtle difference that arguments with defaults may be specified as NULL without necessitating that the function returns null. With this setting enabled, arguments without default value will be set mandatory while arguments with default value will be optional. For example: create function foo(a int, b int, c int = 0, d int = null)... would give a mutation foo(a: Int!, b: Int!, c: Int, d: Int).

这与将函数标记为 STRICT 类似,但有一个细微差别:带有默认值的参数即使显式传入 NULL,也不会导致整个函数被迫返回 null。启用该配置后,没有默认值的参数会被设为必填,有默认值的参数则为可选。例如:create function foo(a int, b int, c int = 0, d int = null)... 会生成 foo(a: Int!, b: Int!, c: Int, d: Int) 这样的 mutation。

Bulk Insert Example批量插入示例​

Here’s an example of a custom mutation that performs a “bulk insert” — inserting and returning a set of records:

下面是一个执行“批量插入”(一次插入并返回多条记录)的自定义 mutation 示例:

create function app_public.create_documents(num integer, type text, location text)
returns setof app_public.document
as $$
insert into app_public.document (type, location)
select create_documents.type, create_documents.location
from generate_series(1, num) i
returning *;
$$ language sql strict volatile;