Back to Details

supabase-database

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Supabase Database Skill

Supabase 数据库技能

Database operations, queries, and Row Level Security.
数据库操作、查询以及行级安全性（Row Level Security，RLS）。

Quick Reference

快速参考

OperationJavaScriptSQL
Select all
supabase.from('table').select('*')
SELECT * FROM table
Select columns
supabase.from('table').select('col1,col2')
SELECT col1, col2 FROM table
Filter
.eq('col', 'value')
WHERE col = 'value'
Insert
.insert({ col: 'value' })
INSERT INTO table (col) VALUES ('value')
Update
.update({ col: 'value' }).eq('id', 1)
UPDATE table SET col = 'value' WHERE id = 1
Delete
.delete().eq('id', 1)
DELETE FROM table WHERE id = 1
操作JavaScriptSQL
查询全部
supabase.from('table').select('*')
SELECT * FROM table
查询指定列
supabase.from('table').select('col1,col2')
SELECT col1, col2 FROM table
过滤
.eq('col', 'value')
WHERE col = 'value'
插入
.insert({ col: 'value' })
INSERT INTO table (col) VALUES ('value')
更新
.update({ col: 'value' }).eq('id', 1)
UPDATE table SET col = 'value' WHERE id = 1
删除
.delete().eq('id', 1)
DELETE FROM table WHERE id = 1

Basic Queries

基础查询

Select

查询

javascript
// All rows
const { data, error } = await supabase
  .from('users')
  .select('*')

// Specific columns
const { data, error } = await supabase
  .from('users')
  .select('id, name, email')

// With count
const { data, count, error } = await supabase
  .from('users')
  .select('*', { count: 'exact' })
javascript
// 查询所有行
const { data, error } = await supabase
  .from('users')
  .select('*')

// 查询指定列
const { data, error } = await supabase
  .from('users')
  .select('id, name, email')

// 附带计数
const { data, count, error } = await supabase
  .from('users')
  .select('*', { count: 'exact' })

Insert

插入

javascript
// Single row
const { data, error } = await supabase
  .from('users')
  .insert({ name: 'John', email: 'john@example.com' })
  .select()

// Multiple rows
const { data, error } = await supabase
  .from('users')
  .insert([
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' }
  ])
  .select()
javascript
// 插入单行
const { data, error } = await supabase
  .from('users')
  .insert({ name: 'John', email: 'john@example.com' })
  .select()

// 插入多行
const { data, error } = await supabase
  .from('users')
  .insert([
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' }
  ])
  .select()

Update

更新

javascript
const { data, error } = await supabase
  .from('users')
  .update({ name: 'John Doe' })
  .eq('id', 1)
  .select()
javascript
const { data, error } = await supabase
  .from('users')
  .update({ name: 'John Doe' })
  .eq('id', 1)
  .select()

Upsert

插入或更新（Upsert）

javascript
const { data, error } = await supabase
  .from('users')
  .upsert({ id: 1, name: 'John', email: 'john@example.com' })
  .select()
javascript
const { data, error } = await supabase
  .from('users')
  .upsert({ id: 1, name: 'John', email: 'john@example.com' })
  .select()

Delete

删除

javascript
const { error } = await supabase
  .from('users')
  .delete()
  .eq('id', 1)
javascript
const { error } = await supabase
  .from('users')
  .delete()
  .eq('id', 1)

Filters

过滤条件

Comparison Operators

比较运算符

javascript
// Equal
.eq('col', 'value')

// Not equal
.neq('col', 'value')

// Greater than
.gt('col', 10)

// Greater or equal
.gte('col', 10)

// Less than
.lt('col', 10)

// Less or equal
.lte('col', 10)
javascript
// 等于
.eq('col', 'value')

// 不等于
.neq('col', 'value')

// 大于
.gt('col', 10)

// 大于等于
.gte('col', 10)

// 小于
.lt('col', 10)

// 小于等于
.lte('col', 10)

Pattern Matching

模式匹配

javascript
// LIKE (case sensitive)
.like('name', '%John%')

