SQL Script

You want to pull in some data and create a view using an SQL query. Write an SQL script and generate away!

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

Now use a table hook. Note the options to paginate and sort.

src/app/script/page.tsx

"use client";

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

function Script() {
  // you can read from an SQL script that is just a SQL file in your source tree
  const { rows } = Scripts.MovieListing.useMovieListing();

  if (rows) {
    // old school - let's make a table - EmbraceSQL generates column
    // name metadata based on the table schemas which makes it easy to iterate
    return (
      <table>
        <thead>
          <tr>
            {Scripts.MovieListing.ColumnNames.map((c) => (
              <th key={c}>{c}</th>
            ))}
          </tr>
        </thead>
        <tbody>
          {rows.map((r) => (
            <tr key={r.rowNumberInResultset}>
              {Scripts.MovieListing.ColumnNames.map((c) => (
                <td key={c}>{`${r[
                  Scripts.MovieListing.Columns[c].fieldName
                ]?.toLocaleString()}`}</td>
              ))}
            </tr>
          ))}
        </tbody>
      </table>
    );
  } 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}>
      <Script />
    </EmbraceSQLProvider>
  );
}