supabase-database

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Supabase Database Operations

Supabase数据库操作

Overview

概述

This skill provides tools for working with Supabase database tables through the REST API. Supports SELECT queries with filtering, INSERT, UPDATE, DELETE operations, and calling RPC functions.

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

Prerequisites

前置条件

Required environment variables:

bash

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

Helper script: This skill uses the shared Supabase API helper. Make sure to source it:

bash

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

所需环境变量：

bash

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

辅助脚本： 本技能使用共享的Supabase API辅助脚本，请确保已引入它：

bash

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

Common Operations

常用操作

SELECT - Query Data

SELECT - 查询数据

Basic select all:

bash

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

基础全量查询：

bash

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

Get all rows from a table

获取表中所有行

supabase_get "/rest/v1/your_table?select=*"


**Select specific columns:**
```bash

supabase_get "/rest/v1/your_table?select=*"


**查询指定列：**
```bash

Get only id and name columns

仅获取id、name和email列

supabase_get "/rest/v1/users?select=id,name,email"


**Filter results:**
```bash

supabase_get "/rest/v1/users?select=id,name,email"


**筛选结果：**
```bash

Equality filter

相等筛选

supabase_get "/rest/v1/users?select=*&email=eq.user@example.com"

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)

模式匹配（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 and limit:**
```bash

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


**排序与限制：**
```bash

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"


**Complex queries:**
```bash

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


**复杂查询：**
```bash

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"

undefined

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

undefined

INSERT - Add Data

INSERT - 新增数据

Insert single row:

bash

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

supabase_post "/rest/v1/users" '{
  "name": "John Doe",
  "email": "john@example.com",
  "age": 30
}'

Insert multiple rows:

bash

supabase_post "/rest/v1/users" '[
  {
    "name": "Alice Smith",
    "email": "alice@example.com"
  },
  {
    "name": "Bob Jones",
    "email": "bob@example.com"
  }
]'

Upsert (insert or update if exists):

bash

undefined

插入单行数据：

bash

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

supabase_post "/rest/v1/users" '{
  "name": "John Doe",
  "email": "john@example.com",
  "age": 30
}'

插入多行数据：

bash

supabase_post "/rest/v1/users" '[
  {
    "name": "Alice Smith",
    "email": "alice@example.com"
  },
  {
    "name": "Bob Jones",
    "email": "bob@example.com"
  }
]'

Upsert（存在则更新，不存在则插入）：

bash

undefined

Use Prefer: resolution=merge-duplicates header

使用Prefer: resolution=merge-duplicates请求头

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": "updated@example.com" }'

undefined

undefined

UPDATE - Modify Data

UPDATE - 修改数据

Update rows matching filter:

bash

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

更新匹配筛选条件的行：

bash

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

Update specific row by id

根据id更新指定行

supabase_patch "/rest/v1/users?id=eq.123" '{ "name": "Updated Name", "email": "newemail@example.com" }'

Update multiple rows

更新多行数据

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

undefined

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

undefined

DELETE - Remove Data

DELETE - 删除数据

Delete rows matching filter:

bash

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

删除匹配筛选条件的行：

bash

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

Delete specific row by id

根据id删除指定行

supabase_delete "/rest/v1/users?id=eq.123"

Delete multiple rows

删除多行数据

supabase_delete "/rest/v1/logs?created_at=lt.2023-01-01"

undefined

supabase_delete "/rest/v1/logs?created_at=lt.2023-01-01"

undefined

RPC - Call Database Functions

RPC - 调用数据库函数

Execute stored procedures:

bash

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

执行存储过程：

bash

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" }'

undefined

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

undefined

Filter Operators Reference

筛选运算符参考

Operator	Description	Example
`eq`	Equals	`id=eq.123`
`neq`	Not equals	`status=neq.deleted`
`gt`	Greater than	`age=gt.18`
`gte`	Greater than or equal	`price=gte.100`
`lt`	Less than	`quantity=lt.10`
`lte`	Less than or equal	`score=lte.50`
`like`	Pattern match (case-sensitive)	`name=like.John`
`ilike`	Pattern match (case-insensitive)	`email=ilike.*@gmail.com`
`is`	Check for exact value (null, true, false)	`deleted_at=is.null`
`in`	In list	`status=in.(active,pending)`
`not`	Negate a condition	`status=not.in.(deleted,banned)`
`or`	Logical OR	`or=(status.eq.active,status.eq.pending)`
`and`	Logical AND	`and=(age.gte.18,age.lte.65)`

运算符	描述	示例
`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=(status.eq.active,status.eq.pending)`
`and`	逻辑与	`and=(age.gte.18,age.lte.65)`

Response Formatting

响应格式化

Pretty print JSON (requires jq):

bash

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

Extract specific field:

bash

undefined

JSON美化输出（需要jq工具）：

bash

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

提取指定字段：

bash

undefined

Get just the names

仅获取名称列表

supabase_get "/rest/v1/users?select=name" | jq -r '.[].name'


**Count results:**
```bash

supabase_get "/rest/v1/users?select=name" | jq -r '.[].name'


**统计结果数量：**
```bash

Add Prefer: count=exact header for total count

添加Prefer: count=exact请求头获取总数量

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

undefined

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

undefined

Common Patterns

常用模式

Check if record exists

检查记录是否存在

bash

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

bash

result=$(supabase_get "/rest/v1/users?select=id&email=eq.test@example.com")
if [[ "$result" == "[]" ]]; then
    echo "用户不存在"
else
    echo "用户已存在"
fi

Create if not exists

不存在则创建

bash

undefined

bash

undefined

Check first

先检查是否存在

existing=$(supabase_get "/rest/v1/users?select=id&email=eq.test@example.com")

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

undefined

existing=$(supabase_get "/rest/v1/users?select=id&email=eq.test@example.com")

if [[ "$existing" == "[]" ]]; then # 创建新用户 supabase_post "/rest/v1/users" '{ "email": "test@example.com", "name": "Test User" }' echo "用户已创建" else echo "用户已存在" fi

undefined

Batch operations

批量操作

bash

undefined

bash

undefined

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

undefined

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

undefined

Error Handling

错误处理

The helper script automatically handles HTTP errors and displays them. Check return codes:

bash

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

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

bash

if supabase_get "/rest/v1/users?select=*"; then
    echo "查询成功"
else
    echo "查询失败"
    exit 1
fi

Security Notes

安全注意事项

Use anon key for client-side operations (respects Row Level Security)
Use service role key for admin operations (bypasses RLS - use carefully)
Always apply Row Level Security policies in your Supabase dashboard
Never commit keys to version control

客户端操作使用anon密钥（遵循行级安全策略）
管理员操作使用service role密钥（绕过RLS - 谨慎使用）
务必在Supabase控制台中配置行级安全策略
切勿将密钥提交到版本控制系统

API Documentation

API文档

Full Supabase REST API documentation: https://supabase.com/docs/guides/api

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