Database Table by Index

Sometimes you just want just a few rows, and you want it fast.

EmbraceSQL generates a useRows hook for every index on every table.

Requirements

Make sure you are read as specified in the Cookbook.

Steps

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/by_index/page.tsx

"use client";

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

function Index() {
  // you can read along any index -- want to new way to get data
  // from your table auto-generated? -- make an index, it'll be fast
  // and all the code to use it will be generated
  const { rows } = Public.Tables.Actor.useIdxActorLastName(
    // and query
    {
      lastName: "Akroyd",
    },
    // using sort and pagination - on top of reading by index
    {
      // showing the first page
      offsetNumberOfRows: 0,
      limitNumberOfRows: 2,
      sort: [Public.Tables.Actor.SortOptions.firstNameAscending],
    },
  );

  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>
            {Public.Tables.Actor.ColumnNames.map((c) => (
              <th key={c}>{c}</th>
            ))}
          </tr>
        </thead>
        <tbody>
          {rows.map((r) => (
            <tr key={r.rowNumberInResultset}>
              {Public.Tables.Actor.ColumnNames.map((c) => (
                <td key={c}>{`${r[
                  Public.Tables.Actor.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}>
      <Index />
    </EmbraceSQLProvider>
  );
}