Database: Select and Join

Basic Select Queries

Reading data is the most common database operation you will perform. Supabase's .select() method maps directly to SQL's SELECT statement. The simplest form fetches every column from a table:

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

This returns an array of all rows the user is allowed to see (RLS filters are applied automatically). For real applications you should always specify the columns you actually need instead of using *. This reduces payload size and makes your intent clear:

const { data, error } = await supabase
  .from('posts')
  .select('id, title, created_at');

If you expect a single row — for example, fetching a post by its ID — use .single() at the end:

const { data, error } = await supabase
  .from('posts')
  .select('*')
  .eq('id', 42)
  .single();

Without .single(), you always get an array, even for one result. With it, you get the row object directly, and an error if zero or multiple rows match.

Filtering

Supabase provides a rich set of filter methods that chain onto your query. The most common ones are:

• .eq(column, value) — exact match
• .neq(column, value) — not equal
• .gt(column, value) — greater than
• .lt(column, value) — less than
• .gte(column, value) — greater than or equal
• .lte(column, value) — less than or equal
• .like(column, pattern) — SQL LIKE (case-sensitive)
• .ilike(column, pattern) — case-insensitive LIKE
• .in(column, values) — matches any value in an array
• .or(filter1, filter2) — OR condition
• .contains(column, value) — for JSON/array columns

Filters can be chained arbitrarily. For example, finding recent posts with a specific word in the title:

const { data, error } = await supabase
  .from('posts')
  .select('id, title, created_at')
  .ilike('title', '%supabase%')
  .gte('created_at', '2025-01-01')
  .order('created_at', { ascending: false });

Ordering and Limiting

Use .order() to sort results and .limit() to cap the number returned:

const { data, error } = await supabase
  .from('posts')
  .select('id, title')
  .order('created_at', { ascending: false })
  .limit(10);

For pagination, .range(start, end) is the go-to tool. It maps to SQL's OFFSET and LIMIT, returning rows from start to end (inclusive, zero-indexed):

const page = 2;
const pageSize = 20;
const { data, error } = await supabase
  .from('posts')
  .select('id, title')
  .order('created_at', { ascending: false })
  .range(page * pageSize, (page + 1) * pageSize - 1);

Joins

One of Supabase's most powerful features is automatic join resolution. When you define a foreign key relationship — like user_id referencing auth.users(id) — Supabase knows about it through its schema cache and lets you pull in related data with a simple syntax.

To include the author's email alongside each post, use the !inner or plain relation syntax:

const { data, error } = await supabase
  .from('posts')
  .select('id, title, content, user_id ( email )');

This fetches every post and, for each one, resolves the user_id foreign key and returns the user's email. The result looks like:

[
  { id: 1, title: 'Hello', content: '...', user_id: { email: 'alice@example.com' } },
  { id: 2, title: 'World', content: '...', user_id: { email: 'bob@example.com' } }
]

If you need an inner join (exclude posts whose user no longer exists), use the !inner hint:

.select('id, title, user_id!inner ( email )')

You can also traverse multiple levels: comments ( user_id ( email ) ) would pull the comment author's email for each comment on a post.

Aggregations

To get a row count without fetching the actual rows, pass count and head options:

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

The head: true tells Supabase not to return rows — just the count. This is efficient for pagination displays ("Showing 1–20 of 342 posts").

Performance Tips

• Always select only the columns you need. .select('*') is convenient but wasteful.
• Add database indexes on columns you filter or sort by frequently (created_at, user_id, status). Without an index, Postgres does a sequential scan, which gets slow as your table grows.
• Use .limit() and .range() on every list query to prevent accidentally returning millions of rows.
• When joining, avoid fetching nested data you do not use. Each join adds cost.