Query Builder

Advanced query patterns with the Postbase SDK.

Overview

The SDK provides a Supabase-compatible query builder powered by Kysely:

const users = await db
  .from('users')
  .eq('active', true)
  .order('created_at', { ascending: false })
  .limit(10)
  .execute()

Select Queries

Basic Select

// Select all rows
const users = await db.from('users').execute()
 
// Select specific columns
const names = await db
  .from('users')
  .select('id', 'name', 'email')
  .execute()

Single Row

// Get one row (throws if not found)
const user = await db
  .from('users')
  .eq('id', 1)
  .single()
 
// Get one row (returns null if not found)
const user = await db
  .from('users')
  .eq('id', 1)
  .maybeSingle()

Filter Methods

Equality

// Equal
.eq('column', value)
 
// Not equal
.neq('column', value)

Comparison

// Greater than
.gt('age', 18)
 
// Greater than or equal
.gte('age', 18)
 
// Less than
.lt('age', 65)
 
// Less than or equal
.lte('age', 65)

Pattern Matching

// LIKE (case-sensitive)
.like('name', 'John%')
 
// ILIKE (case-insensitive)
.ilike('email', '%@gmail.com')

Lists

// IN
.in('status', ['active', 'pending'])
 
// NOT IN
.notIn('status', ['deleted', 'banned'])

Null Checks

// IS NULL
.is('deleted_at', null)
 
// IS NOT NULL
.not('email_verified', null)

Range

// BETWEEN
.between('age', 18, 65)

Text Search

// Full-text search
.textSearch('content', 'hello world')
 
// With configuration
.textSearch('content', 'hello world', { config: 'english' })

Combining Filters

AND (default)

// Multiple .eq() are ANDed
const users = await db
  .from('users')
  .eq('active', true)
  .eq('role', 'admin')
  .execute()
// WHERE active = true AND role = 'admin'

Complex Conditions

For OR and complex conditions, use raw SQL:

const users = await db
  .from('users')
  .where(sql`active = true OR role = 'admin'`)
  .execute()

Ordering

Single Column

// Ascending (default)
.order('name')
 
// Descending
.order('created_at', { ascending: false })

Multiple Columns

.order('status')
.order('created_at', { ascending: false })
// ORDER BY status ASC, created_at DESC

Null Handling

.order('name', { nullsFirst: true })
.order('name', { nullsFirst: false })  // nulls last

Pagination

Limit & Offset

// First 10 rows
.limit(10)
 
// Skip first 20, take next 10
.limit(10)
.offset(20)

Range (convenience)

// Rows 0-9 (first 10)
.range(0, 9)
 
// Rows 10-19 (second page)
.range(10, 19)

Insert Queries

Single Insert

const user = await db
  .from('users')
  .insert({
    email: 'alice@example.com',
    name: 'Alice',
  })
  .returning()
  .single()

Multiple Insert

const users = await db
  .from('users')
  .insert([
    { email: 'alice@example.com', name: 'Alice' },
    { email: 'bob@example.com', name: 'Bob' },
  ])
  .returning()
  .execute()

Upsert

// Insert or update on conflict
const user = await db
  .from('users')
  .upsert(
    { email: 'alice@example.com', name: 'Alice Updated' },
    { onConflict: 'email' }
  )
  .returning()
  .single()

Update Queries

Update with Filter

await db
  .from('users')
  .eq('id', 1)
  .update({ name: 'New Name' })

Update Multiple

await db
  .from('users')
  .eq('active', false)
  .update({ deleted_at: new Date() })

Update with Returning

const updated = await db
  .from('users')
  .eq('id', 1)
  .update({ name: 'New Name' })
  .returning()
  .single()

Delete Queries

Delete with Filter

await db
  .from('users')
  .eq('id', 1)
  .delete()

Soft Delete Pattern

// Soft delete (recommended)
await db
  .from('users')
  .eq('id', 1)
  .update({ deleted_at: new Date() })
 
// Query non-deleted
const users = await db
  .from('users')
  .is('deleted_at', null)
  .execute()

Counting

// Count all rows
const count = await db
  .from('users')
  .count()
 
// Count with filter
const activeCount = await db
  .from('users')
  .eq('active', true)
  .count()

Raw SQL

For complex queries not supported by the builder:

import { sql } from '@postbase/sdk'
 
// Raw where clause
const users = await db
  .from('users')
  .where(sql`created_at > NOW() - INTERVAL '7 days'`)
  .execute()
 
// Full raw query
const result = await db.raw(sql`
  SELECT u.*, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  ORDER BY post_count DESC
`)

Type Safety

With generated types, all queries are fully typed:

import type { Database } from './db/types'
 
const db = createClient<Database>({ connectionString: '...' })
 
// TypeScript knows the shape
const user = await db.from('users').eq('id', 1).single()
user.email  // string
user.name   // string
user.foo    // Error: Property 'foo' does not exist
 
// Insert validation
await db.from('users').insert({
  email: 'test@example.com',
  name: 'Test',
  // missing required fields = TypeScript error
})

Performance Tips

Use Indexes

Ensure columns used in filters have indexes:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active);

Limit Results

Always use .limit() for large tables:

// Good
const users = await db.from('users').limit(100).execute()
 
// Bad (loads all rows)
const users = await db.from('users').execute()

Select Only Needed Columns

// Good
const names = await db.from('users').select('id', 'name').execute()
 
// Less efficient (selects all columns)
const users = await db.from('users').execute()