Postgres 变更

使用 Realtime 的 Postgres Changes 来监听数据库事件。

🌐 Use Realtime's Postgres Changes to listen to database events.

快速开始 #

🌐 Quick start

在这个例子中，我们将设置一个数据库表，用行级安全来保护它，并使用 Supabase 客户端库订阅所有变化。

🌐 In this example we'll set up a database table, secure it with Row Level Security, and subscribe to all changes using the Supabase client libraries.

1
-- Create a table called "todos"
2
-- with a column to store tasks.
3
create table todos (
4
  id serial primary key,
5
  task text
6
);

1
-- Grant the privileges roles need
2
GRANT SELECT ON public.todos TO anon;
3
4
-- Turn on security
5
alter table "todos"
6
enable row level security;
7
8
-- Allow anonymous access
9
create policy "Allow anonymous access"
10
on todos
11
for select
12
to anon
13
using (true);

1
alter publication supabase_realtime
2
add table your_table_name;

1
npm install @supabase/supabase-js

1
import { createClient } from '@supabase/supabase-js'
2
3
const supabase = createClient(
4
  'https://<project>.supabase.co',
5
  '<sb_publishable_... key>'
6
)

1
import { createClient } from '@supabase/supabase-js'
2
const supabase = createClient('your_project_url', 'your_supabase_api_key')
3
4
// ---cut---
5
const channelA = supabase
6
  .channel('schema-db-changes')
7
  .on(
8
    'postgres_changes',
9
    {
10
      event: '*',
11
      schema: 'public',
12
    },
13
    (payload) => console.log(payload)
14
  )
15
  .subscribe()

1
insert into todos (task)
2
values
3
  ('Change!');

用法 #

🌐 Usage

你可以使用 Supabase 客户端库来订阅数据库的变化。

🌐 You can use the Supabase client libraries to subscribe to database changes.

听特定的模式 #

🌐 Listening to specific schemas

使用 schema 参数订阅特定的 schema 事件：

🌐 Subscribe to specific schema events using the schema parameter:

1
const changes = supabase
2
  .channel('schema-db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      schema: 'public', // Subscribes to the "public" schema in Postgres
7
      event: '*',       // Listen to all changes
8
    },
9
    (payload) => console.log(payload)
10
  )
11
  .subscribe()

通道名称可以是任何字符串，但不能是 'realtime'。

🌐 The channel name can be any string except 'realtime'.

听特定事件 #

🌐 Listening to specific events

使用 event 参数只监听特定的数据库事件。event 可以是 INSERT、UPDATE、DELETE 或 * 来监听所有变化。

🌐 Use the event parameter to listen only to a specific database event. event can be INSERT, UPDATE, DELETE, or * to listen to all changes.

1
const changes = supabase
2
  .channel('schema-db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
    },
9
    (payload) => console.log(payload)
10
  )
11
  .subscribe()

通道名称可以是任何字符串，但不能是 'realtime'。

🌐 The channel name can be any string except 'realtime'.

听特定的表 #

🌐 Listening to specific tables

使用 table 参数订阅特定表事件：

🌐 Subscribe to specific table events using the table parameter:

1
const changes = supabase
2
  .channel('table-db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'todos',
9
    },
10
    (payload) => console.log(payload)
11
  )
12
  .subscribe()

通道名称可以是任何字符串，但不能是 'realtime'。

🌐 The channel name can be any string except 'realtime'.

听多个变化 #

🌐 Listening to multiple changes

使用相同的通道监听不同事件和模式/表/筛选器组合：

🌐 To listen to different events and schema/tables/filters combinations with the same channel:

1
const channel = supabase
2
  .channel('db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'messages',
9
    },
10
    (payload) => console.log(payload)
11
  )
12
  .on(
13
    'postgres_changes',
14
    {
15
      event: 'INSERT',
16
      schema: 'public',
17
      table: 'users',
18
    },
19
    (payload) => console.log(payload)
20
  )
21
  .subscribe()

筛选特定的更改 #

🌐 Filtering for specific changes

使用 filter 参数进行细微调整：

🌐 Use the filter parameter for granular changes:

