Supabase

# Supabase CLI

Interact with Supabase projects: queries, CRUD, vector search, and table management.

## Setup

```bash # Required export SUPABASE_URL="https://yourproject.supabase.co" export SUPABASE_SERVICE_KEY="eyJhbGciOiJIUzI1NiIs..."

# Optional: for management API export SUPABASE_ACCESS_TOKEN="sbp_xxxxx" ```

## Quick Commands

```bash # SQL query {baseDir}/scripts/supabase.sh query "SELECT * FROM users LIMIT 5"

# Insert data {baseDir}/scripts/supabase.sh insert users '{"name": "John", "email": "[email protected]"}'

# Select with filters {baseDir}/scripts/supabase.sh select users --eq "status:active" --limit 10

# Update {baseDir}/scripts/supabase.sh update users '{"status": "inactive"}' --eq "id:123"

# Delete {baseDir}/scripts/supabase.sh delete users --eq "id:123"

# Vector similarity search {baseDir}/scripts/supabase.sh vector-search documents "search query" --match-fn match_documents --limit 5

# List tables {baseDir}/scripts/supabase.sh tables

# Describe table {baseDir}/scripts/supabase.sh describe users ```

## Commands Reference

### query - Run raw SQL

```bash {baseDir}/scripts/supabase.sh query "<SQL>"

# Examples {baseDir}/scripts/supabase.sh query "SELECT COUNT(*) FROM users" {baseDir}/scripts/supabase.sh query "CREATE TABLE items (id serial primary key, name text)" {baseDir}/scripts/supabase.sh query "SELECT * FROM users WHERE created_at > '2024-01-01'" ```

### select - Query table with filters

```bash {baseDir}/scripts/supabase.sh select <table> [options]

Options: --columns <cols> Comma-separated columns (default: *) --eq <col:val> Equal filter (can use multiple) --neq <col:val> Not equal filter --gt <col:val> Greater than --lt <col:val> Less than --like <col:val> Pattern match (use % for wildcard) --limit <n> Limit results --offset <n> Offset results --order <col> Order by column --desc Descending order

# Examples {baseDir}/scripts/supabase.sh select users --eq "status:active" --limit 10 {baseDir}/scripts/supabase.sh select posts --columns "id,title,created_at" --order created_at --desc {baseDir}/scripts/supabase.sh select products --gt "price:100" --lt "price:500" ```

### insert - Insert row(s)

```bash {baseDir}/scripts/supabase.sh insert <table> '<json>'

# Single row {baseDir}/scripts/supabase.sh insert users '{"name": "Alice", "email": "[email protected]"}'

# Multiple rows {baseDir}/scripts/supabase.sh insert users '[{"name": "Bob"}, {"name": "Carol"}]' ```

### update - Update rows

```bash {baseDir}/scripts/supabase.sh update <table> '<json>' --eq <col:val>

# Example {baseDir}/scripts/supabase.sh update users '{"status": "inactive"}' --eq "id:123" {baseDir}/scripts/supabase.sh update posts '{"published": true}' --eq "author_id:5" ```

### upsert - Insert or update

```bash {baseDir}/scripts/supabase.sh upsert <table> '<json>'

# Example (requires unique constraint) {baseDir}/scripts/supabase.sh upsert users '{"id": 1, "name": "Updated Name"}' ```

### delete - Delete rows

```bash {baseDir}/scripts/supabase.sh delete <table> --eq <col:val>

# Example {baseDir}/scripts/supabase.sh delete sessions --lt "expires_at:2024-01-01" ```

### vector-search - Similarity search with pgvector

```bash {baseDir}/scripts/supabase.sh vector-search <table> "<query>" [options]

Options: --match-fn <name> RPC function name (default: match_<table>) --limit <n> Number of results (default: 5) --threshold <n> Similarity threshold 0-1 (default: 0.5) --embedding-model <m> Model for query embedding (default: uses OpenAI)

# Example {baseDir}/scripts/supabase.sh vector-search documents "How to set up authentication" --limit 10

# Requires a match function like: # CREATE FUNCTION match_documents(query_embedding vector(1536), match_threshold float, match_count int) ```

### tables - List all tables

```bash {baseDir}/scripts/supabase.sh tables ```

### describe - Show table schema

```bash {baseDir}/scripts/supabase.sh describe <table> ```

### rpc - Call stored procedure

```bash {baseDir}/scripts/supabase.sh rpc <function_name> '<json_params>'

# Example {baseDir}/scripts/supabase.sh rpc get_user_stats '{"user_id": 123}' ```

## Vector Search Setup

### 1. Enable pgvector extension

```sql CREATE EXTENSION IF NOT EXISTS vector; ```

### 2. Create table with embedding column

```sql CREATE TABLE documents ( id bigserial PRIMARY KEY, content text, metadata jsonb, embedding vector(1536) ); ```

### 3. Create similarity search function

```sql CREATE OR REPLACE FUNCTION match_documents( query_embedding vector(1536), match_threshold float DEFAULT 0.5, match_count int DEFAULT 5 ) RETURNS TABLE ( id bigint, content text, metadata jsonb, similarity float ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT documents.id, documents.content, documents.metadata, 1 - (documents.embedding <=> query_embedding) AS similarity FROM documents WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold ORDER BY documents.embedding <=> query_embedding LIMIT match_count; END; $$; ```

### 4. Create index for performance

```sql CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); ```

## Environment Variables

| Variable | Required | Description | |----------|----------|-------------| | SUPABASE_URL | Yes | Project URL (https://xxx.supabase.co) | | SUPABASE_SERVICE_KEY | Yes | Service role key (full access) | | SUPABASE_ANON_KEY | No | Anon key (restricted access) | | SUPABASE_ACCESS_TOKEN | No | Management API token | | OPENAI_API_KEY | No | For generating embeddings |

## Notes

- Service role key bypasses RLS (Row Level Security) - Use anon key for client-side/restricted access - Vector search requires pgvector extension - Embeddings default to OpenAI text-embedding-ada-002 (1536 dimensions)