// ILIKE (case insensitive)
.ilike('name', '%john%')
javascript
// LIKE（区分大小写）
.like('name', '%John%')

// ILIKE（不区分大小写）
.ilike('name', '%john%')

List Operations

列表操作

javascript
// IN array
.in('status', ['active', 'pending'])

// Contains (array column contains value)
.contains('tags', ['sports', 'news'])

// Contained by (value contained by array column)
.containedBy('tags', ['sports', 'news', 'tech'])

// Overlaps (any match)
.overlaps('tags', ['sports', 'tech'])
javascript
// 在数组中
.in('status', ['active', 'pending'])

// 包含（数组列包含指定值）
.contains('tags', ['sports', 'news'])

// 被包含（值被数组列包含）
.containedBy('tags', ['sports', 'news', 'tech'])

// 重叠（存在匹配项）
.overlaps('tags', ['sports', 'tech'])

Range Operations

范围操作

javascript
// Between (exclusive)
.range('price', 10, 100)

// In range type column
.rangeGt('date_range', '2025-01-01')
.rangeLt('date_range', '2025-12-31')
javascript
// 介于两者之间（不包含边界）
.range('price', 10, 100)

// 范围类型列操作
.rangeGt('date_range', '2025-01-01')
.rangeLt('date_range', '2025-12-31')

Null Checks

空值检查

javascript
// Is null
.is('deleted_at', null)

// Is not null
.not('deleted_at', 'is', null)
javascript
// 为空
.is('deleted_at', null)

// 不为空
.not('deleted_at', 'is', null)

Boolean Operators

布尔运算符

javascript
// AND (chain filters)
.eq('status', 'active')
.eq('verified', true)

// OR
.or('status.eq.active,status.eq.pending')

// NOT
.not('status', 'eq', 'deleted')
javascript
// AND（链式过滤）
.eq('status', 'active')
.eq('verified', true)

// OR
.or('status.eq.active,status.eq.pending')

// NOT
.not('status', 'eq', 'deleted')

Ordering & Pagination

排序与分页

javascript
// Order by
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

// Multiple order
.order('category', { ascending: true })
.order('created_at', { ascending: false })

// Limit
.limit(10)

// Range (pagination)
.range(0, 9)  // First 10 rows

// Single row
.single()

// Maybe single (0 or 1)
.maybeSingle()
javascript
// 排序
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

// 多条件排序
.order('category', { ascending: true })
.order('created_at', { ascending: false })

// 限制数量
.limit(10)

// 范围分页
.range(0, 9)  // 前10行

// 查询单行
.single()

// 查询0或1行
.maybeSingle()

Relations (Joins)

关联查询（Joins）

One-to-Many

一对多

javascript
// Users with their posts
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts (
      id,
      title,
      content
    )
  `)
javascript
// 查询用户及其发布的帖子
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts (
      id,
      title,
      content
    )
  `)

Many-to-One

多对一

javascript
// Posts with author
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    users (
      id,
      name
    )
  `)
javascript
// 查询帖子及其作者
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    users (
      id,
      name
    )
  `)

Inner Join

内连接

javascript
// Only users with posts
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts!inner (
      id,
      title
    )
  `)
javascript
// 仅查询有帖子的用户
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts!inner (
      id,
      title
    )
  `)

Many-to-Many

多对多

javascript
// Posts with tags through junction table
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    post_tags (
      tags (
        id,
        name
      )
    )
  `)
javascript
// 通过关联表查询帖子及其标签
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    post_tags (
      tags (
        id,
        name
      )
    )
  `)

Row Level Security (RLS)

行级安全性（RLS）

Enable RLS

启用RLS

sql
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
sql
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Basic Policies

基础策略

sql
-- Users can read their own data
CREATE POLICY "Users can view own data"
ON users FOR SELECT
TO authenticated
USING (auth.uid() = id);

-- Users can insert their own data
CREATE POLICY "Users can insert own data"
ON users FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = id);

