where - 🌲 Sonamu

where is a method that specifies conditions to filter query results. It supports various operators and condition combinations, enabling type-safe filtering.

Basic Usage

Simple Equality Condition

const users = await puri
  .table("users")
  .where("users.status", "active")
  .select({ id: "users.id", name: "users.name" });

// WHERE users.status = 'active'

Comparison Operators

// Greater than
await puri.table("users").where("users.age", ">", 18);

// Less than or equal
await puri.table("orders").where("orders.amount", "<=", 1000);

// Not equal
await puri.table("posts").where("posts.status", "!=", "deleted");

Supported Operators:

= - Equal (default)
!= - Not equal
> - Greater than
>= - Greater than or equal
< - Less than
<= - Less than or equal
like - Pattern matching
not like - Pattern mismatch
ilike - Case-insensitive pattern matching (PostgreSQL)
not ilike - Case-insensitive pattern mismatch (PostgreSQL)

Object-Style Conditions

You can specify multiple AND conditions as an object.

const users = await puri
  .table("users")
  .where({
    "users.status": "active",
    "users.role": "admin",
    "users.verified": true,
  })
  .select({ id: "users.id", name: "users.name" });

// WHERE users.status = 'active'
//   AND users.role = 'admin'
//   AND users.verified = true

NULL Handling

IS NULL

// Explicit null check
await puri.table("users").where("users.deleted_at", null);

// WHERE users.deleted_at IS NULL

// Using operator
await puri.table("users").where("users.deleted_at", "=", null);

// WHERE users.deleted_at IS NULL

IS NOT NULL

await puri.table("users").where("users.email", "!=", null);

// WHERE users.email IS NOT NULL

Pattern Matching (LIKE)

Partial Matching

// Contains
await puri.table("users").where("users.email", "like", "%@gmail.com");

// Starts with
await puri.table("users").where("users.name", "like", "John%");

// Ends with
await puri.table("users").where("users.phone", "like", "%1234");

// Contains anywhere
await puri.table("posts").where("posts.title", "like", "%typescript%");

NOT LIKE

await puri.table("users").where("users.email", "not like", "%spam%");

// WHERE users.email NOT LIKE '%spam%'

ILIKE (Case-Insensitive, PostgreSQL)

Performs case-insensitive pattern matching in PostgreSQL.

// Search regardless of case
await puri.table("users").where("users.name", "ilike", "%john%");

// WHERE users.name ILIKE '%john%'
// Matches 'John', 'JOHN', 'john', etc.

NOT ILIKE (PostgreSQL)

await puri.table("users").where("users.email", "not ilike", "%SPAM%");

// WHERE users.email NOT ILIKE '%SPAM%'
// Excludes 'spam', 'Spam', 'SPAM', etc.

ilike and not ilike are PostgreSQL’s case-insensitive pattern matching operators.

whereIn / whereNotIn

One of Multiple Values

// IN
const userIds = [1, 2, 3, 4, 5];
await puri.table("users").whereIn("users.id", userIds);

// WHERE users.id IN (1, 2, 3, 4, 5)

// NOT IN
await puri.table("users").whereNotIn("users.status", ["banned", "suspended"]);

// WHERE users.status NOT IN ('banned', 'suspended')

Empty Array Handling

// Empty array always returns false
await puri.table("users").whereIn("users.id", []);

// WHERE 1 = 0 (returns nothing)

Complex Condition Groups

whereGroup (AND Group)

Creates conditions wrapped in parentheses.

await puri
  .table("users")
  .where("users.role", "admin")
  .whereGroup((group) => {
    group.where("users.status", "active").orWhere("users.status", "pending");
  });

// WHERE users.role = 'admin'
//   AND (users.status = 'active' OR users.status = 'pending')

orWhereGroup (OR Group)

await puri
  .table("products")
  .where("products.published", true)
  .orWhereGroup((group) => {
    group.where("products.featured", true).where("products.stock", ">", 0);
  });

// WHERE products.published = true
//   OR (products.featured = true AND products.stock > 0)

Nested Groups

await puri.table("orders").whereGroup((outer) => {
  outer.where("orders.status", "completed").whereGroup((inner) => {
    inner.where("orders.amount", ">", 1000).orWhere("orders.vip", true);
  });
});

// WHERE (
//   orders.status = 'completed'
//   AND (orders.amount > 1000 OR orders.vip = true)
// )

