Cookbook 🧑‍🍳

What's an API guide without a cookbook? In here you will find recipes for common database use cases.

The examples are coded up as stand alone NextJS pages. Client only examples will convert well to ViteJS.

Requirements

You are going to need:

• NodeJs >= 18
• PostgreSQL >= 14

These examples assume you are running locally with your shell able to connect to your PostgreSQL with:

psql

Create a new database dvdrental on your local PostgeSQL server.

curl "https://embracesql.github.io/dvdrental.sql" | psql

NextJS

You are going to need:

• NextJS >= 14
• An AppRouter application

Steps

This will set up your NextJS to be able to use EmbraceSQL.

Add EmbraceSQL

Package ahoy! 🚢

npm install @embracesql/shared
npm install @embracesql/react

Generate Code

Generate code, note that we have a command line switch to look for SQL scripts in a designated folder.

#!/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

Create a Server

Here is a route handler that will connect the server-side part of your NextJS application directly to your PostgreSQL, and expose it to the client-side part.

./src/app/embracesql/route.ts

import { OperationDispatcher, Database } from "../../server/dvdrental";
import { EmbraceSQLRequest, EmbraceSQLResponse } from "@embracesql/shared";

/**
 * Connect next app route to EmbraceSQL with a plain
 * PostgreSQL connection url.
 */
function embraceSQL(postgresUrl: string) {
  let database: Database;
  return async (req: Request) => {
    // do we already have a connection
    if (!database) {
      database = await Database.connect(postgresUrl);
    }
    // dispatcher finds the right method for a request
    const dispatcher = new OperationDispatcher(database);
    try {
      // do we have a valid request?
      const request: EmbraceSQLRequest<object, object> = await req.json();
      if (!request.operation && !(request.parameters || request.values)) {
        throw new Error("Invalid Request");
      }
      // now we are 🥘
      const results = await dispatcher.dispatch(request);
      const response: EmbraceSQLResponse<unknown, object, object> = {
        ...request,
        results,
      };
      return Response.json(response);
    } catch (e) {
      return new Response((e as Error)?.message, { status: 400 });
    }
  };
}

/**
 * NextJS POST route connection.
 */
export const POST = embraceSQL(
  "postgres://postgres:postgres@localhost/dvdrental",
);