-- Users can update their own data
CREATE POLICY "Users can update own data"
ON users FOR UPDATE
TO authenticated
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Users can delete their own data
CREATE POLICY "Users can delete own data"
ON users FOR DELETE
TO authenticated
USING (auth.uid() = id);
sql
-- 用户可查看自身数据
CREATE POLICY "Users can view own data"
ON users FOR SELECT
TO authenticated
USING (auth.uid() = id);

-- 用户可插入自身数据
CREATE POLICY "Users can insert own data"
ON users FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = id);

-- 用户可更新自身数据
CREATE POLICY "Users can update own data"
ON users FOR UPDATE
TO authenticated
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- 用户可删除自身数据
CREATE POLICY "Users can delete own data"
ON users FOR DELETE
TO authenticated
USING (auth.uid() = id);

Helper Functions

辅助函数

sql
-- Current user ID
auth.uid()

-- Current user role (anon, authenticated, service_role)
auth.role()

-- Full JWT as JSON
auth.jwt()

-- Check specific JWT claim
auth.jwt()->>'email'
auth.jwt()->'app_metadata'->>'role'
sql
-- 当前用户ID
auth.uid()

-- 当前用户角色（匿名、已认证、服务角色）
auth.role()

-- 完整JWT（JSON格式）
auth.jwt()

-- 检查特定JWT声明
auth.jwt()->>'email'
auth.jwt()->'app_metadata'->>'role'

Performance Optimization

性能优化

sql
-- Wrap auth functions in SELECT for performance
CREATE POLICY "Fast policy"
ON users FOR SELECT
TO authenticated
USING ((SELECT auth.uid()) = user_id);

-- Add indexes for RLS columns
CREATE INDEX idx_posts_user_id ON posts(user_id);
sql
-- 为性能优化，将auth函数包裹在SELECT中
CREATE POLICY "Fast policy"
ON users FOR SELECT
TO authenticated
USING ((SELECT auth.uid()) = user_id);

-- 为RLS相关列添加索引
CREATE INDEX idx_posts_user_id ON posts(user_id);

RPC (Remote Procedure Call)

远程过程调用（RPC）

Define Function

定义函数

sql
CREATE OR REPLACE FUNCTION search_users(query text)
RETURNS TABLE(id uuid, name text, email text)
LANGUAGE sql STABLE
AS $$
  SELECT id, name, email
  FROM users
  WHERE name ILIKE '%' || query || '%'
     OR email ILIKE '%' || query || '%'
  ORDER BY name;
$$;
sql
CREATE OR REPLACE FUNCTION search_users(query text)
RETURNS TABLE(id uuid, name text, email text)
LANGUAGE sql STABLE
AS $$
  SELECT id, name, email
  FROM users
  WHERE name ILIKE '%' || query || '%'
     OR email ILIKE '%' || query || '%'
  ORDER BY name;
$$;

Call Function

调用函数

javascript
const { data, error } = await supabase
  .rpc('search_users', { query: 'john' })
javascript
const { data, error } = await supabase
  .rpc('search_users', { query: 'john' })

TypeScript Types

TypeScript 类型

Generate Types

生成类型

bash
supabase gen types typescript --local > database.types.ts
bash
supabase gen types typescript --local > database.types.ts

Use Types

使用类型

typescript
import { Database } from './database.types'

type User = Database['public']['Tables']['users']['Row']
type NewUser = Database['public']['Tables']['users']['Insert']
type UpdateUser = Database['public']['Tables']['users']['Update']

const supabase = createClient<Database>(url, key)

const { data } = await supabase
  .from('users')
  .select('*')
// data is User[] | null
typescript
import { Database } from './database.types'

type User = Database['public']['Tables']['users']['Row']
type NewUser = Database['public']['Tables']['users']['Insert']
type UpdateUser = Database['public']['Tables']['users']['Update']

const supabase = createClient<Database>(url, key)

const { data } = await supabase
  .from('users')
  .select('*')
// data 类型为 User[] | null

References

参考资料

  • rls-policies.md - Complete RLS patterns
  • query-operators.md - All filter operators
  • postgres-functions.md - Writing SQL functions
  • rls-policies.md - 完整RLS模式
  • query-operators.md - 所有过滤运算符
  • postgres-functions.md - 编写SQL函数