Filtering and Conditions

/**
 * Match only rows where `column` is equal to `value`.
 * To check if the value of `column` is NULL, you should use `.is()` instead.
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
eq<ColumnName extends string>(column: ColumnName, value: any): this;

/**
 * Match only rows where `column` is not equal to `value`
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
neq<ColumnName extends string>(column: ColumnName, value: any): this;

/**
 * Match only rows where `column` is greater than `value`
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
gt(column: string, value: unknown): this;

/**
 * Match only rows where `column` is greater than or equal to `value`
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
gte(column: string, value: unknown): this;

/**
 * Match only rows where `column` is less than `value`
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
lt(column: string, value: unknown): this;

/**
 * Match only rows where `column` is less than or equal to `value`
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
lte(column: string, value: unknown): this;

import { PostgrestClient } from "@supabase/postgrest-js";

const client = new PostgrestClient("https://api.example.com");

// Equal comparison
const { data } = await client
  .from("users")
  .select("*")
  .eq("active", true);

// Not equal
const { data: inactiveUsers } = await client
  .from("users")
  .select("*")
  .neq("status", "deleted");

// Numeric comparisons
const { data: adults } = await client
  .from("users")
  .select("*")
  .gte("age", 18);

const { data: youngAdults } = await client
  .from("users")
  .select("*")
  .gte("age", 18)
  .lt("age", 30);

// Date comparisons
const { data: recentPosts } = await client
  .from("posts")
  .select("*")
  .gt("created_at", "2023-01-01T00:00:00Z");

/**
 * Match only rows where `column` IS `value`.
 * For non-boolean columns, this is only relevant for checking if the value of `column` is NULL
 * by setting `value` to `null`. For boolean columns, you can also set `value` to `true` or `false`
 * and it will behave the same way as `.eq()`.
 * @param column - The column to filter on
 * @param value - The value to filter with
 */
is(column: string, value: boolean | null): this;

// Check for NULL values
const { data: usersWithoutEmail } = await client
  .from("users")
  .select("*")
  .is("email", null);

// Check for non-NULL values (alternative to neq)
const { data: usersWithEmail } = await client
  .from("users")
  .select("*")
  .not("email", "is", null);

// Boolean filtering with is()
const { data: activeUsers } = await client
  .from("users")
  .select("*")
  .is("active", true);

/**
 * Match only rows where `column` is included in the `values` array
 * @param column - The column to filter on
 * @param values - The values array to filter with
 */
in<ColumnName extends string>(column: ColumnName, values: readonly any[]): this;

// Filter by array of values
const { data: specificUsers } = await client
  .from("users")
  .select("*")
  .in("id", [1, 2, 3, 4, 5]);

// Filter by status array
const { data: publishedOrDrafts } = await client
  .from("posts")
  .select("*")
  .in("status", ["published", "draft"]);

// Filter by string array
const { data: specificRoles } = await client
  .from("users")
  .select("*")
  .in("role", ["admin", "moderator", "editor"]);

/**
 * Match only rows where `column` matches `pattern` case-sensitively
 * @param column - The column to filter on
 * @param pattern - The pattern to match with
 */
like(column: string, pattern: string): this;

/**
 * Match only rows where `column` matches all of `patterns` case-sensitively
 * @param column - The column to filter on
 * @param patterns - The patterns to match with
 */
likeAllOf(column: string, patterns: readonly string[]): this;

/**
 * Match only rows where `column` matches any of `patterns` case-sensitively
 * @param column - The column to filter on
 * @param patterns - The patterns to match with
 */
likeAnyOf(column: string, patterns: readonly string[]): this;

/**
 * Match only rows where `column` matches `pattern` case-insensitively
 * @param column - The column to filter on
 * @param pattern - The pattern to match with
 */
ilike(column: string, pattern: string): this;

/**
 * Match only rows where `column` matches all of `patterns` case-insensitively
 * @param column - The column to filter on
 * @param patterns - The patterns to match with
 */
ilikeAllOf(column: string, patterns: readonly string[]): this;

/**
 * Match only rows where `column` matches any of `patterns` case-insensitively
 * @param column - The column to filter on
 * @param patterns - The patterns to match with
 */
ilikeAnyOf(column: string, patterns: readonly string[]): this;

