bobmatnyc/supabase-backend-platform

Supabase Backend Platform Skill

---

progressive_disclosure: entry_point: summary: "Open-source Firebase alternative with Postgres, authentication, storage, and realtime" when_to_use: - "When building full-stack applications" - "When auth, database, and storage are required" - "When realtime subscriptions are needed" - "When using Next.js, React, or Vue" quick_start: - "Create project on Supabase console" - "npm install @supabase/supabase-js" - "Initialize client with URL and anon key" - "Use auth, database, storage, realtime" token_estimate: entry: 80-95 full: 5000-6000

Supabase Fundamentals

What is Supabase?

Open-source Firebase alternative built on:

• Postgres Database: Full SQL database with PostgREST API
• Authentication: Built-in auth with multiple providers
• Storage: File storage with image transformations
• Realtime: WebSocket subscriptions to database changes
• Edge Functions: Serverless functions on Deno runtime
• Row Level Security: Postgres RLS for data access control

Project Setup

# Install Supabase client
npm install @supabase/supabase-js

# Install CLI for local development
npm install -D supabase

# TypeScript types
npm install -D @supabase/supabase-js

Client Initialization

// lib/supabase.ts
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!

export const supabase = createClient(supabaseUrl, supabaseAnonKey)

// With TypeScript types
import { Database } from '@/types/supabase'

export const supabase = createClient<Database>(
  supabaseUrl,
  supabaseAnonKey
)

Database Operations

PostgREST API Basics

Supabase auto-generates REST API from Postgres schema:

// SELECT * FROM posts
const { data, error } = await supabase
  .from('posts')
  .select('*')

