Supabase, an open source backend built on PostgreSQL

Supabase is a set of services built around ordinary PostgreSQL: a database, user authentication, file storage, on demand functions, and realtime change subscriptions. The platform code is open source under Apache 2.0, the client library under MIT, and the repository carries over a hundred and seven thousand stars.

The main difference from the competition

Everything else follows from one decision: real PostgreSQL sits underneath rather than a bespoke engine with a bespoke query language.

Its significance shows once a project stops being simple. You have joins, views, triggers, in database functions, partial indexes, and the whole extension ecosystem, including full text search and vector work. Solutions built on their own data model start needing workarounds at that point, while here you simply write a query.

The second consequence concerns exit. Since this is ordinary PostgreSQL, you export data with a standard tool and load it anywhere else. That is not a marketing advantage but a real one, because changing provider reduces to moving the database and swapping a connection string rather than rewriting the data access layer.

The third is how the API works. The interface exposing tables over HTTP is generated automatically from the database schema, so adding a column appears in the API immediately with no code written. Convenient while prototyping and risky later, as covered shortly.

When this is a good choice

The scenarios are worth naming plainly, because this platform gets chosen reflexively and not every project gains from it.

It works best for a product built by a small team where reaching a working application quickly matters. Auth, database, files, and functions in one place mean the first week goes into the product rather than into assembling infrastructure from parts. That is a real saving and the main reason for this platform's popularity.

The second good scenario is a project whose data is relational and where a query with several joins or a report will inevitably appear. Choosing a document database there means that feature arrives later and worse.

It fares worse in two situations. The first is an application with substantial server side business logic, where you need your own service layer regardless. The part of the platform concerning browser access is then unnecessary, and what remains is hosted PostgreSQL, which you can get more cheaply elsewhere, from Neon for instance.

The second is a requirement to keep data on your own infrastructure. A self hosted variant exists and is open source, but it comprises a dozen or so services, so maintaining it is real work rather than starting one container. Cost that honestly before deciding. On a small project PocketBase delivers the same thing as a single executable carrying an SQLite database, auth, files, and an API inside. The price of that simplicity is one process without replication and a pre 1.0 status where backward compatibility is not guaranteed and an upgrade sometimes needs manual steps.

Row level security, the critical part

This is the most important section here, because it covers the one place where a mistake costs a data leak.

Since the client connects to the database directly from a browser, the only barrier between a user and somebody else's data is the access rules defined in the database itself. There is no application server layer checking permissions along the way. A table without rules enabled is visible to anyone who knows the project address and the public key, and that key reaches the browser by definition.

The symptom is treacherous, because there is none. The application works correctly, tests pass, and the data is available to everyone. Discovery usually happens when a stranger opens the network tab in their browser tools.

The practical rule is therefore: enable access rules on every table immediately after creating it, before writing a single line of client code. A table locked by default shows up as an empty list, which you notice at once, unlike an open one.

The second trap concerns the service key. Alongside the public key there is a second one that bypasses every access rule. It belongs exclusively to server code, and its appearance in a variable reachable from the browser amounts to handing over the whole database. In Next.js the boundary is the environment variable name prefix, and it is worth checking twice.

The third matter is the cost of rules. A rule is a condition appended to every query, so a rule referencing a subquery against another table runs for every row. At a thousand records nobody notices; at a million the query stops returning. If a list slowed down suddenly after adding rules, that is the first place to look.

What is Supabase and why is it so popular?

Supabase is an open-source Backend-as-a-Service (BaaS), often called "open-source Firebase." But unlike Firebase, which uses NoSQL (Firestore), Supabase is built on PostgreSQL - the most powerful open-source relational database.

Supabase offers everything you need to build a modern application:

PostgreSQL Database with a powerful query builder
Authentication with social logins and magic links
Storage for files and images
Real-time subscriptions
Edge Functions (Deno)
Vector embeddings for AI

Why choose Supabase?

Open Source

All Supabase code is available on GitHub. You can self-host it, modify it, and rest assured you won't be locked into a single vendor.

PostgreSQL under the hood

You get the full power of PostgreSQL:

ACID transactions
Foreign keys and constraints
Full-text search
JSON/JSONB support
Extensions (PostGIS, pg_vector, etc.)
Row Level Security

Developer Experience

Excellent SDK, automatically generated API documentation, a dashboard for data management, and easy integration with popular frameworks.

Installation and configuration

Creating a project

Create an account on supabase.com
Create a new project
Save the URL and API keys

SDK installation

Code

Bash

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

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

Client configuration

TSlib/supabase.ts

TypeScript

// 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 })
}

Environment variables

.env.local

ENV

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

PostgreSQL database

Creating tables

In the Supabase Dashboard or via SQL:

Code

SQL

-- 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);

CRUD Operations

Code

TypeScript

// 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()

Advanced queries

Code

TypeScript

// 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')

Authentication

Email/Password

Code

TypeScript

// 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',
})

OAuth (Social Logins)

Code

TypeScript

// 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

Magic Link (Passwordless)

Code

TypeScript

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

User session

Code

TypeScript

// 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
  }
})

Next.js App Router integration

TSapp/auth/callback/route.ts

TypeScript

// 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))
}

TSmiddleware.ts

TypeScript

// 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*'],
}

Row Level Security (RLS)

RLS is a key security feature of Supabase. It allows you to define access policies at the row level.

Code

SQL

-- 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'
  )
);

Storage

Uploading files

Code

TypeScript

// 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)

Downloading and deleting

Code

TypeScript

// 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'])

Storage policies

