Note Taking App

## Introduction 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 -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 -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@/ 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, '&', '&'), '"', '"'),'>', '>'),'<', '<'), '''', ''') $$ 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$

%2$s

%3$s

$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( '

' || string_agg(api.html_note(n), '' ORDER BY n.id) || '

', '

No notes.

' ) 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$ Note Taking App

Notes

$html$ || api.html_all_notes() || $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 -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$ Note Taking App

Create a New Note

$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 `` section includes a script to load HTMX via a CDN. Added to the opening `` 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 -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//.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: 1. Select **GitHub** as the deployment method. 2. Select your code's 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`. 3. In the **Builder** section, select **Dockerfile**. 4. In the **Environment variables** section, click **Add variable**. 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 form that appears, provide the secret name and its corresponding value, then click the **Create** button. 5. Choose a name for the App and Service, and click **Deploy**. 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://htmx.org/docs/) to read more about how to create robust APIs using PostgREST.

HTMX

Use PostgREST and HTMX to Build RESTful APIs from PostgreSQL Databases