Full-Text Search

whereMatch (MySQL)

Uses MySQL’s FULLTEXT index.

await puri.table("posts").whereMatch("posts.content", "typescript programming");

// WHERE MATCH (posts.content) AGAINST ('typescript programming')

To use whereMatch, the column must have a FULLTEXT index.

whereTsSearch (PostgreSQL)

Uses PostgreSQL’s tsvector.

await puri.table("posts").whereTsSearch("posts.content_tsv", "typescript programming");

// WHERE posts.content_tsv @@ websearch_to_tsquery('simple', 'typescript programming')

Options:

// Specify parser
await puri.table("posts").whereTsSearch("posts.content_tsv", "typescript", {
  parser: "plainto_tsquery", // or "to_tsquery", "phraseto_tsquery"
  config: "english", // Language configuration
});

// Specify config only
await puri.table("posts").whereTsSearch("posts.content_tsv", "typescript", "english");

whereSearch (PGroonga)

Full-text search using the PGroonga extension.

// Single column search
await puri.table("posts").whereSearch("posts.title", "타입스크립트");

// WHERE posts.title &@~ pgroonga_condition('타입스크립트')

// Multiple column search (array)
await puri.table("posts").whereSearch(["posts.title", "posts.content"], "타입스크립트");

// WHERE ARRAY[posts.title::text, posts.content::text]
//   &@~ pgroonga_condition('타입스크립트')

// Specify weights
await puri.table("posts").whereSearch(
  ["posts.title", "posts.content"],
  "타입스크립트",
  { weights: [10, 1] }, // title is 10x more important than content
);

PGroonga search must use the same column configuration as the index to be used.

whereFuzzy (pg_trgm)

Fuzzy string matching using PostgreSQL’s pg_trgm extension. Based on trigrams, it is tolerant of typos. Combined with generated columns and GIN indexes, it is effective for multi-column search.

// Basic usage (word similarity, <% operator)
await puri.table("items").whereFuzzy("items.search_text", "typscript");

// WHERE 'typscript'::text <% items.search_text::text

Specifying operators:

// similarity (% operator)
await puri.table("items").whereFuzzy("items.search_text", "typscript", { operator: "%" });

// WHERE items.search_text::text % 'typscript'::text

// strict word similarity (<<% operator)
await puri.table("items").whereFuzzy("items.search_text", "typscript", { operator: "<<%" });

// WHERE 'typscript'::text <<% items.search_text::text

Operator comparison:

Operator	Meaning	SQL	Description
`<%` (default)	word similarity	`'query'::text <% column::text`	Checks if query is similar to a substring of the column
`%`	similarity	`column::text % 'query'::text`	Compares overall string similarity
`<<%`	strict word similarity	`'query'::text <<% column::text`	Stricter substring matching than word similarity

whereFuzzy requires the pg_trgm extension. Install it with CREATE EXTENSION IF NOT EXISTS pg_trgm. Creating a GIN index (gin_trgm_ops) significantly improves performance.

Raw SQL Conditions

whereRaw

You can write complex SQL conditions directly.

// No parameters
await puri.table("users").whereRaw("EXTRACT(YEAR FROM users.created_at) = EXTRACT(YEAR FROM CURRENT_DATE)");

// Parameter binding
await puri.table("users").whereRaw("users.age BETWEEN ? AND ?", [18, 65]);

// Multiple conditions
await puri
  .table("orders")
  .whereRaw("DATE(orders.created_at) = ?", ["2024-01-01"])
  .whereRaw("orders.amount > ?", [1000]);

Always use parameter binding to prevent SQL injection.

Real-World Examples

User Filtering
Order Search
Complex Conditions
Full-Text Search
Date Range

