Taking Control with PostgreSQL Functions: Closing the Gap to ORM Functionality

Unveiling the Disparity: Understanding the Divide Between Direct Driver and ORM Functionality

When it comes to choosing the technologies for developing a backend and manipulating data in a database like PostgreSQL, developers often face the choice between using the official driver or an abstraction layer like an ORM, in this case, Prisma. Both approaches have their advantages and disadvantages, but it has been argued that ORMs are superior due to a myriad of features designed for the common CRUD operations typically performed in a REST API. In this article, we will explore how functions in PostgreSQL allow us to bridge the gap between using the official driver directly and the functionalities provided by an ORM like Prisma. We will specifically focus on the data insertion operations, examining how functions can deliver results similar to those achieved when using an ORM, including the ability to return created objects and much more.

import 'dotenv/config';
import { Client } from 'pg';

const pg = new Client({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  database: process.env.DB_NAME,
});

pg.connect().then(() => {
  console.log('db connected');
  pg.query(
    `INSERT INTO reservations (_date, hora, res_number, res_name, room, meal_plan, pax_number, cost, observations)
      VALUES
        ('2023-07-27', '21:00', 001, 'Jhon Doe', 'P01', 'SC', 2, 50.00, 'Sin observaciones')`,
  ).then((result) => {
    console.log(result);
    pg.end().then(() => {
      console.log('disconnected from db');
    });
  });
});

/*
db connected
Result {
  command: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  ... more metada
}
disconnected from db
*/

In this example run in Nodejs, you can see that there aren't many useful information on the result of that operation and too much metadata.

Now, let's compare it with an insert run in Prisma.

prisma.pokemons.create({
    data:{
      id: 1100,
      name: 'new_pokemon'
    }
  }).then((result: any) => {
    console.log(result);
  })

/*
{ id: 1100, name: 'new_pokemon' }
*/

With Prisma, We can get rid of the metadata and extract the object inserted in the database

Let's try to emulate that type of result.

Intorduction to function in Postgresql.

Let's jump directly to a simple code example of a function in Postgres.

CREATE FUNCTION sum(num1 numeric, num2 numeric) -- declare function and parameteres
RETURNS numeric -- returned type
AS $$
BEGIN -- starting the code
  RETURN num1 + num2; -- result
END; -- end of code
$$ LANGUAGE plpgsql; -- end of the function

SELECT sum(2, 3); -- returns 5

There could be more sections on a function declaration, like catching exceptions or declaring variables. We'll leave that for later.

But now let's dive into another example. A bit more elaborate.

CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  pax INTEGER,
  capacity INTEGER,
  do_res DATE, -- date of reservation
  to_res TIME -- time of reservation
);

-- populating database with bad data that will throw exceptions in our function
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(0, 0, '2022-01-01', '10:00:00');

/* Use it to play with the function if needed
-- populating database with good data
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(2, 4, '2022-01-01', '10:00:00'),
(3, 6, '2022-01-02', '11:00:00'),
(4, 8, '2022-01-03', '12:00:00'),
(5, 10, '2022-01-04', '13:00:00'),
(6, 12, '2022-01-05', '14:00:00'),
(7, 14, '2022-01-06', '15:00:00'),
(8, 16, '2022-01-07', '16:00:00'),
(9, 18, '2022-01-08', '17:00:00'),
(10, 20, '2022-01-09', '18:00:00');
*/

Lets try to create a function that can tell the porcentage of ocupation.

SELECT SUM(pax)*100 / SUM(capacity) AS ocupation FROM reservations;

This is a normal query but now let's generate the function

I know we could use a NULLIF to avoid exceptions, but i will want to throw this exception soon.

CREATE OR REPLACE FUNCTION porcentage_ocupation(fecha_i DATE, fecha_f DATE)
RETURNS FLOAT AS $$
DECLARE -- declaring variables
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentage FLOAT;
BEGIN
  SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
  porcentage := total_pax * 100.0 / total_capacity;
  RETURN porcentage;
END;
$$ LANGUAGE plpgsql;

Now we have created a more complex function but this is still not enough

It is very common and a good practice to let the servers handle any type of errors. If an error occurs inside the database, it will scalate to the server throwing an exception. If you populate the server only with the bad data left behind, and try to run the function this is what you will get.

SELECT porcentage_ocupation('2022-01-01', '2022-12-31');
-- ERROR:  division by zero
-- CONTEXT:  PL/pgSQL function porcentaje_ocupation(date,date) line 8 at assignment

I won't suggest that we should create a different way of handling this exceptions, but we could if we want. In the next part we will handle exceptions and return a JSON with the data and metadata we want to serve from the database.

Taking it Up a Notch: Enhancing Function Capabilities with Customized Responses and Exception Management

And now is time to present you some other features we can use in Postgresql, like a try/catch block inside postgresql and some system data we can get inside the functions.

