Query Operations

/**
 * Perform a SELECT query on the table or view
 * @param columns - The columns to retrieve, separated by commas. Columns can be renamed when returned with `customName:columnName`
 * @param options - Named parameters
 * @param options.head - When set to `true`, `data` will not be returned. Useful if you only need the count
 * @param options.count - Count algorithm to use to count rows in the table or view
 */
select<Query extends string = '*'>(
  columns?: Query,
  options?: {
    head?: boolean;
    count?: 'exact' | 'planned' | 'estimated';
  }
): PostgrestFilterBuilder<...>;

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

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

// Select all columns
const { data: allUsers } = await client
  .from("users")
  .select("*");

// Select specific columns
const { data: userNames } = await client
  .from("users")
  .select("id, name, email");

// Select with column aliasing
const { data: aliasedUsers } = await client
  .from("users")
  .select("user_id:id, full_name:name, contact_email:email");

// Select with count
const { data, count } = await client
  .from("users")
  .select("*", { count: 'exact' });

// Head request (count only, no data)
const { count: userCount } = await client
  .from("users")
  .select("*", { head: true, count: 'exact' });

// Select with relationships  
const { data: postsWithUsers } = await client
  .from("posts")
  .select(`
    id,
    title,
    content,
    users (
      id,
      name,
      email
    )
  `);

/**
 * Perform an INSERT into the table or view. By default, inserted rows are not returned.
 * To return it, chain the call with `.select()`
 * @param values - The values to insert. Pass an object to insert a single row or an array to insert multiple rows
 * @param options - Named parameters
 * @param options.count - Count algorithm to use to count inserted rows
 * @param options.defaultToNull - Make missing fields default to `null`. Otherwise, use the default value for the column. Only applies for bulk inserts
 */
insert(
  values: Row | Row[],
  options?: {
    count?: 'exact' | 'planned' | 'estimated';
    defaultToNull?: boolean;
  }
): PostgrestFilterBuilder<...>;

// Insert single record
const { error } = await client
  .from("users")
  .insert({ name: "John Doe", email: "john@example.com" });

// Insert single record and return it
const { data: newUser, error } = await client
  .from("users")
  .insert({ name: "John Doe", email: "john@example.com" })
  .select()
  .single();

// Insert multiple records
const { data: newUsers, error } = await client
  .from("users")
  .insert([
    { name: "Alice", email: "alice@example.com" },
    { name: "Bob", email: "bob@example.com" }
  ])
  .select();

// Insert with count
const { data, count, error } = await client
  .from("users")
  .insert([
    { name: "Charlie", email: "charlie@example.com" },
    { name: "Diana", email: "diana@example.com" }
  ], { count: 'exact' })
  .select();

// Insert with explicit null handling
const { data, error } = await client
  .from("users")
  .insert([
    { name: "Eve" }, // email will be null if defaultToNull: true
    { name: "Frank", email: "frank@example.com" }
  ], { defaultToNull: true })
  .select();

/**
 * Perform an UPDATE on the table or view. By default, updated rows are not returned.
 * To return it, chain the call with `.select()` after filters
 * @param values - The values to update with
 * @param options - Named parameters
 * @param options.count - Count algorithm to use to count updated rows
 */
update(
  values: Partial<Row>,
  options?: {
    count?: 'exact' | 'planned' | 'estimated';
  }
): PostgrestFilterBuilder<...>;

// Update with filter
const { error } = await client
  .from("users")
  .update({ name: "John Smith" })
  .eq("id", 123);

// Update and return updated records
const { data: updatedUsers, error } = await client
  .from("users")
  .update({ last_login: new Date().toISOString() })
  .eq("active", true)
  .select();

// Update with multiple filters
const { data, error } = await client
  .from("users")
  .update({ status: "verified" })
  .eq("email_verified", true)
  .gt("created_at", "2023-01-01")
  .select();

// Update with count
const { data, count, error } = await client
  .from("users")
  .update({ updated_at: new Date().toISOString() })
  .lt("last_login", "2023-01-01")
  .select("id, name", { count: 'exact' });

