Back to Details

supabase-expert

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Supabase Integration Expert Skill

Supabase集成专家技能

Overview

概述

This skill helps you build secure, scalable Supabase integrations. Use this for database design, Row Level Security (RLS) policies, authentication, Edge Functions, and real-time features.
本技能可帮助您构建安全、可扩展的Supabase集成。适用于数据库设计、行级安全（RLS）策略、身份验证、Edge Functions和实时功能开发场景。

Core Principles

核心原则

1. Security First

1. 安全优先

  • Always enable RLS on tables with user data
  • Use service role key only in secure server contexts
  • Use anon key for client-side operations
  • Test policies thoroughly
  • 对包含用户数据的表始终启用RLS
  • 仅在安全的服务器环境中使用服务角色密钥
  • 客户端操作使用匿名密钥
  • 全面测试策略

2. Type Safety

2. 类型安全

  • Generate TypeScript types from schema
  • Use generated types in application
  • Keep types in sync with schema changes
  • 从模式生成TypeScript类型
  • 在应用中使用生成的类型
  • 保持类型与模式变更同步

3. Performance

3. 性能优化

  • Use indexes for frequently queried columns
  • Implement pagination for large datasets
  • Use select() to limit returned fields
  • Cache when appropriate
  • 为频繁查询的列创建索引
  • 对大型数据集实现分页
  • 使用select()限制返回字段
  • 适时使用缓存

Database Schema Design

数据库模式设计

Basic Table Creation

基础表创建

sql
-- Create a table with standard fields
create table public.items (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null,
  description text,
  status text default 'draft' check (status in ('draft', 'published', 'archived'))
);

-- Create updated_at trigger
create or replace function public.handle_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger set_updated_at
  before update on public.items
  for each row
  execute function public.handle_updated_at();

-- Create index
create index items_user_id_idx on public.items(user_id);
create index items_status_idx on public.items(status);
sql
-- Create a table with standard fields
create table public.items (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null,
  description text,
  status text default 'draft' check (status in ('draft', 'published', 'archived'))
);

-- Create updated_at trigger
create or replace function public.handle_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger set_updated_at
  before update on public.items
  for each row
  execute function public.handle_updated_at();

-- Create index
create index items_user_id_idx on public.items(user_id);
create index items_status_idx on public.items(status);

Foreign Keys & Relations

外键与关系

sql
-- One-to-many relationship
create table public.comments (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  item_id uuid references public.items(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  content text not null
);

-- Many-to-many relationship
create table public.item_tags (
  item_id uuid references public.items(id) on delete cascade,
  tag_id uuid references public.tags(id) on delete cascade,
  primary key (item_id, tag_id)
);
sql
-- One-to-many relationship
create table public.comments (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  item_id uuid references public.items(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  content text not null
);

-- Many-to-many relationship
create table public.item_tags (
  item_id uuid references public.items(id) on delete cascade,
  tag_id uuid references public.tags(id) on delete cascade,
  primary key (item_id, tag_id)
);

Row Level Security (RLS)

行级安全（RLS）

Basic RLS Patterns

基础RLS模式

sql
-- Enable RLS
alter table public.items enable row level security;

-- Users can read their own items
create policy "Users can read own items"
  on public.items for select
  using (auth.uid() = user_id);

-- Users can insert their own items
create policy "Users can insert own items"
  on public.items for insert
  with check (auth.uid() = user_id);

-- Users can update their own items
create policy "Users can update own items"
  on public.items for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Users can delete their own items
create policy "Users can delete own items"
  on public.items for delete
  using (auth.uid() = user_id);
sql
-- Enable RLS
alter table public.items enable row level security;

-- Users can read their own items
create policy "Users can read own items"
  on public.items for select
  using (auth.uid() = user_id);

-- Users can insert their own items
create policy "Users can insert own items"
  on public.items for insert
  with check (auth.uid() = user_id);

-- Users can update their own items
create policy "Users can update own items"
  on public.items for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Users can delete their own items
create policy "Users can delete own items"
  on public.items for delete
  using (auth.uid() = user_id);

Advanced RLS Patterns

高级RLS模式

sql
-- Public read, authenticated write
create policy "Anyone can read published items"
  on public.items for select
  using (status = 'published');

create policy "Authenticated users can insert"
  on public.items for insert
  to authenticated
  with check (true);

-- Role-based access
create policy "Admins can do everything"
  on public.items for all
  using (
    exists (
      select 1 from public.user_roles
      where user_id = auth.uid()
      and role = 'admin'
    )
  );

-- Shared access
create policy "Users can read shared items"
  on public.items for select
  using (
    auth.uid() = user_id
    or exists (
      select 1 from public.item_shares
      where item_id = items.id
      and shared_with = auth.uid()
    )
  );
sql
-- Public read, authenticated write
create policy "Anyone can read published items"
  on public.items for select
  using (status = 'published');

create policy "Authenticated users can insert"
  on public.items for insert
  to authenticated
  with check (true);

-- Role-based access
create policy "Admins can do everything"
  on public.items for all
  using (
    exists (
      select 1 from public.user_roles
      where user_id = auth.uid()
      and role = 'admin'
    )
  );

-- Shared access
create policy "Users can read shared items"
  on public.items for select
  using (
    auth.uid() = user_id
    or exists (
      select 1 from public.item_shares
      where item_id = items.id
      and shared_with = auth.uid()
    )
  );

Anonymous/Guest Access

匿名/访客访问

sql
-- Allow anonymous reads
create policy "Anonymous can read public content"
  on public.items for select
  to anon
  using (status = 'published');

-- Allow anonymous inserts (for guest mode)
create policy "Anonymous can create items"
  on public.items for insert
  to anon
  with check (true);
sql
-- Allow anonymous reads
create policy "Anonymous can read public content"
  on public.items for select
  to anon
  using (status = 'published');

-- Allow anonymous inserts (for guest mode)
create policy "Anonymous can create items"
  on public.items for insert
  to anon
  with check (true);

Client Integration

客户端集成

Setup Client (Next.js)

客户端设置（Next.js）

typescript
// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'

export function createClient() {
  return createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export function createServerClient() {
  const cookieStore = cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
      },
    }
  )
}
typescript
// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'

