supabase-typed-query

name	supabase-typed-query
description	Help developers use supabase-typed-query for type-safe Supabase queries. Use this skill when building queries with the Query API, working with Entity/PartitionedEntity patterns, handling TaskOutcome errors, or implementing soft deletes and multi-tenancy.

supabase-typed-query User Guide

Overview

supabase-typed-query is a type-safe query builder and entity pattern library for Supabase. It provides:

• Fully typed queries leveraging your database schema
• Two complementary APIs: Query API (functional, chainable) and Entity API (CRUD patterns)
• Built on functype for robust error handling with TaskOutcome
• Support for soft deletes, multi-tenancy, and complex queries with OR conditions

When to Use This Skill

Trigger this skill when users:

• Build queries using query() function
• Work with Entity() or PartitionedEntity() patterns
• Handle TaskOutcome or use OrThrow methods
• Implement soft deletes or multi-tenancy
• Look up comparison operators or API methods
• Debug query issues

Quick Start

Installation

npm install supabase-typed-query
# or
pnpm add supabase-typed-query

Generate Database Types

npx supabase gen types typescript --project-id your-project-id > database.types.ts

Core Imports

import { query, Entity, PartitionedEntity } from "supabase-typed-query"
import { createClient } from "@supabase/supabase-js"
import type { Database } from "./database.types"

const client = createClient<Database>(url, key)

Query API

The Query API provides chainable, functional queries with OR support.

Basic Query

import { query } from "supabase-typed-query"

// Simple query with type-safe conditions
const user = await query<"users", Database>(client, "users", { id: "123" }).oneOrThrow()

// Query with comparison operators
const recentPosts = await query<"posts", Database>(client, "posts", {
  created_at: { gte: new Date("2024-01-01") },
}).manyOrThrow()

OR Chaining

// Multiple OR conditions
const results = await query<"users", Database>(client, "users", { role: "admin" })
  .or({ role: "moderator" })
  .or({ role: "editor", active: true })
  .manyOrThrow()
// SQL: WHERE role = 'admin' OR role = 'moderator' OR (role = 'editor' AND active = true)

Functional Operations

// Map: transform each result
const titles = await query<"posts", Database>(client, "posts", { status: "published" })
  .map((post) => post.title)
  .manyOrThrow()

// Filter: client-side filtering after fetch
const activeUsers = await query<"users", Database>(client, "users", {})
  .filter((user) => user.active === true && user.age > 18)
  .manyOrThrow()

// Chain map + filter
const adultNames = await query<"users", Database>(client, "users", {})
  .filter((user) => user.age >= 18)
  .map((user) => user.name)
  .manyOrThrow()

Execution Methods

Method	Returns	Description
.one()	TaskOutcome<Option<T>>	Expects 0-1 results
.many()	TaskOutcome<List<T>>	Expects 0+ results
.first()	TaskOutcome<Option<T>>	Gets first if multiple
.oneOrThrow()	Promise<T>	Throws if not found or error
.manyOrThrow()	Promise<List<T>>	Throws on error
.firstOrThrow()	Promise<T>	Throws if not found or error

Comparison Operators

type ComparisonOperators<V> = {
  gte?: V // Greater than or equal
  gt?: V // Greater than
  lte?: V // Less than or equal
  lt?: V // Less than
  neq?: V // Not equal (use NOT operator for null)
  like?: string // LIKE pattern
  ilike?: string // Case-insensitive LIKE
  in?: V[] // IN array
  is?: null | boolean // IS NULL/TRUE/FALSE
}

// Examples
const results = await query<"posts", Database>(client, "posts", {
  view_count: { gte: 100, lte: 1000 },
  title: { ilike: "%guide%" },
  tags: { in: ["typescript", "supabase"] },
  published_at: { is: null }, // Find unpublished
}).manyOrThrow()

NOT Operator

The not operator follows Supabase conventions for negating IS and IN conditions:

// IS NOT NULL - find posts with external_id set
const linkedPosts = await query<"posts", Database>(client, "posts", {
  not: { is: { external_id: null } },
}).manyOrThrow()

