Database — Using Filters

Narrow results with a small set of composable operators.
Chain filters on a table, then execute with select().

Prerequisite: You have a client created with a dbSpec (schema + relations [+ optional seed, meta]) and an adapter factory for SQLite Web/Expo or Supabase.

Execution Rule

• In v1.0, .select(projection) executes the query.
• Add all filters before calling .select(...).

How filters combine

• Each filter call adds an AND clause.
• Chain freely: .eq('published', true).gt('created_at', someDate)
• You can also use a single object: .where({ published: true })

eq(column, value)

Match exact value.

1const users = await db
2  .from('users')
3  .eq('email', 'ada@example.com')
4  .select('id, name, email')

ne(column, value)

Not equal.

1const posts = await db
2  .from('posts')
3  .ne('published', true)
4  .select('id, title, published')

gt(column, value) / gte(column, value)

Greater than / greater than or equal.

1const cutoff = new Date('2024-01-01')
2const recent = await db
3  .from('posts')
4  .gt('created_at', cutoff) // or .gte('created_at', cutoff)
5  .select('id, title, created_at')

lt(column, value) / lte(column, value)

Less than / less than or equal.

1const until = new Date('2024-01-01')
2const older = await db
3  .from('posts')
4  .lt('created_at', until) // or .lte('created_at', until)
5  .select('id, title, created_at')

in(column, values[])

Column is one of the provided values.

1const byUsers = await db
2  .from('posts')
3  .in('user_id', [1, 2, 3])
4  .select('id, title, user_id')

like(column, pattern)

Case-sensitive pattern match (adapter/DB dependent). % is a wildcard.

1const guides = await db
2  .from('posts')
3  .like('title', '%guide%')
4  .select('id, title')

Composing filters (examples)

1// Draft posts for specific users in 2025
2const start = new Date('2025-01-01')
3const results = await db
4  .from('posts')
5  .eq('published', false)
6  .in('user_id', [1, 2])
7  .gte('created_at', start)
8  .select('id, title, user_id, created_at')
9
10// Name search excluding a specific email
11const matches = await db
12  .from('users')
13  .like('name', 'Ada%')
14  .ne('email', 'ada@old.example')
15  .select('id, name, email')

Types & validation

• Filtered column names are checked against dbSpec.schema.
• Values should match the column’s inferred TypeScript kind (e.g., Date for timestamp).
• Returns typed rows based on your projection.

Adapter notes

• SQLite (Web / Expo): filters run locally against SQLite; ensure your migrations (SQL DDL) align with schema kinds.
• Supabase (bundled in @vibecode-db/client): filters translate to Postgres via Supabase; ensure columns/types match and RLS permits reads.

Summary

Compose intent with .where(...) and operator helpers, then call .select(...) to execute. Keep projections tight for faster responses and clearer types.