Let's recap the first part of "Deep Dive into PostgREST," where we explored the basic functionality to expose and query any table using an API, demonstrated using cURL. All it took was to set up a db-schema and give the db-anon-role some permissions. But unless you are creating the simplest of CRUD applications, this only scratches the surface.

In Part 2, we will expand APIs, provide better abstraction, and implement the foundation of what can be considered business logic, all while extending the sample "Time Off Manager" application. While the previous instalment being introductiory only, make sure you don't miss the important details in this one.

Before we move on, let's do a bit of housekeeping and clean up the permissions setup from the first part. This way, we can start with a clean slate (when it comes to the permissions) and avoid any lingering rights that could confuse us later.

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM time_off_anonymous;

Dedicated schema for the API

Using the public schema (or any schema(s) where your core data model resides) is a fast way to get started. However, using a dedicated schema for the API is beneficial for several reasons:

  • It provides options for better abstraction, which you might appreciate later when refactoring the original data model.
  • Data customisation is also a requirement unless you prefer building a "fat" client and thus transferring the majority of the business logic there. Combining data from multiple tables helps shield the consumer from complex queries and relations.
  • While we won't explore it as a security feature, it can also provide relevant boundaries.

Let's get started with the creation of the schema itself, and expose the users using a view and setting basic permissions. We will do this by setting default permissions, so we don't have to repeat the same for all objects as we create them. Please note that default privileges are applied only to new objects created.

CREATE SCHEMA api;

GRANT USAGE ON SCHEMA api TO time_off_anonymous;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT ON TABLES TO time_off_anonymous;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT EXECUTE ON FUNCTIONS TO time_off_anonymous;

Ok, let's create our first view:

CREATE VIEW api.users AS
SELECT 
    u.user_id,
    u.email,
    m.user_id as manager_user_id,
    m.email AS manager_email,
    u.created_at
FROM 
    public.users AS u
    LEFT JOIN public.users AS m ON u.manager_id = m.user_id
WHERE
    u.deleted_at is null;

This way, we established the foundation for listing users while providing much richer information about a person's manager and preventing potential additional roundtrips between client and API. We also included simple business logic—by omitting the soft-deleted employees.

After creating the views, it's important to update the postgrest.conf file to use the new api schema:

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

This change tells PostgREST to treat the api schema as the primary interface for API requests, rather than the public schema. Once done, feel free to restart the PostgREST server and test the new setup. 

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

