PostgREST-compatible REST API for postgres.do - automatic CRUD endpoints from your schema
npm install @dotdo/postgrestPostgREST-compatible REST API for postgres.do - automatic CRUD endpoints from your PostgreSQL schema.
This package implements the PostgREST API specification, allowing existing applications built for PostgREST or Supabase to work seamlessly with postgres.do. It provides a Hono-based router that automatically creates REST endpoints for your database tables.
- Full PostgREST query syntax - Filters, ordering, pagination, and column selection
- Resource embedding - Automatic JOINs based on foreign key relationships
- Schema caching - Optimal performance with intelligent metadata caching
- Hono router integration - Mount anywhere in your Hono application
- Complete header handling - Prefer, Range, Content-Range headers
- CORS support - Configurable cross-origin resource sharing
- SQL injection protection - Built-in validation and parameterized queries
- Full-text search - Support for PostgreSQL's text search operators
``bash`
npm install @dotdo/postgrest honoor
pnpm add @dotdo/postgrest honoor
yarn add @dotdo/postgrest hono
`typescript
import { Hono } from 'hono'
import { createPostgRESTRouter } from '@dotdo/postgrest'
import postgres from 'postgres.do'
const app = new Hono()
const sql = postgres('postgres://db.postgres.do/mydb')
// Create and mount the PostgREST router
app.route('/rest/v1', createPostgRESTRouter(sql))
export default app
`
You now have a full PostgREST-compatible API:
`bashList users
curl https://api.example.com/rest/v1/users
API Reference
$3
Creates a Hono router with PostgREST-compatible endpoints.
`typescript
import { createPostgRESTRouter } from '@dotdo/postgrest'
import type { SQLExecutor, PostgRESTRouterOptions } from '@dotdo/postgrest'
`#### Parameters
| Parameter | Type | Description |
|-----------|------|-------------|
|
sql | SQLExecutor | Function to execute SQL queries |
| options | PostgRESTRouterOptions | Configuration options |#### SQLExecutor Type
`typescript
interface SQLExecutor {
(sql: string, params?: unknown[]): Promise<{ rows: Record[] }>
}
`#### Options
| Option | Type | Default | Description |
|--------|------|---------|-------------|
|
schema | string | 'public' | PostgreSQL schema to use |
| basePath | string | '' | Base path for Location headers |
| maxLimit | number | 1000 | Maximum rows per request |
| defaultLimit | number | 100 | Default rows per request |
| schemaCacheTTL | number | 60000 | Schema cache TTL in milliseconds |
| cors | boolean | true | Enable CORS headers |
| corsOrigins | string \| string[] | - | Allowed CORS origins |
| corsCredentials | boolean | - | Allow credentials with CORS |
| validateTable | (table: string) => boolean | - | Custom table name validator |
| validateFunction | (fn: string) => boolean | - | Custom function name validator |$3
The router creates the following endpoints for each table:
| Method | Path | Description |
|--------|------|-------------|
|
GET | /:table | Select rows with optional filters |
| HEAD | /:table | Get count without data |
| POST | /:table | Insert new rows |
| PATCH | /:table | Update rows matching filters |
| DELETE | /:table | Delete rows matching filters |
| GET | /rpc/:function | Call a stored function (read-only) |
| POST | /rpc/:function | Call a stored function |Query Syntax
$3
`http
GET /users?select=id,name,email
GET /users?select=*
GET /users?select=id,posts(title,content) # With embedded resources
GET /users?select=id,name:full_name # Column alias
`$3
| Operator | Description | Example |
|----------|-------------|---------|
|
eq | Equal | ?status=eq.active |
| neq | Not equal | ?status=neq.deleted |
| gt | Greater than | ?age=gt.18 |
| gte | Greater or equal | ?score=gte.100 |
| lt | Less than | ?price=lt.50 |
| lte | Less or equal | ?qty=lte.10 |
| like | Pattern match | ?name=like.john |
| ilike | Case-insensitive | ?email=ilike.*@GMAIL.COM |
| is | NULL/boolean check | ?deleted_at=is.null |
| in | In array | ?id=in.(1,2,3) |
| cs | Contains (arrays) | ?tags=cs.{a,b} |
| cd | Contained by | ?tags=cd.{a,b,c} |
| ov | Overlaps | ?tags=ov.{a,b} |
| sl | Strictly left | ?range=sl.[1,5] |
| sr | Strictly right | ?range=sr.[1,5] |
| nxl | Not extends left | ?range=nxl.[1,5] |
| nxr | Not extends right | ?range=nxr.[1,5] |
| adj | Adjacent | ?range=adj.[1,5] |$3
`http
GET /users?age=not.eq.25 # NOT equal
GET /users?name=not.like.test
`$3
`http
GET /users?or=(status.eq.active,role.eq.admin)
GET /users?and=(age.gte.18,age.lte.65)
`$3
`http
GET /posts?content=fts.search+term # to_tsquery
GET /posts?content=plfts.search+term # plainto_tsquery
GET /posts?content=phfts.search+phrase # phraseto_tsquery
GET /posts?content=wfts.search+term # websearch_to_tsquery
`$3
`http
GET /users?order=created_at.desc
GET /users?order=name.asc,id.desc
GET /users?order=name.asc.nullsfirst
GET /users?order=name.desc.nullslast
`$3
`http
GET /users?limit=10&offset=20
`Or using the Range header:
`http
GET /users
Range: items=0-24
`$3
Fetch related data based on foreign key relationships:
`http
GET /posts?select=id,title,author:users(name,email)
GET /users?select=,posts()
`Headers
$3
| Header | Description | Example |
|--------|-------------|---------|
|
Prefer | Request preferences | return=representation, count=exact |
| Range | Pagination range | items=0-24 |$3
| Option | Values | Description |
|--------|--------|-------------|
|
return | representation, minimal, headers-only | Response body preference |
| count | exact, planned, estimated, none | Row count mode |
| resolution | merge-duplicates, ignore-duplicates | Upsert conflict resolution |
| missing | default, null | Missing column handling |
| tx | commit, rollback | Transaction handling |
| max-affected | number | Maximum affected rows |`bash
Return inserted/updated rows
Prefer: return=representationReturn nothing (faster)
Prefer: return=minimalGet exact count
Prefer: count=exact
`$3
| Header | Description |
|--------|-------------|
|
Content-Range | Pagination info: items 0-24/100 |
| Location | URL of created resource (POST) |
| Preference-Applied | Applied preferences |CRUD Operations
$3
`bash
All rows
GET /usersWith filters
GET /users?status=eq.active&age=gt.18Select columns
GET /users?select=id,name,emailWith relations
GET /posts?select=*,author:users(name)Ordering
GET /users?order=created_at.descPagination
GET /users?limit=10&offset=20
`$3
`bash
Single row
POST /users
{"name": "John", "email": "john@example.com"}Multiple rows
POST /users
[
{"name": "John", "email": "john@example.com"},
{"name": "Jane", "email": "jane@example.com"}
]With representation return
POST /users
Prefer: return=representation
{"name": "John", "email": "john@example.com"}
`$3
`bash
Update with filter (required)
PATCH /users?id=eq.123
{"status": "inactive"}
`Note: PATCH requires filters to prevent accidental full-table updates.
$3
`bash
Delete with filter (required)
DELETE /users?id=eq.123
`Note: DELETE requires filters to prevent accidental full-table deletes.
$3
`bash
POST with body
POST /rpc/get_user_stats
{"user_id": 123}GET with query params
GET /rpc/get_user_stats?user_id=123
`Additional Exports
$3
Parse PostgREST query parameters manually:
`typescript
import { PostgrestParser } from '@dotdo/postgrest'
import type { ParsedQuery, Filter, OrderClause, EmbeddedResource, FilterOperator } from '@dotdo/postgrest'const parser = new PostgrestParser()
const params = new URLSearchParams('select=id,name&age=gt.18&order=name.asc')
const parsed = parser.parse(params)
// {
// columns: ['id', 'name'],
// filters: [{ column: 'age', operator: 'gt', value: 18 }],
// order: [{ column: 'name', direction: 'asc' }],
// embedded: [],
// }
`#### ParsedQuery Type
`typescript
interface ParsedQuery {
columns: string[] | '*'
embedded: EmbeddedResource[]
filters: Filter[]
order: OrderClause[]
limit?: number
offset?: number
count?: 'exact' | 'planned' | 'estimated'
}interface Filter {
column: string
operator: FilterOperator
value: unknown
negate?: boolean
}
interface OrderClause {
column: string
direction: 'asc' | 'desc'
nullsFirst?: boolean
}
interface EmbeddedResource {
name: string
columns: string[] | '*'
alias?: string
embedded?: EmbeddedResource[]
filters?: Filter[]
order?: OrderClause[]
limit?: number
offset?: number
}
`$3
Build SQL queries from parsed parameters:
`typescript
import { QueryBuilder, buildQuery } from '@dotdo/postgrest'
import type { BuiltQuery, QueryBuilderOptions } from '@dotdo/postgrest'const builder = new QueryBuilder({ schema: 'public', maxLimit: 1000, defaultLimit: 100 })
const { sql, params, countSql } = builder.buildSelect('users', {
columns: ['id', 'name'],
filters: [{ column: 'active', operator: 'eq', value: true }],
order: [{ column: 'name', direction: 'asc' }],
embedded: [],
limit: 10,
})
// sql: SELECT "users"."id", "users"."name" FROM "public"."users" WHERE "active" = $1 ORDER BY "name" ASC LIMIT 10
// params: [true]
// Convenience function
const query = buildQuery('select', 'users', {
query: parsedQuery,
tableSchema: schema,
builderOptions: { schema: 'public' },
})
`#### QueryBuilder Methods
| Method | Description |
|--------|-------------|
|
buildSelect(table, query, schema?, foreignKeys?) | Build SELECT query |
| buildInsert(table, data, returning?) | Build INSERT query |
| buildUpdate(table, data, filters, returning?) | Build UPDATE query |
| buildDelete(table, filters, returning?) | Build DELETE query |
| buildRPC(functionName, args?) | Build function call |$3
Cache database schema for optimal performance:
`typescript
import { SchemaCache } from '@dotdo/postgrest'
import type { TableSchema, ColumnInfo, ForeignKeyInfo, SchemaCacheOptions } from '@dotdo/postgrest'const cache = new SchemaCache({
schema: 'public',
cacheTTL: 60000,
queryFn: sql,
})
await cache.hasTable('users') // Check table exists
await cache.getTable('users') // Get table schema
await cache.getTables() // Get all tables
await cache.getColumn('users', 'id') // Get column info
await cache.validateColumns('users', ['id', 'name']) // Validate columns exist
await cache.getAllForeignKeys() // Get all foreign keys
await cache.getForeignKeysTo('users') // Get FKs referencing table
await cache.getEmbeddingInfo('posts', 'users') // Get embedding relationship
await cache.refresh() // Force cache refresh
cache.clear() // Clear cache
`#### Schema Types
`typescript
interface TableSchema {
name: string
schema: string
columns: Map
primaryKey: string[]
foreignKeys: ForeignKeyInfo[]
indexes: string[]
}interface ColumnInfo {
name: string
type: string
nullable: boolean
defaultValue?: string
isPrimaryKey: boolean
isUnique: boolean
maxLength?: number
precision?: number
scale?: number
}
interface ForeignKeyInfo {
name: string
column: string
referencedTable: string
referencedColumn: string
onDelete?: 'CASCADE' | 'SET NULL' | 'SET DEFAULT' | 'RESTRICT' | 'NO ACTION'
onUpdate?: 'CASCADE' | 'SET NULL' | 'SET DEFAULT' | 'RESTRICT' | 'NO ACTION'
}
`$3
`typescript
import {
parsePreferHeader,
setResponseHeaders,
buildContentRange,
parseRangeHeader,
buildLocationHeader,
getResponseStatus,
setCORSHeaders,
buildHeadersOnlyResponse,
} from '@dotdo/postgrest'
import type { PreferHeader, ResponseHeaderOptions } from '@dotdo/postgrest'// Parse Prefer header
const prefer = parsePreferHeader('return=representation, count=exact')
// { return: 'representation', count: 'exact' }
// Build Content-Range header
const range = buildContentRange({ offset: 0, rowCount: 25, totalCount: 100 })
// "items 0-24/100"
// Parse Range header
const { offset, limit } = parseRangeHeader('items=0-24')
// { offset: 0, limit: 25 }
// Build Location header for created resource
const location = buildLocationHeader('/rest/v1', 'users', { id: 123 })
// "/rest/v1/users?id=eq.123"
// Get appropriate response status
const status = getResponseStatus('POST', 1, prefer)
// 201
`#### PreferHeader Type
`typescript
interface PreferHeader {
return?: 'representation' | 'minimal' | 'headers-only'
count?: 'exact' | 'planned' | 'estimated' | 'none'
resolution?: 'merge-duplicates' | 'ignore-duplicates'
missing?: 'default' | 'null'
tx?: 'commit' | 'rollback'
maxAffected?: number
}
`$3
Content Security Policy middleware for API routes:
`typescript
import {
csp,
securityHeaders,
API_CSP_DEFAULTS,
STRICT_WEB_CSP,
DEVELOPMENT_CSP,
CSP_PRESETS,
buildCSPHeader,
generateSecureNonce,
getDefaultDirectives,
} from '@dotdo/postgrest'
import type { CSPDirectives, CSPEnvironment, CSPOptions, SecurityHeadersOptions } from '@dotdo/postgrest'// Apply CSP to API routes
app.use('/api/*', csp(API_CSP_DEFAULTS))
// Apply comprehensive security headers
app.use(securityHeaders({ enableCSP: true, environment: 'production' }))
`CORS Configuration
The router provides fine-grained CORS control with a security-first approach.
$3
By default, CORS is enabled but no
Access-Control-Allow-Origin header is set:`typescript
const api = createPostgRESTRouter(sql, {
cors: true, // Enable CORS preflight handling
// No corsOrigins set - restrictive default
})
`$3
`typescript
const api = createPostgRESTRouter(sql, {
cors: true,
corsOrigins: 'https://app.example.com',
})
`$3
`typescript
const api = createPostgRESTRouter(sql, {
cors: true,
corsOrigins: [
'https://app.example.com',
'https://admin.example.com',
],
})
`$3
Warning: Only use for public APIs.
`typescript
const api = createPostgRESTRouter(sql, {
cors: true,
corsOrigins: '*',
})
`$3
`typescript
const api = createPostgRESTRouter(sql, {
cors: true,
corsOrigins: 'https://app.example.com', // Must be specific origin
corsCredentials: true,
})
`Note: Per CORS specification, credentials cannot be used with wildcard origin.
Error Handling
The router returns appropriate HTTP status codes and error messages:
| Status | Condition |
|--------|-----------|
|
400 | Invalid table name, missing filters for PATCH/DELETE, null violation |
| 403 | Permission denied |
| 404 | Table not found |
| 409 | Duplicate key or foreign key violation |
| 500 | Database error |Error response format:
`json
{
"error": "Duplicate key violation",
"message": "duplicate key value violates unique constraint...",
"code": "23505"
}
`PostgREST Compatibility Notes
This package aims for high compatibility with the PostgREST specification.
$3
- Horizontal filtering (all operators)
- Vertical filtering (column selection)
- Ordering with nulls positioning
- Pagination (limit/offset and Range header)
- Resource embedding via foreign keys
- Prefer header (return, count, resolution, missing, tx, max-affected)
- Content-Range response header
- Location header for created resources
- RPC function calls (GET and POST)
- CORS preflight handling
- HEAD requests for count-only queries
$3
- Safety guards: PATCH and DELETE require filters to prevent accidental full-table operations
- Schema introspection: Uses information_schema queries rather than pg_catalog
- Embedding: Uses subqueries rather than lateral joins for simpler queries
- No row-level security: RLS should be implemented at the database level
TypeScript Types
All types are exported for use in your application:
`typescript
import type {
// Router types
SQLExecutor,
PostgRESTRouterOptions, // Parser types
ParsedQuery,
Filter,
FilterOperator,
OrderClause,
EmbeddedResource,
// Schema types
TableSchema,
ColumnInfo,
ForeignKeyInfo,
SchemaCacheOptions,
// Builder types
BuiltQuery,
QueryBuilderOptions,
// Header types
PreferHeader,
ResponseHeaderOptions,
// CSP types
CSPDirectives,
CSPEnvironment,
CSPOptions,
SecurityHeadersOptions,
SecurityMiddlewareOptions,
} from '@dotdo/postgrest'
`Part of the postgres.do Ecosystem
@dotdo/postgrest is part of postgres.do - PostgreSQL at the edge.
postgres.do | SQL tagged template client |
| @dotdo/postgres | PostgreSQL server (DO + PGLite) |
| @dotdo/supabase | Supabase-compatible client |
| @dotdo/neon` | Neon-compatible API |- Documentation
- GitHub
- PostgREST API Spec
MIT