Result Transformation

/**
 * Perform a SELECT on the query result. By default, `.insert()`, `.update()`, `.upsert()`,
 * and `.delete()` do not return modified rows. By calling this method, modified rows are
 * returned in `data`.
 * @param columns - The columns to retrieve, separated by commas
 */
select<Query extends string = '*'>(columns?: Query): PostgrestTransformBuilder<...>;

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

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

// Return inserted data
const { data: newUser } = await client
  .from("users")
  .insert({ name: "John Doe", email: "john@example.com" })
  .select("id, name, email");

// Return updated data with specific columns
const { data: updatedUser } = await client
  .from("users")
  .update({ name: "Jane Doe" })
  .eq("id", 123)
  .select("id, name, updated_at");

// Return all columns after upsert
const { data: upsertedUser } = await client
  .from("users")
  .upsert({ email: "jane@example.com", name: "Jane" })
  .select("*");

// Return deleted records
const { data: deletedPosts } = await client
  .from("posts")
  .delete()
  .eq("status", "archived")
  .select("id, title, deleted_at");

/**
 * Order the query result by `column`. You can call this method multiple times to order
 * by multiple columns. You can order referenced tables, but it only affects the ordering
 * of the parent table if you use `!inner` in the query.
 * @param column - The column to order by
 * @param options - Named parameters
 * @param options.ascending - Sort direction (default: true)
 * @param options.nullsFirst - Position of null values (default: false)
 * @param options.referencedTable - Reference table for ordering
 * @param options.foreignTable - Foreign table for ordering (deprecated)
 */
order(
  column: string,
  options?: {
    ascending?: boolean;
    nullsFirst?: boolean;
    referencedTable?: string;
    foreignTable?: string; // deprecated
  }
): this;

// Basic ascending order
const { data: usersByName } = await client
  .from("users")
  .select("*")
  .order("name");

// Descending order
const { data: newestPosts } = await client
  .from("posts")
  .select("*")
  .order("created_at", { ascending: false });

// Multiple column ordering
const { data: sortedUsers } = await client
  .from("users")
  .select("*")
  .order("department")
  .order("name");

// Null handling
const { data: usersWithNulls } = await client
  .from("users")
  .select("*")
  .order("last_login", { ascending: false, nullsFirst: true });

// Order by referenced table
const { data: postsWithAuthors } = await client
  .from("posts")
  .select(`
    *,
    users (
      id,
      name
    )
  `)
  .order("created_at", { ascending: false })
  .order("name", { referencedTable: "users" });

// Complex ordering
const { data: complexSorted } = await client
  .from("products")
  .select("*")
  .order("category")
  .order("price", { ascending: false })
  .order("name");

/**
 * Limit the query result by `count`
 * @param count - The maximum number of rows to return
 * @param options - Named parameters
 * @param options.referencedTable - Reference table for limit
 * @param options.foreignTable - Foreign table for limit (deprecated)
 */
limit(
  count: number,
  options?: {
    referencedTable?: string;
    foreignTable?: string; // deprecated
  }
): this;

/**
 * Limit the query result by starting at an offset `from` and ending at the offset `to`.
 * Only records within this range are returned. This respects the query order and if there
 * is no order clause the range could behave unexpectedly. The `from` and `to` values are
 * 0-based and inclusive.
 * @param from - The starting index from which to limit the result
 * @param to - The last index to which to limit the result
 * @param options - Named parameters
 * @param options.referencedTable - Reference table for range
 * @param options.foreignTable - Foreign table for range (deprecated)
 */
range(
  from: number,
  to: number,
  options?: {
    referencedTable?: string;
    foreignTable?: string; // deprecated
  }
): this;

// Simple limit
const { data: first10Users } = await client
  .from("users")
  .select("*")
  .limit(10);

// Pagination with range (page 2, 10 items per page)
const { data: page2Users } = await client
  .from("users")
  .select("*")
  .order("created_at")
  .range(10, 19); // Items 11-20 (0-based, inclusive)

// Pagination with limit and offset pattern
const page = 3;
const pageSize = 20;
const { data: page3Users } = await client
  .from("users")
  .select("*")
  .order("id")
  .range((page - 1) * pageSize, page * pageSize - 1);