// IS NOT TRUE / IS NOT FALSE
const nonFeatured = await query<"posts", Database>(client, "posts", {
  not: { is: { featured: true } },
}).manyOrThrow()

// NOT IN - exclude specific values
const activePosts = await query<"posts", Database>(client, "posts", {
  not: { in: { status: ["draft", "archived", "spam"] } },
}).manyOrThrow()

// Entity API supports NOT as well
const items = await PostEntity.getItems({
  where: { status: "published" },
  not: { is: { external_id: null } },
}).manyOrThrow()

Note: neq: null is deprecated. Use not: { is: { field: null } } instead for IS NOT NULL checks.

RPC (Stored Procedures)

The rpc() function provides type-safe invocation of PostgreSQL functions/stored procedures.

Basic RPC Call

import { rpc } from "supabase-typed-query"

// Call a function that returns a single value
const stats = await rpc<"get_user_stats", Database>(client, "get_user_stats", {
  user_id: "123",
}).oneOrThrow()

// Call a function that returns multiple rows
const results = await rpc<"search_products", Database>(client, "search_products", {
  query: "laptop",
  limit: 10,
}).manyOrThrow()

RPC Execution Methods

Method	Returns	Description
.one()	TaskOutcome<Option<T>>	Expects single result or none
.many()	TaskOutcome<List<T>>	Expects 0+ results as a list
.oneOrThrow()	Promise<T>	Throws if not found or error
.manyOrThrow()	Promise<List<T>>	Throws on error

RPC with Options

// With count option for pagination info
const results = await rpc<"search_items", Database>(
  client,
  "search_items",
  { query: "test" },
  { count: "exact" },
).manyOrThrow()

Type Safety

RPC return types are inferred from your database schema. Your Database type should include Functions definitions:

interface Database {
  public: {
    Tables: {
      /* ... */
    }
    Functions: {
      get_user_stats: {
        Args: { user_id: string }
        Returns: { total: number; active: number }
      }
      search_products: {
        Args: { query: string; limit?: number }
        Returns: { id: string; name: string; price: number }[]
      }
    }
  }
}

Error Handling with RPC

// Using TaskOutcome
const result = await rpc<"risky_function", Database>(client, "risky_function", {}).one()

if (result.isOk()) {
  const maybeData = result.orThrow()
  if (maybeData.isSome()) {
    console.log("Data:", maybeData.orElse(null))
  }
} else {
  console.error("RPC failed:", result.error)
}

// Using OrThrow
try {
  const data = await rpc<"risky_function", Database>(client, "risky_function", {}).oneOrThrow()
} catch (error) {
  console.error("Error:", error)
}

Entity API

The Entity API provides consistent CRUD patterns.

Standard Entity

import { Entity } from "supabase-typed-query"

const PostEntity = Entity<"posts", Database>(client, "posts", { softDelete: true })

// Get single item
const post = await PostEntity.getItem({ id: "123" }).oneOrThrow()

// Get multiple items (limit/offset are accepted in params, same as chaining .limit()/.offset())
const posts = await PostEntity.getItems({
  where: { status: "published" },
  order: ["created_at", { ascending: false }],
  limit: 10,
  offset: 0,
}).manyOrThrow()

// Add items (multi-result -> manyOrThrow)
const created = await PostEntity.addItems({
  items: [{ title: "New Post", status: "draft" }],
}).manyOrThrow()

// Update single item (single-result -> oneOrThrow)
const updated = await PostEntity.updateItem({
  where: { id: "123" },
  data: { status: "published" },
}).oneOrThrow()

// Update multiple items (multi-result -> manyOrThrow)
const bulkUpdated = await PostEntity.updateItems({
  where: { status: "draft" },
  data: { status: "archived" },
}).manyOrThrow()

// Upsert items (multi-result -> manyOrThrow)
const upserted = await PostEntity.upsertItems({
  items: [{ id: "123", title: "Updated Title" }],
  identity: "id",
}).manyOrThrow()

// Delete single item (soft delete when softDelete: true; single-result -> oneOrThrow)
const deleted = await PostEntity.deleteItem({
  where: { id: "123" },
}).oneOrThrow()