async function getActiveAdmins() {
  return puri.table("users")
    .where({
      "users.role": "admin",
      "users.status": "active",
      "users.email_verified": true
    })
    .where("users.last_login", ">", new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
    .select({
      id: "users.id",
      email: "users.email",
      name: "users.name",
      last_login: "users.last_login"
    })
    .orderBy("users.last_login", "desc");
}

async function searchOrders(filters: {
  status?: string;
  minAmount?: number;
  userIds?: number[];
  dateFrom?: Date;
  dateTo?: Date;
}) {
  let query = puri.table("orders").selectAll();

  // Status filter
  if (filters.status) {
    query = query.where("orders.status", filters.status);
  }

  // Amount range
  if (filters.minAmount) {
    query = query.where("orders.amount", ">=", filters.minAmount);
  }

  // User list
  if (filters.userIds && filters.userIds.length > 0) {
    query = query.whereIn("orders.user_id", filters.userIds);
  }

  // Date range
  if (filters.dateFrom) {
    query = query.where("orders.created_at", ">=", filters.dateFrom);
  }
  if (filters.dateTo) {
    query = query.where("orders.created_at", "<=", filters.dateTo);
  }

  return query.orderBy("orders.created_at", "desc");
}

async function getEligibleProducts() {
  return puri.table("products")
    // Basic conditions
    .where("products.published", true)
    .where("products.deleted_at", null)

    // (In stock OR Pre-order available) AND Price conditions
    .whereGroup((group1) => {
      group1.whereGroup((group2) => {
        group2.where("products.stock", ">", 0)
              .orWhere("products.preorder_available", true);
      }).whereGroup((group3) => {
        group3.where("products.price", ">=", 1000)
              .where("products.price", "<=", 100000);
      });
    })

    // Exclusion conditions
    .whereNotIn("products.category", ["adult", "restricted"])

    .select({
      id: "products.id",
      name: "products.name",
      price: "products.price",
      stock: "products.stock"
    })
    .orderBy("products.featured", "desc")
    .orderBy("products.created_at", "desc");
}

async function searchPosts(keyword: string) {
  return puri.table("posts")
    // PGroonga full-text search
    .whereSearch(
      ["posts.title", "posts.content"],
      keyword,
      { weights: [10, 1] }  // Higher weight for title
    )

    // Additional filters
    .where("posts.published", true)
    .where("posts.deleted_at", null)

    .select({
      id: "posts.id",

      // Highlighted title/content
      title: Puri.highlight("posts.title", keyword),
      content: Puri.highlight("posts.content", keyword),

      // Search score
      score: Puri.score(),

      created_at: "posts.created_at"
    })

    // Sort by score
    .orderBy("score", "desc")
    .limit(20);
}

async function getRecentActivity(days: number = 7) {
  const startDate = new Date();
  startDate.setDate(startDate.getDate() - days);

  return puri.table("activities")
    // Date range
    .where("activities.created_at", ">=", startDate)
    .where("activities.created_at", "<=", new Date())

    // Active users only
    .whereGroup((group) => {
      group.where("activities.user_status", "active")
           .orWhere("activities.user_status", "premium");
    })

    // Exclude specific types
    .whereNotIn("activities.type", ["system", "automated"])

    .select({
      id: "activities.id",
      type: "activities.type",
      user_id: "activities.user_id",
      created_at: "activities.created_at"
    })
    .orderBy("activities.created_at", "desc");
}

Condition Chaining

All where methods are chainable and are connected with AND by default.

const result = await puri
  .table("users")
  .where("users.role", "admin") // AND
  .where("users.status", "active") // AND
  .where("users.age", ">", 18) // AND
  .whereIn("users.country", ["KR", "US"]) // AND
  .where("users.verified", true); // AND

OR Conditions

orWhere within WhereGroup

await puri.table("users").whereGroup((group) => {
  group
    .where("users.role", "admin")
    .orWhere("users.role", "moderator")
    .orWhere("users.role", "editor");
});

// WHERE (
//   users.role = 'admin'
//   OR users.role = 'moderator'
//   OR users.role = 'editor'
// )

There is no orWhere at the top level. Use OR conditions within whereGroup or orWhereGroup.

Type Safety

WHERE conditions are validated type-safely.

// ✅ Valid column
await puri.table("users").where("users.email", "like", "%@gmail.com");

// ❌ Type error: Non-existent column
await puri.table("users").where("users.unknown_field", "value");

// ✅ Valid type
await puri.table("users").where("users.age", 25); // number

// ❌ Type error: Wrong type
await puri.table("users").where("users.age", "25"); // string (age is number)

Performance Optimization

1. Utilizing Indexes

// ✅ Good: Use indexed column
await puri.table("users").where("users.email", "user@example.com"); // email needs index

// ❌ Bad: Using functions (invalidates index)
await puri.table("users").whereRaw("LOWER(users.email) = ?", ["user@example.com"]);

2. LIKE/ILIKE Patterns

// ✅ Good: Fixed prefix (can use index)
.where("users.name", "like", "John%")
.where("users.name", "ilike", "john%")  // PostgreSQL

// ❌ Bad: Leading wildcard (cannot use index)
.where("users.name", "like", "%John")
.where("users.name", "ilike", "%john")  // PostgreSQL

In PostgreSQL, ilike is convenient for case-insensitive search, but it can affect performance on large datasets. Consider using citext type or pg_trgm extension indexes for such cases.

3. IN vs Multiple ORs

// ✅ Good: Use IN
.whereIn("users.id", [1, 2, 3, 4, 5])

// ❌ Bad: Multiple ORs (inefficient)
.whereGroup((g) => {
  g.where("users.id", 1)
   .orWhere("users.id", 2)
   .orWhere("users.id", 3)
   .orWhere("users.id", 4)
   .orWhere("users.id", 5)
})

Cautions

1. NULL Handling

// ✅ Correct: NULL check
.where("users.deleted_at", null)           // IS NULL
.where("users.deleted_at", "=", null)      // IS NULL
.where("users.deleted_at", "!=", null)     // IS NOT NULL

// ❌ Wrong: Regular comparison (doesn't work)
.where("users.deleted_at", "=", undefined)

2. Empty Arrays

// whereIn with empty array returns no results
const ids = [];
await puri.table("users").whereIn("users.id", ids); // Returns nothing

// Handle conditionally
if (ids.length > 0) {
  query = query.whereIn("users.id", ids);
}

3. Raw SQL Injection

// ❌ Dangerous: SQL injection possible
const userInput = "'; DROP TABLE users; --";
.whereRaw(`users.name = '${userInput}'`)

// ✅ Safe: Parameter binding
.whereRaw("users.name = ?", [userInput])

4. Condition Order

// WHERE order can affect performance

// ✅ Good: Highly selective conditions first
.where("users.id", 123)        // Very selective
.where("users.status", "active") // Less selective

// ❌ Inefficient: Low selectivity first
.where("users.status", "active") // Many rows
.where("users.id", 123)        // Few rows

Next Steps

select

Select fields to retrieve

join

Join tables

order-by

Sort results

limit

Limit number of results

​Basic Usage

​Simple Equality Condition

​Comparison Operators

​Object-Style Conditions

​NULL Handling

​IS NULL

​IS NOT NULL

​Pattern Matching (LIKE)

​Partial Matching

​NOT LIKE

​ILIKE (Case-Insensitive, PostgreSQL)

​NOT ILIKE (PostgreSQL)

​whereIn / whereNotIn

​One of Multiple Values

​Empty Array Handling

​Complex Condition Groups

​whereGroup (AND Group)

​orWhereGroup (OR Group)

​Nested Groups

​Full-Text Search

​whereMatch (MySQL)

​whereTsSearch (PostgreSQL)

​whereSearch (PGroonga)

​whereFuzzy (pg_trgm)

​Raw SQL Conditions

​whereRaw

​Real-World Examples

​Condition Chaining

​OR Conditions

​orWhere within WhereGroup

​Type Safety

​Performance Optimization

​1. Utilizing Indexes

​2. LIKE/ILIKE Patterns

​3. IN vs Multiple ORs

​Cautions

​1. NULL Handling

​2. Empty Arrays

​3. Raw SQL Injection

​4. Condition Order

​Next Steps

select

join

order-by

limit

Basic Usage

Simple Equality Condition

Comparison Operators

Object-Style Conditions

NULL Handling

IS NULL

IS NOT NULL

Pattern Matching (LIKE)

Partial Matching

NOT LIKE

ILIKE (Case-Insensitive, PostgreSQL)

NOT ILIKE (PostgreSQL)

whereIn / whereNotIn

One of Multiple Values

Empty Array Handling

Complex Condition Groups

whereGroup (AND Group)

orWhereGroup (OR Group)

Nested Groups

Full-Text Search

whereMatch (MySQL)

whereTsSearch (PostgreSQL)

whereSearch (PGroonga)

whereFuzzy (pg_trgm)

Raw SQL Conditions

whereRaw

Real-World Examples

Condition Chaining

OR Conditions

orWhere within WhereGroup

Type Safety

Performance Optimization

1. Utilizing Indexes

2. LIKE/ILIKE Patterns

3. IN vs Multiple ORs

Cautions

1. NULL Handling

2. Empty Arrays

3. Raw SQL Injection

4. Condition Order

Next Steps