export function createClient() {
  return createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export function createServerClient() {
  const cookieStore = cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
      },
    }
  )
}

CRUD Operations

CRUD操作

typescript
// Query data
const { data, error } = await supabase
  .from('items')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// Insert data
const { data, error } = await supabase
  .from('items')
  .insert({ title: 'New Item', user_id: userId })
  .select()
  .single()

// Update data
const { data, error } = await supabase
  .from('items')
  .update({ title: 'Updated Title' })
  .eq('id', itemId)
  .select()
  .single()

// Delete data
const { error } = await supabase
  .from('items')
  .delete()
  .eq('id', itemId)

// Complex joins
const { data, error } = await supabase
  .from('items')
  .select(`
    *,
    comments (
      id,
      content,
      user:user_id (
        email
      )
    )
  `)
  .eq('user_id', userId)
typescript
// Query data
const { data, error } = await supabase
  .from('items')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// Insert data
const { data, error } = await supabase
  .from('items')
  .insert({ title: 'New Item', user_id: userId })
  .select()
  .single()

// Update data
const { data, error } = await supabase
  .from('items')
  .update({ title: 'Updated Title' })
  .eq('id', itemId)
  .select()
  .single()

// Delete data
const { error } = await supabase
  .from('items')
  .delete()
  .eq('id', itemId)

// Complex joins
const { data, error } = await supabase
  .from('items')
  .select(`
    *,
    comments (
      id,
      content,
      user:user_id (
        email
      )
    )
  `)
  .eq('user_id', userId)

Real-time Subscriptions

实时订阅

typescript
// Subscribe to changes
const channel = supabase
  .channel('items-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'items',
      filter: `user_id=eq.${userId}`,
    },
    (payload) => {
      console.log('Change received!', payload)
      // Update local state
    }
  )
  .subscribe()

// Cleanup
channel.unsubscribe()
typescript
// Subscribe to changes
const channel = supabase
  .channel('items-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'items',
      filter: `user_id=eq.${userId}`,
    },
    (payload) => {
      console.log('Change received!', payload)
      // Update local state
    }
  )
  .subscribe()

// Cleanup
channel.unsubscribe()

Authentication

身份验证

Email/Password Auth

邮箱/密码认证

typescript
// Sign up
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'password123',
  options: {
    data: {
      display_name: 'User Name',
    },
  },
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: 'user@example.com',
  password: 'password123',
})

// Sign out
const { error } = await supabase.auth.signOut()

