测试概览

测试是数据库开发中一个关键的环节，尤其是在使用像行级安全（RLS）策略这样的功能时。这个指南提供了一个全面的方法来测试你的 Supabase 数据库。

🌐 Testing is a critical part of database development, especially when working with features like Row Level Security (RLS) policies. This guide provides a comprehensive approach to testing your Supabase database.

测试方法 #

🌐 Testing approaches

使用 pgTAP 进行数据库单元测试 #

🌐 Database unit testing with pgTAP

pgTAP 是一个用于 Postgres 的单元测试框架，可以进行以下测试：

• 数据库结构：表格、列、约束
• 行级安全 (RLS) 策略
• 函数和过程
• 数据完整性

这个例子演示了如何为一个基础的待办事项应用设置和测试 RLS 策略：

🌐 This example demonstrates setting up and testing RLS policies for a basic todo application:

1. 创建一个启用了 RLS 的测试表：

   1
   -- Create a todos table
   2
   create table todos (
   3
   id uuid primary key default gen_random_uuid(),
   4
   task text not null,
   5
   user_id uuid references auth.users not null,
   6
   completed boolean default false
   7
   );
   8
   9
   -- Enable RLS
   10
   alter table todos enable row level security;
   11
   12
   -- Create a policy
   13
   create policy "Users can only access their own todos"
   14
   on todos for all -- this policy applies to all operations
   15
   to authenticated
   16
   using ((select auth.uid()) = user_id);

2. 设置你的测试环境：

   1
   # Create a new test for our policies using supabase cli
   2
   supabase test new todos_rls.test

3. 写下你的 RLS 测试：

   1
   begin;
   2
   -- install tests utilities
   3
   -- install pgtap extension for testing
   4
   create extension if not exists pgtap with schema extensions;
   5
   -- Start declare we'll have 4 test cases in our test suite
   6
   select plan(4);
   7
   8
   -- Setup our testing data
   9
   -- Set up auth.users entries
   10
   insert into auth.users (id, email) values
   11
   	('123e4567-e89b-12d3-a456-426614174000', 'user1@test.com'),
   12
   	('987fcdeb-51a2-43d7-9012-345678901234', 'user2@test.com');
   13
   14
   -- Create test todos
   15
   insert into public.todos (task, user_id) values
   16
   	('User 1 Task 1', '123e4567-e89b-12d3-a456-426614174000'),
   17
   	('User 1 Task 2', '123e4567-e89b-12d3-a456-426614174000'),
   18
   	('User 2 Task 1', '987fcdeb-51a2-43d7-9012-345678901234');
   19
   20
   -- as User 1
   21
   set local role authenticated;
   22
   set local request.jwt.claim.sub = '123e4567-e89b-12d3-a456-426614174000';
   23
   24
   -- Test 1: User 1 should only see their own todos
   25
   select results_eq(
   26
   	'select count(*) from todos',
   27
   	ARRAY[2::bigint],
   28
   	'User 1 should only see their 2 todos'
   29
   );
   30
   31
   -- Test 2: User 1 can create their own todo
   32
   select lives_ok(
   33
   	$$insert into todos (task, user_id) values ('New Task', '123e4567-e89b-12d3-a456-426614174000'::uuid)$$,
   34
   	'User 1 can create their own todo'
   35
   );
   36
   37
   -- as User 2
   38
   set local request.jwt.claim.sub = '987fcdeb-51a2-43d7-9012-345678901234';
   39
   40
   -- Test 3: User 2 should only see their own todos
   41
   select results_eq(
   42
   	'select count(*) from todos',
   43
   	ARRAY[1::bigint],
   44
   	'User 2 should only see their 1 todo'
   45
   );
   46
   47
   -- Test 4: User 2 cannot modify User 1's todo
   48
   SELECT results_ne(
   49
   	$$ update todos set task = 'Hacked!' where user_id = '123e4567-e89b-12d3-a456-426614174000'::uuid returning 1 $$,
   50
   	$$ values(1) $$,
   51
   	'User 2 cannot modify User 1 todos'
   52
   );
   53
   54
   select * from finish();
   55
   rollback;