CREATE OR REPLACE FUNCTION porcentage_ocupation2(fecha_i DATE, fecha_f DATE)
RETURNS JSON AS $$ -- returning a JSON is a great option to return customized results
DECLARE
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentage FLOAT;
  stack text; -- will get the stack of errors here
  result JSON; -- will build the result here
BEGIN -- begin the function
  BEGIN -- this second begin works as the try in a try/catch block
    SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
    porcentage := total_pax * 100.0 / total_capacity;
    result := json_build_object('isError', FALSE, 'result', porcentage);
  EXCEPTION -- and here is the catch in the try/catch block
    WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS stack = PG_EXCEPTION_CONTEXT; -- how we can obtain the stack of the error in postgresql
      result := json_build_object('isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE, 'stack', stack); -- SQLERRM, SQLSTATE are system variables provided by postgresql
  END; -- end of the try/catch block
  RETURN result; -- returning the response
END;
$$ LANGUAGE plpgsql;

And with this second function created, lets see the result when we end up dividing by 0.

SELECT porcentage_ocupation2('2022-01-01','2022-12-31');
                                                                      porcentage_ocupation2                                                
----------------------
 {"isError" : true, "message" : "division by zero", "errorCode" : "22012", "stack" : "PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment"}

Like I said before, I won't defend if this is a good practice or not, i will just say that we could handle the exceptions ocurring inside the execution of the code. It will be the decision of the project manager or Senior developers if this is helpful or not.

Let's jump into the controller in Nodejs, because we have reached the prerequisites to achive our goal.

pg.connect().then(() => {
  console.log('db connected');
  pg.query(`SELECT porcentage_ocupation2('2022-01-01','2022-12-31' as result)`).then(
    (result) => {
      console.log(result.rows[0].result); // looking for the result straight to the point it should be
      pg.end().then(() => {
        console.log('disconnected from db');
      });
    },
  );
});
/*
db connected
{
  isError: true,
  message: 'division by zero',
  errorCode: '22012',
  stack: 'PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment'
}
disconnected from db
*/

We are taking advantage of the fact that returning a JSON from the Postgres Function, will return 1 and only 1 row. That's why we can always look for the result in that place of the object. This is exactly the feature we wanted to build. Now let's move forward to implement it on CRUD operations, as it is more meaningful than doing it in a SELECT QUERY.

Turning Concepts into Code: Writing INSERT and UPDATE Logic in PostgreSQL Functions

We are about to finish so let's not waste any more time.

CREATE OR REPLACE FUNCTION create_reservation(
  _pax INTEGER,
  _capacity INTEGER,
  _do_res DATE,
  _to_res TIME
) RETURNS JSON AS $$
DECLARE
  inserted_reservation reservations; -- here will retrived the inserted object
  response JSON;
  stack_info TEXT;
BEGIN
  BEGIN
    IF _do_res < CURRENT_DATE THEN
      response := json_build_object(
        'isError', FALSE, -- It could be considered an error (a bad request error)
        'message', 'Bad request: No record inserted - You cant create a reservation in the past',
        'rowsAffected', 0,
        'result', NULL
      );
    ELSE
      BEGIN
        INSERT INTO reservations (
          pax,
          capacity,
          do_res,
          to_res
        ) VALUES (
          _pax,
          _capacity,
          _do_res,
          _to_res
        )
        RETURNING * INTO inserted_reservation; -- retriving the insert object

        IF inserted_reservation IS NULL THEN
          response := json_build_object(
            'isError', TRUE, 'message', 'No record inserted',
            'rowsAffected', 0
            );
        ELSE
          response := json_build_object(
            'isError', FALSE, 'result', inserted_reservation, 'rowsAffected', 1
            );
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          GET STACKED DIAGNOSTICS stack_info = PG_EXCEPTION_CONTEXT;
          response := json_build_object(
            'isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE,
            'stack', stack_info
            );
      END;
    END IF;

    RETURN response;
  END;
END;
$$ LANGUAGE plpgsql;

SELECT create_reservation(
  2, 4, '2023-07-27', '19:00'
) AS result;

Bear in mind that the function is adding a "constraint" to the create_reservation, not allowing to create a reservation before the CURRENT_DATE. This is an extra control that we can add using functions.

With this we have reached the end of this post.

In conclusion, by harnessing the power of functions in PostgreSQL, we have successfully narrowed the gap between ORM and direct database programming. Through our exploration, we have gained a deeper understanding of how to leverage functions effectively, enabling us to customize responses, handle exceptions and adding more control in our database.

While working directly with the database may require additional effort, it offers unparalleled control over the data manipulation process. Functions empower developers to have fine-grained control and leverage the full potential of PostgreSQL.

The moment when PostgreSQL functions truly surpass ORMs is when the server is faced with the challenge of executing complex queries or providing business intelligence. It is in these scenarios that the real power of function programming in PostgreSQL becomes apparent. Functions offer a level of flexibility and control that goes beyond the capabilities of ORMs, allowing developers to craft intricate and optimized queries tailored to specific business requirements.