Deep Dive into PostgREST - Time Off Manager (Part 1)

The primary motivation behind boringSQL is to explore the robust world of SQL and the PostgreSQL ecosystem, demonstrating how these "boring" tools can cut through the ever-increasing noise and complexity of modern software development. In this series, I'll guide you through building a simple yet fully functional application—a Time Off Manager. The goal of this project is not only to demonstrate practical database/SQL approaches but also to provide a complete, extendable solution that you can immediately build upon. Each part of this series will deliver a self-contained application, setting the stage for introducing more complex functionalities and practices.

The first part of this guide focuses mainly on the application logic and exposing raw data using postgREST, allowing you to grasp the concept.

Requirements

This tutorial assumes you can install PostgreSQL, connect to it using your preferred DB client, have permissions to create a new database, and understand the basics of schema operations and SQL. Similarly, you should be able to follow the installation instructions for postgREST.

The complete source code for the guide is available at GitHub.

The Time Off Manager

When choosing a sample application for this tutorial, I was torn between the popular but simple TodoMVC and a slightly more complex application. In the end, a sample like Time Off Manager provides a much richer database scheme, going beyond the basics and offering a solution closer to real-life systems.

Time Off Manager is also an excellent example that combines simple business logic, workflows, and practicality. The main requirements are:

Maintaining the list of users (employees) with their respective managers
Allowing the maintenance of the time off balance, with an audit history
Introducing an approval workflow and automation

While the application is intended for learning purposes, the database and API can be easily exposed and built upon.

The Database

To get started, you will need to create a database. You can do this in two ways, either using the CREATE DATABASE statement:

CREATE DATABASE time_off_manager;

Users model

The foundation and first sample functionality exposed by postgREST is the users themselves. The table schema behind it represents the employees and manages hierarchical relations, which will become important in the second part when we set up the approval workflow.

CREATE TABLE users (
    user_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    manager_id int REFERENCES users(user_id),
    created_at timestamp with time zone DEFAULT current_timestamp,
    deleted_at timestamp with time zone
);

For testing purposes, let's populate the data using a seed of 3 managers (one being "the boss" and hence not having a manager) and 10 employees.

DO $$
DECLARE
    v_boss_id int;
    v_manager_id int;
    i int;
BEGIN
    -- create "the boss"
    INSERT INTO users (email, manager_id)
    VALUES ('owner@example.com', NULL)
    RETURNING user_id INTO v_boss_id;
 
    -- setup 2 managers 
    FOR i IN 1..2 LOOP
        INSERT INTO users (email, manager_id)
        VALUES ('manager' || i || '@example.com', v_boss_id)
        RETURNING user_id INTO v_manager_id;

        -- and 5 employees for each one of them
        FOR j IN 1..5 LOOP
            INSERT INTO users (email, manager_id)
            VALUES ('employee' || (5 * (i - 1) + j) || '@example.com', v_manager_id);
        END LOOP;
    END LOOP;
END $$;

In case you are not aware, the seed data is produced by an anonymous block. This way, we executed the logic to create the relationship required without the need to create (and later drop) the PL/pgSQL function.

Expose users using postgREST

Having the users table in place, together with sample data, we are ready to expose the data using the REST API. To start, you only need to create a very simple configuration for postgREST using the file postgrest.conf (you can place the file in the folder created for this sample project).

Sample postgrest.conf:

db-uri = "postgres://username:password@localhost/time_off_manager"
db-schema = "public"
db-anon-role = "time_off_anonymous"

The most important part is the db-uri, which follows the standard PostgreSQL connection string format:

postgres://username:password@host/database_name

You need to replace:

username - with your actual database username. Ideally, you will use a separate user (for example, time_off_manager) for each application, rather than your personal account.
password - the password for the user
host - either localhost (if running locally) or a remote server name or IP address
database_name - in this case, time_off_manager we created earlier.

NOTE: We are using hardcoded data (which can potentially get stored in your source code repository), and this is mainly for learning purposes. Any deployment resembling a production environment should follow best practices to reduce security risks.

The other configuration options are db-schema, which we will leave pointing to the public schema for this part, and db-anon-role, configuring the role postgREST should use when executing requests on behalf of unauthenticated clients (effectively all requests in Part 1).

For PostgreSQL 15 and higher, please make sure your username is the owner of the database created (should you create it alternatively) in order to be able to create objects in public schema.

To set up the database role, you need to run the following commands:

CREATE ROLE time_off_anonymous NOLOGIN;
GRANT SELECT, INSERT, UPDATE ON users TO time_off_anonymous;
GRANT time_off_anonymous TO CURRENT_USER;

Please note you this example assumes CURRENT_USER is same as the username used in the db-uri configured above. The latter GRANT statement assigns the role time_off_anonymous to the configured user to execute the anonymous commands.