4. 运行测试：

   1
   supabase test db
   2
   psql:todos_rls.test.sql:4: NOTICE:  extension "pgtap" already exists, skipping
   3
   ./todos_rls.test.sql .. ok
   4
   All tests successful.
   5
   Files=1, Tests=6,  0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
   6
   Result: PASS

应用级测试 #

🌐 Application-Level testing

通过应用代码进行测试可以提供端到端的验证。与使用 pgTAP 的数据库级测试不同，应用级测试无法使用事务来实现隔离。

🌐 Testing through application code provides end-to-end verification. Unlike database-level testing with pgTAP, application-level tests cannot use transactions for isolation.

这里有一个使用 TypeScript 的例子，它与上面的 pgTAP 测试相呼应：

🌐 Here's an example using TypeScript that mirrors the pgTAP tests above:

1
import crypto from 'crypto'
2
import { createClient } from '@supabase/supabase-js'
3
import { beforeAll, describe, expect, it } from 'vitest'
4
5
describe('Todos RLS', () => {
6
  // Generate unique IDs for this test suite to avoid conflicts with other tests
7
  const USER_1_ID = crypto.randomUUID()
8
  const USER_2_ID = crypto.randomUUID()
9
10
  const supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_PUBLISHABLE_KEY!)
11
12
  beforeAll(async () => {
13
    // Setup test data specific to this test suite
14
    const adminSupabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SECRET_KEY!)
15
16
    // Create test users with unique IDs
17
    await adminSupabase.auth.admin.createUser({
18
      id: USER_1_ID,
19
      email: `user1-${USER_1_ID}@test.com`,
20
      password: 'password123',
21
      // We want the user to be usable right away without email confirmation
22
      email_confirm: true,
23
    })
24
    await adminSupabase.auth.admin.createUser({
25
      id: USER_2_ID,
26
      email: `user2-${USER_2_ID}@test.com`,
27
      password: 'password123',
28
      email_confirm: true,
29
    })
30
31
    // Create initial todos
32
    await adminSupabase.from('todos').insert([
33
      { task: 'User 1 Task 1', user_id: USER_1_ID },
34
      { task: 'User 1 Task 2', user_id: USER_1_ID },
35
      { task: 'User 2 Task 1', user_id: USER_2_ID },
36
    ])
37
  })
38
39
  it('should allow User 1 to only see their own todos', async () => {
40
    // Sign in as User 1
41
    await supabase.auth.signInWithPassword({
42
      email: `user1-${USER_1_ID}@test.com`,
43
      password: 'password123',
44
    })
45
46
    const { data: todos } = await supabase.from('todos').select('*')
47
48
    expect(todos).toHaveLength(2)
49
    todos?.forEach((todo) => {
50
      expect(todo.user_id).toBe(USER_1_ID)
51
    })
52
  })
53
54
  it('should allow User 1 to create their own todo', async () => {
55
    await supabase.auth.signInWithPassword({
56
      email: `user1-${USER_1_ID}@test.com`,
57
      password: 'password123',
58
    })
59
60
    const { error } = await supabase.from('todos').insert({ task: 'New Task', user_id: USER_1_ID })
61
62
    expect(error).toBeNull()
63
  })
64
65
  it('should allow User 2 to only see their own todos', async () => {
66
    // Sign in as User 2
67
    await supabase.auth.signInWithPassword({
68
      email: `user2-${USER_2_ID}@test.com`,
69
      password: 'password123',
70
    })
71
72
    const { data: todos } = await supabase.from('todos').select('*')
73
    expect(todos).toHaveLength(1)
74
    todos?.forEach((todo) => {
75
      expect(todo.user_id).toBe(USER_2_ID)
76
    })
77
  })
