JSPM

postgres.do

0.1.1
  • ESM via JSPM
  • ES Module Entrypoint
  • Export Map
  • Keywords
  • License
  • Repository URL
  • TypeScript Types
  • README
  • Created
  • Published
  • Downloads 114
  • Score
    100M100P100Q64061F
  • License MIT

PostgreSQL for Cloudflare Workers - sql tagged template client with Drizzle ORM support

Package Exports

  • postgres.do
  • postgres.do/drizzle
  • postgres.do/drizzle/migrator
  • postgres.do/extensions
  • postgres.do/pglite
  • postgres.do/query-builder
  • postgres.do/retention
  • postgres.do/rpc

Readme

postgres.do

Deploy PostgreSQL to the edge in seconds. Full CLI and SQL client.

npm version npm downloads License: MIT TypeScript Cloudflare Workers

import postgres from 'postgres.do'

const sql = postgres('https://db.postgres.do/mydb')
const users = await sql`SELECT * FROM users WHERE active = ${true}`

SQL injection safe. Works everywhere. Zero config.

Table of Contents

Installation

npm install postgres.do

Or with your preferred package manager:

# yarn
yarn add postgres.do

# pnpm
pnpm add postgres.do

# bun
bun add postgres.do

Requirements

  • Node.js 18+ (for local development and CLI)
  • Works in all modern runtimes: Cloudflare Workers, Vercel Edge, Deno, Bun, browsers

Optional Dependencies

For local development with PGLite:

npm install @dotdo/pglite

For Drizzle ORM integration:

npm install drizzle-orm

Quick Start

1. Install

npm install postgres.do

2. Query

import postgres from 'postgres.do'

const sql = postgres('https://db.postgres.do/mydb')

// Tagged template queries (SQL injection safe)
const users = await sql`SELECT * FROM users WHERE id = ${userId}`

// Transactions
const result = await sql.begin(async (tx) => {
  await tx`INSERT INTO orders (user_id) VALUES (${userId})`
  return tx`SELECT * FROM orders WHERE user_id = ${userId}`
})

That's it. No connection strings. No poolers. No cold starts.

The Problem

You need a database for your edge application. But:

  • Setting up PostgreSQL means provisioning servers, configuring networking, managing backups
  • Using managed databases means 100ms+ latency from edge locations and connection limits
  • Serverless databases charge per query and still require connection string management

You just want to write SELECT * FROM users and have it work. Fast.

The Solution

postgres.do is a complete PostgreSQL solution for the edge:

  1. SQL Client - Tagged template literals for safe, ergonomic queries
  2. CLI - Create, manage, migrate, and deploy databases from your terminal
  3. Edge Runtime - Works in Cloudflare Workers, Vercel Edge, Deno, Bun, and browsers
Your Code                postgres.do                  Cloudflare Edge
+----------+            +------------+            +------------------+
|   sql`   |  ------>   |   Client   |  ------>   |   PostgresDO     |
| SELECT   |    HTTP    |   (this    |            |   (PGLite in     |
|   ...`   |    or WS   |   package) |            |   Durable Object)|
+----------+            +------------+            +------------------+

CLI

The postgres.do CLI manages your entire database lifecycle:

Database Management

# Create a new database
postgres.do create mydb

# List all databases
postgres.do list

# Get database info
postgres.do info mydb

# Delete a database
postgres.do delete mydb --force

Local Development

# Start local dev server with PGLite
postgres.do dev

# Reset local database
postgres.do dev:reset

# Run with seed data
postgres.do dev --seed

Migrations

# Create a migration
postgres.do migrate:create add_users_table

# Run pending migrations
postgres.do migrate

# Check migration status
postgres.do migrate:status

# Rollback last migration
postgres.do migrate:rollback

# Preview without applying
postgres.do migrate:dry-run

Schema Management

# Generate Drizzle schema from existing database
postgres.do introspect --url $DATABASE_URL --output ./schema.ts

# Compare two schemas
postgres.do schema:diff --from $LOCAL_URL --to $PROD_URL

# Pull remote schema to local file
postgres.do schema:pull --url $DATABASE_URL

# Push local schema to database
postgres.do schema:push --input ./schema.ts

Backup and Restore

# Create backup
postgres.do backup mydb -o backup.sql

# List backups
postgres.do backup:list mydb

# Restore from backup
postgres.do restore backup.sql --database mydb

# Clone a database
postgres.do clone production staging

Interactive Shell

# Start SQL REPL
postgres.do shell mydb

# Execute single command
postgres.do shell mydb -c "SELECT * FROM users"

# Execute from file
postgres.do shell mydb -f queries.sql

Logs

# View logs
postgres.do logs mydb

# Follow logs in real-time
postgres.do logs mydb --tail

# Filter by level
postgres.do logs mydb --level error

Why postgres.do?