// Case-sensitive pattern matching
const { data: johnUsers } = await client
  .from("users")
  .select("*")
  .like("name", "John%"); // Names starting with "John"

// Case-insensitive pattern matching
const { data: emailUsers } = await client
  .from("users")
  .select("*")
  .ilike("email", "%@gmail.com"); // Gmail addresses

// Wildcard patterns
const { data: middleNameUsers } = await client
  .from("users")
  .select("*")
  .like("name", "% % %"); // Names with middle names

// Multiple pattern matching
const { data: techUsers } = await client
  .from("users")
  .select("*")
  .ilikeAnyOf("bio", ["%developer%", "%engineer%", "%programmer%"]);

// All patterns must match
const { data: seniorDevs } = await client
  .from("users")
  .select("*")
  .ilikeAllOf("bio", ["%senior%", "%developer%"]);

/**
 * Only relevant for jsonb, array, and range columns. Match only rows where `column`
 * contains every element appearing in `value`
 * @param column - The jsonb, array, or range column to filter on
 * @param value - The jsonb, array, or range value to filter with
 */
contains(column: string, value: string | readonly unknown[] | Record<string, unknown>): this;

/**
 * Only relevant for jsonb, array, and range columns. Match only rows where every element
 * appearing in `column` is contained by `value`
 * @param column - The jsonb, array, or range column to filter on
 * @param value - The jsonb, array, or range value to filter with
 */
containedBy(column: string, value: string | readonly unknown[] | Record<string, unknown>): this;

/**
 * Only relevant for array and range columns. Match only rows where `column` and `value`
 * have an element in common
 * @param column - The array or range column to filter on
 * @param value - The array or range value to filter with
 */
overlaps(column: string, value: string | readonly unknown[]): this;

// JSONB contains
const { data: usersWithSkills } = await client
  .from("users")
  .select("*")
  .contains("skills", { javascript: true, react: true });

// Array contains
const { data: usersWithTags } = await client
  .from("users")
  .select("*")
  .contains("tags", ["developer", "javascript"]);

// JSONB contained by
const { data: basicUsers } = await client
  .from("users")
  .select("*")
  .containedBy("skills", { 
    javascript: true, 
    react: true, 
    node: true, 
    python: true 
  });

// Array overlaps
const { data: overlappingUsers } = await client
  .from("users")
  .select("*")
  .overlaps("interests", ["coding", "music"]);

// JSONB path queries
const { data: specificConfig } = await client
  .from("apps")
  .select("*")
  .contains("config", { api: { timeout: 5000 } });

/**
 * Only relevant for range columns. Match only rows where every element in `column`
 * is greater than any element in `range`
 * @param column - The range column to filter on
 * @param range - The range to filter with
 */
rangeGt(column: string, range: string): this;

/**
 * Only relevant for range columns. Match only rows where every element in `column`
 * is either contained in `range` or greater than any element in `range`
 * @param column - The range column to filter on
 * @param range - The range to filter with
 */
rangeGte(column: string, range: string): this;

/**
 * Only relevant for range columns. Match only rows where every element in `column`
 * is less than any element in `range`
 * @param column - The range column to filter on
 * @param range - The range to filter with
 */
rangeLt(column: string, range: string): this;

/**
 * Only relevant for range columns. Match only rows where every element in `column`
 * is either contained in `range` or less than any element in `range`
 * @param column - The range column to filter on
 * @param range - The range to filter with
 */
rangeLte(column: string, range: string): this;

/**
 * Only relevant for range columns. Match only rows where `column` is mutually exclusive
 * to `range` and there can be no element between the two ranges
 * @param column - The range column to filter on
 * @param range - The range to filter with
 */
rangeAdjacent(column: string, range: string): this;

// Date range comparisons
const { data: futureEvents } = await client
  .from("events")
  .select("*")
  .rangeGt("date_range", "[2023-01-01,2023-12-31]");

// Integer range operations
const { data: highPriceItems } = await client
  .from("products")
  .select("*")
  .rangeGte("price_range", "[100,500]");

// Adjacent ranges
const { data: adjacentSlots } = await client
  .from("time_slots")
  .select("*")
  .rangeAdjacent("time_range", "[09:00,12:00]");

/**
 * Only relevant for text and tsvector columns. Match only rows where `column` matches
 * the query string in `query`
 * @param column - The text or tsvector column to filter on
 * @param query - The query text to match with
 * @param options - Named parameters
 * @param options.config - Text search configuration
 * @param options.type - Search type
 */
