alisdairbr for Koyeb

Posted on Feb 5 • Edited on Feb 9 • Originally published at koyeb.com

Use PostgREST and HTMX to Build RESTful APIs from PostgreSQL Databases

#postgres #api #webdev #tutorial

Developing software products today requires a rapid development cycle, from conceptualization to market launch. Many software products rely on RESTful 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 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 to deliver HTML content.

As you read this guide, you can follow along with the tutorial repository to view the referenced files.

Requirements

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

Docker installed on your development machine.
Git installed on your development machine.
A PostgreSQL client installed on your development machine.
A Koyeb account to deploy the application.

Steps

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

Configure the database
Set up PostgREST
Configure PostgREST to display notes
Allow users to add new notes
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 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. 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:

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:

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:

-- 01_db.sql
CREATE SCHEMA api;

The SQL query above creates a schema 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:

-- 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:

-- 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:

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:

-- 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:

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
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:

# .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:

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:

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:

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:

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:

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:

-- 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:

-- 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:

-- 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:

-- 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:

-- 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:

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:

-- 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:

-- 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:

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:

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. 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 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 to read more about how to create robust APIs using PostgREST.