What is pgrest-lambda?

Point it at a Postgres schema and get a Supabase-compatible REST API, user signup/login, OAuth, magic links, and an interactive OpenAPI explorer. Run it as a CLI for local development, embed it in your own server, or deploy the reference AWS SAM template.

Works with the @supabase/supabase-js client unchanged. If you've been using Supabase but want your own stack on your own account, pgrest-lambda drops into the same client code.

Key features

• PostgREST-compatible query syntax — filtering, ordering, pagination, upserts, exact counts, and resource embedding (joins).
• Supabase-wire-compatible auth — signup, signin, refresh, user profile, magic link, OAuth, and JWKS.
• Cedar authorization — policy-as-code row-level filters, translated into SQL WHERE clauses before each query runs.
• OpenAPI 3.0 auto-generation — live spec and an interactive Scalar explorer on every running instance.
• Multiple database backends — Aurora DSQL (IAM auth), Aurora Serverless v2, RDS PostgreSQL, or any reachable Postgres.
• Swappable auth providers — better-auth (default, DB-only, no AWS) or Amazon Cognito.
• Deploy-agnostic core — the library doesn't care whether it's behind API Gateway, Kong, Cloudflare Workers, or plain Express.

Quickstart

Get to a working REST API in under 60 seconds. Requires Node.js 20+ and a running Docker daemon.

npx --yes pgrest-lambda dev

That's it. No clone, no config, no AWS account. The command:

1. Starts a Postgres container on localhost:54322 (first run only).
2. Applies the better_auth schema.
3. Starts the API on http://localhost:3000.
4. Writes JWT_SECRET and BETTER_AUTH_SECRET to .env.local so apikeys stay stable across restarts.
5. Prints a banner with the DATABASE_URL, an anon apikey, a service-role apikey, and the docs URL.

Open http://localhost:3000/rest/v1/_docs for the live Scalar API explorer on your own schema. Then point any Supabase client at it:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  'http://localhost:3000',
  '<anon apikey from the banner>',
);

// Signup
await supabase.auth.signUp({
  email: '[email protected]',
  password: 'Passw0rd!',
});

// Query any table in your `public` schema — endpoints are
// auto-generated from schema introspection.
const { data: posts } = await supabase.from('posts').select();

DATABASE_URL=postgres://user:pass@host:5432/db \
  npx pgrest-lambda dev --skip-docker

pgrest-lambda creates the better_auth schema (tables user, session, account, verification, jwks) on first boot. The migration is idempotent, and your public schema is untouched.

Installation

For everyday use, install globally:

npm install -g pgrest-lambda

Or as a dependency, if you want to embed it in your own server or Lambda:

npm install pgrest-lambda

Requirements

• Node.js 20+
• A Docker daemon (only for pgrest-lambda dev with the bundled Postgres container — not needed if you pass your own DATABASE_URL)

Use as a library

createPgrest(config) returns a handler you can route requests into. It accepts an API Gateway-style event and returns a { statusCode, headers, body } response, so it works on AWS Lambda, Fastify, Express, Cloudflare Workers, or any platform you can translate to that shape.

import { createPgrest } from 'pgrest-lambda';

const pgrest = createPgrest({
  database: { connectionString: process.env.DATABASE_URL },
  jwtSecret: process.env.JWT_SECRET,
  auth: {
    provider: 'better-auth',
    betterAuthSecret: process.env.BETTER_AUTH_SECRET,
    betterAuthUrl: process.env.BETTER_AUTH_URL,
  },
});

export const handler = pgrest.handler;

All configuration can be passed as an argument or read from environment variables — explicit arguments win over env vars, env vars win over defaults. The full reference lives in docs/configuration.md; the most common keys are:

| Config key | Env var | Notes | |---|---|---| | database.connectionString | DATABASE_URL | Standard Postgres URI. | | database.dsqlEndpoint | DSQL_ENDPOINT | Switches to Aurora DSQL IAM auth. | | database.passwordSsmParam | PG_PASSWORD_SSM_PARAM | SSM SecureString parameter name; the password is read at connect time instead of PG_PASSWORD. | | jwtSecret | JWT_SECRET | HS256 secret for apikeys, ≥ 32 chars. | | auth.provider | AUTH_PROVIDER | better-auth (default) or cognito. | | policies | POLICIES_PATH | Path or s3://bucket/prefix/ for .cedar files. Defaults to ./policies. | | cors.allowedOrigins | — | '*' rejected in production; provide a list. |

REST-only, no auth:

createPgrest({ auth: false });