textSearch(
  column: string,
  query: string,
  options?: {
    config?: string;
    type?: 'plain' | 'phrase' | 'websearch';
  }
): this;

// Basic text search
const { data: searchResults } = await client
  .from("documents")
  .select("*")
  .textSearch("content", "postgresql database");

// Phrase search
const { data: exactMatches } = await client
  .from("articles")
  .select("*")
  .textSearch("title", "machine learning", { type: 'phrase' });

// Web-style search
const { data: webResults } = await client
  .from("posts")
  .select("*")
  .textSearch("content", "react OR vue", { type: 'websearch' });

// Custom text search configuration
const { data: customResults } = await client
  .from("documents")
  .select("*")
  .textSearch("content", "javascript", { config: 'english' });

// Search on tsvector column
const { data: vectorResults } = await client
  .from("indexed_documents")
  .select("*")
  .textSearch("search_vector", "typescript & react");

/**
 * Match only rows where each column in `query` keys is equal to its associated value.
 * Shorthand for multiple `.eq()`s
 * @param query - The object to filter with, with column names as keys mapped to their filter values
 */
match(query: Record<string, unknown>): this;

/**
 * Match only rows which doesn't satisfy the filter. Unlike most filters, `operator` and
 * `value` are used as-is and need to follow PostgREST syntax. You also need to make sure
 * they are properly sanitized.
 * @param column - The column to filter on
 * @param operator - The operator to be negated to filter with, following PostgREST syntax
 * @param value - The value to filter with, following PostgREST syntax
 */
not(column: string, operator: string, value: unknown): this;

/**
 * Match only rows which satisfy at least one of the filters. Unlike most filters, `filters`
 * is used as-is and needs to follow PostgREST syntax. You also need to make sure it's
 * properly sanitized. It's currently not possible to do an `.or()` filter across multiple tables.
 * @param filters - The filters to use, following PostgREST syntax
 * @param options - Named parameters
 * @param options.referencedTable - Reference table for OR operations
 */
or(filters: string, options?: { referencedTable?: string }): this;

/**
 * Match only rows which satisfy the filter. This is an escape hatch - you should use the
 * specific filter methods wherever possible. Unlike most filters, `operator` and `value`
 * are used as-is and need to follow PostgREST syntax. You also need to make sure they
 * are properly sanitized.
 * @param column - The column to filter on
 * @param operator - The operator to filter with, following PostgREST syntax
 * @param value - The value to filter with, following PostgREST syntax
 */
filter(column: string, operator: string, value: unknown): this;

// Match multiple conditions (shorthand for multiple eq)
const { data: specificUsers } = await client
  .from("users")
  .select("*")
  .match({ 
    active: true, 
    role: "admin", 
    department: "engineering" 
  });

// NOT operations
const { data: notDeleted } = await client
  .from("posts")
  .select("*")
  .not("status", "eq", "deleted");

const { data: notInRange } = await client
  .from("products")
  .select("*")
  .not("price", "gte", 100)
  .not("price", "lte", 500);

// OR operations
const { data: publishedOrFeatured } = await client
  .from("posts")
  .select("*")
  .or("status.eq.published,featured.eq.true");

const { data: urgentTasks } = await client
  .from("tasks")
  .select("*")
  .or("priority.eq.high,due_date.lt.2023-12-01");

// Custom filter (escape hatch)
const { data: customFiltered } = await client
  .from("analytics")
  .select("*")
  .filter("views", "gte", 1000)
  .filter("created_at", "gte", "2023-01-01");

// Complex OR with referenced table
const { data: postsWithActiveAuthors } = await client
  .from("posts")
  .select(`
    *,
    users (
      id,
      name,
      active
    )
  `)
  .or("users.active.eq.true,users.role.eq.admin", { referencedTable: "users" });

// Complex filtering example
const { data: complexResults } = await client
  .from("users")
  .select("*")
  .eq("active", true)
  .neq("status", "banned")
  .gte("age", 18)
  .in("role", ["user", "premium", "admin"])
  .ilike("name", "%john%")
  .not("email", "ilike", "%spam%")
  .or("verified.eq.true,created_at.gte.2023-01-01")
  .order("created_at", { ascending: false })
  .limit(50);