// Limit on referenced table
const { data: usersWithRecentPosts } = await client
  .from("users")
  .select(`
    *,
    posts (
      id,
      title,
      created_at
    )
  `)
  .limit(5) // Limit users
  .limit(3, { referencedTable: "posts" }); // Limit posts per user

// Range on referenced table
const { data: usersWithPostRange } = await client
  .from("users")
  .select(`
    *,
    posts (
      id,
      title
    )
  `)
  .range(0, 9) // First 10 users
  .range(0, 4, { referencedTable: "posts" }); // First 5 posts per user

/**
 * Return `data` as a single object instead of an array of objects. Query result must be
 * one row (e.g. using `.limit(1)`), otherwise this returns an error.
 */
single(): PostgrestBuilder<ClientOptions, ResultOne>;

/**
 * Return `data` as a single object instead of an array of objects. Query result must be
 * zero or one row (e.g. using `.limit(1)`), otherwise this returns an error.
 */
maybeSingle(): PostgrestBuilder<ClientOptions, ResultOne | null>;

/**
 * Return `data` as a string in CSV format
 */
csv(): PostgrestBuilder<ClientOptions, string>;

/**
 * Return `data` as an object in GeoJSON format
 */
geojson(): PostgrestBuilder<ClientOptions, Record<string, unknown>>;

// Get single record (must return exactly 1 row)
const { data: user } = await client
  .from("users")
  .select("*")
  .eq("id", 123)
  .single(); // data is User object, not User[]

// Get single record or null (0 or 1 rows)
const { data: user } = await client
  .from("users")
  .select("*")
  .eq("email", "might-not-exist@example.com")
  .maybeSingle(); // data is User | null

// Get results as CSV
const { data: csvData } = await client
  .from("users")
  .select("name, email, created_at")
  .csv(); // data is string in CSV format

// Get geographic data as GeoJSON
const { data: geoData } = await client
  .from("locations")
  .select("*")
  .geojson(); // data is GeoJSON object

// Typical single record patterns
const { data: currentUser } = await client
  .from("users")
  .select("*")
  .eq("id", userId)
  .single();

const { data: latestPost } = await client
  .from("posts")
  .select("*")
  .order("created_at", { ascending: false })
  .limit(1)
  .single();

/**
 * Return `data` as the EXPLAIN plan for the query. You need to enable the db_plan_enabled
 * setting before using this method.
 * @param options - Named parameters
 * @param options.analyze - Include execution statistics
 * @param options.verbose - Verbose output
 * @param options.settings - Include settings
 * @param options.buffers - Include buffer usage
 * @param options.wal - Include WAL usage
 * @param options.format - Output format
 */
explain(
  options?: {
    analyze?: boolean;
    verbose?: boolean;
    settings?: boolean;
    buffers?: boolean;
    wal?: boolean;
    format?: 'json' | 'text';
  }
): PostgrestBuilder<ClientOptions, Record<string, unknown>[] | string>;

// Basic query plan
const { data: plan } = await client
  .from("users")
  .select("*")
  .eq("active", true)
  .explain();

// Detailed analysis with execution stats
const { data: detailedPlan } = await client
  .from("large_table")
  .select("*")
  .gt("created_at", "2023-01-01")
  .explain({
    analyze: true,
    verbose: true,
    buffers: true
  });

// JSON format for programmatic analysis
const { data: jsonPlan } = await client
  .from("complex_query")
  .select(`
    *,
    related_table (
      id,
      name
    )
  `)
  .explain({ format: 'json', analyze: true });

// Text format for human reading
const { data: textPlan } = await client
  .from("performance_test")
  .select("*")
  .in("category", ["important", "urgent"])
  .explain({ format: 'text', analyze: true });

/**
 * Rollback the query. `data` will still be returned, but the query is not committed.
 */
rollback(): this;

// Test insert without committing
const { data: testUser } = await client
  .from("users")
  .insert({ name: "Test User", email: "test@example.com" })
  .select()
  .rollback(); // Data returned but not committed

// Test complex operation
const { data: results } = await client
  .from("orders")
  .update({ status: "processed" })
  .eq("payment_status", "completed")
  .select()
  .rollback(); // See what would be updated without committing

/**
 * Set the AbortSignal for the fetch request
 * @param signal - The AbortSignal to use for the fetch request
 */
abortSignal(signal: AbortSignal): this;

// Request cancellation
const controller = new AbortController();

