Supabase SQL Query Cheat Sheet

Supabase is an open-source alternative to Firebase, offering a powerful Postgres-based database with built-in authentication, real-time capabilities, and storage. Whether you're a beginner or an experienced developer, mastering SQL queries is essential to harnessing the full potential of Supabase.

This cheat sheet provides a quick reference for common SQL operations, including CRUD operations, joins, filtering, indexing, row-level security, triggers, JSON handling, and more. Keep this guide handy to efficiently manage your Supabase database and optimize performance.

1. Basic CRUD Operations

Create (Insert Data)

INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com');

Read (Retrieve Data)

SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

Update Data

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

Delete Data

DELETE FROM users WHERE id = 1;

2. Joins & Relationships

Inner Join

SELECT users.name, orders.product_name
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Left Join

SELECT users.name, orders.product_name
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Right Join

SELECT users.name, orders.product_name
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

3. Filtering & Sorting

Filtering with WHERE Clause

SELECT * FROM users WHERE email LIKE '%@gmail.com';

Sorting Results

SELECT * FROM users ORDER BY name ASC;
SELECT * FROM orders ORDER BY created_at DESC;

4. Aggregations & Grouping

Counting Rows

SELECT COUNT(*) FROM users;

Grouping Data

SELECT role, COUNT(*) FROM users GROUP BY role;

Summing Data

SELECT SUM(price) FROM orders WHERE user_id = 1;

5. Indexes & Performance Optimization

Creating an Index

CREATE INDEX idx_users_email ON users(email);

Using EXPLAIN ANALYZE to Optimize Queries

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

6. Row Level Security (RLS)

Enable RLS on a Table

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Create RLS Policy for User-Specific Data Access

CREATE POLICY "user can view own data"
ON users
FOR SELECT
USING (auth.uid() = id);

Create RLS Policy with a Joined Table

CREATE POLICY "user can view their own orders"
ON orders
FOR SELECT
USING (
  auth.uid() = (SELECT user_id FROM users WHERE users.id = orders.user_id)
);

7. Trigger Functions

Create a Trigger for Auto-updating Timestamps

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

8. Working with JSON Data

Insert JSON Data

INSERT INTO logs (event_data) VALUES ('{"user": "John", "action": "login"}');

Query JSON Data

SELECT event_data->>'user' FROM logs;

9. Backup & Restore

Backup a Table

COPY users TO '/path/to/backup/users_backup.csv' WITH CSV HEADER;

Restore a Table

COPY users FROM '/path/to/backup/users_backup.csv' WITH CSV HEADER;

10. Advanced Queries for Supabase Users

Using Row Number for Pagination

SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num FROM users LIMIT 10;

Fetching the Last Inserted Record

SELECT * FROM users ORDER BY id DESC LIMIT 1;

Get Users Who Haven't Placed an Order

SELECT users.* FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE orders.id IS NULL;

Supabase offers a scalable database, but effective management requires strong SQL skills. This cheat sheet is a quick reference, and for visualizing data, Draxlr can help.

Draxlr is a no-code self-service BI tool that seamlessly integrates with Supabase, allowing you to create dashboards and set alerts without writing complex SQL queries. If you want better insights and streamlined reporting, try Draxlr to get the most out of your Supabase data.

Bookmark this cheat sheet for easy access to essential SQL queries!