Skip to main content

Build a Web App

µQuery answers SQL over HTTP, so a web app only needs a few lines of server code to query data files. This tutorial runs the small app of the examples/web-app folder, which charts two public datasets:

  • NYC taxi trips, from a remote Parquet file
  • CO₂ emissions by country, from a remote CSV file

Prerequisites​

Start the app​

git clone https://github.com/fb64/uquery-rs.git
cd uquery-rs/examples/web-app
podman compose up

With Docker, run docker compose up instead.

Open http://localhost:8000. The taxi chart shows the trips of each hour of the day, across about 3 million rows. Choosing another country in the list redraws the emissions chart.

How it works​

The browser never talks to µQuery. It calls the routes of a small server, and that server holds the SQL:

Anyone can change the code of a web page, so SQL written in a page could be replaced with any other query. µQuery also rejects requests sent by web browsers, so that other websites can't query it through your visitors' browsers.

note

--cors-enabled lets a page call µQuery directly, but it allows every website, and visitors could send their own queries. Keep it for trusted networks.

The services​

compose.yaml
services:
  # Not published: only the web app can reach it.
  uquery:
    image: ${UQUERY_IMAGE:-docker.io/fb64/uquery:0.8}

  web:
    image: docker.io/denoland/deno:2.9.8
    command: run --no-lock --allow-net=0.0.0.0:8000,uquery:8080 --allow-read=public server.js
    working_dir: /app
    volumes:
      - .:/app:ro,z
    ports:
      - 127.0.0.1:8000:8000
    depends_on:
      - uquery

Only the web service has a published port. It runs on Deno, which downloads Hono, the web framework, on first start: there's nothing to install or build. The --allow-net and --allow-read flags limit what the server can reach.

The server​

server.js
import { Hono } from "jsr:@hono/hono@4.13.14";
import { serveStatic } from "jsr:@hono/hono@4.13.14/deno";

const UQUERY_URL = "http://uquery:8080";
const TAXI_FILE = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet";
const CO2_FILE = "https://raw.githubusercontent.com/owid/co2-data/master/owid-co2-data.csv";

// The SQL stays on the server: visitors can only call the routes below.
function query(sql) {
  return fetch(UQUERY_URL, {
    method: "POST",
    headers: { Accept: "application/json" },
    body: sql,
  });
}

const app = new Hono();

app.get("/api/taxi-trips", () =>
  query(`
    SELECT hour(tpep_pickup_datetime) AS hour, count(*) AS trips
    FROM '${TAXI_FILE}'
    GROUP BY hour ORDER BY hour`));

app.get("/api/countries", () =>
  query(`
    SELECT DISTINCT iso_code, country
    FROM '${CO2_FILE}'
    WHERE iso_code IS NOT NULL
    ORDER BY country`));

// The route only matches three capital letters, which makes the code safe to put in SQL.
app.get("/api/emissions/:code{[A-Z]{3}}", (c) =>
  query(`
    SELECT year, co2
    FROM '${CO2_FILE}'
    WHERE iso_code = '${c.req.param("code")}' AND co2 IS NOT NULL
    ORDER BY year`));

app.use("/*", serveStatic({ root: "./public" }));

Deno.serve(app.fetch);

Each route sends one query to µQuery and returns its response as it is. The emissions route takes a value from the visitor, so its pattern only accepts country codes. Always validate a value this way before you put it in SQL.

The page​

The page is a single HTML file. It only knows the routes:

async function get(path) {
  const response = await fetch(path);
  if (!response.ok) throw new Error(`${path}: HTTP ${response.status}`);
  return response.json();
}
async function showEmissions(code) {
  const rows = await get(`/api/emissions/${code}`);
  draw("co2", "line", rows.map((r) => r.year), rows.map((r) => r.co2));
}

The charts are drawn with Chart.js.

Try a route​

curl http://localhost:8000/api/taxi-trips

Expected output, with one object per hour:

[{"hour":0,"trips":79094},{"hour":1,"trips":53627},...]

Stop the app​

podman compose down

With Docker, run docker compose down instead.

Larger results​

JSON suits this app: the SQL aggregates the data on the server, so each result has a few hundred rows at most.

When a page needs many rows in the browser, send Accept: application/vnd.apache.arrow.stream in query() and read the result with Apache Arrow JS. Arrow loads typed columns without parsing text, and compressed sizes stay close to JSON. BIGINT columns such as count(*) arrive as JavaScript BigInt, so convert them with Number() before you chart them.

Going public​

Visitors can only call the routes of the server, so they can't run their own queries. Before you put an app like this online:

  • Serve it over HTTPS, usually from your hosting platform or a reverse proxy.
  • Check every value a route puts in SQL.
  • Keep µQuery unpublished, and set limits that match your data:
FlagLimits
--allowed-directories, --allowed-urlsWhat queries can read
--query-timeout-secs, --max-query-duration-secsHow long a query can run
--memory-limitHow much memory queries can use

Data sources​

  • CO₂ and Greenhouse Gas Emissions by Our World in Data, licensed under CC BY. Data that Our World in Data takes from third parties keeps its own terms.
  • TLC Trip Record Data published by the NYC Taxi and Limousine Commission. The TLC states that it did not create the trip data and makes no representations as to its accuracy.