1
const changes = supabase
2
  .channel('table-filter-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'todos',
9
      filter: 'id=eq.1',
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

可用过滤器 #

🌐 Available filters

Realtime 提供了过滤器，让你可以更细致地指定客户收到的数据。过滤器是一个 column=operator.value 表达式（例如 id=eq.1 或 title=like.%foo%），Realtime 会在服务器上进行评估，所以被过滤掉的事件永远不会离开数据库。

🌐 Realtime offers filters so you can specify the data your client receives at a more granular level. A filter is a column=operator.value expression (for example id=eq.1 or title=like.%foo%) that Realtime evaluates on the server, so filtered-out events never leave the database.

以下运算符可用：

🌐 The following operators are available:

你也可以用 not. 来否定任何运算符，并用逗号组合多个条件（作为 AND 应用）。

🌐 You can also negate any operator with not. and combine multiple conditions with commas (applied as an AND).

1
import { postgresChangesFilter } from '@supabase/supabase-js'
2
3
// → 'quantity=gte.10,status=eq.open'
4
const filter = postgresChangesFilter().gte('quantity', 10).eq('status', 'open')

等于 (eq#

🌐 Equal to (eq)

当表中某列的值等于客户端指定的值时，监听其变化：

🌐 To listen to changes when a column's value in a table equals a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'messages',
9
      filter: postgresChangesFilter().eq('body', 'hey'),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用了 Postgres 的 = 过滤器。

🌐 This filter uses Postgres's = filter.

不等于 (neq#

🌐 Not equal to (neq)

当表中某列的值不等于客户端指定的值时监听变化：

🌐 To listen to changes when a column's value in a table does not equal a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'messages',
9
      filter: postgresChangesFilter().neq('body', 'bye'),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用了 Postgres 的 != 过滤器。

🌐 This filter uses Postgres's != filter.

少于 (lt#

🌐 Less than (lt)

当表中某列的值小于客户端指定的值时，监听变化：

🌐 To listen to changes when a column's value in a table is less than a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'profiles',
9
      filter: postgresChangesFilter().lt('age', 65),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用了 Postgres 的 < 过滤器，所以它适用于非数字类型。记得检查一下被比较数据类型的预期行为。

🌐 This filter uses Postgres's < filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

小于或等于 (lte#

🌐 Less than or equal to (lte)

当表中某列的值小于或等于客户端指定的值时，监听变化：

🌐 To listen to changes when a column's value in a table is less than or equal to a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'profiles',
9
      filter: postgresChangesFilter().lte('age', 65),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用了 Postgres 的 <= 过滤器，所以它适用于非数字类型。记得检查一下被比较数据类型的预期行为。

🌐 This filter uses Postgres' <= filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

大于 (gt#

🌐 Greater than (gt)

当表中某列的值大于客户端指定的值时，监听变化：

🌐 To listen to changes when a column's value in a table is greater than a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'products',
9
      filter: postgresChangesFilter().gt('quantity', 10),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用了 Postgres 的 > 过滤器，所以它适用于非数字类型。记得检查一下被比较数据类型的预期行为。

🌐 This filter uses Postgres's > filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

大于或等于（gte#

🌐 Greater than or equal to (gte)

当表中某列的值大于或等于客户端指定的值时，监听变化：

🌐 To listen to changes when a column's value in a table is greater than or equal to a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'products',
9
      filter: postgresChangesFilter().gte('quantity', 10),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用了 Postgres 的 >= 过滤器，所以它适用于非数字类型。记得检查一下被比较数据类型的预期行为。

🌐 This filter uses Postgres's >= filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

包含在列表中 #

🌐 Contained in list (in)

当表中某列的值等于任意客户端指定的值时，监听变化：

🌐 To listen to changes when a column's value in a table equals any client-specified values:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'colors',
9
      filter: postgresChangesFilter().in('name', ['red', 'blue', 'yellow']),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

这个过滤器使用 Postgres 的 = ANY。实时模式下，这个过滤器最多允许 100 个值。

🌐 This filter uses Postgres's = ANY. Realtime allows a maximum of 100 values for this filter.

模式匹配（like、ilike#

🌐 Pattern matching (like, ilike)

当文本列匹配某个模式时，要监听变化，可以使用 like（区分大小写）或 ilike（不区分大小写）。使用 % 可以匹配任意字符序列，使用 _ 可以匹配单个字符。

🌐 To listen to changes when a text column matches a pattern, use like (case-sensitive) or ilike (case-insensitive). Use % to match any sequence of characters and _ to match a single character.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'articles',
9
      // matches "Breaking News", "BREAKING", ...
10
      filter: postgresChangesFilter().ilike('title', '%breaking%'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

like 使用 Postgres 的 LIKE，而 ilike 使用 ILIKE。两者都需要一个文本兼容的列。上面的例子使用了 ilike；如果要进行区分大小写的匹配，可以换成 like——其他用法完全相同。

正则表达式匹配（match、imatch#

🌐 Regular expression matching (match, imatch)

要监听文本列匹配 POSIX 正则表达式时的变化，使用 match（区分大小写）或 imatch（不区分大小写）。

🌐 To listen to changes when a text column matches a POSIX regular expression, use match (case-sensitive) or imatch (case-insensitive).

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'posts',
9
      // matches "post-1", "post-42", ...
10
      filter: postgresChangesFilter().match('slug', '^post-\\d+$'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

match 使用 Postgres 的 ~ 操作符，而 imatch 使用 ~*。两者都需要兼容文本的列，并且在你订阅时会验证模式。上面的示例使用了 match；如果想不区分大小写匹配，可以换成 imatch——其他用法完全一样。

空值和布尔检查（is#

🌐 Null and boolean checks (is)

要监听当列 IS、null、true、false 或 unknown 发生变化时的情况，可以使用 is。is.null 适用于任何列类型；而 is.true、is.false 和 is.unknown 则需要布尔类型的列。

🌐 To listen to changes when a column IS null, true, false, or unknown, use is. is.null works on any column type; is.true, is.false, and is.unknown require a boolean column.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'todos',
9
      // only rows that are not yet completed
10
      filter: postgresChangesFilter().is('completed_at', null),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

这个过滤器使用了 Postgres 的 IS 操作符。

🌐 This filter uses Postgres's IS operator.

与 (isdistinct#

🌐 Distinct from (isdistinct)

isdistinct 是一个 NULL 安全的不等式 (IS DISTINCT FROM)。与 neq 不同，它将 null 视为可比较的值，所以 null 列会被认为与非空值不同。

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'orders',
9
      // includes rows where status is null
10
      filter: postgresChangesFilter().isDistinct('status', 'shipped'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

否定一个过滤器（not#

🌐 Negating a filter (not)

在任何运算符前加上 not. 来将其取反——例如 not.in、not.is 或 not.like。

🌐 Prefix any operator with not. to invert it — for example not.in, not.is, or not.like.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'posts',
9
      // anything except drafts and archived
10
      filter: postgresChangesFilter().not('status', 'in', ['draft', 'archived']),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

将过滤器与 AND#

🌐 Combining filters with AND

将多个条件用逗号分开组合。所有条件都必须匹配（逻辑 AND）。你只能使用 AND 来组合条件 — OR 不支持。

🌐 Combine multiple conditions by separating them with commas. All conditions must match (logical AND). You can only combine conditions with AND — OR is not supported.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'orders',
9
      // amount > 100 AND status = "open"
10
      filter: postgresChangesFilter().gt('amount', 100).eq('status', 'open'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

选择特定列 #

🌐 Selecting specific columns

默认情况下，每个变更事件都包含整行数据。使用 select 可以只接收部分列。这样可以减少每个事件的负载大小和传输的数据量，对于拥有大量 bytea、jsonb 或 text 列的表格尤其有用。

🌐 By default each change event contains the full row. Use select to receive only a subset of columns instead. This reduces payload size and the data transferred per event, which is especially useful for tables with large bytea, jsonb, or text columns.

列出的列必须可以被订阅的角色选择，并且表的主键总是包含在内，这样你就可以识别行。select 需要明确的 schema 和 table ——通配符订阅不支持它。

🌐 The listed columns must be selectable by the subscribing role, and the table's primary key is always included so you can identify the row. select requires an explicit schema and table — it's not supported on wildcard subscriptions.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'profiles',
9
      select: ['id', 'username'], // payload.new only contains { id, username }
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

正在接收 old#

🌐 Receiving old records

默认情况下，只有 new 记录的更改会被发送，但如果你想在每次 UPDATE 或 DELETE 一条记录时都收到 old 记录（之前的值），你可以将表的 replica identity 设置为 full：

🌐 By default, only new record changes are sent but if you want to receive the old record (previous values) whenever you UPDATE or DELETE a record, you can set the replica identity of your table to full:

1
alter table
2
  messages replica identity full;

私有模式 #

🌐 Private schemas

Postgres Changes 能开箱即用地处理 public 模式下的表。你可以通过将表 SELECT 的权限授予你访问令牌中的数据库角色来监听你私有模式下的表。你可以运行类似下面的查询：

🌐 Postgres Changes works out of the box for tables in the public schema. You can listen to tables in your private schemas by granting table SELECT permissions to the database role found in your access token. You can run a query similar to the following:

1
grant select on "non_private_schema"."some_table" to authenticated;

自定义令牌 #

🌐 Custom tokens

你可以选择为自己的令牌签名，以自定义可以在 RLS 策略中检查的声明。

🌐 You may choose to sign your own tokens to customize claims that can be checked in your RLS policies.

你的项目 JWT 密钥可以在仪表板的 设置 > API 密钥 部分找到。

🌐 Your project JWT secret is found in the Settings > API keys section of the Dashboard.

要在 Realtime 中使用你自己的 JWT，确保在实例化 Supabase 客户端之后、连接通道之前设置好 token。

🌐 To use your own JWT with Realtime make sure to set the token after instantiating the Supabase client and before connecting to a Channel.

1
const { createClient } = require('@supabase/supabase-js')
2
3
const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_KEY, {})
4
5
// Set your custom JWT here
6
supabase.realtime.setAuth('your-custom-jwt')
7
8
const channel = supabase
9
  .channel('db-changes')
10
  .on(
11
    'postgres_changes',
12
    {
13
      event: '*',
14
      schema: 'public',
15
      table: 'messages',
16
      filter: 'body=eq.bye',
17
    },
18
    (payload) => console.log(payload)
19
  )
20
  .subscribe()

限制 #

🌐 Limitations

删除事件 #

🌐 Delete events

只有在表设置了 replica identity 为 full 时，才能在跟踪 Postgres 变更时筛选删除事件。请参见 接收旧记录。

🌐 You can only filter Delete events when tracking Postgres Changes if the table has the replica identity set to full. See Receiving old records.

扩展 Postgres 变更 #

🌐 Scaling Postgres Changes

Postgres Changes 会对每个订阅者验证每个事件。当你对一个有 100 个订阅用户的表做一次修改时，Realtime 会进行 100 次授权检查——每个用户一次——所以吞吐量会随着订阅者数量而增长，而不是写入频率。Changes 也在单线程上处理，以保持顺序，这意味着更大的计算插件并不会显著提升 Postgres Changes 的吞吐量。

🌐 Postgres Changes authorizes every event against each subscriber. When you make a single change to a table with 100 subscribed users, Realtime performs 100 authorization checks — one per user — so throughput scales with the number of subscribers, not the write rate. Changes are also processed on a single thread to preserve their order, which means larger compute add-ons don't meaningfully increase Postgres Changes throughput.

对于大多数应用来说，这已经足够了。要获得最佳性能：

🌐 For most applications this is plenty. To get the best performance:

• 使用筛选器和列选择只发送每个客户所需的事件和列。
• 通过编写、索引RLS策略来保持授权成本低。

使用下面的估算器来评估你的实例的最大吞吐量，并自行运行基准测试以确认它是否适合你的使用场景：

🌐 Use the estimator below to gauge the maximum throughput for your instance, and run your own benchmarks to confirm it fits your use case:

如果你不确定哪种方法适合你的使用场景，可以通过支持表单联系我们 — 我们的工程师很乐意帮你找到最佳解决方案。

🌐 If you're unsure which approach fits your use case, reach out through the Support Form — our engineers are happy to help you find the best solution.