Supabase表操作 #

Dashboard > Table Editor > Create a new table

步骤
├── 输入表名
├── 添加列
├── 设置主键
├── 配置外键
├── 启用RLS
└── 保存

-- 基础创建表
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    email TEXT UNIQUE NOT NULL,
    name TEXT,
    avatar_url TEXT
);

-- 完整创建表
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    slug TEXT UNIQUE NOT NULL,
    published BOOLEAN DEFAULT FALSE,
    author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    view_count INTEGER DEFAULT 0,
    metadata JSONB DEFAULT '{}'::jsonb
);

-- 添加注释
COMMENT ON TABLE posts IS '博客文章表';
COMMENT ON COLUMN posts.title IS '文章标题';

CREATE TABLE products (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL DEFAULT 0,
    stock INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    tags TEXT[] DEFAULT '{}',
    attributes JSONB DEFAULT '{}'::jsonb
);

-- 单列主键
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);

-- 复合主键
CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- UUID主键
CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id BIGINT NOT NULL
);

-- 基础外键
CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT REFERENCES posts(id),
    content TEXT NOT NULL
);

-- 外键选项
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    status TEXT DEFAULT 'pending'
);

-- 外键选项说明
-- ON DELETE CASCADE: 删除父记录时删除子记录
-- ON DELETE SET NULL: 删除父记录时设为NULL
-- ON DELETE RESTRICT: 阻止删除有子记录的父记录
-- ON UPDATE CASCADE: 更新父记录时更新外键

-- 列级唯一约束
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username TEXT UNIQUE
);

-- 表级唯一约束
CREATE TABLE user_profiles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    platform TEXT NOT NULL,
    handle TEXT NOT NULL,
    UNIQUE (platform, handle)
);

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT unique_phone UNIQUE (phone);

-- 列级检查约束
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0)
);

-- 表级检查约束
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    CONSTRAINT valid_dates CHECK (completed_at IS NULL OR completed_at >= created_at)
);

-- 添加检查约束
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);

CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author_id BIGINT NOT NULL REFERENCES users(id),
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 添加单列
ALTER TABLE users ADD COLUMN phone TEXT;

-- 添加带约束的列
ALTER TABLE users ADD COLUMN age INTEGER CHECK (age >= 0);

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- 添加多列
ALTER TABLE users 
    ADD COLUMN bio TEXT,
    ADD COLUMN website TEXT;

-- 修改数据类型
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);

-- 修改默认值
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT false;

-- 删除默认值
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;

-- 设置非空
ALTER TABLE users ALTER COLUMN name SET NOT NULL;

-- 取消非空
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

-- 重命名表
ALTER TABLE users RENAME TO members;

-- 重命名列
ALTER TABLE users RENAME COLUMN name TO full_name;

-- 重命名约束
ALTER TABLE users RENAME CONSTRAINT users_email_key TO unique_email;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 删除列及依赖
ALTER TABLE users DROP COLUMN phone CASCADE;

-- 条件删除
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE posts 
    ADD CONSTRAINT fk_author 
    FOREIGN KEY (author_id) REFERENCES users(id);

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- 添加检查约束
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT unique_email;

-- 条件删除
ALTER TABLE users DROP CONSTRAINT IF EXISTS unique_email;

-- 删除表
DROP TABLE users;

-- 条件删除
DROP TABLE IF EXISTS users;

-- 级联删除
DROP TABLE users CASCADE;

-- 清空表数据
TRUNCATE TABLE users;

-- 重置自增序列
TRUNCATE TABLE users RESTART IDENTITY;

-- 级联清空
TRUNCATE TABLE users CASCADE;

-- 普通索引
CREATE INDEX idx_posts_author ON posts(author_id);

-- 复合索引
CREATE INDEX idx_posts_author_date ON posts(author_id, created_at DESC);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 部分索引
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- GIN索引 (用于JSONB、数组)
CREATE INDEX idx_metadata ON products USING GIN (metadata);

-- 向量索引 (pgvector)
CREATE INDEX idx_embedding ON embeddings USING ivfflat (embedding vector_cosine_ops);

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

-- 删除索引
DROP INDEX idx_posts_author;

-- 条件删除
DROP INDEX IF EXISTS idx_posts_author;

-- 重建索引
REINDEX INDEX idx_posts_author;
REINDEX TABLE posts;

# 创建迁移文件
supabase migration new create_posts_table

-- supabase/migrations/20240101000000_create_posts_table.sql
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    title TEXT NOT NULL,
    content TEXT,
    author_id BIGINT REFERENCES users(id)
);

-- 启用RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY "Users can read own posts" ON posts
    FOR SELECT USING (author_id = auth.uid());

# 应用迁移
supabase db push

// 获取表信息
const { data, error } = await supabase
  .from('posts')
  .select('*')
  .limit(1)

console.log('表结构:', Object.keys(data?.[0] || {}))

表名
├── 使用小写字母
├── 使用下划线分隔
├── 使用复数形式
└── 例如: users, posts, order_items

列名
├── 使用小写字母
├── 使用下划线分隔
├── 使用单数形式
└── 例如: user_id, created_at, is_active

外键
├── 格式: {table}_id
└── 例如: user_id, post_id

-- 标准表模板
CREATE TABLE example (
    -- 主键
    id BIGSERIAL PRIMARY KEY,
    
    -- 时间戳
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    
    -- 软删除
    deleted_at TIMESTAMPTZ,
    
    -- 业务字段
    name TEXT NOT NULL,
    
    -- 外键
    user_id BIGINT REFERENCES users(id),
    
    -- 状态
    status TEXT DEFAULT 'active',
    
    -- 元数据
    metadata JSONB DEFAULT '{}'::jsonb
);

-- 自动更新updated_at触发器
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON example
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

设计原则
├── 合理使用索引
│   ├── 为查询条件创建索引
│   ├── 为排序字段创建索引
│   └── 避免过多索引
│
├── 选择合适的数据类型
│   ├── TEXT vs VARCHAR
│   ├── INTEGER vs BIGINT
│   └── JSONB vs JSON
│
├── 避免过度规范化
│   ├── 适当的冗余
│   └── 预计算统计
│
└── 分区大表
    ├── 按时间分区
    └── 按范围分区

CREATE TABLE profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    username TEXT UNIQUE,
    full_name TEXT,
    avatar_url TEXT,
    website TEXT,
    bio TEXT,
    
    CONSTRAINT username_length CHECK (char_length(username) >= 3)
);

-- 启用RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public profiles are viewable by everyone"
    ON profiles FOR SELECT USING (true);

CREATE POLICY "Users can insert own profile"
    ON profiles FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own profile"
    ON profiles FOR UPDATE USING (auth.uid() = id);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT,
    excerpt TEXT,
    cover_image TEXT,
    published BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMPTZ,
    author_id UUID REFERENCES auth.users(id) NOT NULL,
    view_count INTEGER DEFAULT 0,
    tags TEXT[] DEFAULT '{}',
    
    CONSTRAINT slug_format CHECK (slug ~ '^[a-z0-9-]+$')
);

-- 索引
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published, published_at DESC);
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    content TEXT NOT NULL,
    post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    parent_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,
    
    CONSTRAINT content_length CHECK (char_length(content) >= 1 AND char_length(content) <= 10000)
);

-- 索引
CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_user ON comments(user_id);
CREATE INDEX idx_comments_parent ON comments(parent_id);