Northwind Traders (by subZero)

Running on Cloudflare's Pages + PostgreSQL (Neon)

This is a demo of subZero library capabilities, leveraged in a NextJS app, to automatically expose a PostgREST compatible backend on top of the underlying database.

See the live version at northwind-postgresql.pages.dev and the source code on GitHub.

Features / Advantages

  • Integrates in your codebase as a library (no need to deploy a separate service)
  • Runs in any context (Docker, AWS Lambda, Vercel, Netlify, Fly.io, Cloudflare Pages, Deno, Node, etc)
  • Implemented in Rust with JS/TypeScript bindings through WASM with no dependencies
  • Multiple databases supported:
    • SQLite (including Cloudflare D1)
    • PostgreSQL (including YugabyteDB, CockroachDB, TimescaleDB, etc)
    • ClickHouse
    • MySQL (PlanetScaleDB upcoming)
  • Supports advanced analytical queries (window functions, aggregates, etc)

Example details

  • Frontend is implemented in NextJS

  • Everything is deployed to Cloudflare Pages

  • Data is stored in a PostgreSQL database hosted on Neon

  • The backend runs in a single serverless function (see Cloudflare Functions for details). Most of the code deals with the configuration of the backend, and 99% of the functionality is within these lines:

    // generate the SQL query from request object
const { query, parameters } = await subzero.fmtStatement(publicSchema, `${urlPrefix}/`, role, req, queryEnv)
// .....
// prepare the statement
result = (await db.query(query, parameters)).rows[0]

const body = result.body // this is a json string
return new Response(body, { ... })

Running locally

  • Clone the repo 
    git clone https://github.com/subzerocloud/showcase.git
  • cd to the example directory 
    cd showcase/cloudflare-postgresql-neon
  • Install dependencies (you will need to have SQLite installed on your machine) 
    yarn install
  • Copy .dev.vars file and set the DATABASE_URL environment variable to your neon database connection string 
    cp .dev.vars.example .dev.vars
  • Run in dev mode Note: this currently is not working due to missing wasm support in wrangler pages (though it works fine with wrangler dev) 
    yarn dev
  • Open the app in your browser 
    open http://localhost:3000

Deploying to Cloudflare Pages

  • Setup a PostgreSQL database on Neon and get a connection string

  • Provision the database

    psql <db_connection_string> -f northwindtraders-postgresql.sql

  • Create a new Cloudflare Pages project

    npx wrangler pages project create <project-name>

  • Set the DATABASE_URL environment variable in cloudflare dashboard and click encrypt button (see docs)

  • Deploy to Cloudflare Pages

    yarn deploy

Credits