数据库基础

Supabase 的核心是一个完整的 PostgreSQL 数据库。本章节介绍如何在 Supabase 中创建和管理数据库表、执行 SQL 查询、以及使用数据库扩展。

Table Editor 可视化操作

Supabase 提供了可视化的表编辑器，即使不熟悉 SQL 也可以创建和管理数据表。

创建数据表

1. 在项目仪表板中点击 "Table Editor"
2. 点击 "Create a new table"
3. 填写表名和字段信息

创建一个用户表作为示例：

字段名	类型	默认值	说明
id	int8	自动生成	主键
email	text	-	邮箱，唯一
name	text	-	用户名
avatar_url	text	-	头像地址
created_at	timestamptz	now()	创建时间

在 "Advanced options" 中可以设置：

• 主键：选择 id 作为主键
• 自增：勾选 "Is Identity"
• 唯一约束：为 email 字段添加唯一约束

数据类型

PostgreSQL 支持丰富的数据类型：

数值类型：

• int2 / smallint：小整数，-32768 到 32767
• int4 / integer：常用整数
• int8 / bigint：大整数
• float4 / real：单精度浮点数
• float8 / double precision：双精度浮点数
• numeric / decimal：精确数值

字符串类型：

• char(n)：定长字符串
• varchar(n)：变长字符串，有长度限制
• text：变长字符串，无长度限制（推荐）

布尔类型：

• boolean：true 或 false

日期时间类型：

• date：日期
• time：时间
• timestamp：时间戳（不带时区）
• timestamptz：时间戳（带时区，推荐）

JSON 类型：

• json：JSON 数据
• jsonb：二进制 JSON，支持索引（推荐）

UUID 类型：

• uuid：通用唯一标识符

数组类型：

• text[]：文本数组
• int[]：整数数组

插入和编辑数据

创建表后，可以直接在 Table Editor 中添加数据：

1. 点击表名进入数据视图
2. 点击 "Insert" → "Insert row"
3. 填写字段值（id 和 created_at 会自动填充）
4. 点击 "Save"

也可以直接编辑现有数据，双击单元格即可修改。

SQL Editor 执行查询

对于复杂的操作，使用 SQL Editor 更加灵活。

基本查询

打开 SQL Editor，执行第一个查询：

-- 查询所有用户
select * from users;

创建表

使用 SQL 创建一个文章表：

