SQL Script Report

How often do you need to write a report? Like -- all the time. Query the database and get it on the page.

This example even shows a 'trick' for using PostgreSQL arrays to get a report in one trip to the database.

EmbraceSQL lets you ... write SQL ... in a plain SQL file, which lets your editor help you out.

Requirements

Make sure you are read as specified in the Cookbook.

Steps

Write an SQL Script

Here is our SQL, we're going to make a report of all the movies in the sample database, along with their actors. Using an ARRAY, which you might have avoided in the past becuase it is a pain to deal with using off the shelf PostgreSQL drivers.

./sql/movie_listing.sql

SELECT
  film_id,
  title,
  release_year,
  rating,
  -- nested array so we don't need multiple trips to the database
  -- this is a nice way to embrace SQL!
  -- this is also a query that won't parse well with off the shelf
  -- postgres client drivers, but we'll generate strong types automatically
  ARRAY(
	  SELECT
	    first_name || ' ' || last_name
	  FROM
	    public.actor JOIN public.film_actor fa USING (actor_id)
	  WHERE
        fa.film_id = f.film_id
  ) AS actors

FROM 
  public.film f

Generate Code

Now that there is a SQL Script - regenerate from the database. If you are already running NextJS, it'll hot reload.

#!/usr/bin/env bash

mkdir -p src/server
npx embracesqlcli generate node --database postgres://postgres:postgres@localhost/dvdrental --sqlScriptsFrom ./sql > ./src/server/dvdrental.ts

mkdir -p src/client
npx embracesqlcli generate react --database postgres://postgres:postgres@localhost/dvdrental --sqlScriptsFrom ./sql > ./src/client/dvdrental-react.ts

Code React

Hook generated, you just need to import the generated code away you go!

No mapping code written by you. ⏰ saved.

src/app/report/page.tsx

"use client";

import {
  EmbraceSQLClient,
  EmbraceSQLProvider,
  Scripts,
} from "../../client/dvdrental-react";

function Report() {
  // this is hooking the SQL Script
  const { rows } = Scripts.MovieListing.useMovieListing();

  if (rows) {
    // fully typed data from the report -- autocomplete away
    return (
      <div
        style={{ display: "flex", flexDirection: "column", maxWidth: "40em" }}
      >
        {rows.map((row) => (
          <div
            style={{
              display: "flex",
              flexDirection: "column",
              marginBottom: "1em",
            }}
          >
            <div style={{ display: "flex", flexDirection: "row" }}>
              <div style={{ flex: 3, textAlign: "start" }}>
                {row.title}{" "}
                <span style={{ fontSize: "0.5em", fontStyle: "italic" }}>
                  {row.rating}
                </span>
              </div>
              <div style={{ flex: 1, textAlign: "end" }}>{row.releaseYear}</div>
            </div>
            <div
              style={{
                display: "flex",
                flexDirection: "row",
                flexWrap: "wrap",
              }}
            >
              {row.actors.map((actor) => (
                <div style={{ marginRight: "1em", fontSize: "0.75em" }}>
                  {actor}
                </div>
              ))}
            </div>
          </div>
        ))}
      </div>
    );
  } else {
    return null;
  }
}

export default function Page() {
  // connect to where we mounted EmbraceSQL in our server
  const client = new EmbraceSQLClient({
    url: `/embracesql`,
  });
  // set up the EmbraceSQL context
  return (
    <EmbraceSQLProvider client={client}>
      <Report />
    </EmbraceSQLProvider>
  );
}