vercel-postgresql-neon

Northwind Traders (by subZero)

Running on Vercel + 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.vercel.app/ and 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 Vercel
• Data is stored in a PostgreSQL database hosted on Neon
• The backend runs in a single serverless function. 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)
  // .....
  // execute the query
  result = (await db.query(query, parameters)).rows[0]
  // .....
  // send the response back to the client
  res.send(result.body)

Running locally

• Clone the repo

  git clone https://github.com/subzerocloud/showcase.git

• cd to the example directory

  cd showcase/vercel-postgresql-neon

• Install dependencies

  yarn install

• Copy .env.local file

  cp .env.local.example .env.local

• Run in dev mode

  yarn dev

• Open the app in your browser

  open http://localhost:3000

Deploying to Vercel

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

  psql <db_connection_string> -f northwindtraders-postgresql.sql

• Link the current directory to a Vercel project

  vercel link

• Set the DATABASE_URL environment variable

    vercel env add DATABASE_URL <db_connection_string>

• Deploy the project

  vercel --prod

Credits

• This dataset was sourced from northwind-SQLite3
• Inspired by Cloudflare D1 Demo