// Delete multiple items (multi-result -> manyOrThrow)
const deletedMany = await PostEntity.deleteItems({
  where: { status: "archived" },
}).manyOrThrow()

Note: When softDelete: true, delete methods set the deleted timestamp instead of physically removing rows. When softDelete: false, rows are permanently removed.

PartitionedEntity (Multi-Tenancy)

import { PartitionedEntity } from "supabase-typed-query"

const TenantPostEntity = PartitionedEntity<"posts", string, Database>(client, "posts", {
  partitionField: "tenant_id",
  softDelete: true,
})

// All queries automatically include partition filter
const tenantPosts = await TenantPostEntity.getItems(tenantId, {
  where: { status: "published" },
}).manyOrThrow()
// SQL: WHERE tenant_id = 'tenantId' AND status = 'published' AND deleted IS NULL

ViewEntity (Read-Only Views)

Database views are read-only in Supabase and only have a Row type (no Insert or Update). Use ViewEntity for type-safe querying of views.

import { ViewEntity } from "supabase-typed-query"

// Create a read-only view entity
const AuthUsersView = ViewEntity<"auth_users_view", Database, "agent_gate">(client, "auth_users_view", {
  schema: "agent_gate",
})

// Query the view - only getItem and getItems are available
const user = await AuthUsersView.getItem({ id: "123" }).oneOrThrow()
const activeUsers = await AuthUsersView.getItems({
  where: { is_active: true },
  order: ["created_at", { ascending: false }],
}).manyOrThrow()

Key differences from Entity:

• Only getItem() and getItems() methods available (no write operations)
• No softDelete configuration (views are read-only snapshots)
• Uses ViewNames and ViewRow types instead of TableNames and TableRow

PartitionedViewEntity (Multi-Tenant Views)

For views that require partition-based access control:

import { PartitionedViewEntity } from "supabase-typed-query"

const TenantStatsView = PartitionedViewEntity<"tenant_stats_view", string, Database>(client, "tenant_stats_view", {
  partitionField: "tenant_id",
})

// Partition key is required for all queries
const stats = await TenantStatsView.getItems(tenantId, {
  where: { period: "monthly" },
}).manyOrThrow()

Error Handling

TaskOutcome Pattern (Explicit)

import { Ok, Err } from "functype"

const result = await query<"users", Database>(client, "users", { id: userId }).one()

if (result.isOk()) {
  const maybeUser = result.getOrThrow() // Option<User>
  if (maybeUser.isSome()) {
    const user = maybeUser.getOrThrow()
    console.log(user)
  }
} else {
  console.error("Query failed:", result.error)
}

OrThrow Methods (Simple)

try {
  const user = await query<"users", Database>(client, "users", { id: userId }).oneOrThrow()
  console.log("User:", user)
} catch (error) {
  if (error instanceof SupabaseError) {
    console.log(error.code) // PostgreSQL error code
    console.log(error.details) // Additional details
    console.log(error.hint) // Hint for fixing
  }
}

Custom Schema Support

PostgreSQL supports multiple schemas beyond the default public schema. Both Entity and Query APIs support querying from custom schemas.

Entity with Custom Schema

// Query from a custom schema (e.g., "inventory" schema)
const InventoryEntity = Entity<"items", Database>(client, "items", {
  softDelete: false,
  schema: "inventory", // Uses client.schema("inventory").from("items")
})

// All queries will target the "inventory" schema
const items = await InventoryEntity.getItems({ where: { active: true } }).manyOrThrow()

PartitionedEntity with Custom Schema

// Multi-tenant data in a custom schema
const TenantItemsEntity = PartitionedEntity<"items", string, Database>(client, "items", {
  partitionField: "tenant_id",
  softDelete: true,
  schema: "tenant_data", // Custom schema
})

// Queries target the custom schema with partition filter
const items = await TenantItemsEntity.getItems(tenantId).manyOrThrow()

Query API with Custom Schema

import { query } from "supabase-typed-query"