We can further extend the example by provisioning a view with a summary of the vacation days available per calendar year (of course, for simplicity we won't consider transfers between years, etc.).

CREATE VIEW api.vacation_balances AS
SELECT 
    EXTRACT(YEAR FROM transaction_date) AS year,
    user_id,
    SUM(amount) AS total_amount
FROM public.time_off_transactions
JOIN api.users USING (user_id)
WHERE 
    leave_type_id = (SELECT leave_type_id FROM public.leave_types WHERE label = 'vacation')
GROUP BY 
    EXTRACT(YEAR FROM transaction_date), user_id;

While this is nothing groundbreaking, we could (and should) have certainly used views in the first part. So what makes this important now? As mentioned above, the main use cases are abstraction, data access, and security. Additional benefits might include other concerns like derived columns (e.g., full name if we were using first and last name columns), enriching data (e.g., building full URLs from fragments), and other logic specific to the presentation layer.

For practice, we can expose possible time off types via a simple view:

CREATE VIEW api.leave_types AS 
SELECT
    label
FROM public.leave_types
WHERE deleted_at is null;

Let's modify the data

While the simplicity of performing CRUD operations on tables in the public schema was straightforward in the first part of the tutorial, using VIEWs introduces certain complexities. One key limitation of VIEWs is their restricted capability for data modification. Let's look at how to encapsulate the logic.

You might have guessed it—the most obvious way forward is to introduce a database stored procedure for any business logic we want to expose.

CREATE OR REPLACE FUNCTION api.add_user(
    email text,
    manager_id integer
) RETURNS integer AS $$
DECLARE
    v_new_user_id integer;
BEGIN
    -- check if email already exists
    PERFORM 1 FROM api.users WHERE users.email = add_user.email;
    IF FOUND THEN
        RAISE EXCEPTION 'The email address % is already in use', add_user.email
            USING ERRCODE = 'unique_violation';
    END IF;
    
    -- sample business logic: all employees must have manager
    IF manager_id IS NULL THEN
        RAISE EXCEPTION 'manager_id must be provided and cannot be null';
    END IF;
    
    INSERT INTO public.users (email, manager_id)
    VALUES (add_user.email, add_user.manager_id)
    RETURNING user_id INTO v_new_user_id;
    
    RETURN v_new_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Ok, we have introduced a rather complex piece of logic there. While it might look simple (if you've ever seen a PL/pgSQL function), it comes with a couple of important details.

First and foremost, the function api.add_user presents an example of how to implement business logic. In this case, it's providing a custom error message should the employee's email already exist and enforcing the logic that manager_id must be provided. You can probably think of more cases to enrich data.

Now for the more difficult part—you might not be familiar with the SECURITY attribute. Every function created can have two modes, INVOKER (which is the default) and DEFINER. If you look above, when we removed all the permissions for our db-anon-role from schema public, the user lost privileges to perform any operations there. Without modifying the security attribute, the function would result in an error message permission denied for table users. Using SECURITY DEFINER guarantees your application user's (the one used for the creating the view) permissions will be used when calling the function.

This is the opposite of how VIEWs work in this example, as they come with the default option security_invoker disabled by default. You can refer to the documentation to learn more.

If you are not familiar with PL/pgSQL, I recommend you check the basic statements.

With the function in place, let's call it using cURL:

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

This is the second time we need to deconstruct the seemingly simple logic. Let's start with the URL. The /rpc/ prefix is important. Every stored procedure in the schema defined using db-schema is accessible under this prefix. The prefix helps to differentiate object types, as in PostgreSQL you are allowed to have the same name for a table/view and a function.

The second part is the request method, in this case POST. While you can use both GET and POST, it's important to understand the implications. Using the GET method sets the PostgREST transaction to read-only mode and is a powerful security measure—explicitly preventing the operation from performing any modification of the data (even indirectly via functions or triggers). Should you perform the function using GET, you would get a cannot execute INSERT in a read-only transaction error message to confirm it.

The third part of the call is the way the data is presented. In this example, it's using JSON (declared as Content-Type: application/json header) and passed within the request body. If required, you can choose other formats—like application/x-www-form-urlencoded, text/xml, application/octet-stream for bytea, and more using custom media type handlers.

Therefore, the same can be achieved using:

$ curl "http://localhost:3000/rpc/add_user" -X POST \
 	-d "email=admin2%40example.com&manager_id=2" \
	-H "Content-Type: application/x-www-form-urlencoded"

Let's omit the XML example completely [sic].

As we have introduced the function that modifies the data, we should explore the option to do the same with read-only functions.

CREATE OR REPLACE FUNCTION api.get_max_vacation_days()
RETURNS INTEGER STABLE AS $$
    SELECT max_days
    FROM public.leave_types
    WHERE label = 'vacation';
$$ LANGUAGE sql SECURITY DEFINER;

Here, we defined the function get_max_vacation_days, which enables retrieving the current number of vacation days for all employees. To call the function, we can simply run:

$ curl "http://localhost:3000/rpc/get_max_vacation_days"

This summarises the basics of using a dedicated schema and VIEWs. While using stored procedures is the most obvious way, you can also use updatable views and INSTEAD OF TRIGGERS. This approach allows you to hide the possible transition from table to views and implement the insert logic using the trigger function. While this is a powerful technique, I recommend using direct function calls for clarity (at least when you are getting started).

Simple workflow management

As we have introduced quite a lot of (potentially new) concepts, let's practice more and get our hands dirty with the implementation of a simple workflow management system. In our case, it's functionality for employees to request time off and manage approval flow. The premise is simple:

  • Employee can request a time off of a certain type
  • Employee can approve/reject the time off request for their reports
  • The boss can do whatever they want

Let's start with defining the table for time off requests. Please notice we are going to create it again in schema public and not directly expose it via the API.

CREATE TABLE public.time_off_requests (
    request_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES public.users(user_id),
    leave_type_id INT REFERENCES public.leave_types(leave_type_id),
    requested_date DATE,
    period DATERANGE,
    status TEXT CHECK (status IN ('pending', 'approved', 'rejected')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

We will add functionality to request time off via a function:

CREATE OR REPLACE FUNCTION api.request_time_off(
    user_id INT,
    leave_type TEXT,
    period DATERANGE
) RETURNS INTEGER AS $$
DECLARE
    v_leave_type_id INT;
    v_request_id INT;
BEGIN
    -- validate the leave type
    SELECT leave_type_id INTO v_leave_type_id 
    FROM public.leave_types 
    WHERE label = request_time_off.leave_type;
    
    IF v_leave_type_id IS NULL THEN
        RAISE EXCEPTION 'Invalid leave type: %', request_time_off.leave_type;
    END IF;

    -- check if the user ID is valid
    PERFORM 1 FROM public.users WHERE users.user_id = request_time_off.user_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Invalid user ID: %', request_time_off.user_id;
    END IF;
    
    -- insert the new time off request
    INSERT INTO public.time_off_requests (
        user_id, leave_type_id, requested_date, period, status
    ) VALUES (
        request_time_off.user_id, 
		v_leave_type_id,
		CURRENT_DATE,
		request_time_off.period,
		'pending'
    ) RETURNING request_id INTO v_request_id;
    
    RETURN v_request_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

For the purposes of this guide, let's leave details out, and we would expect the period to be the applicable working days. To reiterate, the function above:

  • Provides basic application logic, setting the status to 'pending'
  • Explicitly declares SECURITY DEFINER to facilitate permissions to access the newly created table.

We can call it using:

$ curl -X POST "http://localhost:3000/rpc/request_time_off" \
	-d '{"user_id": 6, "leave_type": "vacation", "period": "[2024-05-20,2024-05-21]"} ' \
	-H "Content-Type: application/json"

For the client to be able to display the pending requests, let's introduce the view using the request fields and adding the manager ID of the employee, which we will utilise in the next step:

CREATE VIEW api.pending_requests AS
SELECT
    r.request_id,
    r.user_id,
    r.leave_type_id,
    r.requested_date,
    r.period,
    r.status,
    r.created_at,
    u.manager_id
FROM public.time_off_requests r
JOIN public.users u ON r.user_id = u.user_id
WHERE r.status = 'pending'
ORDER BY r.created_at;

With this in place, we can move forward with implementing the function api.update_request to provide the necessary business logic to approve/reject the requests.

CREATE OR REPLACE FUNCTION api.update_request(
    request_id INT,
    user_id INT,
    new_status TEXT
) RETURNS VOID AS $$
DECLARE
    v_requested_user_id INT;
    v_request_manager_id INT;
BEGIN
    -- validate the new status
    IF new_status NOT IN ('approved', 'rejected') THEN
        RAISE EXCEPTION 'Invalid status: %. Only "approved" or "rejected" are allowed', new_status;
    END IF;

    -- retrieve the request together with the users associated with it
    SELECT pr.user_id, pr.manager_id INTO v_requested_user_id, v_request_manager_id
    FROM api.pending_requests pr
    WHERE pr.request_id = update_request.request_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'There''s no pending Time off request ID %', request_id;
    END IF;

    -- prevent users from self-approving their requests
    IF user_id = v_requested_user_id THEN
        RAISE EXCEPTION 'User cannot approve or reject their own request';
    END IF;

    -- check if the user is either the requester’s manager or the boss
    IF (v_request_manager_id IS NOT NULL AND user_id <> v_request_manager_id) THEN
        RAISE EXCEPTION 'Only the manager or The Boss can approve or reject the request';
    END IF;

    -- update the request status
    UPDATE public.time_off_requests
    SET status = new_status
    WHERE time_off_requests.request_id = update_request.request_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

This time, the function shouldn't have any surprises—just ensure you follow all the points addressed previously. With the functionality to approve/reject the requests, we are missing the last crucial step: deducting the balance upon approval. There are two ways to go about this: either add the logic to the function above or implement triggers. While a full discussion goes beyond this guide, let's provide a brief summary of when to choose which solution.

Choose a function if you have complex logic reusable by many functions (which does not prevent re-use in triggers), and use triggers if you prefer the automatic business rule enforcement and consistency on the database level. For this tutorial let's do triggers to expand further the use of different SQL techniques (plus I would personally choose this solution anyway).

Before jumping in, let's create a helper function that will calculate the number of days to deduct from the balance.

CREATE OR REPLACE FUNCTION public.days_in_daterange(period daterange) RETURNS INT AS $$
    SELECT upper(period) - lower(period)
$$ LANGUAGE sql;

With it in place, we can create the function:

CREATE OR REPLACE FUNCTION public.create_transaction_on_approval() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'approved' THEN
        INSERT INTO time_off_transactions (
            user_id, 
            leave_type_id, 
            transaction_date, 
            time_off_period, 
            amount
        ) VALUES (
            NEW.user_id,
            NEW.leave_type_id,
            CURRENT_DATE,
            NEW.period,
            -public.days_in_daterange(NEW.period)
        );
    END IF;
  
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

And set up the trigger itself:

CREATE TRIGGER create_transaction_on_approval
AFTER UPDATE ON time_off_requests
FOR EACH ROW
WHEN (NEW.status = 'approved' AND OLD.status = 'pending')
EXECUTE FUNCTION create_transaction_on_approval();

With the approval logic in place, we can try the original function via API:

$ curl -X POST "http://localhost:3000/rpc/update_request" \
	-d '{"request_id": 1, "user_id": 2, "new_status": "approved"}' \
	-H "Content-Type: application/json"

If you have used the correct IDs (both for request ID and user ID), you can now verify the balances using the pre-defined view vacation_balances.

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

This demonstrates the basic workflow and how it can be exposed using PostgREST as an API. If you want to continue practising more database logic, you can add the following logic:

  • Prevent negative vacation balances (intermediate)
  • Prevent employees of a single manager from requesting overlapping vacations (advanced)

End of Part 2

By introducing a dedicated API schema, we have added an abstraction layer that provides an effective boundary between the model/logic itself and the API specifics. While this is a good practice, it's up to you how to expose functionality. The main benefit of a dedicated schema is simplicity, allowing you to grant privileges on the schema in bulk rather than maintaining granular permissions. It also provides an easy way to expose existing databases with PostgREST.

While the current state of the sample API for Time Off Manager would work for small teams, in the next part, we will move towards authentication for added security and privacy.

And don't forget you can find the source code in the GitHub repository.