Once you have the configuration file ready (assuming it's in the current directory), you can start the postgREST server and expose it locally on port 3000 (by default):

$ postgrest postgrest.con
10/May/2024:22:06:12 +0200: Starting PostgREST 12.0.3...
10/May/2024:22:06:12 +0200: Attempting to connect to the database...
10/May/2024:22:06:12 +0200: Connection successful
10/May/2024:22:06:12 +0200: Listening on port 3000
10/May/2024:22:06:12 +0200: Listening for notifications on the pgrst channel
10/May/2024:22:06:12 +0200: Config reloaded
10/May/2024:22:06:12 +0200: Schema cache loaded

Exploring the Users Model Using cURL

When you have the server successfully running, you can try to access the data using:

$ curl "http://localhost:3000/users"

and get the list of all the sample data we created above. The API for reading data is described in detail in the documentation, but you can try different alternatives.

# get one specific user
$ curl http://localhost:3000/users?user_id=eq.10
# query only active (i.e. non-deleted users)
curl "http://localhost:3000/users?deleted_at=is.null"
# or display users without any manager (i.e. boss)
curl "http://localhost:3000/users?manager_id=is.null"

Similarly, you can create, update, and delete users as required—giving you full CRUD functionality with rich filtering options.

Create user:

$ curl -X POST "http://localhost:3000/users" \
       -H "Content-Type: application/json" \
       -d '{"email": "admin1@example.com", "manager_id": 1}'

Update user:

$ curl -X PATCH "http://localhost:3000/users?user_id=eq.10" \
       -H "Content-Type: application/json" \
       -d '{"email": "updateduser@example.com"}'

or, delete one:

$ curl -X DELETE "http://localhost:3000/users?user_id=eq.10"

I haven't mentioned CRUD by accident; that's the primary use-case of postgREST—exposing CRUD operations on the tables within the configured schema (public in this part). You can delegate authorisation at the database level.

Please note, the IDs are hardcoded, and you might need to check the output of the commands to get the correct ones (should you have truncated the table before or otherwise manipulated the data).

Basic models for managing time off

With the User model set up and tested via the API, it's time to return to the core of the Time Off Manager functionality: absence tracking itself. For these purposes, we will define two tables—leave types (identifying the reason or type of the leave) and the time off transactions (or updates) themselves.

CREATE TABLE leave_types (
    leave_type_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    label text NOT NULL,
    description text,
    max_days int,
    created_at timestamp with time zone DEFAULT current_timestamp,
    deleted_at timestamp with time zone
);

CREATE TABLE time_off_transactions (
    transaction_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamp with time zone DEFAULT current_timestamp,
    user_id int NOT NULL REFERENCES users(user_id),
    leave_type_id int NOT NULL REFERENCES leave_types(leave_type_id),
    transaction_date date,
    time_off_period daterange,
    amount int NOT NULL,
    description text
);

CREATE INDEX transaction_for_user ON time_off_transactions(user_id);

The tables should be self-explanatory, but for clarity, let's delve into the detail of the transaction tracking. Time off is tracked for individual users, the time off transaction type to identify the reason for the change, the period during which the absence is recorded, and the amount of effective days (simplified logic to account for weekends, public holidays, and similar).

To get started we also need to seed the data:

INSERT INTO leave_types (label, description, max_days) VALUES 
('vacation', 'Annual vacation leave', 25),
('sick-leave', 'Leave for health reasons', 10),
('unpaid-leave', 'Leave without pay', NULL),
('sabbatical', 'Extended leave for study or travel', NULL);

and create the initial time off balances (this is a simplified version, assuming the employee always has the full balance, ignoring possible mid-year joiners, etc.):

DO $$
DECLARE
    v_leave_type record;
BEGIN
    FOR v_leave_type IN SELECT * FROM leave_types WHERE label = 'vacation' LOOP
        INSERT INTO time_off_transactions (user_id, leave_type_id, transaction_date, amount, description)
        SELECT user_id, v_leave_type.leave_type_id, '2024-01-01', v_leave_type.max_days, 'Initial balance for year 2024'
        FROM users;
    END LOOP;
END $$;

This now gives us the ability to start tracking the leave of absence for users and keep a full audit log of it.

Before we can start using the table via the API, we need to complete the last important step: giving access to the configured db-anon-role for the tables. We can do this by assigning the grant to individual tables using:

GRANT SELECT, INSERT, UPDATE ON leave_types TO time_off_anonymous;
GRANT SELECT, INSERT, UPDATE ON time_off_transactions TO time_off_anonymous;

or modify the default privileges. Spoiler alert—we will move away from using the public schema in Part 2, so let's not do more than we need at the moment :)

Working with absence

Similar to the users example, we can now track absence directly using the API. Before firing off cURL commands, there's one last thing to remember. postgREST requires metadata about the database schema itself, and it might be expensive to perform on the fly, hence to avoid this, the server caches the schema.

To reload the schema (after making schema changes), it is required to reload the cache. This can be done via several basic options:

restarting the server
issuing a (SIGUSR1) signal to the server process

and more advanced ways (outside Part 1 of this tutorial).

Once reloaded, you can start exploring more:

# getting a time off transaction for particular user
$ curl "http://localhost:3000/time_off_transactions?user_id=eq.1"
# submitting new leave of absence
$ curl -X POST http://localhost:3000/time_off_transactions \
       -H "Content-Type: application/json" \
       -d '{"user_id": 5, "leave_type_id": 1, "transaction_date": "2024-02-26", "time_off_period": "[2024-02-28,2024-03-04]", "amount": -4, "description": "Vacation to avoid panic over leap year"}'

All these commands work as expected. Should you forget to reload the schema, you might face an HTTP Status 404 (Not Found) on the newly created objects.

End of Part 1

While not being very sophisticated, I hope this first part has demonstrated the CRUD capabilities postgREST can offer with minimal effort. In the next part, we will move away from accessing the database tables directly and provide more functionality via a new schema api, providing a per-user time off balance view and introducing workflow management.

Requirements#

The Time Off Manager#

The Database#

Users model#

Expose users using postgREST#

Exploring the Users Model Using cURL#

Basic models for managing time off#

Working with absence#

End of Part 1#