// Cancel request after 5 seconds
setTimeout(() => controller.abort(), 5000);

const { data, error } = await client
  .from("large_table")
  .select("*")
  .abortSignal(controller.signal);

// User-initiated cancellation
const searchController = new AbortController();

const searchPromise = client
  .from("documents")
  .select("*")
  .textSearch("content", "search terms")
  .abortSignal(searchController.signal);

// Cancel if user starts new search
function handleNewSearch() {
  searchController.abort();
  // Start new search...
}

/**
 * Set the maximum number of rows that can be affected by the query. Only available in
 * PostgREST v13+ and only works with PATCH and DELETE methods.
 * @param value - The maximum number of rows that can be affected
 */
maxAffected(value: number): this;

// Prevent accidental mass updates
const { data, error } = await client
  .from("users")
  .update({ updated_at: new Date().toISOString() })
  .eq("active", true)
  .maxAffected(100) // Fail if more than 100 rows would be affected
  .select();

// Safe delete with limit
const { data, error } = await client
  .from("old_logs")
  .delete()
  .lt("created_at", "2022-01-01")
  .maxAffected(1000) // Prevent deleting more than 1000 rows
  .select("id");

// Batch processing with safety limit
const { data, error } = await client
  .from("pending_emails")
  .update({ status: "processing" })
  .eq("status", "queued")
  .maxAffected(50) // Process max 50 emails at once
  .select();

/**
 * Override the type of the returned `data` field in the response.
 * @typeParam NewResult - The new type to cast the response data to
 * @typeParam Options - Optional type configuration (defaults to { merge: true })
 * @typeParam Options.merge - When true, merges the new type with existing return type. When false, replaces the existing types entirely (defaults to true)
 */
overrideTypes<
  NewResult,
  Options extends { merge?: boolean } = { merge: true }
>(): PostgrestBuilder<...>;

/**
 * Override the type of the returned `data` (deprecated)
 * @deprecated Use overrideTypes<yourType, { merge: false }>() method at the end of your call chain instead
 */
returns<NewResult>(): PostgrestBuilder<...>;

// Merge with existing types (default behavior)
interface CustomFields {
  computed_score: number;
  display_name: string;
}

const { data: enhancedUsers } = await client
  .from("users")
  .select("id, name, email") // Original fields: { id, name, email }
  .overrideTypes<CustomFields>(); 
// Result type: { id, name, email, computed_score, display_name }

// Replace existing types completely
interface SimpleUser {
  id: number;
  display_name: string;
}

const { data: simpleUsers } = await client
  .from("users")
  .select("*")
  .overrideTypes<SimpleUser, { merge: false }>(); 
// Result type: { id, display_name } only

// Override array result types
interface ProcessedData {
  processed_at: string;
  result: any;
}

const { data: processedResults } = await client
  .from("raw_data")
  .select("*")
  .overrideTypes<ProcessedData[]>(); // Explicit array type

// Override for single object results
const { data: processedUser } = await client
  .from("users")
  .select("*")
  .eq("id", 123)
  .single()
  .overrideTypes<CustomUser>(); // Single object override

// Type-safe merging with complex types
interface UserWithMetrics {
  total_orders: number;
  last_login: string;
  preferences: {
    theme: 'light' | 'dark';
    notifications: boolean;
  };
}

const { data: usersWithMetrics } = await client
  .from("users")
  .select("id, name, email") // Base fields preserved
  .overrideTypes<UserWithMetrics>(); // Additional fields merged
// Result: { id, name, email, total_orders, last_login, preferences }

// Error handling with type overrides
try {
  const { data } = await client
    .from("complex_view")
    .select("*")
    .overrideTypes<MyCustomType>()
    .throwOnError();
  // data is properly typed as MyCustomType[]
} catch (error) {
  console.error("Query failed:", error);
}

// Complex transformation pipeline
const { data: processedResults } = await client
  .from("analytics")
  .select(`
    id,
    event_name,
    user_id,
    created_at,
    metadata,
    users (
      id,
      name,
      segment
    )
  `)
  .gte("created_at", "2023-01-01")
  .eq("event_name", "purchase")
  .order("created_at", { ascending: false })
  .order("name", { referencedTable: "users" })
  .limit(100)
  .range(0, 49) // First 50 results
  .limit(5, { referencedTable: "users" }); // Limit user data per event