// Use the 7th parameter for schema
const results = await query<"items", Database>(
  client,
  "items",
  { active: true },
  undefined, // is conditions
  undefined, // wherein conditions
  undefined, // order
  "inventory", // schema
).manyOrThrow()

Default Behavior

• If no schema is specified, queries use the default public schema (via client.from())
• When a schema is specified, queries use client.schema(name).from(table)

Augmenting Types for Not-Yet-Generated Tables

When a table exists in the database (e.g. a fresh migration) but hasn't been picked up by supabase gen types, use WithTable to layer it onto your Database type instead of hand-rolling intersections or falling back to Entity<any, any, any>:

import { PartitionedEntity, WithTable } from "supabase-typed-query"

type DbDigest = { id: string; user_id: string; generated_at: string }

type WithDigest = WithTable<
  Database,
  "agent_schedule",
  "digest_history",
  { Row: DbDigest; Insert: DbDigest; Update: Partial<DbDigest> }
>

const DigestEntity = PartitionedEntity<"digest_history", UserId, WithDigest, "agent_schedule">(
  client,
  "digest_history",
  { partitionField: "user_id", softDelete: false, schema: "agent_schedule" },
)

WithTable preserves every other schema and table, so the result drops straight into the DB generic. Once types are regenerated, remove the wrapper and use Database directly.

Soft Delete

Configuration

The softDelete configuration affects both queries AND delete operations:

// Entity with soft deletes enabled
const UserEntity = Entity<"users", Database>(client, "users", { softDelete: true })
// - Queries automatically filter: WHERE deleted IS NULL
// - deleteItem/deleteItems set deleted timestamp (soft delete)

// Entity without soft deletes
const AllUsersEntity = Entity<"users", Database>(client, "users", { softDelete: false })
// - Queries include all records (no automatic filtering)
// - deleteItem/deleteItems permanently remove rows (hard delete)

Per-Query Override

// Override for specific queries
const allUsers = await UserEntity.getItems().includeDeleted().many()
const deletedOnly = await UserEntity.getItems().onlyDeleted().many()
const activeOnly = await UserEntity.getItems().excludeDeleted().many()

// Works with query() too
const all = await query<"users", Database>(client, "users", {}).includeDeleted().many()

Common Patterns

Safe Data Fetching

import { Option } from "functype"

async function getUserEmail(userId: string): Promise<Option<string>> {
  const result = await query<"users", Database>(client, "users", { id: userId }).one()
  return result.map((maybeUser) => maybeUser.flatMap((user) => Option(user.email))).orElse(Option.none())
}

Batch Operations

// Add multiple items
const posts = await PostEntity.addItems({
  items: [
    { title: "Post 1", status: "draft" },
    { title: "Post 2", status: "draft" },
  ],
}).manyOrThrow()

// Update all matching items
const archived = await PostEntity.updateItems({
  where: { status: "draft" },
  data: { status: "archived" },
}).manyOrThrow()

Complex Queries

// Combining operators
const results = await query<"posts", Database>(client, "posts", {
  created_at: { gte: startDate, lte: endDate },
  status: { in: ["published", "featured"] },
  view_count: { gt: 100 },
})
  .or({ is_pinned: true })
  .limit(20)
  .manyOrThrow()

Debugging Tips

Common Issues

"Type 'X' is not assignable to type 'Y'"

• Ensure you're passing the Database type as a generic: query<"users", Database>(...)
• Check that your database types are up-to-date with npx supabase gen types

"Property 'X' does not exist"

• Verify table name matches your database schema
• Regenerate types if you've added new columns

Empty results when expecting data

• Check soft delete configuration: { softDelete: true } filters out deleted records
• Use .includeDeleted() to see all records

OrThrow throwing unexpectedly

• Use explicit TaskOutcome methods (.one(), .many()) for debugging
• Check result.isOk() and inspect result.error for details

Resources

references/

• quick-reference.md - API cheat sheet
• common-patterns.md - Usage patterns and recipes

External Links

• GitHub: https://github.com/jordanburke/supabase-typed-query
• NPM: https://www.npmjs.com/package/supabase-typed-query
• functype: https://github.com/jordanburke/functype