// Get current user
const { data: { user } } = await supabase.auth.getUser()
typescript
// Sign up
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'password123',
  options: {
    data: {
      display_name: 'User Name',
    },
  },
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: 'user@example.com',
  password: 'password123',
})

// Sign out
const { error } = await supabase.auth.signOut()

// Get current user
const { data: { user } } = await supabase.auth.getUser()

OAuth Providers

OAuth提供商

typescript
// Google OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: `${window.location.origin}/auth/callback`,
  },
})

// Handle callback
// app/auth/callback/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const code = searchParams.get('code')

  if (code) {
    const supabase = createServerClient()
    await supabase.auth.exchangeCodeForSession(code)
  }

  return NextResponse.redirect(new URL('/dashboard', request.url))
}
typescript
// Google OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: `${window.location.origin}/auth/callback`,
  },
})

// Handle callback
// app/auth/callback/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const code = searchParams.get('code')

  if (code) {
    const supabase = createServerClient()
    await supabase.auth.exchangeCodeForSession(code)
  }

  return NextResponse.redirect(new URL('/dashboard', request.url))
}

Auth Middleware

认证中间件

typescript
// middleware.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  const response = NextResponse.next()

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: any) {
          response.cookies.set(name, value, options)
        },
        remove(name: string, options: any) {
          response.cookies.set(name, '', { ...options, maxAge: 0 })
        },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Redirect to login if not authenticated
  if (!user && request.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  return response
}

export const config = {
  matcher: ['/dashboard/:path*'],
}
typescript
// middleware.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  const response = NextResponse.next()

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: any) {
          response.cookies.set(name, value, options)
        },
        remove(name: string, options: any) {
          response.cookies.set(name, '', { ...options, maxAge: 0 })
        },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Redirect to login if not authenticated
  if (!user && request.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  return response
}

export const config = {
  matcher: ['/dashboard/:path*'],
}

Edge Functions

Edge Functions

Basic Edge Function

基础Edge Function

typescript
// supabase/functions/hello/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  try {
    // Get Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      {
        auth: {
          autoRefreshToken: false,
          persistSession: false,
        },
      }
    )

    // Get user from auth header
    const authHeader = req.headers.get('Authorization')
    const token = authHeader?.replace('Bearer ', '')
    const { data: { user } } = await supabase.auth.getUser(token)

    if (!user) {
      return new Response(
        JSON.stringify({ error: 'Unauthorized' }),
        { status: 401, headers: { 'Content-Type': 'application/json' } }
      )
    }

    // Your logic here
    const { data, error } = await supabase
      .from('items')
      .select('*')
      .eq('user_id', user.id)

    return new Response(
      JSON.stringify({ data }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500, headers: { 'Content-Type': 'application/json' } }
    )
  }
})
typescript
// supabase/functions/hello/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  try {
    // Get Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      {
        auth: {
          autoRefreshToken: false,
          persistSession: false,
        },
      }
    )

    // Get user from auth header
    const authHeader = req.headers.get('Authorization')
    const token = authHeader?.replace('Bearer ', '')
    const { data: { user } } = await supabase.auth.getUser(token)

    if (!user) {
      return new Response(
        JSON.stringify({ error: 'Unauthorized' }),
        { status: 401, headers: { 'Content-Type': 'application/json' } }
      )
    }

    // Your logic here
    const { data, error } = await supabase
      .from('items')
      .select('*')
      .eq('user_id', user.id)

    return new Response(
      JSON.stringify({ data }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500, headers: { 'Content-Type': 'application/json' } }
    )
  }
})

Type Generation

类型生成

bash
undefined
bash
undefined

Generate TypeScript types

Generate TypeScript types

npx supabase gen types typescript --project-id your-project-id > types/supabase.ts
npx supabase gen types typescript --project-id your-project-id > types/supabase.ts

Use in code

Use in code

import { Database } from '@/types/supabase'
type Item = Database['public']['Tables']['items']['Row'] type ItemInsert = Database['public']['Tables']['items']['Insert'] type ItemUpdate = Database['public']['Tables']['items']['Update']
undefined
import { Database } from '@/types/supabase'
type Item = Database['public']['Tables']['items']['Row'] type ItemInsert = Database['public']['Tables']['items']['Insert'] type ItemUpdate = Database['public']['Tables']['items']['Update']
undefined

Common Patterns

通用模式

Soft Deletes

软删除

sql
alter table public.items add column deleted_at timestamp with time zone;

