Stored Procedure

You can use stored procedures in your database as the database API. This is a strange concept for folks that grew up with the database as a place where objects go to rest when not in memory -- mere storage. But to folks that embrace SQL, the database is a valid active player in your architecture.

EmbraceSQL generates code to call all stored procedures in the database, along with React hooks to call stored procedures from the UI, through an EmbraceSQL generated REST server, to the database, and back again. And you don't have to code any of it.

Requirements

Make sure you are read as specified in the Cookbook.

Steps

Write a Stored Procedure

Here is a stored procedure example from the dvdrental database. Pure API, it's even stateless in that it doesn't store data.

CREATE FUNCTION public.last_day(timestamp without time zone) RETURNS date
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$
    SELECT
        CASE WHEN EXTRACT(MONTH FROM $1) = 12 THEN
(((EXTRACT(YEAR FROM $1) + 1)
                    OPERATOR(pg_catalog. ||) '-01-01')::date - INTERVAL '1 day')::date
        ELSE
((EXTRACT(YEAR FROM $1)
                    OPERATOR(pg_catalog. ||) '-' OPERATOR(pg_catalog. ||)(EXTRACT(MONTH FROM $1) + 1)
                    OPERATOR(pg_catalog. ||) '-01')::date - INTERVAL '1 day')::date
        END
$_$;


ALTER FUNCTION public.last_day(timestamp without time zone) OWNER TO postgres;

Generate Code

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

Import the generated hook.

EmbraceSQL includes database friendly utilities including type parsers and react utility hooks to capture UI changes.

src/app/procedure/page.tsx

"use client";

import {
  EmbraceSQLClient,
  EmbraceSQLProvider,
  Public,
  PgCatalog,
} from "../../client/dvdrental-react";
import { useDebouncedValue, useOnChange } from "@embracesql/react";

function Procedure() {
  // event hook
  const { value, onChange } = useOnChange(PgCatalog.Types.Date.parse);
  // debounce, this is a good way to not clobber the database as a user types
  const debouncedValue = useDebouncedValue(value);
  // database talks in 'NULL' not 'undefined'
  const { results } = Public.Procedures.LastDay.useLastDay({
    argument_0: debouncedValue ?? null,
  });
  return (
    <div style={{ display: "flex", flexDirection: "row", width: "32em" }}>
      <input type="date" style={{ flex: 1 }} onChange={onChange}></input>
      <div style={{ flex: 3 }}>
        {results ? `Last Day ${results.toISOString()}` : ``}
      </div>
    </div>
  );
}

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}>
      <Procedure />
    </EmbraceSQLProvider>
  );
}