// SELECT with filters
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// SELECT with joins
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    author:profiles(name, avatar),
    comments(count)
  `)

// INSERT
const { data, error } = await supabase
  .from('posts')
  .insert({ title: 'Hello', content: 'World' })
  .select()
  .single()

// UPDATE
const { data } = await supabase
  .from('posts')
  .update({ status: 'published' })
  .eq('id', postId)
  .select()

// DELETE
const { error } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId)

// UPSERT
const { data } = await supabase
  .from('posts')
  .upsert({ id: 1, title: 'Updated' })
  .select()

Advanced Queries

// Full-text search
const { data } = await supabase
  .from('posts')
  .select('*')
  .textSearch('title', 'postgresql', {
    type: 'websearch',
    config: 'english'
  })

// Range queries
const { data } = await supabase
  .from('posts')
  .select('*')
  .gte('created_at', '2024-01-01')
  .lte('created_at', '2024-12-31')

// Array contains
const { data } = await supabase
  .from('posts')
  .select('*')
  .contains('tags', ['postgres', 'supabase'])

// JSON operations
const { data } = await supabase
  .from('users')
  .select('*')
  .eq('metadata->theme', 'dark')

// Count without data
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })

// Pagination
const pageSize = 10
const page = 2
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(page * pageSize, (page + 1) * pageSize - 1)

Database Functions and RPC

// Call Postgres function
const { data, error } = await supabase
  .rpc('get_trending_posts', {
    days: 7,
    min_score: 10
  })

// Example function in SQL
/*
CREATE OR REPLACE FUNCTION get_trending_posts(
  days INTEGER,
  min_score INTEGER
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  score INTEGER
) AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.title, COUNT(v.id)::INTEGER as score
  FROM posts p
  LEFT JOIN votes v ON p.id = v.post_id
  WHERE p.created_at > NOW() - INTERVAL '1 day' * days
  GROUP BY p.id
  HAVING COUNT(v.id) >= min_score
  ORDER BY score DESC;
END;
$$ LANGUAGE plpgsql;
*/

Authentication

Email/Password Authentication

// Sign up
const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'secure-password',
  options: {
    data: {
      name: 'John Doe',
      avatar_url: 'https://...'
    }
  }
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: '[email protected]',
  password: 'secure-password'
})

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

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

// Get session
const { data: { session } } = await supabase.auth.getSession()

OAuth Providers

// Sign in with OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'github',
  options: {
    redirectTo: 'http://localhost:3000/auth/callback',
    scopes: 'repo user'
  }
})

// Available providers
// github, google, gitlab, bitbucket, azure, discord, facebook,
// linkedin, notion, slack, spotify, twitch, twitter, apple

Magic Links

// Send magic link
const { data, error } = await supabase.auth.signInWithOtp({
  email: '[email protected]',
  options: {
    emailRedirectTo: 'http://localhost:3000/auth/callback'
  }
})

// Verify OTP
const { data, error } = await supabase.auth.verifyOtp({
  email: '[email protected]',
  token: '123456',
  type: 'email'
})

Phone Authentication

// Sign in with phone
const { data, error } = await supabase.auth.signInWithOtp({
  phone: '+1234567890'
})

// Verify phone OTP
const { data, error } = await supabase.auth.verifyOtp({
  phone: '+1234567890',
  token: '123456',
  type: 'sms'
})

Auth State Management

// Listen to auth changes
supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_IN') {
    console.log('User signed in:', session?.user)
  }
  if (event === 'SIGNED_OUT') {
    console.log('User signed out')
  }
  if (event === 'TOKEN_REFRESHED') {
    console.log('Token refreshed')
  }
})

// Update user metadata
const { data, error } = await supabase.auth.updateUser({
  data: { theme: 'dark' }
})

// Change password
const { data, error } = await supabase.auth.updateUser({
  password: 'new-password'
})

Row Level Security (RLS)

RLS Fundamentals

Postgres Row Level Security controls data access at the database level:

-- Enable RLS on table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: Users can read all published posts
CREATE POLICY "Public posts are viewable by everyone"
ON posts FOR SELECT
USING (status = 'published');

-- Policy: Users can only update their own posts
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (auth.uid() = author_id);

-- Policy: Authenticated users can insert posts
CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() = author_id);

-- Policy: Users can delete their own posts
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = author_id);

Common RLS Patterns

-- Public read, authenticated write
CREATE POLICY "Anyone can view posts"
ON posts FOR SELECT
USING (true);

CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL);

-- Organization-based access
CREATE POLICY "Users can view org data"
ON documents FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id
    FROM memberships
    WHERE user_id = auth.uid()
  )
);

-- Role-based access
CREATE POLICY "Admins can do anything"
ON posts FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
    AND role = 'admin'
  )
);

-- Time-based access
CREATE POLICY "View published or scheduled posts"
ON posts FOR SELECT
USING (
  status = 'published'
  OR (status = 'scheduled' AND publish_at <= NOW())
);

RLS Helper Functions

-- Get current user ID
SELECT auth.uid();

-- Get current user JWT
SELECT auth.jwt();

-- Get specific claim
SELECT auth.jwt()->>'email';

-- Custom claims
SELECT auth.jwt()->'app_metadata'->>'role';

Storage

File Upload

// Upload file
const { data, error } = await supabase.storage
  .from('avatars')
  .upload('public/avatar1.png', file, {
    cacheControl: '3600',
    upsert: false
  })

// Upload with progress
const { data, error } = await supabase.storage
  .from('avatars')
  .upload('public/avatar1.png', file, {
    onUploadProgress: (progress) => {
      console.log(`${progress.loaded}/${progress.total}`)
    }
  })

// Upload from URL
const { data, error } = await supabase.storage
  .from('avatars')
  .uploadToSignedUrl('path', token, file)

File Operations

// Download file
const { data, error } = await supabase.storage
  .from('avatars')
  .download('public/avatar1.png')

// Get public URL
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('public/avatar1.png')

// Create signed URL (temporary access)
const { data, error } = await supabase.storage
  .from('avatars')
  .createSignedUrl('private/document.pdf', 3600) // 1 hour

// List files
const { data, error } = await supabase.storage
  .from('avatars')
  .list('public', {
    limit: 100,
    offset: 0,
    sortBy: { column: 'name', order: 'asc' }
  })

// Delete file
const { data, error } = await supabase.storage
  .from('avatars')
  .remove(['public/avatar1.png'])

// Move file
const { data, error } = await supabase.storage
  .from('avatars')
  .move('public/avatar1.png', 'public/avatar2.png')

Image Transformations

// Transform image
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('avatar1.png', {
    transform: {
      width: 200,
      height: 200,
      resize: 'cover',
      quality: 80
    }
  })

// Available transformations
// width, height, resize (cover|contain|fill),
// quality (1-100), format (origin|jpeg|png|webp)

Storage RLS

-- Enable RLS on storage
CREATE POLICY "Avatar images are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'avatars' AND (storage.foldername(name))[1] = 'public');

CREATE POLICY "Users can upload their own avatar"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'avatars'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

CREATE POLICY "Users can delete their own avatar"
ON storage.objects FOR DELETE
USING (
  bucket_id = 'avatars'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

Realtime Subscriptions

Database Changes

// Subscribe to inserts
const channel = supabase
  .channel('posts-insert')
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'posts'
    },
    (payload) => {
      console.log('New post:', payload.new)
    }
  )
  .subscribe()

// Subscribe to updates
const channel = supabase
  .channel('posts-update')
  .on(
    'postgres_changes',
    {
      event: 'UPDATE',
      schema: 'public',
      table: 'posts',
      filter: 'id=eq.1'
    },
    (payload) => {
      console.log('Updated:', payload.new)
      console.log('Previous:', payload.old)
    }
  )
  .subscribe()

// Subscribe to all changes
const channel = supabase
  .channel('posts-all')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'posts'
    },
    (payload) => {
      console.log('Change:', payload)
    }
  )
  .subscribe()

// Unsubscribe
supabase.removeChannel(channel)

Presence (Track Online Users)

// Track presence
const channel = supabase.channel('room-1')

// Track current user
channel
  .on('presence', { event: 'sync' }, () => {
    const state = channel.presenceState()
    console.log('Online users:', state)
  })
  .on('presence', { event: 'join' }, ({ key, newPresences }) => {
    console.log('User joined:', newPresences)
  })
  .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
    console.log('User left:', leftPresences)
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await channel.track({
        user_id: userId,
        online_at: new Date().toISOString()
      })
    }
  })

// Untrack
await channel.untrack()

Broadcast (Send Messages)

// Broadcast messages
const channel = supabase.channel('chat-room')

channel
  .on('broadcast', { event: 'message' }, (payload) => {
    console.log('Message:', payload)
  })
  .subscribe()

// Send message
await channel.send({
  type: 'broadcast',
  event: 'message',
  payload: { text: 'Hello', user: 'John' }
})

Edge Functions

Edge Function Basics

Serverless functions on Deno runtime:

# Create function
supabase functions new my-function

# Serve locally
supabase functions serve

# Deploy
supabase functions deploy my-function

Edge Function Example

// supabase/functions/my-function/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  try {
    // Get auth header
    const authHeader = req.headers.get('Authorization')!

    // Create Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      { global: { headers: { Authorization: authHeader } } }
    )

    // Verify user
    const { data: { user }, error } = await supabase.auth.getUser()
    if (error) throw error

    // Process request
    const { data } = await supabase
      .from('posts')
      .select('*')
      .eq('author_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: 400, headers: { 'Content-Type': 'application/json' } }
    )
  }
})

Invoke Edge Function

// From client
const { data, error } = await supabase.functions.invoke('my-function', {
  body: { name: 'John' }
})

// With auth
const { data, error } = await supabase.functions.invoke('my-function', {
  headers: {
    Authorization: `Bearer ${session.access_token}`
  },
  body: { name: 'John' }
})

Next.js Integration

App Router Setup

// lib/supabase/client.ts (Client Component)
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 (Server Component)
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { cookies } from 'next/headers'

export async function createClient() {
  const cookieStore = await 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
        },
        set(name: string, value: string, options: CookieOptions) {
          cookieStore.set({ name, value, ...options })
        },
        remove(name: string, options: CookieOptions) {
          cookieStore.set({ name, value: '', ...options })
        },
      },
    }
  )
}

// lib/supabase/middleware.ts (Middleware)
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { NextResponse, type NextRequest } from 'next/server'

export async function updateSession(request: NextRequest) {
  let response = NextResponse.next({
    request: {
      headers: request.headers,
    },
  })

  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: CookieOptions) {
          request.cookies.set({ name, value, ...options })
          response = NextResponse.next({
            request: { headers: request.headers },
          })
          response.cookies.set({ name, value, ...options })
        },
        remove(name: string, options: CookieOptions) {
          request.cookies.set({ name, value: '', ...options })
          response = NextResponse.next({
            request: { headers: request.headers },
          })
          response.cookies.set({ name, value: '', ...options })
        },
      },
    }
  )

  await supabase.auth.getUser()
  return response
}

Middleware

// middleware.ts
import { updateSession } from '@/lib/supabase/middleware'

export async function middleware(request: NextRequest) {
  return await updateSession(request)
}

export const config = {
  matcher: [
    '/((?!_next/static|_next/image|favicon.ico|.*\\.(?:svg|png|jpg|jpeg|gif|webp)$).*)',
  ],
}

Server Component

// app/posts/page.tsx
import { createClient } from '@/lib/supabase/server'

export default async function PostsPage() {
  const supabase = await createClient()

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

  return (
    <div>
      {posts?.map((post) => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>{post.content}</p>
        </article>
      ))}
    </div>
  )
}

Client Component

// app/components/new-post.tsx
'use client'

import { useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function NewPost() {
  const [title, setTitle] = useState('')
  const supabase = createClient()

  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault()

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

    const { error } = await supabase
      .from('posts')
      .insert({ title, author_id: user.id })

    if (!error) {
      setTitle('')
    }
  }

  return (
    <form onSubmit={handleSubmit}>
      <input
        value={title}
        onChange={(e) => setTitle(e.target.value)}
        placeholder="Post title"
      />
      <button>Create</button>
    </form>
  )
}

Server Actions

// app/actions/posts.ts
'use server'

import { revalidatePath } from 'next/cache'
import { createClient } from '@/lib/supabase/server'

export async function createPost(formData: FormData) {
  const supabase = await createClient()

  const { data: { user } } = await supabase.auth.getUser()
  if (!user) {
    return { error: 'Not authenticated' }
  }

  const title = formData.get('title') as string

  const { error } = await supabase
    .from('posts')
    .insert({ title, author_id: user.id })

  if (error) {
    return { error: error.message }
  }

  revalidatePath('/posts')
  return { success: true }
}

TypeScript Type Generation

Generate Types from Database

# Install CLI
npm install -D supabase

# Login
npx supabase login

# Link project
npx supabase link --project-ref your-project-ref

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

# Or from local database
npx supabase gen types typescript --local > types/supabase.ts

Use Generated Types

// types/supabase.ts (generated)
export type Database = {
  public: {
    Tables: {
      posts: {
        Row: {
          id: string
          title: string
          content: string | null
          author_id: string
          created_at: string
        }
        Insert: {
          id?: string
          title: string
          content?: string | null
          author_id: string
          created_at?: string
        }
        Update: {
          id?: string
          title?: string
          content?: string | null
          author_id?: string
          created_at?: string
        }
      }
    }
  }
}

// Usage
import { createClient } from '@supabase/supabase-js'
import { Database } from '@/types/supabase'

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

// Type-safe queries
const { data } = await supabase
  .from('posts') // TypeScript knows this table exists
  .select('title, content') // Autocomplete for columns
  .single()

// data is typed as { title: string; content: string | null }

Supabase CLI and Local Development

Setup Local Development

# Initialize Supabase
npx supabase init

# Start local Supabase (Postgres, Auth, Storage, etc.)
npx supabase start

# Stop
npx supabase stop

# Reset database
npx supabase db reset

# Status
npx supabase status

Database Migrations

# Create migration
npx supabase migration new create_posts_table

# Edit migration file
# supabase/migrations/20240101000000_create_posts_table.sql

# Apply migrations
npx supabase db push

# Pull remote schema
npx supabase db pull

# Diff local vs remote
npx supabase db diff

Migration Example

-- supabase/migrations/20240101000000_create_posts_table.sql
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID NOT NULL REFERENCES auth.users(id),
  status TEXT NOT NULL DEFAULT 'draft',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policies
CREATE POLICY "Anyone can view published posts"
ON posts FOR SELECT
USING (status = 'published');

CREATE POLICY "Users can create their own posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() = author_id);

CREATE POLICY "Users can update their own posts"
ON posts FOR UPDATE
USING (auth.uid() = author_id);

-- Indexes
CREATE INDEX posts_author_id_idx ON posts(author_id);
CREATE INDEX posts_status_idx ON posts(status);

-- Trigger for updated_at
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION moddatetime(updated_at);

Security Best Practices

API Key Management

// NEVER expose service_role key in client
// Use anon key for client-side
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! // Public
)

// Service role key only on server
const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // Secret, bypasses RLS
  { auth: { persistSession: false } }
)

RLS Best Practices

-- Always enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Default deny (no policy = no access)
-- Explicitly grant access with policies

-- Test policies as different users
SET request.jwt.claims.sub = 'user-id';
SELECT * FROM posts; -- Test as this user

-- Disable RLS only for admin operations
-- Use service_role key from server, never client

Input Validation

// Validate on client and server
function validatePost(data: unknown) {
  const schema = z.object({
    title: z.string().min(1).max(200),
    content: z.string().max(10000).optional()
  })

  return schema.parse(data)
}

// Server-side validation in Edge Function
serve(async (req) => {
  const body = await req.json()

  try {
    const validated = validatePost(body)
    // Process validated data
  } catch (error) {
    return new Response(
      JSON.stringify({ error: 'Invalid input' }),
      { status: 400 }
    )
  }
})

Environment Variables

# .env.local
NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ... # Public
SUPABASE_SERVICE_ROLE_KEY=eyJ... # Secret, server-only

# Production: Use environment variables in hosting platform
# Never commit .env files to git

Production Deployment

Database Optimization

-- Add indexes for common queries
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);
CREATE INDEX posts_author_status_idx ON posts(author_id, status);

-- Optimize full-text search
CREATE INDEX posts_title_search_idx ON posts
USING GIN (to_tsvector('english', title));

-- Analyze query performance
EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = 'xxx';

-- Vacuum and analyze
VACUUM ANALYZE posts;

Connection Pooling

// Use connection pooling for serverless
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(url, key, {
  db: {
    schema: 'public',
  },
  auth: {
    persistSession: true,
    autoRefreshToken: true,
  },
  global: {
    headers: { 'x-my-custom-header': 'my-value' },
  },
})

// Configure pool in Supabase dashboard
// Settings > Database > Connection pooling

Monitoring

// Enable query logging
const supabase = createClient(url, key, {
  global: {
    fetch: async (url, options) => {
      console.log('Query:', url)
      return fetch(url, options)
    }
  }
})

// Monitor in Supabase Dashboard
// - Database performance
// - API usage
// - Storage usage
// - Auth activity

Backup Strategy

# Automatic backups (Pro plan+)
# Daily backups with point-in-time recovery

# Manual backup
pg_dump -h db.xxx.supabase.co -U postgres -d postgres > backup.sql

# Restore
psql -h db.xxx.supabase.co -U postgres -d postgres < backup.sql

Supabase vs Firebase

Similarities

• Backend-as-a-Service platform
• Authentication with multiple providers
• Realtime data synchronization
• File storage
• Serverless functions
• Generous free tier

Key Differences

Database

• Supabase: PostgreSQL (SQL, full control)
• Firebase: Firestore (NoSQL, limited queries)

Queries

• Supabase: Full SQL, joins, aggregations
• Firebase: Limited filtering, no joins

Security

• Supabase: Row Level Security (Postgres native)
• Firebase: Security Rules (custom syntax)

Open Source

• Supabase: Fully open source, self-hostable
• Firebase: Proprietary, Google-hosted only

Pricing

• Supabase: Compute-based, predictable
• Firebase: Usage-based, can spike

Ecosystem

• Supabase: Postgres ecosystem (extensions, tools)
• Firebase: Google Cloud Platform integration

Migration Considerations

// Firestore collection query
const snapshot = await db
  .collection('posts')
  .where('status', '==', 'published')
  .orderBy('createdAt', 'desc')
  .limit(10)
  .get()

// Equivalent Supabase query
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// Complex queries easier in Supabase
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    author:profiles!inner(name),
    comments(count)
  `)
  .gte('created_at', startDate)
  .lte('created_at', endDate)
  .order('created_at', { ascending: false })
