Supabase Database Setup for Web Application

Setting Up Supabase for Web Application

Supabase is an open source Firebase alternative based on PostgreSQL. Includes database, authentication, realtime subscriptions, file storage and Edge Functions. Suitable for rapid development and projects where development speed is crucial.

Creating a project

Via dashboard.supabase.com — select region, set database password. For self-hosted:

git clone --depth 1 https://github.com/supabase/supabase
cd supabase/docker
cp .env.example .env
# Edit .env: POSTGRES_PASSWORD, JWT_SECRET, ANON_KEY, SERVICE_ROLE_KEY
docker compose up -d

Client connection

import { createClient } from '@supabase/supabase-js'
import type { Database } from './database.types'

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

// For server-side operations — service role key
export const supabaseAdmin = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

Generating types from schema

npx supabase gen types typescript --project-id your-project-id > src/types/database.types.ts

This gives autocomplete and type checking when working with tables.

Schema and Row Level Security

-- Profile table (extends auth.users)
CREATE TABLE profiles (
  id          uuid PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  username    text UNIQUE,
  avatar_url  text,
  bio         text,
  updated_at  timestamptz DEFAULT now()
);

-- RLS — each user sees only their own
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "profiles_select_own" ON profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "profiles_update_own" ON profiles
  FOR UPDATE USING (auth.uid() = id);

-- Public profiles — everyone can read
CREATE POLICY "profiles_select_public" ON profiles
  FOR SELECT USING (true);

-- Posts table with RLS
CREATE TABLE posts (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid REFERENCES auth.users NOT NULL,
  title       text NOT NULL,
  content     text,
  is_public   boolean DEFAULT false,
  created_at  timestamptz DEFAULT now()
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "posts_select_public_or_own" ON posts
  FOR SELECT USING (
    is_public = true OR auth.uid() = user_id
  );

CREATE POLICY "posts_insert_own" ON posts
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "posts_update_own" ON posts
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "posts_delete_own" ON posts
  FOR DELETE USING (auth.uid() = user_id);

Authentication

// Sign up
const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'password',
  options: {
    data: { username: 'john_doe' }  // additional data in user_metadata
  }
})

// OAuth (Google, GitHub, etc.)
await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: { redirectTo: `${window.location.origin}/auth/callback` }
})

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

// Subscribe to session changes
supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_IN') router.push('/dashboard')
  if (event === 'SIGNED_OUT') router.push('/login')
})

CRUD operations

// Insert with return
const { data: post, error } = await supabase
  .from('posts')
  .insert({ title, content, user_id: user.id })
  .select()
  .single()

// Query with filters and JOIN
const { data: posts } = await supabase
  .from('posts')
  .select(`
    id, title, created_at,
    profiles ( username, avatar_url ),
    tags ( name )
  `)
  .eq('is_public', true)
  .order('created_at', { ascending: false })
  .range(0, 23)  // pagination

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

Realtime subscriptions

// Subscribe to new records
const channel = supabase
  .channel('posts-feed')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'posts',
    filter: 'is_public=eq.true'
  }, (payload) => {
    setPosts(prev => [payload.new as Post, ...prev])
  })
  .subscribe()

// Unsubscribe on unmount
return () => supabase.removeChannel(channel)

Edge Functions

// supabase/functions/send-notification/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) => {
  const { userId, message } = await req.json()
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )

  await supabase
    .from('notifications')
    .insert({ user_id: userId, message })

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

# Deploy function
supabase functions deploy send-notification

File storage

// Upload avatar
const { data, error } = await supabase.storage
  .from('avatars')
  .upload(`${user.id}/avatar.jpg`, file, {
    cacheControl: '3600',
    upsert: true
  })

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('avatars')
  .getPublicUrl(`${user.id}/avatar.jpg`)

Timelines

Supabase project setup with authentication, RLS policies and basic CRUD: 1–2 days. Adding realtime, Edge Functions, storage and frontend integration: 2–3 more days. Self-hosted deployment with backup and monitoring configuration: 1–2 more days.