SELECT Queries

Master the art of selecting data with genaql's intuitive syntax.

Basic SELECT

basic.ts
// Select all columns
cook`main:users slay:*`
// → SELECT * FROM users

// Select specific columns
cook`main:users slay:id,name,email`
// → SELECT id, name, email FROM users

Column Aliases

aliases.ts
// Alias with @
cook`main:users slay:id,name@username,email@contact`
// → SELECT id, name AS username, email AS contact FROM users

WHERE Conditions

where.ts
// Simple condition
cook`main:users slay:* sus:active=true`
// → SELECT * FROM users WHERE active = $1

// Multiple conditions (AND)
cook`main:users slay:* sus:active=true sus:role=admin`
// → SELECT * FROM users WHERE active = $1 AND role = $2

// Comma-separated (also AND)
cook`main:users slay:* sus:active=true,role=admin`
// → SELECT * FROM users WHERE active = $1 AND role = $2

// OR conditions with pipe
cook`main:users slay:* sus:role=admin|role=moderator`
// → SELECT * FROM users WHERE role = $1 OR role = $2

Comparison Operators

operators.ts
// Greater than
cook`main:users slay:* sus:age>18`

// Less than or equal
cook`main:products slay:* sus:price<=100`

// Not equal
cook`main:users slay:* sus:status!=banned`

// LIKE pattern matching
cook`main:users slay:* sus:email~%@gmail.com`
// → WHERE email LIKE $1  (params: ['%@gmail.com'])

// IN clause
cook`main:users slay:* sus:role.in(admin,moderator,editor)`
// → WHERE role IN ($1, $2, $3)

// NULL checks
cook`main:users slay:* sus:deleted_at.null`
// → WHERE deleted_at IS NULL

cook`main:users slay:* sus:email.!null`
// → WHERE email IS NOT NULL

Ordering Results

ordering.ts
// Default ascending
cook`main:users slay:* vibe:name`
// → ORDER BY name ASC

// Descending
cook`main:users slay:* vibe:created_at/desc`
// → ORDER BY created_at DESC

// Multiple columns
cook`main:users slay:* vibe:role,name/desc`
// → ORDER BY role ASC, name DESC

Pagination

pagination.ts
// Limit results
cook`main:users slay:* bet:10`
// → LIMIT 10

// Offset for pagination
cook`main:users slay:* bet:10 skip:20`
// → LIMIT 10 OFFSET 20

// Page 3 with 10 items per page
const page = 3;
const perPage = 10;
cook`main:users slay:* bet:${perPage} skip:${(page - 1) * perPage}`

DISTINCT

distinct.ts
// Select distinct values
cook`main:users slay:role/distinct`
// → SELECT DISTINCT role FROM users

// Count distinct
cook`main:orders slay:cnt:user_id/distinct`
// → SELECT COUNT(DISTINCT user_id) FROM orders

Pro Tip

Chain utilities in any order — genaql will generate valid SQL regardless of how you arrange your clauses.