Filters, Joins & Raw SQL

All filter methods below are available on select(), update(), and delete() chains.

Filter Operators

• .eq(col, val): col = val

• .neq(col, val): col != val

• .gt(col, val): col > val

• .gte(col, val): col >= val

• .lt(col, val): col < val

• .lte(col, val): col <= val

• .like(col, pattern): col LIKE pattern

• .ilike(col, pattern): col ILIKE pattern

• .in(col, values): col IN (values)

• .is(col, null | boolean): col IS NULL / TRUE / FALSE

• .contains(col, val): col @> val

• .overlaps(col, val): col && val

• .textSearch(col, query): full-text search

• .or(filters): col = val OR col = val

• .not(col, op, val): NOT col op val

.or() — Supabase-compatible filter string

Pass a Supabase-style filter string and the SDK parses it into structured filters before sending to the server. Commas separate OR conditions; values with commas are safe inside parentheses (used by in).

// Simple OR: match either condition
const { data } = await postbase
  .from('users')
  .select()
  .or('email.ilike.%alice%,name.ilike.%alice%')

// OR with in operator — values in parens are safe
const { data } = await postbase
  .from('orders')
  .select()
  .or('status.eq.active,status.in.(pending,review)')

// Combine OR with AND filters — the .eq() is ANDed with the OR group
const { data } = await postbase
  .from('posts')
  .select()
  .eq('published', true)
  .or('title.ilike.%hello%,body.ilike.%hello%')

Supported operators inside .or(): eq, neq, gt, gte, lt, lte, like, ilike, in, is

Joins

Use .join() to combine data from related tables. Chains are immutable and can be stacked.

// Left join — include orders even if no matching user
const { data } = await postbase
  .from('orders')
  .join('users', { on: 'orders.user_id = users.id', type: 'left' })
  .select('orders.id, orders.total, users.email')

// Multiple joins
const { data } = await postbase
  .from('orders')
  .join('users',    { on: 'orders.user_id = users.id',       type: 'left' })
  .join('products', { on: 'orders.product_id = products.id' })
  .select('orders.id, users.email, products.name')
  .eq('orders.status', 'active')
  .order('orders.created_at', { ascending: false })
  .limit(20)

Join types (type defaults to inner if omitted):

• 'inner': INNER JOIN

• 'left': LEFT JOIN

• 'right': RIGHT JOIN

• 'full': FULL OUTER JOIN

on expression rules — the server validates the on string against a strict allow-list:

• table.column = table.column

• Comparison operators: =, <, >, !=, <=, >=

• Identifiers and dotted column references only — no raw SQL, no functions, no subqueries

// Valid
{ on: 'orders.user_id = users.id' }
{ on: 'order_items.order_id = orders.id' }

// Invalid — will be rejected by the server
{ on: 'orders.user_id = users.id AND users.active = true' }  // AND not allowed
{ on: "orders.status = 'active'" }                           // string literals not allowed

Selecting columns from joined tables — use table.column notation in .select():

.select('orders.id, users.email, products.name, products.price')

Column aliases — when two joined tables share a column name (e.g. both have id or name), use AS to rename them. The SDK strips the alias before sending to the server and renames the keys in the returned rows client-side.

const { data } = await postbase
  .from('apis')
  .join('pricing_plans', { on: 'apis.pricing_plan_id = pricing_plans.id', type: 'left' })
  .select('apis.id as api_id, apis.name, pricing_plans.id as plan_id, pricing_plans.name as plan_name')
// data[0] → { api_id: '...', name: '...', plan_id: '...', plan_name: '...' }

Limitation: if you select two columns with the same base name without aliasing both (e.g. apis.id, pricing_plans.id), the server collapses them to one id key before the SDK sees the response — only one value survives. Always alias at least all but one of any colliding columns.

Raw SQL

For complex queries that can’t be expressed with the builder (multi-table aggregates, CTEs, window functions), use postbase.sql(). RLS context is still enforced — the authenticated user’s JWT is forwarded exactly as with .from().

// Simple parameterized query
const { data, error } = await postbase.sql<{ id: string; email: string }>(
  `SELECT o.id, u.email
   FROM orders o
   INNER JOIN users u ON o.user_id = u.id
   WHERE o.status = $1`,
  ['active']
)

// Multiple params
const { data } = await postbase.sql<{ title: string; count: number }>(
  `SELECT p.title, COUNT(c.id) AS count
   FROM posts p
   LEFT JOIN comments c ON c.post_id = p.id
   WHERE p.author_id = $1 AND p.status = $2
   GROUP BY p.id, p.title
   ORDER BY count DESC
   LIMIT $3`,
  [userId, 'published', 10]
)

Params replace $1, $2, $3, … placeholders (standard PostgreSQL positional parameters). Never interpolate values directly into the query string — always use params to prevent SQL injection.