supabase-database（Supabase 数据库）

Supabase Database Skill

Database operations, queries, and Row Level Security.

Quick Reference

Basic Queries

Select

// All rows
const { data, error } = await supabase
  .from('users')
  .select('*')

// Specific columns
const { data, error } = await supabase
  .from('users')
  .select('id, name, email')

// With count
const { data, count, error } = await supabase
  .from('users')
  .select('*', { count: 'exact' })

Insert

// Single row
const { data, error } = await supabase
  .from('users')
  .insert({ name: 'John', email: 'john@example.com' })
  .select()

// Multiple rows
const { data, error } = await supabase
  .from('users')
  .insert([
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' }
  ])
  .select()

Update

const { data, error } = await supabase
  .from('users')
  .update({ name: 'John Doe' })
  .eq('id', 1)
  .select()

Upsert

const { data, error } = await supabase
  .from('users')
  .upsert({ id: 1, name: 'John', email: 'john@example.com' })
  .select()

Delete

const { error } = await supabase
  .from('users')
  .delete()
  .eq('id', 1)

Filters

Comparison Operators

// Equal
.eq('col', 'value')

// Not equal
.neq('col', 'value')

// Greater than
.gt('col', 10)

// Greater or equal
.gte('col', 10)

// Less than
.lt('col', 10)

// Less or equal
.lte('col', 10)

Pattern Matching

// LIKE (case sensitive)
.like('name', '%John%')

// ILIKE (case insensitive)
.ilike('name', '%john%')

List Operations

// IN array
.in('status', ['active', 'pending'])

// Contains (array column contains value)
.contains('tags', ['sports', 'news'])

// Contained by (value contained by array column)
.containedBy('tags', ['sports', 'news', 'tech'])

// Overlaps (any match)
.overlaps('tags', ['sports', 'tech'])

Range Operations

// Between (exclusive)
.range('price', 10, 100)

// In range type column
.rangeGt('date_range', '2025-01-01')
.rangeLt('date_range', '2025-12-31')

Null Checks

// Is null
.is('deleted_at', null)

// Is not null
.not('deleted_at', 'is', null)

Boolean Operators

// AND (chain filters)
.eq('status', 'active')
.eq('verified', true)

// OR
.or('status.eq.active,status.eq.pending')

// NOT
.not('status', 'eq', 'deleted')

Ordering & Pagination

// Order by
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

// Multiple order
.order('category', { ascending: true })
.order('created_at', { ascending: false })

// Limit
.limit(10)

// Range (pagination)
.range(0, 9)  // First 10 rows

// Single row
.single()

// Maybe single (0 or 1)
.maybeSingle()

Relations (Joins)

One-to-Many

// Users with their posts
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts (
      id,
      title,
      content
    )
  `)

Many-to-One

// Posts with author
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    users (
      id,
      name
    )
  `)

Inner Join

// Only users with posts
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts!inner (
      id,
      title
    )
  `)

Many-to-Many

// Posts with tags through junction table
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    post_tags (
      tags (
        id,
        name
      )
    )
  `)

Row Level Security (RLS)

Enable RLS

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Basic Policies

-- Users can read their own data
CREATE POLICY "Users can view own data"
ON users FOR SELECT
TO authenticated
USING (auth.uid() = id);

-- Users can insert their own data
CREATE POLICY "Users can insert own data"
ON users FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = id);

-- Users can update their own data
CREATE POLICY "Users can update own data"
ON users FOR UPDATE
TO authenticated
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Users can delete their own data
CREATE POLICY "Users can delete own data"
ON users FOR DELETE
TO authenticated
USING (auth.uid() = id);

Helper Functions

-- Current user ID
auth.uid()

-- Current user role (anon, authenticated, service_role)
auth.role()

-- Full JWT as JSON
auth.jwt()

-- Check specific JWT claim
auth.jwt()->>'email'
auth.jwt()->'app_metadata'->>'role'

Performance Optimization

-- Wrap auth functions in SELECT for performance
CREATE POLICY "Fast policy"
ON users FOR SELECT
TO authenticated
USING ((SELECT auth.uid()) = user_id);

-- Add indexes for RLS columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

RPC (Remote Procedure Call)

Define Function

CREATE OR REPLACE FUNCTION search_users(query text)
RETURNS TABLE(id uuid, name text, email text)
LANGUAGE sql STABLE
AS $$
  SELECT id, name, email
  FROM users
  WHERE name ILIKE '%' || query || '%'
     OR email ILIKE '%' || query || '%'
  ORDER BY name;
$$;

Call Function

const { data, error } = await supabase
  .rpc('search_users', { query: 'john' })

TypeScript Types

Generate Types

supabase gen types typescript --local > database.types.ts

Use Types

import { Database } from './database.types'

type User = Database['public']['Tables']['users']['Row']
type NewUser = Database['public']['Tables']['users']['Insert']
type UpdateUser = Database['public']['Tables']['users']['Update']

const supabase = createClient<Database>(url, key)

const { data } = await supabase
  .from('users')
  .select('*')
// data is User[] | null

References

• rls-policies.md - Complete RLS patterns
• query-operators.md - All filter operators
• postgres-functions.md - Writing SQL functions