supabase-database

Supabase 数据库操作

概述

此技能提供通过 REST API 操作 Supabase 数据库表的工具。支持带筛选条件的 SELECT 查询、INSERT、UPDATE、DELETE 操作，以及调用 RPC 函数。

前提条件

必需的环境变量：

export SUPABASE_URL="https://your-project.supabase.co"
export SUPABASE_KEY="your-anon-or-service-role-key"

辅助脚本： 此技能使用共享的 Supabase API 辅助脚本。请确保已加载该脚本：

source "$(dirname "${BASH_SOURCE[0]}")/../../scripts/supabase-api.sh"

常见操作

SELECT - 查询数据

基本的全量查询：

source "$(dirname "${BASH_SOURCE[0]}")/../../scripts/supabase-api.sh"

# Get all rows from a table
supabase_get "/rest/v1/your_table?select=*"

选择指定列：

# Get only id and name columns
supabase_get "/rest/v1/users?select=id,name,email"

筛选结果：

# Equality filter
supabase_get "/rest/v1/users?select=*&[email protected]"

# Greater than
supabase_get "/rest/v1/products?select=*&price=gt.100"

# Less than or equal
supabase_get "/rest/v1/orders?select=*&quantity=lte.10"

# Pattern matching (LIKE)
supabase_get "/rest/v1/users?select=*&name=like.*John*"

# In list
supabase_get "/rest/v1/products?select=*&category=in.(electronics,books)"

# Is null
supabase_get "/rest/v1/users?select=*&deleted_at=is.null"

排序和限制数量：

# Order by column (ascending)
supabase_get "/rest/v1/posts?select=*&order=created_at.asc"

# Order by column (descending)
supabase_get "/rest/v1/posts?select=*&order=created_at.desc"

# Limit results
supabase_get "/rest/v1/posts?select=*&limit=10"

# Pagination (offset)
supabase_get "/rest/v1/posts?select=*&limit=10&offset=20"

# Range pagination
supabase_get "/rest/v1/posts?select=*" -H "Range: 0-9"

复杂查询：

# Multiple filters (AND)
supabase_get "/rest/v1/products?select=*&category=eq.electronics&price=gt.100"

# OR filter
supabase_get "/rest/v1/users?select=*&or=(status.eq.active,status.eq.pending)"

# Nested filters
supabase_get "/rest/v1/users?select=*,posts(*)&posts.published=eq.true"

INSERT - 添加数据

插入单行：

source "$(dirname "${BASH_SOURCE[0]}")/../../scripts/supabase-api.sh"

supabase_post "/rest/v1/users" '{
  "name": "John Doe",
  "email": "[email protected]",
  "age": 30
}'

插入多行：

supabase_post "/rest/v1/users" '[
  {
    "name": "Alice Smith",
    "email": "[email protected]"
  },
  {
    "name": "Bob Jones",
    "email": "[email protected]"
  }
]'

Upsert（插入；若已存在则更新）：

# Use Prefer: resolution=merge-duplicates header
curl -s -X POST \
    "${SUPABASE_URL}/rest/v1/users" \
    -H "apikey: ${SUPABASE_KEY}" \
    -H "Authorization: Bearer ${SUPABASE_KEY}" \
    -H "Content-Type: application/json" \
    -H "Prefer: resolution=merge-duplicates" \
    -d '{
      "id": 1,
      "name": "Updated Name",
      "email": "[email protected]"
    }'

UPDATE - 修改数据

更新符合筛选条件的行：

source "$(dirname "${BASH_SOURCE[0]}")/../../scripts/supabase-api.sh"

# Update specific row by id
supabase_patch "/rest/v1/users?id=eq.123" '{
  "name": "Updated Name",
  "email": "[email protected]"
}'

# Update multiple rows
supabase_patch "/rest/v1/products?category=eq.electronics" '{
  "discount": 10
}'

DELETE - 删除数据

删除符合筛选条件的行：

source "$(dirname "${BASH_SOURCE[0]}")/../../scripts/supabase-api.sh"

# Delete specific row by id
supabase_delete "/rest/v1/users?id=eq.123"

# Delete multiple rows
supabase_delete "/rest/v1/logs?created_at=lt.2023-01-01"

RPC - 调用数据库函数

执行存储过程：

source "$(dirname "${BASH_SOURCE[0]}")/../../scripts/supabase-api.sh"

# Call function without parameters
supabase_post "/rest/v1/rpc/function_name" '{}'

# Call function with parameters
supabase_post "/rest/v1/rpc/calculate_total" '{
  "user_id": 123,
  "start_date": "2023-01-01",
  "end_date": "2023-12-31"
}'

筛选运算符参考

运算符	说明	示例
eq	等于	id=eq.123
neq	不等于	status=neq.deleted
gt	大于	age=gt.18
gte	大于或等于	price=gte.100
lt	小于	quantity=lt.10
lte	小于或等于	score=lte.50
like	模式匹配（区分大小写）	name=like.*John*
ilike	模式匹配（不区分大小写）	email=ilike.*@gmail.com
is	检查是否为精确值（null、true、false）	deleted_at=is.null
in	在列表中	status=in.(active,pending)
not	对条件取反	status=not.in.(deleted,banned)
or	逻辑 OR	or=(status.eq.active,status.eq.pending)
and	逻辑 AND	and=(age.gte.18,age.lte.65)

响应格式化

美化打印 JSON（需要 jq）：

supabase_get "/rest/v1/users?select=*" | jq '.'

提取指定字段：

# Get just the names
supabase_get "/rest/v1/users?select=name" | jq -r '.[].name'

统计结果数量：

# Add Prefer: count=exact header for total count
curl -s -X GET \
    "${SUPABASE_URL}/rest/v1/users?select=*" \
    -H "apikey: ${SUPABASE_KEY}" \
    -H "Authorization: Bearer ${SUPABASE_KEY}" \
    -H "Prefer: count=exact" \
    -I | grep -i content-range

常见模式

检查记录是否存在

result=$(supabase_get "/rest/v1/users?select=id&[email protected]")
if [[ "$result" == "[]" ]]; then
    echo "User does not exist"
else
    echo "User exists"
fi

不存在时创建

# Check first
existing=$(supabase_get "/rest/v1/users?select=id&[email protected]")

if [[ "$existing" == "[]" ]]; then
    # Create new user
    supabase_post "/rest/v1/users" '{
        "email": "[email protected]",
        "name": "Test User"
    }'
    echo "User created"
else
    echo "User already exists"
fi

批量操作

# Process multiple records
ids=(123 456 789)

for id in "${ids[@]}"; do
    supabase_patch "/rest/v1/users?id=eq.$id" '{
        "updated_at": "'"$(date -u +%Y-%m-%dT%H:%M:%SZ)"'"
    }'
done

错误处理

辅助脚本会自动处理 HTTP 错误并显示错误信息。检查返回码：

if supabase_get "/rest/v1/users?select=*"; then
    echo "Query successful"
else
    echo "Query failed"
    exit 1
fi

安全说明

• 客户端操作使用 anon key（遵循行级安全策略）
• 管理操作使用 service role key（绕过 RLS，请谨慎使用）
• 始终在 Supabase 控制面板中应用行级安全策略
• 切勿将密钥提交到版本控制中

API 文档

完整的 Supabase REST API 文档：https://supabase.com/docs/guides/api