// Firebase would require multiple queries + client-side joins

Advanced Patterns

Optimistic Updates

'use client'

import { useState, useOptimistic } from 'react'
import { createClient } from '@/lib/supabase/client'

export function PostList({ initialPosts }: { initialPosts: Post[] }) {
  const [posts, setPosts] = useState(initialPosts)
  const [optimisticPosts, addOptimisticPost] = useOptimistic(
    posts,
    (state, newPost: Post) => [...state, newPost]
  )

  const supabase = createClient()

  const createPost = async (title: string) => {
    const tempPost = {
      id: crypto.randomUUID(),
      title,
      created_at: new Date().toISOString()
    }

    addOptimisticPost(tempPost)

    const { data } = await supabase
      .from('posts')
      .insert({ title })
      .select()
      .single()

    if (data) {
      setPosts([...posts, data])
    }
  }

  return (
    <div>
      {optimisticPosts.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
    </div>
  )
}

Infinite Scroll

'use client'

import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

const PAGE_SIZE = 20

export function InfinitePostList() {
  const [posts, setPosts] = useState<Post[]>([])
  const [page, setPage] = useState(0)
  const [hasMore, setHasMore] = useState(true)

  const supabase = createClient()

  useEffect(() => {
    const loadMore = async () => {
      const { data } = await supabase
        .from('posts')
        .select('*')
        .range(page * PAGE_SIZE, (page + 1) * PAGE_SIZE - 1)
        .order('created_at', { ascending: false })

      if (data) {
        setPosts([...posts, ...data])
        setHasMore(data.length === PAGE_SIZE)
      }
    }

    loadMore()
  }, [page])

  return (
    <div>
      {posts.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
      {hasMore && (
        <button onClick={() => setPage(page + 1)}>
          Load More
        </button>
      )}
    </div>
  )
}

Debounced Search

'use client'

import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'
import { useDebounce } from '@/hooks/use-debounce'

export function SearchPosts() {
  const [query, setQuery] = useState('')
  const [results, setResults] = useState<Post[]>([])
  const debouncedQuery = useDebounce(query, 300)

  const supabase = createClient()

  useEffect(() => {
    if (!debouncedQuery) {
      setResults([])
      return
    }

    const search = async () => {
      const { data } = await supabase
        .from('posts')
        .select('*')
        .textSearch('title', debouncedQuery)
        .limit(10)

      if (data) setResults(data)
    }

    search()
  }, [debouncedQuery])

  return (
    <div>
      <input
        value={query}
        onChange={(e) => setQuery(e.target.value)}
        placeholder="Search posts..."
      />
      {results.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
    </div>
  )
}

---

Summary

Supabase provides a complete backend platform with:

• Postgres Database with REST and GraphQL APIs
• Built-in Authentication with multiple providers
• Row Level Security for granular access control
• File Storage with image transformations
• Realtime Subscriptions for live updates
• Edge Functions for serverless compute
• Next.js Integration with Server and Client Components
• TypeScript Support with auto-generated types
• Local Development with Supabase CLI
• Production Ready with monitoring and backups

Use Supabase when a full-featured backend with the power of Postgres, built-in auth, and realtime capabilities is needed, all with excellent TypeScript and Next.js integration.