Supabase

# JavaScript/TypeScript
npm install @supabase/supabase-js

# React-specific hooks
npm install @supabase/auth-helpers-react @supabase/auth-helpers-nextjs

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

// Types generated by Supabase CLI
import { Database } from '@/types/database.types'

export const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Server-side client (Next.js App Router)
import { createServerComponentClient } from '@supabase/auth-helpers-nextjs'
import { cookies } from 'next/headers'

export const createServerClient = () => {
  return createServerComponentClient<Database>({ cookies })
}

# .env.local
NEXT_PUBLIC_SUPABASE_URL=https://xxxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGci...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGci...  # Server-side only!

-- Users table (extends auth.users)
CREATE TABLE public.profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  bio TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Posts table
CREATE TABLE public.posts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  author_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  content TEXT,
  slug TEXT UNIQUE NOT NULL,
  published BOOLEAN DEFAULT FALSE,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX posts_author_id_idx ON public.posts(author_id);
CREATE INDEX posts_published_idx ON public.posts(published) WHERE published = true;
CREATE INDEX posts_slug_idx ON public.posts(slug);

// SELECT - fetching data
const { data: posts, error } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    slug,
    content,
    published_at,
    author:profiles(id, username, avatar_url)
  `)
  .eq('published', true)
  .order('published_at', { ascending: false })
  .limit(10)

// SELECT with filtering
const { data } = await supabase
  .from('posts')
  .select('*')
  .or('title.ilike.%react%,content.ilike.%react%')
  .gte('published_at', '2024-01-01')
  .lte('published_at', '2024-12-31')

// INSERT
const { data: newPost, error } = await supabase
  .from('posts')
  .insert({
    author_id: userId,
    title: 'My First Post',
    slug: 'my-first-post',
    content: 'Hello World!',
  })
  .select()
  .single()

// UPDATE
const { data, error } = await supabase
  .from('posts')
  .update({
    title: 'Updated Title',
    updated_at: new Date().toISOString(),
  })
  .eq('id', postId)
  .select()
  .single()

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

// UPSERT (insert or update)
const { data, error } = await supabase
  .from('profiles')
  .upsert({
    id: userId,
    username: 'john_doe',
    full_name: 'John Doe',
  })
  .select()
  .single()

// Pagination
const pageSize = 10
const page = 1

const { data, count } = await supabase
  .from('posts')
  .select('*', { count: 'exact' })
  .range((page - 1) * pageSize, page * pageSize - 1)

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

// Aggregations via RPC
// First create a function in SQL:
// CREATE FUNCTION get_post_stats() RETURNS TABLE (total bigint, published bigint)
// AS $$ SELECT COUNT(*), COUNT(*) FILTER (WHERE published) FROM posts $$
// LANGUAGE sql;

const { data } = await supabase.rpc('get_post_stats')

// Sign up
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'securepassword123',
  options: {
    data: {
      full_name: 'John Doe',
    },
    emailRedirectTo: 'https://myapp.com/auth/callback',
  },
})

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

// Sign out
await supabase.auth.signOut()

// Password reset
await supabase.auth.resetPasswordForEmail('user@example.com', {
  redirectTo: 'https://myapp.com/reset-password',
})

// GitHub
await supabase.auth.signInWithOAuth({
  provider: 'github',
  options: {
    redirectTo: 'https://myapp.com/auth/callback',
    scopes: 'read:user user:email',
  },
})

// Google
await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: 'https://myapp.com/auth/callback',
    queryParams: {
      access_type: 'offline',
      prompt: 'consent',
    },
  },
})

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

const { error } = await supabase.auth.signInWithOtp({
  email: 'user@example.com',
  options: {
    emailRedirectTo: 'https://myapp.com/auth/callback',
  },
})

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

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

// Listen to authentication changes
supabase.auth.onAuthStateChange((event, session) => {
  console.log('Auth event:', event)
  console.log('Session:', session)

  if (event === 'SIGNED_IN') {
    // User signed in
  } else if (event === 'SIGNED_OUT') {
    // User signed out
  } else if (event === 'TOKEN_REFRESHED') {
    // Token was refreshed
  }
})

// app/auth/callback/route.ts
import { createRouteHandlerClient } from '@supabase/auth-helpers-nextjs'
import { cookies } from 'next/headers'
import { NextResponse } from 'next/server'

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const code = searchParams.get('code')

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

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

// middleware.ts
import { createMiddlewareClient } from '@supabase/auth-helpers-nextjs'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(req: NextRequest) {
  const res = NextResponse.next()
  const supabase = createMiddlewareClient({ req, res })

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

  // Protect routes that require authentication
  if (!session && req.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', req.url))
  }

  return res
}

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

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

-- Policy: everyone can read published posts
CREATE POLICY "Public posts are viewable by everyone"
ON public.posts FOR SELECT
USING (published = true);

-- Policy: users can read their own posts
CREATE POLICY "Users can view own posts"
ON public.posts FOR SELECT
USING (auth.uid() = author_id);

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

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

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

-- Policy with roles (e.g. admin)
CREATE POLICY "Admins can do everything"
ON public.posts FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM public.profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'admin'
  )
);

// Upload from the browser
const file = event.target.files[0]
const fileExt = file.name.split('.').pop()
const fileName = `${userId}/${Date.now()}.${fileExt}`

const { data, error } = await supabase.storage
  .from('avatars')
  .upload(fileName, file, {
    cacheControl: '3600',
    upsert: true,
  })

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('avatars')
  .getPublicUrl(fileName)

// Download
const { data, error } = await supabase.storage
  .from('documents')
  .download('folder/file.pdf')

// List files
const { data: files } = await supabase.storage
  .from('documents')
  .list('folder', {
    limit: 100,
    offset: 0,
    sortBy: { column: 'created_at', order: 'desc' },
  })

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

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

-- Policy: public read access
CREATE POLICY "Avatars are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'avatars');

// Listen to table changes
const channel = supabase
  .channel('posts-changes')
  .on(
    'postgres_changes',
    {
      event: '*', // INSERT, UPDATE, DELETE or *
      schema: 'public',
      table: 'posts',
      filter: 'published=eq.true', // Optional filter
    },
    (payload) => {
      console.log('Change received!', payload)

      if (payload.eventType === 'INSERT') {
        // New post
        setPosts(prev => [payload.new, ...prev])
      } else if (payload.eventType === 'UPDATE') {
        // Updated post
        setPosts(prev =>
          prev.map(p => p.id === payload.new.id ? payload.new : p)
        )
      } else if (payload.eventType === 'DELETE') {
        // Deleted post
        setPosts(prev => prev.filter(p => p.id !== payload.old.id))
      }
    }
  )
  .subscribe()

// Cleanup
return () => {
  supabase.removeChannel(channel)
}

// Sending
const channel = supabase.channel('room:123')
channel.send({
  type: 'broadcast',
  event: 'cursor-position',
  payload: { x: 100, y: 200 },
})

// Receiving
channel.on('broadcast', { event: 'cursor-position' }, (payload) => {
  console.log('Cursor at:', payload.payload)
})

const channel = supabase.channel('room:123')

// Track presence
channel.on('presence', { event: 'sync' }, () => {
  const state = channel.presenceState()
  console.log('Online users:', Object.keys(state).length)
})

channel.on('presence', { event: 'join' }, ({ key, newPresences }) => {
  console.log('User joined:', key)
})

channel.on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
  console.log('User left:', key)
})

// Track current user
channel.subscribe(async (status) => {
  if (status === 'SUBSCRIBED') {
    await channel.track({
      user_id: userId,
      online_at: new Date().toISOString(),
    })
  }
})

// supabase/functions/send-email/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'

serve(async (req) => {
  const { to, subject, body } = await req.json()

  // Send email using e.g. Resend
  const response = await fetch('https://api.resend.com/emails', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${Deno.env.get('RESEND_API_KEY')}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      from: 'noreply@myapp.com',
      to,
      subject,
      html: body,
    }),
  })

  const data = await response.json()

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

const { data, error } = await supabase.functions.invoke('send-email', {
  body: {
    to: 'user@example.com',
    subject: 'Welcome!',
    body: '<h1>Welcome to our app</h1>',
  },
})

# Install Supabase CLI
npm install -g supabase

# Log in
supabase login

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

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

type Post = Database['public']['Tables']['posts']['Row']
type NewPost = Database['public']['Tables']['posts']['Insert']
type UpdatePost = Database['public']['Tables']['posts']['Update']

// Now you have full typing
const { data } = await supabase
  .from('posts')
  .select('*')
  .single()
// data is of type Post | null

-- Never leave tables without RLS in production!
ALTER TABLE public.your_table ENABLE ROW LEVEL SECURITY;

# Regenerate types after every schema change
supabase gen types typescript --project-id xxx > types/database.types.ts

// Select only the columns you need
const { data } = await supabase
  .from('posts')
  .select('id, title, slug') // Not select('*')
  .limit(10)

-- Add indexes for frequently filtered columns
CREATE INDEX posts_published_idx ON posts(published_at)
WHERE published = true;