Feature postgres.do Traditional Setup
Setup time 10 seconds Hours to days
Latency <10ms (edge) 50-200ms (regional)
SQL injection Impossible Manual prevention
Connection management Automatic Pool configuration
Local development postgres.do dev Docker + config
Migrations Built-in CLI Third-party tools

SQL Client Features

Tagged Template Literals

SQL injection is impossible by design:

// Safe - values are parameterized automatically
const users = await sql`SELECT * FROM users WHERE id = ${userId}`

// Multiple parameters work seamlessly
const users = await sql`
  SELECT * FROM users
  WHERE status = ${status}
  AND created_at > ${startDate}
`

// Use sql() helper for arrays in IN clauses
const ids = [1, 2, 3]
const users = await sql`SELECT * FROM users WHERE id IN ${sql(ids)}`

// Dynamic column selection
const columns = ['id', 'name', 'email']
const users = await sql`SELECT ${sql(columns)} FROM users`

Transactions

Automatic rollback on error:

const result = await sql.begin(async (tx) => {
  await tx`INSERT INTO orders (user_id, total) VALUES (${userId}, ${total})`
  await tx`UPDATE inventory SET stock = stock - 1 WHERE id = ${productId}`
  return tx`SELECT * FROM orders WHERE user_id = ${userId}`
})

// With transaction options
const result = await sql.begin(async (tx) => {
  return tx`SELECT * FROM orders`
}, {
  isolationLevel: 'serializable',
  readOnly: true
})

// Nested savepoints
const result = await sql.begin(async (tx) => {
  await tx`INSERT INTO users (name) VALUES ('Alice')`

  await tx.savepoint(async (sp) => {
    await sp`INSERT INTO orders (user_id) VALUES (1)`
    // If this throws, only the savepoint is rolled back
  })

  return tx`SELECT * FROM users`
})

Type Parsing

Automatic conversion for PostgreSQL types:

const [row] = await sql`SELECT
  created_at,  -- Date object
  is_active,   -- boolean
  metadata,    -- parsed JSON object (JSONB)
  big_number   -- BigInt for int8
FROM users WHERE id = 1`

// Supported automatic type conversions:
// - Boolean (bool)           -> boolean
// - Integer (int2, int4)     -> number
// - BigInt (int8)            -> BigInt
// - Float (float4, float8)   -> number
// - Numeric (numeric)        -> number
// - Timestamp (timestamp)    -> Date
// - Timestamptz (timestamptz)-> Date
// - JSON/JSONB               -> parsed object/array

Custom Type Parsers

Override default type parsing:

const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  parsers: {
    // OID 1082 = date type - return as string instead of Date
    1082: (value) => value,
    // OID 1700 = numeric type - use decimal.js for precision
    1700: (value) => new Decimal(value),
  }
})

Raw SQL Queries

For dynamic queries or ORM integration:

// Execute raw SQL with parameters
const users = await sql.unsafe(
  'SELECT * FROM users WHERE status = $1 AND role = $2',
  ['active', 'admin']
)

// Useful for dynamic table names (be careful with user input!)
const tableName = 'users' // validated elsewhere
const rows = await sql.unsafe(`SELECT * FROM ${tableName}`)

Transport Options

// HTTP (default) - Best for serverless, stateless requests
const sql = postgres({ url: 'https://db.postgres.do/mydb' })

// WebSocket - Best for high-throughput, persistent connections
const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  transport: 'ws'
})

// With full configuration
const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  transport: 'ws',
  apiKey: process.env.POSTGRES_API_KEY,
  connectTimeout: 5000,  // 5 second connection timeout
  queryTimeout: 30000,   // 30 second query timeout
})

Drizzle ORM Integration

Drop-in compatible with Drizzle:

import postgres from 'postgres.do'
import { drizzle } from 'drizzle-orm/postgres-js'
import * as schema from './schema'

const sql = postgres('https://db.postgres.do/mydb')
const db = drizzle(sql, { schema })

// Use Drizzle's type-safe query builder
const users = await db.select().from(schema.users)

// Transactions work seamlessly
await db.transaction(async (tx) => {
  await tx.insert(schema.users).values({ name: 'Alice' })
  await tx.insert(schema.orders).values({ userId: 1 })
})

Error Handling

import { PostgresError, ConnectionError, TimeoutError } from 'postgres.do'

try {
  await sql`INSERT INTO users (email) VALUES (${email})`
} catch (e) {
  if (e instanceof PostgresError) {
    // Standard PostgreSQL error fields
    console.log(e.code)       // '23505' (unique_violation)
    console.log(e.message)    // 'duplicate key value violates unique constraint'
    console.log(e.severity)   // 'ERROR'
    console.log(e.detail)     // 'Key (email)=(foo@bar.com) already exists.'
    console.log(e.hint)       // Suggestion for fixing the error (if any)
    console.log(e.constraint) // 'users_email_key'
    console.log(e.table)      // 'users'
    console.log(e.column)     // 'email'
  } else if (e instanceof ConnectionError) {
    console.log('Failed to connect:', e.message)
  } else if (e instanceof TimeoutError) {
    console.log('Query timed out:', e.message)
  }
}

