Building a Conflict-Safe Notes App with Supabase and Vanilla JavaScript

查看原文 / View Original 编辑 / Edit

Building a Conflict-Safe Notes App with Supabase and Vanilla JavaScript

使用 Supabase 和原生 JavaScript 构建冲突安全的笔记应用

This article shows how one SQL trigger and one extra WHERE clause let a vanilla JavaScript client detect and resolve edit conflicts — with no locking system, no hand-maintained version column, and no custom server. 本文展示了如何通过一个 SQL 触发器和一个额外的 WHERE 子句，让原生 JavaScript 客户端能够检测并解决编辑冲突——无需锁定系统、无需手动维护版本列，也无需自定义服务器。

The example assumes you already know SQL and JavaScript. With that background, Supabase can feel deceptively easy to pick up: it’s Postgres with a JavaScript client bolted on. That’s mostly true, except for one spot where the two layers stop being separate and have to hand off responsibility to each other — what happens when two people edit the same row at the same time. 本示例假设你已经具备 SQL 和 JavaScript 基础。有了这些背景，Supabase 上手会感觉出奇地简单：它本质上就是 Postgres 加上一个 JavaScript 客户端。这大体上没错，但有一个地方例外，即两个层级不再独立，必须相互交接责任——当两个人同时编辑同一行数据时会发生什么。

What you’ll build

你将构建什么

A single-page notes app: a form to add notes, a list of note cards, and inline edit, save, and delete actions. Multiple browser tabs — or multiple people — can have the app open at once, and a Supabase realtime channel keeps them in sync. This article covers one slice of that app: what happens when two clients edit the same note at the same time. The realtime sync and the basic CRUD operations are standard Supabase patterns, so the article moves through them quickly to spend most of its time on the conflict-detection logic described above. 一个单页笔记应用：包含添加笔记的表单、笔记卡片列表，以及行内编辑、保存和删除操作。多个浏览器标签页（或多个人）可以同时打开该应用，Supabase 的实时频道（realtime channel）会保持它们同步。本文涵盖该应用的一个片段：当两个客户端同时编辑同一条笔记时会发生什么。实时同步和基本的 CRUD 操作是标准的 Supabase 模式，因此本文将快速略过这些内容，将大部分篇幅用于上述冲突检测逻辑。

Set up your Supabase project

设置你的 Supabase 项目

Your schema.sql file does more than create a table. Supabase requires two settings that a plain, self-managed Postgres setup doesn’t need, plus one more piece — a trigger — that the rest of this article depends on. 你的 schema.sql 文件不仅仅是创建表。Supabase 需要两个普通自托管 Postgres 设置所不需要的配置，外加一个关键部分——触发器，本文后续内容都依赖于它。

Enable Row Level Security

启用行级安全性 (RLS)

In a typical backend, your server connects to Postgres with credentials that already have table permissions, and that’s the end of it. Supabase flips this: the client ships an anon key straight into the browser, so anyone holding that key can call the API. Row Level Security (RLS) is the gate that decides what the anon key can actually do. 在典型的后端中，服务器使用已具备表权限的凭据连接到 Postgres，这就足够了。Supabase 则反其道而行之：客户端直接将匿名密钥（anon key）发送到浏览器，因此任何持有该密钥的人都可以调用 API。行级安全性 (RLS) 是决定该匿名密钥具体能做什么的关卡。

alter table notes enable row level security;
create policy "public full access (demo only)" on notes for all using (true) with check (true);

With RLS enabled and no policy, Supabase denies all access by default — the opposite of a typical open Postgres setup. The policy above allows everything, which works for learning but is wrong for anything real. 在启用 RLS 但没有策略的情况下，Supabase 默认拒绝所有访问——这与典型的开放式 Postgres 设置正好相反。上述策略允许所有操作，这适合学习，但不适用于任何实际生产环境。

Enable realtime updates

启用实时更新

Creating a table doesn’t automatically stream its changes. You also need to add the table to Supabase’s realtime publication: 创建表并不会自动流式传输其变更。你还需要将该表添加到 Supabase 的实时发布（realtime publication）中：

alter publication supabase_realtime add table notes 
where not exists ( 
  select 1 from pg_publication_tables 
  where pubname = 'supabase_realtime' and tablename = 'notes' 
);

Skip this step, and postgres_changes subscriptions on notes will connect successfully but never fire. This silent failure is worth knowing about before you go looking for bugs elsewhere. 跳过这一步，对 notes 表的 postgres_changes 订阅虽然能成功连接，但永远不会触发。在去别处寻找 Bug 之前，了解这种静默失败非常重要。

Add the updated_at trigger

添加 updated_at 触发器

The rest of this article depends on one more piece: a trigger that stamps every update with a server-generated timestamp. 本文的其余部分还依赖于另一个关键点：一个在每次更新时自动打上服务器生成时间戳的触发器。

create or replace function set_updated_at() returns trigger as $$ 
begin 
  new.updated_at = now(); 
  return new; 
end; 
$$ language plpgsql;

create trigger trg_notes_updated_at 
before update on notes 
for each row execute function set_updated_at();

