Supabase Database (PostgreSQL) - Supabase Tutorial | TechLead

Supabase Database

Supabase provides a full PostgreSQL database with auto-generated APIs. You get all the power of PostgreSQL including complex queries, joins, indexes, and extensions.

🗄️ Database Features

Full PostgreSQL: All SQL features and extensions
Auto APIs: REST and GraphQL generated automatically
Table Editor: Visual interface for data management
SQL Editor: Run raw SQL queries in the dashboard

Creating Tables

Create tables using SQL in the Supabase SQL Editor:

-- Create a posts table
CREATE TABLE posts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  user_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  published BOOLEAN DEFAULT false
);

-- Create an index for faster queries
CREATE INDEX posts_user_id_idx ON posts(user_id);

CRUD Operations

Insert Data

// Insert a single row
const { data, error } = await supabase
  .from('posts')
  .insert({ title: 'My First Post', content: 'Hello World!' })
  .select()

// Insert multiple rows
const { data, error } = await supabase
  .from('posts')
  .insert([
    { title: 'Post 1', content: 'Content 1' },
    { title: 'Post 2', content: 'Content 2' }
  ])
  .select()

Select Data

// Select all columns
const { data } = await supabase.from('posts').select('*')

// Select specific columns
const { data } = await supabase.from('posts').select('id, title')

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

Update Data

const { data, error } = await supabase
  .from('posts')
  .update({ title: 'Updated Title', published: true })
  .eq('id', postId)
  .select()

Delete Data

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

Filtering Queries

// Equal
.eq('column', 'value')

// Not equal
.neq('column', 'value')

// Greater than / Less than
.gt('column', value)
.lt('column', value)
.gte('column', value)
.lte('column', value)

// Pattern matching
.like('column', '%pattern%')
.ilike('column', '%pattern%')  // case-insensitive

// In array
.in('column', ['value1', 'value2'])

// Is null
.is('column', null)

// Contains (for arrays/JSON)
.contains('tags', ['react', 'nextjs'])

Joins and Relations

// Fetch posts with author info
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    user:users (
      id,
      name,
      email
    )
  `)

// Nested relations
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    comments (
      id,
      content,
      user:users (name)
    )
  `)

Pagination

// Range-based pagination
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(0, 9)  // First 10 items (0-9)

// With count
const { data, count } = await supabase
  .from('posts')
  .select('*', { count: 'exact' })
  .range(0, 9)

💡 Key Takeaways

• Supabase uses PostgreSQL - you get full SQL power
• Auto-generated APIs make CRUD operations simple
• Chain filter methods for complex queries
• Use select() with relations for efficient joins

📚 Learn More

Database Documentation →
Complete guide to Supabase database features.
Query Reference →
Full API reference for database queries.