// Handle specific PostgreSQL error codes
try {
  await sql`INSERT INTO users (email) VALUES (${email})`
} catch (e) {
  if (e instanceof PostgresError && e.code === '23505') {
    // Handle duplicate key error
    return { error: 'Email already exists' }
  }
  throw e // Re-throw other errors
}

Authentication

Integrate with oauth.do for user-scoped databases:

import { createAuthenticatedClient, withAuth } from 'postgres.do'

// Option 1: Create client with token
const sql = createAuthenticatedClient(token, {
  url: 'https://db.postgres.do/mydb'
})

// Option 2: Wrap your handler
export default {
  fetch: withAuth(async (request, { user, sql }) => {
    const data = await sql`SELECT * FROM my_table`
    return Response.json({ user, data })
  })
}

API Reference

postgres(url?, options?)

Create a PostgreSQL client.

Option Type Default Description
url string - Connection URL (https:// or postgres://)
transport 'http' | 'ws' 'http' Transport protocol
apiKey string - API key for authentication
connectTimeout number 10000 Connection timeout in ms
queryTimeout number 30000 Query timeout in ms

sql\query``

Execute a tagged template query. Returns Promise<Row[]>.

sql(value)

Helper for dynamic values in tagged templates (arrays, identifiers).

sql.begin(fn, options?)

Start a transaction. Callback receives transaction-scoped sql.

Option Type Description
isolationLevel string Transaction isolation level
readOnly boolean Read-only transaction

sql.unsafe(query, params?)

Execute raw SQL. Use with caution - does not prevent SQL injection.

sql.end()

Close the connection and release resources.

Troubleshooting

Connection Issues

Problem: ConnectionError: Failed to connect to database

  1. Check the URL format: Ensure your URL is valid (https://db.postgres.do/mydb or postgres://db.postgres.do/mydb)
  2. Verify network access: Ensure your environment can reach db.postgres.do
  3. Check API key: If using authentication, verify your API key is correct
// Debug connection issues
const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  connectTimeout: 5000  // Reduce timeout for faster feedback
})

Query Timeouts

Problem: TimeoutError: Query timed out

  1. Long-running queries: Increase the query timeout for complex operations
  2. Large result sets: Use pagination or streaming for large datasets
  3. Index optimization: Ensure proper indexes exist for your queries
const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  queryTimeout: 60000  // 60 second timeout for complex queries
})

// Use LIMIT for large tables
const users = await sql`SELECT * FROM users LIMIT 100 OFFSET ${page * 100}`

SQL Errors

Problem: PostgresError: relation "users" does not exist

  1. Table doesn't exist: Run migrations to create the table
  2. Schema mismatch: Verify you're connected to the correct database
  3. Case sensitivity: PostgreSQL lowercases unquoted identifiers
# Check migration status
postgres.do migrate:status

# Run pending migrations
postgres.do migrate

WebSocket Connection Drops

Problem: WebSocket connection frequently disconnects

  1. Network instability: WebSocket requires stable connection
  2. Idle timeout: Connections may close after inactivity
  3. Consider HTTP: For infrequent queries, HTTP transport may be more reliable
// Fall back to HTTP if WebSocket fails
const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  transport: 'http'  // More reliable for unstable networks
})

Type Parsing Issues

Problem: Dates or JSON not parsing correctly

  1. Custom parsers: Override default parsing behavior
  2. Check column types: Ensure PostgreSQL types match expectations
const sql = postgres({
  url: 'https://db.postgres.do/mydb',
  parsers: {
    // Return dates as ISO strings instead of Date objects
    1082: (value) => value,
    1114: (value) => value,
    1184: (value) => value,
  }
})

Memory Issues in Local Development

Problem: PGLite consuming too much memory

  1. Close connections: Always call sql.end() when done
  2. Limit concurrent queries: Don't run too many queries in parallel
  3. Restart dev server: postgres.do dev:reset to clear state
// Always close connections
const sql = postgres('https://db.postgres.do/mydb')
try {
  const users = await sql`SELECT * FROM users`
  return users
} finally {
  await sql.end()
}

CLI Not Found

Problem: command not found: postgres.do

  1. Installation: Ensure package is installed globally or locally
  2. Path issues: Use npx for local installations
# Global installation
npm install -g postgres.do
postgres.do --help

# Local installation (use npx)
npm install postgres.do
npx postgres.do --help

# Or add to package.json scripts
# "scripts": { "db": "postgres.do" }
npm run db -- --help

License

MIT