Code

SQL

-- 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');

Real-time subscriptions

Code

TypeScript

// 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)
}

Broadcast (Custom Events)

Code

TypeScript

// 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)
})

Presence (Online Status)

Code

TypeScript

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(),
    })
  }
})

Edge Functions

Edge Functions are serverless functions written in TypeScript and run on Deno:

TSsupabase/functions/send-email/index.ts

TypeScript

// 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' } }
  )
})

Calling from the client:

Code

TypeScript

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

For a single message this call is enough. Once welcome sequences and trial expiry reminders appear, it is simpler to send the event itself from the function to a tool like Loops, which keeps the copy and the sending conditions on its own side. It bills per contact rather than per message, though, so a list of dead addresses costs the same as an active one.

TypeScript type generation

Code

Bash

# 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

Using generated types:

Code

TypeScript

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

Supabase vs Firebase comparison

Feature	Supabase	Firebase
Database	PostgreSQL (relational)	Firestore (NoSQL)
Open Source	Yes	No
Self-hosting	Yes	No
SQL	Full support	None
Relations	Foreign keys, JOINs	None
Real-time	PostgreSQL changes	Firestore snapshots
Auth	Similar capabilities	Similar capabilities
Storage	Similar capabilities	Similar capabilities
Edge Functions	Deno	Node.js
Pricing	More transparent	Pay-as-you-go

Pricing (2025)

Free

500 MB database
1 GB storage
2 GB bandwidth
50,000 MAU (auth)
500,000 Edge Function invocations

Pro ($25/month)

8 GB database
100 GB storage
250 GB bandwidth
100,000 MAU
2M Edge Function invocations
Daily backups

Team ($599/month)

Everything from Pro
SOC2 compliance
SSO/SAML
Priority support
14-day PITR

Enterprise (Custom)

Dedicated infrastructure
Custom contracts
SLA guarantees

Best practices

1. Always use RLS

Code

SQL

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

2. Use TypeScript types

Code

Bash

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

3. Optimize queries

Code

TypeScript

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

4. Use indexes

Code

SQL

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

FAQ

Is Supabase free?

Yes, the Free plan allows you to build and test applications. For production, the Pro plan is recommended.

Can I self-host Supabase?

Yes, all components are open-source. The documentation includes instructions for Docker and Kubernetes.

How to migrate from Firebase?

Supabase offers migration tools. The main change is transitioning from the NoSQL to the SQL data model.

Does Supabase scale?

Yes, PostgreSQL is known for good scalability. Supabase also offers read replicas and connection pooling.

Pricing and the free plan trap

Plan	Monthly price	Database	Files	Egress	Active users
Free	0 USD	500 MB	1 GB	5 GB	50k
Pro	25 USD	8 GB per project	100 GB	250 GB	100k
Team	599 USD	8 GB per project	100 GB	250 GB	100k
Enterprise	custom quote	negotiated	negotiated	negotiated	negotiated

The paid plan includes ten dollars of compute credit, covering the smallest instance, with anything larger charged on top. API request counts are unlimited on every plan.

The credit applies per organisation, though, while compute bills per project, and that is the commonest surprise on this platform. An organisation with one project on the smallest instance pays twenty five dollars, since the credit covers the whole of it. A second project on the same instance raises the bill to thirty five, although the plan has not changed. With several environments, where a separate project serves as the staging copy, count that line once per environment rather than once in total.

The most important thing about the free plan concerns not limits but pausing, though. Free projects are paused after a week of inactivity, and the active project limit is two. For learning and experiments that hardly matters, since a project resumes manually. For anything meant to be reachable by other people it is disqualifying: an application shown to a client after a two week gap will not answer.

When estimating costs, watch egress, since it is the most commonly underestimated line. Serving images straight from file storage consumes that allowance faster than anything else, particularly when files do not pass through a content delivery network. If your application hands out photographs, price that separately before choosing a plan.

Common mistakes

The first is a table without access rules, covered above. It is the only mistake on this list ending in a data leak rather than a failure.

The second is holding connections without pooling. PostgreSQL enforces a hard cap on concurrent connections, and a runtime invoking functions on demand opens a new one per call. Supabase exposes a separate pooled address, and that is the one stateless code should use. Schema migrations and long administrative jobs need a direct connection, though, since the pool's transaction mode does not support everything.

The third is relying on the auto generated API for complex logic. An interface exposing tables works beautifully for reading a list and writing a record, and stops sufficing once one operation has to change three tables atomically. That calls for database functions invoked as procedures, not a chain of separate requests from a browser, since that chain carries no consistency guarantee.

The fourth concerns realtime change subscriptions. Wiring them into everything is tempting, but each subscription is an open connection and a stream of events to handle client side. For a list updated once an hour, an ordinary fetch is cheaper and simpler; save the realtime mechanism for what genuinely changes before a user's eyes.

The fifth is forgetting migrations. Clicking through the dashboard is fast and convenient, and six months later nobody knows why a column looks the way it does or how to recreate the database from scratch. Keeping schema changes in migration files in the repository is the same discipline as keeping code in version control.

The sixth, visible only under the first serious traffic, is the absence of indexes for the queries you actually run. The database assumes an index on the primary key and on foreign keys, but not on the column you filter a list by, nor on the combination of columns used for sorting. At a few hundred rows the difference is invisible, since the database reads everything anyway; at a few hundred thousand the same page stops opening. Look through the slowest queries in the dashboard occasionally and check whether any of them scans a whole table.

The source lives in the Supabase repository, and current plans on the pricing page.