create policy "Users cannot see deleted items"
  on public.items for select
  using (deleted_at is null);

-- Soft delete function
create or replace function soft_delete_item(item_id uuid)
returns void as $$
begin
  update public.items
  set deleted_at = now()
  where id = item_id;
end;
$$ language plpgsql security definer;
sql
alter table public.items add column deleted_at timestamp with time zone;

create policy "Users cannot see deleted items"
  on public.items for select
  using (deleted_at is null);

-- Soft delete function
create or replace function soft_delete_item(item_id uuid)
returns void as $$
begin
  update public.items
  set deleted_at = now()
  where id = item_id;
end;
$$ language plpgsql security definer;

Audit Logs

审计日志

sql
create table public.audit_logs (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  user_id uuid references auth.users(id),
  table_name text not null,
  record_id uuid not null,
  action text not null,
  changes jsonb
);

-- Trigger function
create or replace function public.audit_trigger()
returns trigger as $$
begin
  insert into public.audit_logs (user_id, table_name, record_id, action, changes)
  values (
    auth.uid(),
    TG_TABLE_NAME,
    NEW.id,
    TG_OP,
    to_jsonb(NEW) - to_jsonb(OLD)
  );
  return NEW;
end;
$$ language plpgsql security definer;
sql
create table public.audit_logs (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  user_id uuid references auth.users(id),
  table_name text not null,
  record_id uuid not null,
  action text not null,
  changes jsonb
);

-- Trigger function
create or replace function public.audit_trigger()
returns trigger as $$
begin
  insert into public.audit_logs (user_id, table_name, record_id, action, changes)
  values (
    auth.uid(),
    TG_TABLE_NAME,
    NEW.id,
    TG_OP,
    to_jsonb(NEW) - to_jsonb(OLD)
  );
  return NEW;
end;
$$ language plpgsql security definer;

Troubleshooting

故障排除

Common Issues

常见问题

  1. 401 Errors: Check RLS policies, ensure user is authenticated
  2. 403 Errors: RLS policy blocking operation
  3. Row not found: Policy may be filtering it out
  4. Connection issues: Check URL and API keys
  5. Type mismatches: Regenerate types after schema changes
  1. 401错误: 检查RLS策略，确保用户已认证
  2. 403错误: RLS策略阻止了操作
  3. 未找到行: 策略可能过滤掉了该行
  4. 连接问题: 检查URL和API密钥
  5. 类型不匹配: 模式变更后重新生成类型

Debugging RLS

RLS调试

sql
-- Test as specific user
set request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Check what policies apply
select * from pg_policies where tablename = 'items';

-- Disable RLS temporarily (for testing only!)
alter table public.items disable row level security;
sql
-- Test as specific user
set request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Check what policies apply
select * from pg_policies where tablename = 'items';

-- Disable RLS temporarily (for testing only!)
alter table public.items disable row level security;

Best Practices Checklist

最佳实践检查表

  • Enable RLS on all tables with user data
  • Create indexes for foreign keys and frequently queried columns
  • Use UUID for primary keys
  • Add created_at and updated_at timestamps
  • Implement soft deletes for important data
  • Use check constraints for enum-like fields
  • Generate and use TypeScript types
  • Test RLS policies thoroughly
  • Use service role key only server-side
  • Implement proper error handling
  • Add audit logs for sensitive operations
  • Use transactions for multi-step operations
  • 对所有含用户数据的表启用RLS
  • 为外键和频繁查询的列创建索引
  • 使用UUID作为主键
  • 添加created_at和updated_at时间戳
  • 为重要数据实现软删除
  • 为枚举类字段使用检查约束
  • 生成并使用TypeScript类型
  • 全面测试RLS策略
  • 仅在服务器端使用服务角色密钥
  • 实现适当的错误处理
  • 为敏感操作添加审计日志
  • 对多步骤操作使用事务

When to Use This Skill

何时使用本技能

Invoke this skill when:
  • Designing database schemas
  • Creating or debugging RLS policies
  • Setting up authentication
  • Building Edge Functions
  • Implementing real-time features
  • Troubleshooting Supabase issues
  • Optimizing database queries
  • Setting up type generation
在以下场景调用本技能：
  • 设计数据库模式
  • 创建或调试RLS策略
  • 设置身份验证
  • 构建Edge Functions
  • 实现实时功能
  • 排查Supabase问题
  • 优化数据库查询
  • 设置类型生成