/**
 * Perform an UPSERT on the table or view. Allows you to perform the equivalent of `.insert()`
 * if a row with the corresponding `onConflict` columns doesn't exist, or if it does exist,
 * perform an alternative action depending on `ignoreDuplicates`
 * @param values - The values to upsert with. Pass an object to upsert a single row or an array to upsert multiple rows
 * @param options - Named parameters
 * @param options.onConflict - Comma-separated UNIQUE column(s) to specify how duplicate rows are determined
 * @param options.ignoreDuplicates - If `true`, duplicate rows are ignored. If `false`, duplicate rows are merged with existing rows
 * @param options.count - Count algorithm to use to count upserted rows
 * @param options.defaultToNull - Make missing fields default to `null`. Only applies when inserting new rows and doing bulk upserts
 */
upsert(
  values: Row | Row[],
  options?: {
    onConflict?: string;
    ignoreDuplicates?: boolean;
    count?: 'exact' | 'planned' | 'estimated';
    defaultToNull?: boolean;
  }
): PostgrestFilterBuilder<...>;

// Basic upsert (merge on primary key)
const { data, error } = await client
  .from("users")
  .upsert({ id: 123, name: "John Doe", email: "john@example.com" })
  .select();

// Upsert with specific conflict column
const { data, error } = await client
  .from("users")
  .upsert(
    { email: "john@example.com", name: "John Doe", age: 30 },
    { onConflict: "email" }
  )
  .select();

// Upsert multiple records
const { data, error } = await client
  .from("users")
  .upsert([
    { email: "alice@example.com", name: "Alice" },
    { email: "bob@example.com", name: "Bob" }
  ], { onConflict: "email" })
  .select();

// Ignore duplicates instead of merging
const { data, error } = await client
  .from("users")
  .upsert(
    [
      { email: "existing@example.com", name: "Won't Override" },
      { email: "new@example.com", name: "Will Insert" }
    ],
    { onConflict: "email", ignoreDuplicates: true }
  )
  .select();

// Upsert with composite key
const { data, error } = await client
  .from("user_preferences")
  .upsert(
    { user_id: 123, setting_key: "theme", setting_value: "dark" },
    { onConflict: "user_id,setting_key" }
  )
  .select();

/**
 * Perform a DELETE on the table or view. By default, deleted rows are not returned.
 * To return it, chain the call with `.select()` after filters
 * @param options - Named parameters
 * @param options.count - Count algorithm to use to count deleted rows
 */
delete(
  options?: {
    count?: 'exact' | 'planned' | 'estimated';
  }
): PostgrestFilterBuilder<...>;

// Delete with filter
const { error } = await client
  .from("users")
  .delete()
  .eq("id", 123);

// Delete and return deleted records
const { data: deletedUsers, error } = await client
  .from("users")
  .delete()
  .eq("active", false)
  .select();

// Delete with multiple filters
const { data, error } = await client
  .from("posts")
  .delete()
  .eq("published", false)
  .lt("created_at", "2022-01-01")
  .select("id, title");

// Delete with count
const { data, count, error } = await client
  .from("logs")
  .delete({ count: 'exact' })
  .lt("created_at", "2023-01-01")
  .select("id");

// Conditional delete with complex filters
const { data, error } = await client
  .from("user_sessions")
  .delete()
  .or("expires_at.lt.now(),last_activity.lt.2023-01-01")
  .select();

// Exact count (slower but precise)
const { data, count } = await client
  .from("users")
  .select("*", { count: 'exact' });

// Planned count (faster but approximate)
const { data, count } = await client
  .from("large_table")
  .select("*", { count: 'planned' });

// Estimated count (hybrid approach)
const { data, count } = await client
  .from("posts")
  .select("*", { count: 'estimated' });

interface Database {
  public: {
    Tables: {
      users: {
        Row: { id: number; name: string; email: string; active: boolean };
        Insert: { name: string; email: string; active?: boolean };
        Update: { name?: string; email?: string; active?: boolean };
      };
    };
  };
}

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

// Type-safe insert
const { data, error } = await client
  .from("users")
  .insert({
    name: "John", // ✅ Required field
    email: "john@example.com", // ✅ Required field
    active: true // ✅ Optional field
    // id: 123 // ❌ TypeScript error - not in Insert type
  })
  .select();

// Type-safe update
const { data, error } = await client
  .from("users")
  .update({
    name: "Jane", // ✅ Optional in Update type
    // invalid_field: "value" // ❌ TypeScript error
  })
  .eq("id", 123);