supabase（Supabase 后端开发）

Supabase CLI

Interact with Supabase projects: queries and schema management. The script auto-loads .env.supabase.* files as needed.

Quick Commands

# SQL query (management API, returns results)
scripts/supabase.sh sql "SELECT * FROM users LIMIT 5"

# SQL file (management API)
scripts/supabase.sh sql-file ./migrations/001_init.sql

Commands Reference

sql - Run raw SQL via management API (returns results)

scripts/supabase.sh sql "<SQL>"

# Examples
scripts/supabase.sh sql "SELECT COUNT(*) FROM users"
scripts/supabase.sh sql "CREATE TABLE items (id serial primary key, name text)"
scripts/supabase.sh sql "SELECT * FROM users WHERE created_at > '2024-01-01'"
scripts/supabase.sh sql "INSERT INTO users (name, email) VALUES ('Alice', 'alice@test.com')"
scripts/supabase.sh sql "UPDATE users SET status = 'inactive' WHERE id = '123'"
scripts/supabase.sh sql "DELETE FROM sessions WHERE expires_at < now()"

sql-file - Run raw SQL from a file via management API

scripts/supabase.sh sql-file <path>

# Example
scripts/supabase.sh sql-file ./migrations/001_init.sql

Common Operations via sql/sql-file

DDL (schema changes)

# Create table
scripts/supabase.sh sql "CREATE TABLE public.items (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL);"

# Alter table
scripts/supabase.sh sql "ALTER TABLE public.items ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();"

# Drop table
scripts/supabase.sh sql "DROP TABLE public.items;"

# Enable extension
scripts/supabase.sh sql "CREATE EXTENSION IF NOT EXISTS vector;"

Views

scripts/supabase.sh sql "CREATE OR REPLACE VIEW public.active_items AS SELECT * FROM public.items WHERE deleted_at IS NULL;"

Functions / RPC

scripts/supabase.sh sql \"CREATE OR REPLACE FUNCTION public.ping() RETURNS text LANGUAGE sql AS $$ SELECT 'ok'::text; $$;\"
scripts/supabase.sh sql \"GRANT EXECUTE ON FUNCTION public.ping() TO authenticated;\"

Triggers

scripts/supabase.sh sql \"CREATE OR REPLACE FUNCTION public.set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;\"
scripts/supabase.sh sql \"CREATE TRIGGER items_set_updated_at BEFORE UPDATE ON public.items FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();\"

RLS (Row Level Security)

# Enable RLS
scripts/supabase.sh sql "ALTER TABLE public.items ENABLE ROW LEVEL SECURITY;"

# Example policy (owners can read)
scripts/supabase.sh sql \"CREATE POLICY \\\"items_read_own\\\" ON public.items FOR SELECT TO authenticated USING (owner_id = auth.uid());\"

# Example policy (owners can write)
scripts/supabase.sh sql \"CREATE POLICY \\\"items_write_own\\\" ON public.items FOR INSERT TO authenticated WITH CHECK (owner_id = auth.uid());\"

Storage Buckets (metadata only; file upload uses Storage API)

# Create bucket
scripts/supabase.sh sql \"INSERT INTO storage.buckets (id, name, public) VALUES ('payment-proofs', 'payment-proofs', false);\"

# Toggle public
scripts/supabase.sh sql \"UPDATE storage.buckets SET public = true WHERE id = 'payment-proofs';\"

# Delete bucket metadata (does not delete files)
scripts/supabase.sh sql \"DELETE FROM storage.buckets WHERE id = 'payment-proofs';\"

Storage RLS Policies

# Enable RLS (if not already enabled)
scripts/supabase.sh sql "ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;"

# Allow authenticated users to read from a specific bucket
scripts/supabase.sh sql \"CREATE POLICY \\\"read_payment_proofs\\\" ON storage.objects FOR SELECT TO authenticated USING (bucket_id = 'payment-proofs');\"

# Allow authenticated users to upload to a specific bucket
scripts/supabase.sh sql \"CREATE POLICY \\\"write_payment_proofs\\\" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'payment-proofs');\"

Introspection / Debugging

# List public tables
scripts/supabase.sh sql \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;\"

# List columns for a table
scripts/supabase.sh sql \"SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'items' ORDER BY ordinal_position;\"

# Show policies
scripts/supabase.sh sql \"SELECT schemaname, tablename, policyname, roles, cmd, qual, with_check FROM pg_policies ORDER BY schemaname, tablename, policyname;\"

Notes

• sql / sql-file run with management API privileges; treat like admin access