flyio-sqlite-litefs

Northwind Traders (by subZero)

Running on Fly.io + SQLite (LiteFS)

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-sqlite.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 as a single app
• Data is stored in a SQLite database that is replicated to all nodes using LiteFS. See aditional documentation about scaling and configuration of LiteFS
• 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
  const result = await db.get(query, parameters)
  // .....
  // return the result to the client
  res.send(result.body)

• There is some code specific to a LiteFS setup that periodically checks is the current node is the primary node and if not, it will redirect the request to the primary node.

Running locally

• Clone the repo

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

• cd to the example directory

  cd showcase/flyio-sqlite-litefs

• Install dependencies (you will need to have SQLite installed on your machine)

  yarn install

• Populate the database

  yarn seed

• Run in dev mode

  yarn dev

• Open the app in your browser

  open http://localhost:3000

Deploying to Fly.io

• Create the fly.toml file

  cp fly.toml.example fly.toml

• Launch the app

  fly launch

  Note: select Yes for Would you like to copy its configuration to the new app?

Credits

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