78
79
  it('should prevent User 2 from modifying User 1 todos', async () => {
80
    await supabase.auth.signInWithPassword({
81
      email: `user2-${USER_2_ID}@test.com`,
82
      password: 'password123',
83
    })
84
85
    // Attempt to update the todos we shouldn't have access to
86
    // result will be a no-op
87
    await supabase.from('todos').update({ task: 'Hacked!' }).eq('user_id', USER_1_ID)
88
89
    // Log back in as User 1 to verify their todos weren't changed
90
    await supabase.auth.signInWithPassword({
91
      email: `user1-${USER_1_ID}@test.com`,
92
      password: 'password123',
93
    })
94
95
    // Fetch User 1's todos
96
    const { data: todos } = await supabase.from('todos').select('*')
97
98
    // Verify that none of the todos were changed to "Hacked!"
99
    expect(todos).toBeDefined()
100
    todos?.forEach((todo) => {
101
      expect(todo.task).not.toBe('Hacked!')
102
    })
103
  })
104
})

测试隔离策略 #

🌐 Test isolation strategies

对于应用级测试，可以考虑以下方法来实现测试隔离：

🌐 For application-level testing, consider these approaches for test isolation:

1. 唯一标识符：为每个测试套件生成唯一 ID，以防止数据冲突
2. 测试后清理：如果需要，在 afterAll 或 afterEach 钩子里清理创建的数据
3. 隔离数据集：在数据中使用前缀或命名空间来区分测试用例

持续集成测试 #

🌐 Continuous integration testing

在你的 CI 流水线中设置自动化数据库测试：

🌐 Set up automated database testing in your CI pipeline:

1. 创建一个 GitHub Actions 工作流 .github/workflows/db-tests.yml：

1
name: Database Tests
2
3
on:
4
  push:
5
    branches: [main]
6
  pull_request:
7
    branches: [main]
8
9
jobs:
10
  test:
11
    runs-on: ubuntu-latest
12
13
    steps:
14
      - uses: actions/checkout@v4
15
16
      - name: Setup Supabase CLI
17
        uses: supabase/setup-cli@v1
18
19
      - name: Start Supabase
20
        run: supabase start
21
22
      - name: Run Tests
23
        run: supabase test db

最佳实践 #

🌐 Best practices

1. 测试数据设置
   • 使用 begin 和 rollback 来确保测试隔离
   • 创建涵盖边缘情况的真实测试数据
   • 在测试中使用不同的用户角色和权限
2. RLS 政策测试
   • 测试创建、读取、更新、删除操作
   • 测试不同的用户角色：匿名用户和已认证用户
   • 测试边缘情况和潜在的安全绕过
   • 总是测试负面情况：用户不应该能够做的事情
3. CI/CD 集成
   • 在每次拉取请求时自动运行测试
   • 在部署流程中加入数据库测试
   • 使用事务让测试运行更快

现实世界的例子 #

🌐 Real-World examples

想了解更复杂、真实案例的数据库测试，可以看看：

🌐 For more complex, real-world examples of database testing, check out:

• 数据库测试示例仓库 - 一个用于测试 RLS 策略的生产级示例
• RLS 指南和最佳实践

故障排除 #

🌐 Troubleshooting

常见问题及解决方法：

🌐 Common issues and solutions:

1. 由于 RLS 导致的测试失败
   • 确保你已经设置了正确的角色 set local role authenticated;
   • 验证 JWT 声明是否已设置 set local "request.jwt.claims"
   • 检查策略定义是否符合你的测试假设
2. CI 流水线问题
   • 确认Supabase CLI安装正确
   • 确保在测试前运行数据库迁移
   • 使用事务检查测试是否隔离得当

额外资源 #

🌐 Additional resources

• pgTAP 文档
• Supabase CLI 参考
• pgTAP Supabase 参考
• 数据库测试参考