Supabase数据库概述 #

PostgreSQL优势
├── 40+年发展历史
├── ACID事务保证
├── 完整SQL标准支持
├── 丰富的数据类型
├── 强大的扩展生态
├── 活跃的社区支持
└── 企业级可靠性

Supabase数据库架构
├── public schema
│   ├── 用户表
│   ┴── 业务表
│
├── auth schema
│   ├── users (用户表)
│   ├── sessions (会话表)
│   ├── refresh_tokens
│   └── identities
│
├── storage schema
│   ├── buckets (存储桶)
│   ├── objects (文件对象)
│   └── migrations
│
└── extensions
    ├── pgvector (向量搜索)
    ├── pgjwt (JWT支持)
    ├── pg_cron (定时任务)
    └── pg_net (HTTP请求)

Dashboard > Table Editor

功能
├── 可视化创建表
├── 编辑数据
├── 设置列属性
├── 添加外键关系
└── 设置RLS策略

点击 "Create a new table"

基本设置
├── 表名: 例如 users
├── 启用RLS: 建议开启
└── 添加列

列设置
├── 列名
├── 数据类型
├── 默认值
├── 是否可空
└── 是否主键

创建 users 表

| 列名 | 类型 | 默认值 | 可空 | 主键 |
|------|------|--------|------|------|
| id | int8 | 自动生成 | 否 | 是 |
| created_at | timestamptz | now() | 否 | 否 |
| name | text | - | 是 | 否 |
| email | text | - | 否 | 否 |
| avatar_url | text | - | 是 | 否 |

点击 "Save" 保存

表编辑器数据视图
├── 行内编辑: 双击单元格直接编辑
├── 添加行: 点击 "Insert row"
├── 删除行: 选中行后点击删除
├── 过滤: 使用过滤器筛选数据
└── 排序: 点击列头排序

Dashboard > SQL Editor

功能
├── 执行SQL语句
├── 保存查询
├── 查询历史
├── 格式化SQL
└── 导出结果

-- 创建表
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    author_id BIGINT REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    published BOOLEAN DEFAULT FALSE
);

-- 插入数据
INSERT INTO posts (title, content, author_id)
VALUES ('First Post', 'Hello World', 1);

-- 查询数据
SELECT * FROM posts;

-- 更新数据
UPDATE posts SET published = true WHERE id = 1;

-- 删除数据
DELETE FROM posts WHERE id = 1;

SQL编辑器 > Save query

保存的查询可以:
├── 重复使用
├── 分享给团队成员
├── 组织到文件夹
└── 设置为快捷查询

Settings > Database

连接方式
├── 直连 (端口 5432)
│   └── 适合长连接、事务操作
│
└── 连接池 (端口 6543)
    └── 适合Serverless、短连接

# 直连
postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres

# 连接池
postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

# 使用psql连接
psql "postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"

# 使用DBeaver、pgAdmin等GUI工具
# 填入连接信息即可

import { createClient } from '@supabase/supabase-js'
import { Pool } from 'pg'

// 使用Supabase客户端
const supabase = createClient(url, key)

// 或使用原生PostgreSQL客户端
const pool = new Pool({
  connectionString: 'postgresql://...',
})

默认Schema
├── public - 用户数据
├── auth - 认证数据
├── storage - 存储元数据
├── _realtime - 实时订阅
└── extensions - 扩展数据

-- 创建schema
CREATE SCHEMA app;

-- 在schema中创建表
CREATE TABLE app.settings (
    id BIGSERIAL PRIMARY KEY,
    key TEXT UNIQUE NOT NULL,
    value JSONB
);

-- 查询时指定schema
SELECT * FROM app.settings;

-- 授予schema使用权限
GRANT USAGE ON SCHEMA app TO authenticated;

-- 授予表操作权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO authenticated;

Settings > Database > Extensions

常用扩展
├── pgvector - 向量存储和搜索
├── pgjwt - JWT处理
├── pg_cron - 定时任务
├── pg_net - HTTP请求
├── pg_stat_statements - 查询统计
├── uuid-ossp - UUID生成
├── postgis - 地理空间
└── pg_trgm - 文本相似度

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgvector";
CREATE EXTENSION IF NOT EXISTS "pg_cron";

-- 查看已安装扩展
SELECT * FROM pg_extension;

-- uuid-ossp: 生成UUID
SELECT uuid_generate_v4();

-- pgvector: 向量操作
CREATE TABLE embeddings (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)
);

INSERT INTO embeddings (content, embedding)
VALUES ('Hello', '[0.1, 0.2, ...]'::vector);

-- 向量相似度搜索
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) as similarity
FROM embeddings
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

-- 创建简单函数
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS BIGINT AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM auth.users);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 调用函数
SELECT get_user_count();

CREATE OR REPLACE FUNCTION get_posts_by_user(user_id BIGINT)
RETURNS SETOF posts AS $$
BEGIN
    RETURN QUERY SELECT * FROM posts WHERE author_id = user_id;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_posts_by_user(1);

// 调用数据库函数
const { data, error } = await supabase.rpc('get_user_count')

// 带参数调用
const { data, error } = await supabase.rpc('get_posts_by_user', {
  user_id: 1
})

-- 创建触发器函数
CREATE OR REPLACE FUNCTION handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER on_users_update
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();

-- 自动创建用户资料
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, email)
    VALUES (NEW.id, NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION handle_new_user();

-- 创建视图
CREATE VIEW active_users AS
SELECT id, email, created_at
FROM auth.users
WHERE last_sign_in_at > NOW() - INTERVAL '30 days';

-- 查询视图
SELECT * FROM active_users;

-- 创建物化视图
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    u.id,
    u.email,
    COUNT(p.id) as post_count
FROM auth.users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id, u.email;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_stats;

-- 创建索引
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- 查看索引
SELECT * FROM pg_indexes WHERE tablename = 'posts';

-- 分析查询
EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 1;

优化建议
├── 为常用查询条件创建索引
├── 避免SELECT *
├── 使用LIMIT限制结果
├── 合理使用JOIN
└── 定期VACUUM