Use PostgREST and HTMX to Build RESTful APIs from PostgreSQL Databases
Tags: postgres, api, webdev, tutorial
Published: 2024-02-05 12:55:55

Developing software products today requires a rapid development cycle, from conceptualization to market launch. Many software products rely on [RESTful](https://en.wikipedia.org/wiki/REST) APIs to communicate with a database. Therefore, it is vital to be able to create robust and compliant RESTful APIs with minimal boilerplate code. This expedites development and allows developers to focus on business logic instead of getting caught up in the complexities of API implementation details.

[PostgREST](https://postgrest.org/) is a standalone web server that turns your PostgreSQL database into a RESTful API using the database's structural constraints and permissions to define the API's endpoints and operations. In this tutorial, you will create a simple note-taking app by leveraging PostgREST to construct a RESTful API for the app and using [htmx](https://htmx.org/) to deliver HTML content.

As you read this guide, you can follow along with the [tutorial repository](https://github.com/koyeb/example-postgrest-htmx) to view the referenced files.

## Requirements

To successfully follow along with this tutorial, ensure you have the following prerequisites:

- [Docker](https://www.docker.com/) installed on your development machine.
- Git installed on your development machine.
- A PostgreSQL client installed on your development machine.
- A [Koyeb](https://app.koyeb.com/) account to deploy the application.

## Steps

We will set up a RESTful API with PostgREST and HTMX with the following steps:

1. [Configure the database](#Configure-the-database)
2. [Set up PostgREST](#Set-up-PostgREST)
3. [Configure PostgREST to display notes](#Configure-PostgREST-to-display-notes)
4. [Allow users to add new notes](#Allow-users-to-add-new-notes)
5. [Deploy to Koyeb](#Deploy-to-Koyeb)

## Configure the database

PostgREST creates RESTful APIs by leveraging the database schema, utilizing database tables, stored procedures, functions, and views to identify and define the available resources along with their properties. Every table within the database transforms into a resource, and endpoints are created to facilitate [CRUD operations](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete) for each resource. PostgREST dynamically formulates SQL queries in response to HTTP requests received by the server, delivering the query results as JSON responses to the client.

In this section, you'll create and configure a PostgreSQL database to integrate seamlessly with PostgREST.

### Create a PostgreSQL database on Koyeb

To create a PostgreSQL database, first log in to the [Koyeb control panel](https://app.koyeb.com/). Navigate to the Databases tab and select the **Create Database Service** option. You can either input a custom name for your database or use the default generated name. Choose the desired region and specify a default role (or leave it as-is). Finally, click **Create Database Service** to create your PostgreSQL database service.

After creating the PostgreSQL database service, a list of your database services will be presented. Click on your recently generated service from the list, copy the `psql` database connection string, and store it safely for future use.

### Create a database schema and table

In this section, you will create a schema and a database table in your database for the note-taking app.

To begin, create a root directory for the app by running the command below in your terminal window:

```bash
mkdir postgrest_htmx_note
```

The command above creates a directory named `postgrest_htmx_note`. Next, initialise a Git repository in the `postgrest_htmx_note` directory by running the command below:

```bash
cd postgrest_htmx_note
git init
```

The commands above change the current directory of your terminal to the `postgrest_htmx_note` directory and initialize a Git repository within that specific directory.

Next, create a `01_db.sql` file in the root directory and add the query below to the file:

```sql
-- 01_db.sql
CREATE SCHEMA api;
```

The SQL query above creates a [schema](https://www.postgresql.org/docs/current/ddl-schemas.html) named `api` in the database. PostgREST will be granted access to this schema to create RESTful APIs for the database tables in it.

Next, append the query below to the `01_db.sql` file:

```sql
-- 01_db.sql
. . .

CREATE TABLE api.notes (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL CHECK (title <> ''),
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

This query creates a `notes` database table in the `api` schema consisting of the following columns:

- An `id` column for storing unique identifiers that auto-increment for each row of data.
- A `title` column that is not nullable and does not accept empty strings.
- A `content` column with a `text` data type for storing the note's content.
- A `created_at` column for holding date-time information on when a note was created.

PostgREST will create a `/notes` API endpoint for this table with the ability to perform CRUD operations on all columns in the database.

Finally, add the query below to the `01_db.sql` file:

```sql
-- 01_db.sql
. . .

INSERT INTO api.notes (title, content) VALUES ('PostgREST', 'Notes from learning PostgREST & HTMX');
```

The query above adds a sample note to the `notes` database table.

To run the queries in the `01_db.sql` file, connect your PostgreSQL client to the database and execute the file using the client. For demonstration, we will show you how to do this with the `psql` client, but any PostgreSQL client should work:

```bash
psql <YOUR DATABASE_CONNECTION_STRING> -f 01_db.sql
```

Successfully running the queries in the `01_db.sql` file will return the output below:

```
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
```

### Set up a user role

PostgREST ensures security & authorisation by limiting database operations to authorized users via PostgreSQL roles & permissions. In this section, you'll create a database user role with unrestricted access to the `api` schema. PostgREST will connect to the database using this user.

Create a `02_role.sql` file in your project's root and add the following query to it to create a user role in your database:

```sql
-- 02_role.sql
CREATE ROLE auth_user NOINHERIT LOGIN PASSWORD 'auth_user_password';
GRANT USAGE ON SCHEMA api TO auth_user;
GRANT ALL ON api.notes TO auth_user;
GRANT USAGE, SELECT ON SEQUENCE api.notes_id_seq TO auth_user;
```

- The `create role` query creates a role named `auth_user` with a set password. This role is granted login privileges but does not inherit any additional privileges.
- The `grant usage` query allows the `auth_user` role to read objects in the `api` schema.
- The `grant all` query authorizes the `auth_user` role to perform all operations on the `notes` database.
- The `grant usage, select` query gives the `auth_user` role permission to read and retrieve values from the `notes_id_seq` sequence in the `api` schema, allowing it to access unique identifiers in the `api` schema.

To execute the `02_role.sql` file, run the file in your PostgreSQL client:

```bash
psql <YOUR DATABASE_CONNECTION_STRING> -f 02_role.sql
```

Successfully executing the file should not return an error message.

This final step completes all the necessary database setup to prepare it for integration with PostgREST. In the next section, you will set up PostgREST and connect it to the database so that it can automatically create a RESTful API endpoint for the note-taking app.

## Set up PostgREST

PostgREST provides several installation options, including tailored packages for various operating systems, a pre-built binary, and a Docker image. The fastest way to install and run PostgREST for the note-taking application is by using the Docker image option.

To begin, create a `Dockerfile` in the root directory add the code below to it:

```dockerfile
# Dockerfile
FROM postgrest/postgrest:latest

# Create and set the working directory
WORKDIR /app
ARG PORT

# Set environment variables for PostgREST configuration
ENV PGRST_DB_URI=${PGRST_DB_URI}
ENV PGRST_DB_SCHEMA=${PGRST_DB_SCHEMA}
ENV PGRST_DB_ANON_ROLE=${PGRST_DB_ANON_ROLE}
ENV PGRST_SERVER_PORT=${PORT:-8000}

# Expose the port on which PostgREST will run
EXPOSE ${PORT:-8000}

# Command to run PostgREST when the container starts
CMD ["postgrest"]
```

The code above sets up a Docker container environment to run PostgREST. It starts by selecting the most recent PostgREST image available as the base image. After that, the working directory within the container is set to `/app`, where all subsequent commands are executed.

Afterwards, the code sets up four environment variables within the Docker container, obtaining the values for `PGRST_DB_URI`, `PGRST_DB_SCHEMA`, and `PGRST_DB_ANON_ROLE` from corresponding external environment variables. In addition, the code makes the port specified by the `PORT` environment variable available for PostgREST to use (with port 8000 as a fallback value). Lastly, the code specifies the command that should run upon container startup, which is the `postgrest` command.

Next, create an `.env` file in the project's root directory and add the following code to it:

```ini
# .env
PGRST_DB_URI=postgres://auth_user:auth_user_password@<YOUR DATABASE HOST NAME>/<YOUR DATABASE NAME>
PGRST_DB_SCHEMA=api
PGRST_DB_ANON_ROLE=auth_user
```

**Note:** the value of `PGRST_DB_URI` is _not_ the exact connection string you copied from the Koyeb control panel. The new connection string uses the role and role password that we created with the `02_role.sql` file.

The `.env` file's code sets values for environment variables used to configure corresponding variables inside the Docker container. The variables include:

- `PGRST_DB_URI`: This stores the database connection information for PostgREST to establish a connection with the database. The `auth_user` and its associated password replace the username and password sections in your database URL, resulting in this final value.
- `PGRST_DB_SCHEMA`: This specifies the database schema containing the database tables PostgREST should access.
- `PGRST_DB_ANON_ROLE`: This value specifies the database role PostgREST should use for unauthenticated requests.

To ensure the contents of the `.env` file are not committed to Git history, run the command below:

```bash
printf "%s\n" ".env" > .gitignore
```

The command above creates a `.gitignore` file and includes the `.env` file into it, ensuring it is excluded from the Git history.

That's all of the code required to set up PostgREST. To create a Docker image from the instructions in the `Dockerfile`, ensure Docker is running on your machine and run the command below in your Terminal window while in your project's root directory:

```bash
docker build -t pg_notes .
```

Optionally, if you'd like to change the port that PostgREST will run on, pass in `PORT` as a build argument like this:

```bash
docker build --build-arg="PORT=5555" -t pg_notes .
```

The commands above create a Docker image named `pg_notes` using the instructions from the `Dockerfile`. The period (`.`) at the end of the command specifies that the Dockerfile is located in the current directory.

To create and run a Docker container from the `pg_notes` image, run the command below in your terminal window:

```bash
docker run -p 8000:8000 --env-file .env pg_notes
```

Remember to switch the port specification if you modified the port configuration during the build:

```bash
docker run -p 5555:5555 --env-file .env pg_notes
```

The commands above create and run a container built from the `pg_notes`. The `-p` flag maps the port on the host machine to port in the Docker container and the `--env-file` option instructs Docker to load environment variables from the `.env` file during container instantiation.

With the Docker container now active, PostgREST has established a successful connection to the database and generated an API for the note-taking application. To verify the API, visit `http://localhost:8000/notes` in your browser. You should be able to view a JSON object displaying the sample note you inserted into the `notes` database.

In the upcoming section, you will implement the logic to display the notes in your database on a webpage.

## Configure PostgREST to display notes

Besides returning JSON responses for database data, PostgREST can also serve HTML documents for requests that include the `Accept: text/html` header. PostgREST can serve HTML files created by database functions through requests to routes that match the function name.

To create a page to display the notes in your database, start by creating a `03_index.sql` file in your project's root directory and add the following query to it:

```sql
-- 03_index.sql

-- Add media type handler for `text/html` requests
CREATE DOMAIN "text/html" AS TEXT;
```

The query above adds a `text/html` media type handler, enabling PostgREST to recognize browser requests with an `Accept: text/html` header and deliver HTML document files in response.

Next, add the query below to the `03_index.sql` file to create a function that sanitizes HTML content in the note title and content to mitigate injection risks:

```sql
-- 03_index.sql
. . .

-- Sanitize text to replace characters with HTML entities
CREATE OR REPLACE FUNCTION api.sanitize_html(text) RETURNS text AS $$
  SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE($1, '&', '&amp;'), '"', '&quot;'),'>', '&gt;'),'<', '&lt;'), '''', '&apos;')
$$ language sql;
```

The query above creates a SQL function named `sanitize_html` which takes a text, replaces character entities in it with HTML entities and returns the sanitized text.

Next, append the query below to the `03_index.sql` file to add a function that formats all notes in the database as HTML cards:

```sql
-- 03_index.sql
. . .

-- Format all notes as HTML cards
CREATE OR REPLACE FUNCTION api.html_note(api.notes) RETURNS text AS $$
  SELECT FORMAT($html$
    <div class="card">
      <div class="card-body">
        <h5 class="card-title">%2$s</h5>
        <p class="card-text text-truncate">%3$s</p>
      </div>
    </div>
  $html$,
  $1.id,
  api.sanitize_html($1.title),
  api.sanitize_html($1.content)
  );
$$ language sql stable;
```

The provided SQL query creates an `html_note` function within the `api` schema. This function takes the `api.notes` table as a parameter and produces formatted HTML markup for the notes.

Utilizing the `format` function in PostgreSQL, an HTML template is enclosed within the dollar-quoted strings `$html$`. The `%2$s` and `%3$s` placeholders within the template denote the second and third arguments supplied to the `format` function. These arguments consist of the note's ID (`$1.id`), the sanitized note title (`$1.title`), which undergoes sanitization using the previously established `api.sanitize_html` function, and the sanitized note content (`$1.content`), also sanitized with the `api.sanitize_html` function.

To create the HTML markup to display all notes, add the query below to the `03_index.sql` file:

```sql
-- 03_index.sql
. . .

-- Create HTML to display all notes
CREATE OR REPLACE FUNCTION api.html_all_notes() RETURNS text AS $$
  SELECT COALESCE(
    '<div class="card-columns">'
      || string_agg(api.html_note(n), '' ORDER BY n.id) ||
    '</div>',
    '<p class="">No notes.</p>'
  )
  FROM api.notes n;
$$ language sql;
```

The query provided above creates a function called `html_all_notes` in the `api` schema, which returns text. The `SELECT` statement within the function uses the `COALESCE` function to generate HTML markup based on whether notes are present in the database or not.

If notes are present, the `string_agg` function combines the HTML representation of notes returned by the `html_note` function. These notes are ordered by their `id` values and enclosed within a `div` element with a `card-columns` class. If there are no notes, a paragraph element with the text `No notes.` is returned.

With the HTML markup for all notes now obtainable through a function, add the following query to the `03_index.sql` file to generate a page for presenting the notes:

```sql
-- 03_index.sql
. . .

-- Generate page to display notes
CREATE OR REPLACE FUNCTION api.index() RETURNS "text/html" AS $$
  SELECT $html$
    <!DOCTYPE html>
    <html lang="en">
    <head>
      <meta charset="UTF-8">
      <meta name="viewport" content="width=device-width, initial-scale=1.0">
      <title>Note Taking App</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    </head>

    <body>
      <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <a class="navbar-brand" href="/rpc/index">Note App</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav"
          aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
          <ul class="navbar-nav">
            <li class="nav-item active">
              <a class="nav-link" href="/rpc/index">Notes</a>
            </li>
            <li class="nav-item">
              <a class="nav-link" href="/rpc/new">Create Note</a>
            </li>
          </ul>
        </div>
      </nav>

      <div class="container mt-4">
        <h2>Notes</h2>
        $html$
          || api.html_all_notes() ||
        $html$
      </div>

      <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
      <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.2/dist/umd/popper.min.js"></script>
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    </body>
    </html>
  $html$
$$ language sql;
```

The query above defines an `index` function that returns a `text/html` MIME type. The markup returned is a basic HTML page with style sheet and script tags for Bootstrap.

The page body contains a Bootstrap `navbar` and a `div` element with the `container mt-4` class. Within this container, the `html_all_notes()` function is invoked to display all existing notes.

To execute the `index.sql` file, run the file in your PostgreSQL client. With `psql`, this would look something like this:

```bash
psql <YOUR DATABASE_CONNECTION_STRING> -f 03_index.sql
```

Successfully executing the file should return the output:

```
CREATE DOMAIN
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
```

The HTML page generated by the `index` function is accessible at the `/rpc/index` route. To view the page, restart your running Docker container, and navigate to `http://localhost:8000/rpc/index` in your browser. You should see a page showcasing all available notes from your database.

In this section, you've successfully served a webpage that directly fetches and displays a list of notes from the database using PostgREST. Moving forward, you'll enhance the functionality by incorporating the ability to add new notes.

## Allow users to add new notes

Adding new notes to the existing database entries involves creating a page for users to enter and submit notes and creating an endpoint (database function) to receive values for new notes and save them to the database.

Begin by creating a `04_new.sql` file in your project's root directory and add the query below to create an endpoint for adding new notes:

```sql
-- 04_new.sql

-- Create an endpoint for adding new notes
CREATE OR REPLACE FUNCTION api.add_note(_title text, _content text) RETURNS "text/html" AS $$
  BEGIN
    INSERT INTO api.notes(title, content) VALUES (_title, _content);
    RETURN 'Note added successfully.' AS result;
  EXCEPTION
    WHEN others THEN
      -- An error occurred during the insert operation
      RAISE NOTICE 'An error occurred: %', SQLERRM;
      RETURN 'An error occurred.' AS result;
  END;
$$ LANGUAGE plpgsql;
```

The query above adds an `add_note` function to the `api` schema. This function accepts `_title` and `_content` parameters, inserts the values into the `notes` database and returns a message indicating success or failure based on the outcome of the insert operation.

Next, the query below to the `new.sql` file to create a page featuring a form for submitting new notes to the `add_notes` endpoint using HTMX:

```sql
-- 04_new.sql
. . .

-- Create page for submitting new notes
CREATE OR REPLACE FUNCTION api.new() RETURNS "text/html" AS $$
  SELECT $html$
    <!DOCTYPE html>
    <html lang="en">

    <head>
      <meta charset="UTF-8">
      <meta name="viewport" content="width=device-width, initial-scale=1.0">
      <title>Note Taking App</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
      <!-- htmx for AJAX requests -->
      <script src="https://unpkg.com/htmx.org"></script>
    </head>

    <body hx-headers='{"Accept": "text/html"}'>
      <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <a class="navbar-brand" href="/rpc/index">Note App</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav"
          aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
          <ul class="navbar-nav">
            <li class="nav-item">
              <a class="nav-link" href="/rpc/index">Notes</a>
            </li>
            <li class="nav-item active">
              <a class="nav-link" href="/rpc/new">Create Note</a>
            </li>
          </ul>
        </div>
      </nav>

      <div class="container mt-4">
        <h2>Create a New Note</h2>
        <form hx-post="/rpc/add_note" hx-trigger="submit" hx-on="htmx:afterRequest: this.reset()" hx-target="#response-area">
          <p class="text-success" id="response-area"></p>
          <div class="form-group">
            <label for="note-title">Title:</label>
            <input type="text" class="form-control" id="note-title" name="_title" placeholder="Enter note title" required>
          </div>
          <div class="form-group">
            <label for="note-content">Content:</label>
            <textarea class="form-control" id="note-content" name="_content" rows="4" placeholder="Enter note content"
              required></textarea>
          </div>
          <button type="submit" class="btn btn-primary">Save Note</button>
        </form>
      </div>
    </body>
    </html>
  $html$;
$$ language sql;
```

The provided query creates a `new` function in the `api` schema, returning content with a MIME type of `text/html`. Similar to the `api.index` function, this function generates a standard HTML page, and in addition to the Bootstrap style sheet and script tags, the `<head>` section includes a script to load HTMX via a CDN.

Added to the opening `<body>` tag is the `hx-headers='{"Accept": "text/html"}'` HTMX attribute. This inclusion ensures that HTMX elements include this header in every request, ensuring PostgREST handles the request appropriately.

The note creation form includes two input fields named `_title` and `_content`, aligning with the parameters expected by the `add_note` endpoint. Additionally, the form incorporates HTMX attributes that enable AJAX requests directly from HTML. These attributes are:

- `hx-post`: This attribute directs the form to initiate a `POST` request to a specified URL, in this case, `/rpc/add_note`.
- `hx-trigger`: This attribute defines the browser event that triggers the form action. The value `submit` indicates that the action is triggered upon form submission.
- `hx-on`: This attribute enables the embedding of inline scripts. The value `htmx:afterRequest: this.reset()` resets the form after executing the submission request.
- `hx-target`: This attribute directs HTMX to insert any server response into an element with the id `response-area`.

Upon form submission, HTMX initiates a POST request to the `add_note` endpoint, submitting the values from the `_title` and `_content` fields. The `add_note` endpoint then stores these submitted values in the database.

To execute the code within the `04_new.sql` file, use your PostgreSQL client to run the file. If you're using the `psql` client, you would want to execute the following:

```bash
psql <YOUR DATABASE_CONNECTION_STRING> -f new.sql
```

To test this functionality, restart your Docker container and go to `http://localhost:8000/rpc/new` in your browser; a form should be visible on the page. Complete and submit the form and you should see the message "Note added successfully" displayed. Navigate back to the `/rpc/index` page to view your newly added note listed on the page.

You've successfully developed a functional note-taking application integrated directly with your PostgreSQL database. In the upcoming section, you'll deploy the application online on Koyeb.

## Deploy to Koyeb

Now that the code writing is finished, the final step involves deploying the app online on Koyeb.

Begin by creating a GitHub repository for your code, then execute the following command in your terminal window to push your local code to the repository:

```bash
git add --all
git commit -m "Note-taking app with PostgREST and HTMX."
git remote add origin git@github.com/<YOUR_GITHUB_USERNAME>/<YOUR_REPOSITORY_NAME>.git
git branch -M main
git push -u origin main
```

To deploy the code on GitHub, navigate to the [Koyeb control panel](https://app.koyeb.com/). On the **Overview tab**, initiate the deployment process by clicking the **Create Web Service** button. On the App deployment page:

- Select the GitHub deployment option.
- Select your code's GitHub repository from the drop-down menu. Alternatively, you can enter our public [PostgREST and HTMX example repository](https://github.com/koyeb/example-postgrest-htmx) into the **Public GitHub repository** at the bottom of the page: `https://github.com/koyeb/example-postgrest-htmx`.
- Select the branch you intend to deploy (e.g., `main`).
- Select the `Dockerfile` builder option.
- Click on the **Advanced** button and choose **Add Variable** to add extra environment variables.
- For each environment variable specified in your `.env` file, enter the variable name, choose the **Secret** type, and select the **Create secret** option in the value field. In the modal that appears, provide the secret name and its corresponding value, then click the **Create** button.
- Enter a name for the application or use the one already provided.
- Lastly, initiate the deployment process by clicking the **Deploy** button.

Throughout the deployment process, you can monitor the progress via the logs. Once deployment concludes and the health checks pass successfully, your application will be live.

To access your live application, add `/rpc/index` to your app's public URL and open the resulting URL in your web browser.

## Conclusion

In this tutorial, we built a basic note-taking app directly served from a PostgreSQL database using PostgREST. The service builds an API and web page directly from database queries using a combination of PostgreSQL functions and HTMX. Once the application was ready, we deployed it to Koyeb to make it accessible globally.

While this guide demonstrated the basic way you can build RESTful services from a PostgreSQL database, PostgREST provides extensive capabilities beyond what's covered here. Explore the [PostgREST documentation](https://postgrest.org/en/stable/) to read more about how to create robust APIs using PostgREST.