Custom auth handler — your function replaces /auth/v1/*:

createPgrest({ auth: (event) => yourHandler(event) });

Amazon Cognito instead of better-auth:

createPgrest({
  auth: {
    provider: 'cognito',
    region: 'us-east-1',
    clientId: process.env.USER_POOL_CLIENT_ID,
  },
});

Authorization

Every REST request runs through a Cedar policy check. The engine combines all .cedar files in policies/ (or wherever POLICIES_PATH points), translates each row-level predicate into a SQL WHERE clause, and attaches it to the query before execution.

The shipped policies/default.cedar lets authenticated users read and write their own rows (resource.user_id == principal) and lets service_role bypass all checks.

// Everyone — including anon — can read the posts table.
permit(
    principal,
    action == PgrestLambda::Action::"select",
    resource is PgrestLambda::Row
) when {
    context.table == "posts"
};

See docs/authorization.md for a full guide with recipes (public read, team-scoped, admin override, forbid-on-archived), an error reference, and a Cedar syntax cheatsheet.

How it works

Client (supabase-js, fetch, curl)
  |
  v
Your platform (API Gateway, Kong, Cloudflare Workers, Express, ...)
  |
  v
pgrest-lambda handler
  |
  +--- /auth/v1/*  -->  Auth provider (better-auth or Cognito)
  |                        signup, signin, refresh, OAuth, magic link, JWKS
  |
  +--- /rest/v1/*  -->  REST engine
                           schema introspection (pg_catalog)
                           query parsing (PostgREST-compatible)
                           Cedar authorization (compiled to SQL WHERE)
                           OpenAPI generation
                           PostgreSQL (DSQL, Aurora, RDS, any)

The library exposes createPgrest(config). Everything else — how requests arrive, how the result is returned to the client — is a deploy-target concern. See deploy/ for reference integrations.

API surface

A running instance exposes its own OpenAPI 3.0 spec at GET /rest/v1/ and an interactive explorer at GET /rest/v1/_docs. The high-level shape:

| Method | Path | Purpose | |---|---|---| | GET / POST / PATCH / DELETE | /rest/v1/:table | CRUD on any table in the public schema | | POST | /rest/v1/_refresh | Reload schema cache and Cedar policies (requires role=service_role; 401 PGRST301 otherwise) | | POST | /auth/v1/signup | Register a new user | | POST | /auth/v1/token | Password or refresh-token grant | | GET | /auth/v1/user | Current user profile | | POST | /auth/v1/otp · /verify | Magic-link email flow | | GET | /auth/v1/authorize · /callback | OAuth flow | | GET | /auth/v1/jwks | Public JWKS for asymmetric verification |

The query syntax is PostgREST-compatible: filters like status=eq.published, ordering (order=created_at.desc), pagination (limit, offset), upserts (on_conflict), exact counts (Prefer: count=exact), and resource embedding (select=id,customers(name,email)). Browse the live _docs UI for the full, schema-specific reference.

CLI commands

| Command | What it does | |---|---| | pgrest-lambda dev | Boot a local dev stack (Postgres + API + auth + docs). | | pgrest-lambda refresh | Reload schema cache and Cedar policies without restarting. | | pgrest-lambda generate-key <anon\|service_role> | Mint an apikey JWT. | | pgrest-lambda migrate-auth | Apply the better_auth schema against DATABASE_URL. For production bootstraps. | | pgrest-lambda help | Full reference. |

pgrest-lambda dev accepts --port N and --skip-docker. refresh accepts --url (or set PGREST_URL).

Deploy

Each subfolder under deploy/ is one way to run pgrest-lambda in production. The core library stays deploy-agnostic.

• AWS SAM — API Gateway + Lambda + (optional) Cognito. Supports DSQL, Aurora, and standard Postgres. Ships a Lambda authorizer on the pgrest-lambda/aws-sam subpath export:

  import { createAuthorizer } from 'pgrest-lambda/aws-sam';
  export const authorizer = createAuthorizer({
    jwtSecret: process.env.JWT_SECRET,
  }).handler;

More targets are welcome — the pattern is in deploy/aws-sam/README.md.

Documentation

• Configuration reference — every config key and env var, plus secret-management patterns.
• Authorization guide — Cedar policies with recipes and a syntax cheatsheet.
• RPC guide — calling PostgreSQL functions from clients, with a section on how to do the same work on Aurora DSQL (which doesn't support RPC).
• AWS SAM deploy runbook — end-to-end live-on-AWS walkthrough.
• Changelog — what's shipped and what's coming.
• Agent integration guide — notes for AI coding agents working in this repo.

License

MIT. See LICENSE.