On every UPDATE to this table, the database overwrites updated_at unconditionally, before it writes the row. The client can send whatever it wants in that field — the trigger ignores it. Hold onto that fact: it’s what makes the conflict detection in this article trustworthy. 在对该表执行每次 UPDATE 操作时，数据库会在写入行之前无条件覆盖 updated_at。客户端可以在该字段中发送任何内容——触发器都会忽略它。请记住这一点：这正是本文冲突检测机制可靠的原因。

Connect the JavaScript client

连接 JavaScript 客户端

On the JavaScript side, setup is one line: 在 JavaScript 端，设置只需一行代码：

const db = supabase.createClient(SUPABASE_URL, SUPABASE_ANON_KEY);

This client is your only connection to Postgres from here on. Every query in the rest of this article is a method chain on db. 此后，该客户端是你连接 Postgres 的唯一途径。本文后续的所有查询都是基于 db 的方法链。

Add the standard CRUD operations fetchNotes, createNote, and deleteNote map onto SQL about as literally as you’d expect. Here’s fetchNotes: 添加标准的 CRUD 操作 fetchNotes、createNote 和 deleteNote，它们与 SQL 的映射关系正如你所预期的那样直观。以下是 fetchNotes：

async function fetchNotes() { 
  const { data, error } = await db 
    .from('notes') 
    .select('*') 
    .order('created_at', { ascending: true }); 
  if (error) throw error; 
  return data; 
}

createNote and deleteNote follow the same pattern: createNote 和 deleteNote 遵循相同的模式：

  • .from('notes').select('*') is SELECT * FROM notes.

  • .insert({...}).select().single() is an INSERT ... RETURNING, unwrapped from an array to a single object because exactly one row comes back.

  • .delete().eq('id', id).select() is a DELETE ... RETURNING; the client uses the returned row to confirm a row actually existed to delete.

  • .from('notes').select('*') 即 SELECT * FROM notes。

  • .insert({...}).select().single() 即 INSERT ... RETURNING，由于返回的正好是一行，因此从数组中解包为一个单一对象。

  • .delete().eq('id', id).select() 即 DELETE ... RETURNING；客户端使用返回的行来确认确实存在一行数据被删除。

None of this logic is Supabase-specific — it’s SQL wearing a JavaScript accent. The next section covers the one operation that needs more than a literal translation: UPDATE. 这些逻辑都不是 Supabase 特有的——它们只是披着 JavaScript 外衣的 SQL。下一节将介绍唯一需要非字面翻译的操作：UPDATE。

Detect conflicts with a compare-and-swap WHERE clause

使用“比较并交换” WHERE 子句检测冲突

Here’s the bug this app is designed around. Two tabs open the same note. Both load it with the same updated_at. Tab B saves first. If Tab A’s save just runs UPDATE notes SET title = ..., content = ... WHERE id = X, it silently clobbers Tab B’s change — no error, no warning, just wrong data. This is the classic “last write wins” trap, and it’s invisible until two people actually collide. 这就是本应用旨在解决的 Bug。两个标签页打开同一条笔记，它们加载时都带有相同的 updated_at。标签页 B 先保存。如果标签页 A 的保存操作只是运行 UPDATE notes SET title = ..., content = ... WHERE id = X，它会静默覆盖标签页 B 的修改——没有错误，没有警告，只有错误的数据。这是经典的“最后写入者胜”陷阱，在两人真正发生冲突之前，它是不可见的。

The fix isn’t more JavaScript. It’s one extra clause in the SQL: 解决方法不是编写更多的 JavaScript，而是在 SQL 中增加一个额外的子句：

async function updateNote(id, title, content, knownUpdatedAt) { 
  const { data, error } = await db 
    .from('notes') 
    .update({ title, content }) 
    .eq('id', id) 
    .eq('updated_at', knownUpdatedAt) 
    .select(); 
  if (error) throw error; 
  return data.length === 0 ? null : data[0]; // null = conflict 
}

That second .eq() turns this into UPDATE notes SET ... WHERE id = X AND updated_at = Y. Postgres evaluates the whole WHERE clause atomically — there’s no gap in time between “check if this row still matches” and “write to it” for another transaction to sneak into. Either the row’s updated_at still equals what this client last saw, and the write happens, or it doesn’t, and Postgres touches zero rows. 第二个 .eq() 将其转换为 UPDATE notes SET ... WHERE id = X AND updated_at = Y。Postgres 会原子性地评估整个 WHERE 子句——在“检查该行是否仍然匹配”和“执行写入”之间没有时间间隙，其他事务无法趁虚而入。要么该行的 updated_at 仍然等于客户端上次看到的值并执行写入，要么不相等，Postgres 将不会触及任何行。

This is a compare-and-swap, done entirely inside a single statement, with no application-level locking at all. The JS side just has to notice which of those two outcomes happened, and .select() after an .update() gives it a clean signal for free: if the WHERE matched nothing, Supabase returns an empty array — not an error. data.length === 0 is the conflict. 这是一种“比较并交换”操作，完全在单个语句内完成，无需任何应用层面的锁定。JavaScript 端只需注意这两种结果中的哪一种发生了，而 .update() 之后的 .select() 可以免费提供一个清晰的信号：如果 WHERE 子句没有匹配到任何内容，Supabase 会返回一个空数组——而不是错误。data.length === 0 即代表冲突。

← 返回新闻列表 / Back to news list