Are you an LLM? Read llms.txt for a summary of the docs, or llms-full.txt for the full context.
Skip to content

SDK Overview

Type-safe PostgreSQL client with Supabase-compatible query builder.

Features

  • Type-safe queries - Full TypeScript support from database schema
  • Supabase-compatible API - Familiar .eq(), .single(), .order() methods
  • Kysely integration - Powerful query builder under the hood
  • RPC support - Call PostgreSQL functions with type safety
  • Realtime subscriptions - LISTEN/NOTIFY with automatic reconnection

Installation

npm install @postbase/sdk pg
npm install -D @types/pg

Quick Start

import { createClient } from '@postbase/sdk'
import type { Database } from './db/types'
 
// Create client
const db = createClient<Database>({
  connectionString: process.env.DATABASE_URL,
})
 
// Query data
const users = await db
  .from('users')
  .eq('active', true)
  .order('created_at', { ascending: false })
  .limit(10)
  .execute()
 
// Insert data
const newUser = await db
  .from('users')
  .insert({ email: 'alice@example.com', name: 'Alice' })
  .returning()
  .single()
 
// Update data
await db
  .from('users')
  .eq('id', 1)
  .update({ name: 'Alice Smith' })
 
// Delete data
await db
  .from('users')
  .eq('id', 1)
  .delete()

Generating Types

Generate TypeScript types from your database:

postbase types generate \
  --database myapp \
  --all \
  --output ./src/db/types.ts

This creates fully typed interfaces:

export interface Database {
  public: {
    Tables: {
      users: {
        Row: {
          id: number
          email: string
          name: string
          created_at: Date
        }
        Insert: {
          email: string
          name: string
          created_at?: Date
        }
        Update: {
          email?: string
          name?: string
        }
      }
    }
  }
}

Query Builder

Select Queries

// All rows
const users = await db.from('users').execute()
 
// With conditions
const activeUsers = await db
  .from('users')
  .eq('active', true)
  .gt('age', 18)
  .order('name')
  .execute()
 
// Single row
const user = await db
  .from('users')
  .eq('id', 1)
  .single()
 
// With limit
const recentUsers = await db
  .from('users')
  .order('created_at', { ascending: false })
  .limit(10)
  .execute()

Filter Methods

// Equality
.eq('column', value)           // column = value
.neq('column', value)          // column != value
 
// Comparison
.gt('column', value)           // column > value
.gte('column', value)          // column >= value
.lt('column', value)           // column < value
.lte('column', value)          // column <= value
 
// Patterns
.like('column', 'pattern%')    // column LIKE 'pattern%'
.ilike('column', 'pattern%')   // column ILIKE 'pattern%'
 
// Lists
.in('column', [1, 2, 3])       // column IN (1, 2, 3)
 
// Null checks
.is('column', null)            // column IS NULL
.not('column', null)           // column IS NOT NULL
 
// Range
.between('age', 18, 65)        // age BETWEEN 18 AND 65
 
// Text search
.textSearch('content', 'query')

Ordering & Pagination

// Order by single column
.order('created_at', { ascending: false })
 
// Order by multiple columns
.order('status')
.order('created_at', { ascending: false })
 
// Pagination
.limit(10)
.offset(20)
 
// Range (convenience)
.range(0, 9)  // First 10 rows

Selecting Columns

// Select specific columns
const users = await db
  .from('users')
  .select('id', 'name', 'email')
  .execute()

Insert Operations

// Insert single row
const user = await db
  .from('users')
  .insert({ email: 'alice@example.com', name: 'Alice' })
  .returning()
  .single()
 
// Insert multiple rows
const users = await db
  .from('users')
  .insert([
    { email: 'alice@example.com', name: 'Alice' },
    { email: 'bob@example.com', name: 'Bob' },
  ])
  .returning()
  .execute()
 
// Upsert (insert or update)
const user = await db
  .from('users')
  .upsert(
    { email: 'alice@example.com', name: 'Alice Updated' },
    { onConflict: 'email' }
  )
  .returning()
  .single()

Update Operations

// Update with filter
await db
  .from('users')
  .eq('id', 1)
  .update({ name: 'New Name' })
 
// Update multiple rows
await db
  .from('users')
  .eq('active', false)
  .update({ deleted_at: new Date() })
 
// Update with returning
const updated = await db
  .from('users')
  .eq('id', 1)
  .update({ name: 'New Name' })
  .returning()
  .single()

Delete Operations

// Delete with filter
await db
  .from('users')
  .eq('id', 1)
  .delete()
 
// Delete multiple rows
await db
  .from('users')
  .lt('last_login', new Date('2025-01-01'))
  .delete()
 
// Soft delete pattern
await db
  .from('users')
  .eq('id', 1)
  .update({ deleted_at: new Date() })

Error Handling

try {
  const user = await db
    .from('users')
    .eq('id', 1)
    .single()
} catch (error) {
  if (error instanceof PostbaseError) {
    console.error('Database error:', error.message)
    console.error('Code:', error.code)
  }
}

Connection Management

// Create client with connection pool
const db = createClient<Database>({
  connectionString: process.env.DATABASE_URL,
  pool: {
    min: 2,
    max: 10,
  },
})
 
// Close connections when done
await db.close()

Next Steps