SQL Basics for Supabase

Essential SQL queries to get, filter and join your data

Last updated: 8/15/2025

SQL is just a way to ask your database questions. Master these patterns and you can get any data you need.

The Core Question: SELECT

Every SQL query starts with "I want to SELECT some data..."

-- Get everything from a table
SELECT * FROM customers;

-- Get specific columns
SELECT id, email, name FROM customers;

-- Get unique values
SELECT DISTINCT city FROM customers;

Translation: "Show me the id, email and name columns from the customers table."

Filtering with WHERE

Add conditions to narrow down results:

-- Single condition
SELECT * FROM customers WHERE city = 'Brisbane';

-- Multiple conditions
SELECT * FROM orders 
WHERE total > 100 AND created_at > '2025-01-01';

-- Pattern matching
SELECT * FROM customers WHERE email LIKE '%@gmail.com';

-- Lists of values
SELECT * FROM products WHERE category IN ('electronics', 'books');

Sorting and Limiting

Control the order and amount of results:

-- Sort by one column
SELECT * FROM customers ORDER BY name;

-- Sort by multiple columns
SELECT * FROM orders ORDER BY created_at DESC, total ASC;

-- Limit results
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- Skip and limit (pagination)
SELECT * FROM customers ORDER BY name LIMIT 20 OFFSET 40;

Joining Related Data

Connect tables to get related information in one query:

-- Inner join: customers and their orders
SELECT 
  c.name,
  c.email,
  o.total,
  o.created_at
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Left join: all customers, even those without orders
SELECT 
  c.name,
  c.email,
  o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Real-World Join Example

Get customer names with their order totals:

SELECT 
  c.name AS customer_name,
  c.email,
  o.id AS order_id,
  o.total,
  o.created_at AS order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at > '2025-01-01'
ORDER BY o.created_at DESC;

Aggregating Data

Count, sum and calculate across multiple rows:

-- Count rows
SELECT COUNT(*) FROM customers;

-- Sum values
SELECT SUM(total) FROM orders;

-- Group by categories
SELECT 
  city,
  COUNT(*) as customer_count
FROM customers 
GROUP BY city
ORDER BY customer_count DESC;

-- Multiple aggregations
SELECT 
  c.city,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_revenue,
  AVG(o.total) as average_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.city;

Common Patterns for Apps

Get User's Recent Activity

SELECT 
  o.id,
  o.total,
  o.created_at,
  COUNT(oi.id) as item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = 'user-uuid-here'
GROUP BY o.id, o.total, o.created_at
ORDER BY o.created_at DESC
LIMIT 10;

Search Products

SELECT 
  p.*,
  c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE 
  p.name ILIKE '%search-term%' 
  OR p.description ILIKE '%search-term%'
ORDER BY p.name;

Dashboard Statistics

-- Revenue by month
SELECT 
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

How SQL Translates to Supabase APIs

Supabase automatically converts your tables into REST endpoints. Here's how SQL maps to API calls:

Basic Queries

-- SQL
SELECT id, name, email FROM customers WHERE city = 'Brisbane';

// Supabase client
const { data } = await supabase
  .from('customers')
  .select('id, name, email')
  .eq('city', 'Brisbane');

Joins

-- SQL
SELECT c.name, o.total 
FROM customers c 
JOIN orders o ON c.id = o.customer_id;

// Supabase client
const { data } = await supabase
  .from('customers')
  .select('name, orders(total)');

SQL Functions for Advanced Logic

Create reusable database functions:

-- Function to get customer order summary
CREATE OR REPLACE FUNCTION get_customer_summary(customer_uuid UUID)
RETURNS TABLE(
  total_orders INTEGER,
  total_spent DECIMAL,
  last_order_date TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COUNT(o.id)::INTEGER,
    COALESCE(SUM(o.total), 0),
    MAX(o.created_at)
  FROM orders o
  WHERE o.customer_id = customer_uuid;
END;
$$ LANGUAGE plpgsql;

Call from your app:

const { data } = await supabase
  .rpc('get_customer_summary', { customer_uuid: userId });

SQL Best Practices

Use meaningful aliases:

-- Clear and readable
SELECT 
  c.name AS customer_name,
  o.total AS order_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Index frequently queried columns:

-- Speed up email lookups
CREATE INDEX idx_customers_email ON customers(email);

-- Speed up date range queries
CREATE INDEX idx_orders_created_at ON orders(created_at);

Always use WHERE with UPDATE/DELETE:

-- Good: specific update
UPDATE customers 
SET city = 'Brisbane' 
WHERE id = 'specific-customer-id';

-- Dangerous: updates everything
UPDATE customers SET city = 'Brisbane';

SQL is your direct line to getting exactly the data you need. Start with simple SELECT statements and gradually add JOINs and aggregations as your queries become more sophisticated.

Learn