Skip to content

Latest commit

 

History

History

flyio-postgresql

README.md

Northwind Traders (by subZero)

Running on Fly.io + PostgreSQL

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.fly.dev 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 Fly.io
  • Data is stored in a PostgreSQL database Hosted on Fly.io
  • 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]
// .....
// return the result 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/flyio-postgresql
  • 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 Fly.io

  1. Create the app on Fly.io without deploying (we don't have a database yet)

    fly launch --no-deploy

  2. Create a new database app (in fly, the database is just another app)

    fly postgres create

  3. Attach the database to our app. Notice the <app-name> (our app) and <db-app-name> (the database app) parameters. Those are the names you chose when you created the apps on step 1 and 2. This step will create a new database in PostgreSQL (that is running in as the <db-app-name>) and the name of the database will be the same as the <app-name>.

    fly postgres attach --app <app-name> <db-app-name>

  4. We can not directly connect to the new database (firewall) so we need to use the prxy command from fly. Open another terminal and run the command.

    fly proxy 5432 -a <db-app-name>

  5. Return to the original terminal and run the command to populate the database. Notice the <app-name> part in the connection string. That is the name you chose when you created the app on step 1. When asked for the password, use the password that was generated on step 2. Takea break, this will take a while.

    psql postgres://postgres@localhost/<app-name> -f northwindtraders-postgres.sql

  6. No that we have our database ready, we can deploy the app

    fly deploy

Credits