create table posts (
  id bigint primary key generated always as identity,
  title text not null,
  content text,
  author_id bigint references users(id),
  status text default 'draft' check (status in ('draft', 'published')),
  published_at timestamptz,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- 创建索引加速查询
create index idx_posts_author on posts(author_id);
create index idx_posts_status on posts(status);

插入数据

-- 插入单条数据
insert into posts (title, content, author_id, status)
values ('第一篇文章', '这是文章内容', 1, 'published');

-- 插入多条数据
insert into posts (title, content, author_id, status) values
  ('学习 Supabase', 'Supabase 是一个强大的后端平台...', 1, 'published'),
  ('PostgreSQL 入门', 'PostgreSQL 是一个功能丰富的数据库...', 1, 'draft'),
  ('实时应用开发', '使用 Supabase Realtime 构建实时应用...', 2, 'published');

查询数据

-- 条件查询
select * from posts where status = 'published';

-- 排序
select * from posts order by created_at desc;

-- 分页
select * from posts 
order by created_at desc 
limit 10 offset 0;

-- 联表查询
select 
  posts.id,
  posts.title,
  users.name as author_name,
  posts.created_at
from posts
join users on posts.author_id = users.id
where posts.status = 'published';

-- 聚合查询
select 
  author_id,
  count(*) as post_count,
  count(*) filter (where status = 'published') as published_count
from posts
group by author_id;

更新数据

-- 更新单条记录
update posts 
set status = 'published', 
    published_at = now(),
    updated_at = now()
where id = 2;

-- 批量更新
update posts 
set updated_at = now()
where author_id = 1;

删除数据

-- 删除单条记录
delete from posts where id = 1;

-- 条件删除
delete from posts where status = 'draft' and created_at < now() - interval '30 days';

数据库扩展

Supabase 支持丰富的 PostgreSQL 扩展，可以在 Database → Extensions 中启用。

常用扩展

uuid-ossp：生成 UUID

-- 启用扩展
create extension if not exists "uuid-ossp";

-- 使用函数生成 UUID
select uuid_generate_v4();

-- 在表中使用
create table sessions (
  id uuid primary key default uuid_generate_v4(),
  user_id bigint references users(id),
  created_at timestamptz default now()
);

pgcrypto：加密函数

create extension if not exists pgcrypto;

-- 密码哈希
select crypt('password123', gen_salt('bf'));

-- 验证密码
select crypt('password123', stored_hash) = stored_hash;

pgvector：向量存储和搜索（AI 应用必备）

create extension if not exists vector;

-- 创建带向量列的表
create table documents (
  id bigint primary key generated always as identity,
  content text,
  embedding vector(1536)
);

-- 创建向量索引
create index on documents 
using ivfflat (embedding vector_cosine_ops)
with (lists = 100);

-- 向量搜索
select id, content
from documents
order by embedding <=> '[1,2,3,...]'::vector
limit 10;

postgis：地理信息处理

create extension if not exists postgis;

-- 创建地理位置列
alter table users 
add column location geography(point, 4326);

-- 查询附近用户
select id, name
from users
where st_dwithin(
  location,
  st_makepoint(116.4, 39.9)::geography,
  5000  -- 5公里内
);

pg_cron：定时任务

create extension if not exists pg_cron;

-- 每天凌晨清理过期数据
select cron.schedule(
  'cleanup_expired_sessions',
  '0 0 * * *',
  $$delete from sessions where created_at < now() - interval '7 days'$$
);

视图和函数

创建视图

视图可以简化复杂查询：

-- 创建已发布文章视图
create view published_posts as
select 
  posts.id,
  posts.title,
  posts.content,
  posts.published_at,
  users.name as author_name,
  users.email as author_email
from posts
join users on posts.author_id = users.id
where posts.status = 'published';

-- 查询视图
select * from published_posts order by published_at desc;

创建函数

PostgreSQL 函数可以在数据库层面实现业务逻辑：

-- 创建更新时间触发器函数
create or replace function update_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- 创建触发器
create trigger posts_updated_at
  before update on posts
  for each row
  execute function update_updated_at();

统计函数示例：

-- 获取用户统计信息
create or replace function get_user_stats(user_id bigint)
returns json as $$
declare
  result json;
begin
  select json_build_object(
    'total_posts', (select count(*) from posts where author_id = user_id),
    'published_posts', (select count(*) from posts where author_id = user_id and status = 'published'),
    'draft_posts', (select count(*) from posts where author_id = user_id and status = 'draft')
  ) into result;
  return result;
end;
$$ language plpgsql;

-- 调用函数
select get_user_stats(1);

数据导入导出

导入 CSV

Supabase 支持直接导入 CSV 文件：

1. 在 Table Editor 中点击 "Import data from CSV"
2. 选择 CSV 文件
3. 映射列名到表字段
4. 点击 "Import"

也可以使用 SQL：

-- 使用 copy 命令（需要超级用户权限）
-- 通常通过 Supabase Dashboard 导入更方便

导出数据

在 Table Editor 中点击 "Export" 可以导出为 CSV 或 JSON 格式。

也可以使用 SQL：

-- 导出为 JSON
select json_agg(t) from (
  select * from users
) t;

数据库备份

Supabase 自动每天备份付费项目的数据库，免费项目需要手动备份。

手动备份

使用 pg_dump 命令：

pg_dump "postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres" > backup.sql

恢复数据

psql "postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres" < backup.sql

性能优化

索引优化

为常用查询条件创建索引：

-- 单列索引
create index idx_posts_status on posts(status);

-- 复合索引
create index idx_posts_author_status on posts(author_id, status);

-- 部分索引（只索引部分数据）
create index idx_posts_published on posts(created_at) 
where status = 'published';

-- 全文搜索索引
create index idx_posts_title_search on posts using gin(to_tsvector('english', title));

查询优化

使用 explain analyze 分析查询：

explain analyze select * from posts where status = 'published';

查看执行计划，找出慢查询的原因：

• 是否使用了索引扫描
• 是否有全表扫描
• 连接顺序是否合理

连接池

Supabase 自动提供连接池，使用端口 6543：

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

连接池模式适合 Serverless 环境，可以复用连接，避免连接数过多。

常见问题

表名大小写问题

PostgreSQL 会将未加引号的标识符转为小写。如果表名包含大写字母：

-- 错误：会查找 users 表
select * from Users;

-- 正确：使用双引号
select * from "Users";

建议：统一使用小写表名和字段名，用下划线分隔单词。

时区问题

使用 timestamptz 而不是 timestamp，它会自动处理时区转换：

-- 创建带时区的时间戳
created_at timestamptz default now()

-- 查询时转换时区
select created_at at time zone 'Asia/Shanghai' from posts;

空值处理

PostgreSQL 中空值需要特殊处理：

-- 错误：null 不等于 null
select * from posts where author_id = null;

-- 正确：使用 is null
select * from posts where author_id is null;

-- 使用 coalesce 提供默认值
select coalesce(title, '无标题') from posts;

下一步

掌握数据库基础后，你可以继续学习：

• REST API - 使用自动生成的 API 操作数据
• 行级安全策略 - 控制数据访问权限
• 向量搜索 - 使用 pgvector 构建 AI 应用