Intermediate20 min1 prerequisite

Work with PostgreSQL database in Supabase: create tables, run queries, and handle data operations.

Database & Queries

Supabase runs PostgreSQL, giving you full SQL power with a simple JavaScript API.

Creating Tables

Using Dashboard

Go to Table Editor → New Table
Define columns with types
Set primary key (usually id)
Enable Row Level Security

Using SQL

Terminal

-- Create users table
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Create posts table with foreign key
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Create comments table
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content TEXT NOT NULL,
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now()
);

Common Column Types

Type	Use Case
`UUID`	Primary keys, references
`TEXT`	Strings of any length
`VARCHAR(n)`	Limited length strings
`INTEGER`	Whole numbers
`BIGINT`	Large whole numbers
`NUMERIC`	Precise decimals
`BOOLEAN`	True/false
`TIMESTAMPTZ`	Date and time with timezone
`DATE`	Date only
`JSONB`	JSON data (preferred)
`TEXT[]`	Array of strings

Basic Queries

Select Data

Terminal

const supabase = createClient()

// Get all rows
const { data, error } = await supabase
  .from('posts')
  .select('*')

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

// With filtering
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('published', true)

// Multiple filters
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('published', true)
  .eq('user_id', userId)

Insert Data

Terminal

// Insert single row
const { data, error } = await supabase
  .from('posts')
  .insert({
    title: 'My Post',
    content: 'Hello world',
    user_id: userId
  })
  .select()  // Return inserted row

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

Update Data

Terminal

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

// Update multiple conditions
const { data, error } = await supabase
  .from('posts')
  .update({ published: false })
  .eq('user_id', userId)
  .eq('published', true)
  .select()

Delete Data

Terminal

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

// Delete with multiple conditions
const { error } = await supabase
  .from('posts')
  .delete()
  .eq('user_id', userId)
  .eq('published', false)

Upsert (Insert or Update)

Terminal

// Insert or update based on primary key
const { data, error } = await supabase
  .from('profiles')
  .upsert({
    id: userId,
    name: 'New Name',
    updated_at: new Date().toISOString()
  })
  .select()

Filtering

Comparison Operators

Terminal

// Equal
.eq('status', 'active')

// Not equal
.neq('status', 'deleted')

// Greater than
.gt('price', 100)

// Greater than or equal
.gte('age', 18)

// Less than
.lt('quantity', 10)

// Less than or equal
.lte('priority', 5)

Text Operators

Terminal

// LIKE (case sensitive)
.like('title', '%hello%')

// ILIKE (case insensitive)
.ilike('title', '%hello%')

// Full-text search
.textSearch('content', 'hello world')

Array and Range

Terminal

// Value in list
.in('status', ['active', 'pending'])

// Contains (arrays)
.contains('tags', ['javascript'])

// Contained by
.containedBy('tags', ['js', 'ts', 'python'])

Null Handling

Terminal

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

// Is not null
.not('avatar_url', 'is', null)

Combining Filters

Terminal

// AND (chained)
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('published', true)
  .eq('user_id', userId)

// OR
const { data } = await supabase
  .from('posts')
  .select('*')
  .or('status.eq.active,status.eq.pending')

Sorting and Pagination

Order By

Terminal

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

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

Pagination

Terminal

// Limit results
const { data } = await supabase
  .from('posts')
  .select('*')
  .limit(10)

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

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

Relationships

Foreign Key Joins

Terminal

// Get post with author
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    author:users(id, name, avatar_url)
  `)

// Get post with comments and their authors
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    comments(
      id,
      content,
      author:users(name)
    )
  `)

Many-to-Many

Terminal

-- Junction table
CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Terminal

// Get posts with tags
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    post_tags(
      tags(id, name)
    )
  `)

Inner vs Left Join

Terminal

// Left join (default) - returns post even if no comments
const { data } = await supabase
  .from('posts')
  .select('*, comments(*)')

// Inner join - only returns posts with comments
const { data } = await supabase
  .from('posts')
  .select('*, comments!inner(*)')

Aggregations

Count

Terminal

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

// Count with filter
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })
  .eq('published', true)

Using SQL Functions

Terminal

// Via RPC (stored procedure)
const { data } = await supabase.rpc('get_post_stats', {
  post_id: postId
})

Terminal

-- Create function
CREATE FUNCTION get_post_stats(post_id UUID)
RETURNS TABLE (
  comment_count BIGINT,
  like_count BIGINT
) AS $$
  SELECT
    (SELECT COUNT(*) FROM comments WHERE post_id = $1),
    (SELECT COUNT(*) FROM likes WHERE post_id = $1)
$$ LANGUAGE SQL;

Error Handling

Check for Errors

Terminal

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

if (error) {
  console.error('Error:', error.message)
  // Handle error appropriately
  return
}

// Use data safely
console.log(data)

Common Errors

Error Code	Meaning
`PGRST116`	No rows returned (not always error)
`23505`	Unique constraint violation
`23503`	Foreign key violation
`42501`	RLS policy violation

Error Pattern

Terminal

async function createPost(post: PostInput) {
  const { data, error } = await supabase
    .from('posts')
    .insert(post)
    .select()
    .single()

  if (error) {
    if (error.code === '23505') {
      throw new Error('A post with this title already exists')
    }
    if (error.code === '42501') {
      throw new Error('You do not have permission to create posts')
    }
    throw new Error('Failed to create post')
  }

  return data
}

AI-Generated Query Patterns

CRUD Operations

Terminal

// AI typically generates complete CRUD
export const postService = {
  async getAll() {
    const { data, error } = await supabase
      .from('posts')
      .select('*')
      .order('created_at', { ascending: false })

    if (error) throw error
    return data
  },

  async getById(id: string) {
    const { data, error } = await supabase
      .from('posts')
      .select('*')
      .eq('id', id)
      .single()

    if (error) throw error
    return data
  },

  async create(post: PostInput) {
    const { data, error } = await supabase
      .from('posts')
      .insert(post)
      .select()
      .single()

    if (error) throw error
    return data
  },

  async update(id: string, updates: Partial<PostInput>) {
    const { data, error } = await supabase
      .from('posts')
      .update(updates)
      .eq('id', id)
      .select()
      .single()

    if (error) throw error
    return data
  },

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

    if (error) throw error
  }
}

Summary

Select: .from('table').select('columns')
Insert: .from('table').insert(data).select()
Update: .from('table').update(data).eq('id', id).select()
Delete: .from('table').delete().eq('id', id)
Filter: .eq(), .gt(), .like(), .in()
Sort: .order('column', { ascending: false })
Paginate: .range(from, to) or .limit(n)
Join: select('*, relation(*)')

Next Steps

Learn to implement user authentication with Supabase Auth.

Mark this lesson as complete to track your progress