DEV Community: Jose Javier Sanahuja

Beyond CRUD: Decoupling with Hexagonal Architecture and Events (Without Losing Your Mind)

Jose Javier Sanahuja — Tue, 29 Jul 2025 22:47:16 +0000

Introduction: Beyond CRUD - Our Journey to a Resilient Hexagon

Let's be honest. When you dive into Hexagonal Architecture tutorials, it often feels like Groundhog Day. How many times have we seen the 'CreateUserUseCase' with its trusty 'UserRepository'? Don't get me wrong, these examples are fantastic for grasping the core concepts of Ports and Adapters. They show us how to keep our precious domain logic isolated and pristine. But let's face it: the real world isn't a simple CRUD operation where everything flows synchronously from a single HTTP request.

Before we go further, stop and think about the last project you opened. What did its file structure scream at you? Did it scream "I AM A SPRING BOOT APP!" with its pom.xml and framework-specific annotations everywhere? Or did it scream "I AM A RESERVATION SYSTEM!" or "I AM A NOTIFICATION ENGINE!"? This isn't just semantics; it's the heart of building resilient software. An architecture that screams its purpose—what it does, not what it uses—is an architecture that survives. When your shiny framework dies or becomes obsolete (and it will), you simply swap out an adapter; you don't have to perform a heart transplant on the soul of your application.

That's the philosophy we're embracing today. In this post, we're cutting through the noise and diving deep into a more realistic, and frankly, more exciting application of Hexagonal Architecture: building an asynchronous notification system. We'll explore how events can act as primary drivers, initiating actions within our application core without direct user intervention. More importantly, we'll unravel the intricate dance of secondary adapters – not just your garden-variety database persistence, but external services like email, SMS, push notifications, templating engines, and even HTML validators. By the end, you'll see how embracing these concepts allows us to create truly resilient, testable, and maintainable systems that adapt to change without losing their minds (or yours!).

To make these ideas tangible, we'll be dissecting a real-world notification domain. Think of it as a crucial, transversal component in any modern application. This isn't a hypothetical setup; it's a blueprint inspired by battle-tested systems, showing how a complex domain can be structured using Hexagonal Architecture. Get ready to peek under the hood and see how notification requests are processed, rendered, and dispatched across multiple channels, all while keeping the core business logic delightfully decoupled from the technology that powers it.

Alright, Theory's Over. Let's Map This to Real Code

That was the textbook definition. Now, let's see how Hexagonal Architecture translates to an actual directory structure. Instead of abstract boxes and arrows, we have concrete files and folders. This is where the architecture truly starts to "scream" its purpose, telling you what the system does rather than what technologies it uses.

Here's the simplified layout of our notification domain module, which we'll dissect piece by piece. This structure is a direct reflection of the Hexagonal principles we just discussed:

/notification
├── port
│   ├── in
│   │   └── SendNotificationUseCase.java
│   └── out
│       ├── NotificationSenderPort.java
│       ├── NotificationRepositoryPort.java
│       └── RendererPort.java
├── domain
│   ├── model
│   │   ├── Notification.java
│   │   ├── TemplateCode.java  <-- A beautiful Value Object!
│   │   └── ... (other pure domain models)
│   └── exceptions
│       └── ... (custom business exceptions)
├── application
│   └── NotificationApplicationService.java
└── adapter
    ├── sender
    │   ├── EmailNotificationSenderAdapter.java
    │   └── SmsNotificationSenderAdapter.java
    ├── persistence
    │   └── jOOQNotificationRepositoryAdapter.java
    └── renderer
        └── ThymeleafRendererAdapter.java

Let's break down each part and how it aligns with the Hexagonal paradigm:

The port directory is your Hexagon's clear boundary.
- The port/in directory holds your Inbound Ports. In our case, SendNotificationUseCase.java is an interface that defines precisely what our application's core can do: send a notification.
- The port/out directory contains your Outbound Ports. These are interfaces like NotificationSenderPort or NotificationRepositoryPort that define what our application's core needs from the outside world: a way to send notifications and a way to save them.
The domain directory is the crown jewel. This is your pure core. It contains all the essential business logic and models, completely independent of any technical details. Notice domain/model/TemplateCode.java. This isn't just a String; it's a Value Object. It encapsulates crucial business rules, like perhaps a template must have a way to say if it has all the requiredVariables. This is real business logic, right here in a tiny, pure, and easily testable object.

// notifications/domain/model/Template.java
public class Template {
    // ... other fields ...
    private final List<String> requiredVariables;
    // ... other fields ...

    /**
     * Checks if the provided map of variables is sufficient to render the template.
     * This is a core business rule.
     * @param variables The map of variables provided for rendering.
     * @return true if all required variables are present, false otherwise.
     */
    public boolean hasSufficientVariables(Map<String, Object> variables) {
        // A null map of variables is never sufficient.
        if (variables == null) {
            return false;
        }

        // If the template requires no variables, the condition is always met.
        if (this.requiredVariables == null || this.requiredVariables.isEmpty()) {
            return true;
        }

        // The business rule: Does the provided map of variables
        // contain all the keys that this template requires?
        return requiredVariables.stream().allMatch(variables::containsKey);
    }
}

The application directory is the orchestrator. NotificationApplicationService.java is the concrete implementation of our inbound port (SendNotificationUseCase). It coordinates the work: it takes a command, leverages the domain models to perform business logic, and then calls outbound ports to get things done (like saving the notification via NotificationRepositoryPort).
The adapter directory is where reality hits. This is where we implement the outbound ports, connecting our core to specific technologies. jOOQNotificationRepositoryAdapter knows how to talk to a PostgreSQL database using jOOQ. EmailNotificationSenderAdapter knows how to connect to an SMTP server. The beauty? The core doesn't know or care about jOOQ or SMTP; it just talks to the NotificationRepositoryPort or NotificationSenderPort.

The Golden Rule: Conventions That Set You Free

At first, all these names (UseCase, Port, Service, Adapter) can feel like a bit much. It's a new convention, and it takes a minute to get used to. But it pays off by creating a crystal-clear, self-documenting structure. The golden rule for a clean Hexagonal setup is simple:

An Inbound Port (e.g., SendNotificationUseCase) is an interface located in port/in. It is implemented by an Application Service in the application layer.
An Outbound Port (e.g., NotificationRepositoryPort) is an interface located in port/out. It is implemented by an Adapter in the adapter layer.

Stick to this, and you've won half the battle. This simple convention is what gives you the superpowers of a truly decoupled application.

So, Why All This Fuss? The Glorious Payoff

Following this structure isn't just for academic brownie points or chasing buzzwords. It gives you tangible, game-changing benefits:

INSANE TESTABILITY: You can test your entire application logic (NotificationApplicationService + Domain objects) without a single external dependency. Just mock the outbound port interfaces. Your tests become lightning-fast, ultra-reliable, and they test your code, not whether your database is having a bad day.
TECHNOLOGY MIGRATION FOR DAYS: Woke up and decided you hate PostgreSQL and want to use MongoDB? Fine. Write a new MongoNotificationRepositoryAdapter that implements NotificationRepositoryPort, change one line in your configuration, and you're done. The application core doesn't even notice the seismic shift. You're swapping out LEGO bricks, not performing open-heart surgery.
CRYSTAL CLEAR BUSINESS RULES (NO SPAGHETTI!): The business logic lives purely in the domain. The orchestration of that logic happens in the application layer. The messy technical details are confined to the adapters. There's no ambiguity. You, your team, and the new hire who starts next month will know exactly where to look for what. It's the ultimate spaghetti code repellent.

The Unexpected Twist: The Event as a Primary Adapter

So far, we've implicitly considered primary adapters as things like REST controllers that receive HTTP requests. That's certainly true, and it's a very common way to drive a Hexagonal application. But what happens when an action isn't triggered by a direct external request? What if a significant event occurs within your application – say, an OrderPlacedEvent – and this event needs to trigger further processing, like sending a confirmation notification?

This is where the true power of Hexagonal Architecture, combined with an event-driven mindset, really shines. Instead of tightly coupling your order service to your notification service, or creating awkward direct calls, we can introduce an Event Listener as a Primary Adapter.

Meet our hero for this scenario: NotificationSpringEventListener. This adapter ensures our notification core remains decoupled while reacting to relevant application events.

// notification/adapter/event/NotificationSpringEventListener.java

@Component
@RequiredArgsConstructor // Lombok for constructor injection, nice and clean!
public class NotificationSpringEventListener {

    private final SendNotificationUseCase sendNotificationUseCase;
    private final NotificationMapper notificationMapper; // To map the event data to a command

    @Async // Crucial: We don't want to block the thread that published the event!
    @EventListener
    public void handleNotificationEvent(final NotificationEvent event) {
        // Here you might add logging, tracing, or metrics
        System.out.println("Received NotificationEvent for user: " + event.recipientId()); // Example log

        // The adapter's job: translate the external event into an internal command
        SendNotificationCommand command = notificationMapper.toCommand(event);

        // Drive the core! The use case doesn't know this came from an event.
        sendNotificationUseCase.sendNotification(command);
    }
}

In this setup:

The NotificationSpringEventListener is a Primary Adapter. It's actively listening for something – a NotificationEvent published by another part of your application. For instance, imagine a VerificationCodeForTwoFactorAuthCreated event being published after a user requests a new 2FA code. Our listener picks this up and acts accordingly.
When it "hears" an event, it acts as the entry point into our notification hexagon. It takes the data from the event and transforms it into a SendNotificationCommand – a format that our core SendNotificationUseCase understands.
The @Async annotation is vital here. It ensures that publishing the NotificationEvent doesn't block the original thread (e.g., the thread creating the 2FA code). Our notification sending process runs in the background, keeping the main flow responsive.
Crucially, your SendNotificationUseCase (the Inbound Port) remains completely oblivious to how it was invoked. It doesn't know if a REST controller called it, a CLI command, or an event listener. It just receives a command and executes the business logic. This is the essence of decoupling!

This pattern allows your application to react to internal state changes or events in a highly decoupled manner, making your system more flexible and resilient. It opens up a world of possibilities beyond simple request-response flows.

The Orchestra of Minions: Secondary Adapters in Action

If our event listener was the driving force, the secondary (or driven) adapters are the components that are put to work by our application core. They are the concrete implementations of our outbound ports—the promises our application core needs fulfilled by the outside world.

Most tutorials stop at the classic UserRepository implementation when discussing driven adapters. That's fine, but the real world is a chaotic orchestra of databases, third-party APIs, rendering engines, and more. Our notification domain is the perfect stage to showcase this diversity, demonstrating that secondary adapters are far more than just data access objects.

The Classic: The Persistence Adapter

Let's get the obvious one out of the way. Our application needs to save the state of a notification, so it requires a persistence mechanism.

The Port (port/out): NotificationRepositoryPort.java

public interface NotificationRepositoryPort {
    Notification save(Notification notification);
    Optional<Notification> findById(UUID id);
}

The Adapter (adapter/persistence): DatabaseNotificationRepositoryAdapter.java
This class would contain all the jOOQ, JPA, or JDBC logic needed to talk to our PostgreSQL database. The core doesn't care how it's done, only that the save and findById contracts are fulfilled. This keeps your domain blissfully ignorant of your chosen database technology.

The Messengers: The Sender Adapters

Here's where it gets more interesting. A notification isn't useful until it's sent. Sending an email is a fundamentally different technical process than sending an SMS, or a push notification. Each requires a distinct external interaction.

The Port (port/out): NotificationSenderPort.java

public interface NotificationSenderPort {
    void send(Notification notification);
    Channel getSupportedChannel(); // e.g., EMAIL, SMS, PUSH
}

The Adapters (adapter/sender):

EmailNotificationSenderAdapter.java: Implements NotificationSenderPort. It knows how to connect to an SMTP server or a third-party API like SendGrid. It probably uses a library like JavaMail or a specific SDK.
SmsNotificationSenderAdapter.java: Also implements NotificationSenderPort. This one knows how to talk to a messaging API like Twilio.
(And perhaps PushNotificationSenderAdapter.java, WebSocketNotificationSenderAdapter.java, etc.)

The beauty is that the ApplicationService just asks the port to send. It has no idea about SMTP, Twilio, or specific push notification protocols. It just speaks the language of the NotificationSenderPort.

The Artist: The Template Renderer Adapter

Notifications often use dynamic content, which means they rely on HTML templates that need data injected into them. Rendering that HTML is an external concern, handled by a specific library.

The Port (port/out): TemplateRendererPort.java

public interface TemplateRendererPort {
    String render(TemplateCode templateCode, Map<String, Object> variables);
}

The Adapter (adapter/renderer): ThymeleafTemplateRendererAdapter.java
This adapter implements the port by using the Thymeleaf library to process a template file and inject variables. If you wanted to switch to a different engine like Handlebars or FreeMarker tomorrow, you'd just write a new HandlebarsTemplateRendererAdapter (or similar) and swap the implementation in your configuration. The core application wouldn't change at all, demonstrating powerful flexibility.

The Pro Move: The Composite Adapter

This is where you can really flex the power of the Hexagonal pattern and polymorphism. What if you want to allow your ApplicationService to send a notification without knowing the specific channel (email, SMS, push) upfront? You can use a Composite Adapter.

The Adapter (adapter/sender): CompositeNotificationSenderAdapter.java
This special class also implements NotificationSenderPort, but it doesn't send anything itself. Instead, it holds a collection of all other NotificationSenderPort implementations and delegates the work to the correct one based on the notification's channel.

// notification/adapter/sender/CompositeNotificationSenderAdapter.java

@Component("compositeSender") // A cool name for the bean!
@Primary // Tells Spring to inject this one by default when NotificationSenderPort is requested
public class CompositeNotificationSenderAdapter implements NotificationSenderPort {

    private final Map<Channel, NotificationSenderPort> senders;

    // Spring cleverly injects a list of all beans that implement NotificationSenderPort
    public CompositeNotificationSenderAdapter(List<NotificationSenderPort> senderList) {
        this.senders = senderList.stream()
            .filter(s -> s.getSupportedChannel() != null) // Exclude the composite itself if it were in the list
            .collect(Collectors.toMap(NotificationSenderPort::getSupportedChannel, s -> s));
    }

    @Override
    public void send(Notification notification) {
        // Here's the magic: we find the appropriate sender and delegate.
        // The use case remains completely oblivious to this routing complexity.
        NotificationSenderPort sender = senders.get(notification.getChannel());
        if (sender != null) {
            sender.send(notification);
        } else {
            // It's good practice to handle unsupported channels, e.g., log or throw
            throw new UnsupportedOperationException("No sender available for channel: " + notification.getChannel());
        }
    }

    @Override
    public Channel getSupportedChannel() {
       // This composite doesn't represent a single channel; it's a router.
       // Returning null or throwing an exception here is common for composites.
       return null;
    }
}

This is freedom. This is power. Your ApplicationService can now be injected with a single NotificationSenderPort (the composite) and remain completely ignorant of the complex routing logic and the multitude of sender implementations. It simply says, "Hey, NotificationSenderPort, send this Notification." The composite then figures out the correct technical adapter.

With all these pieces in place—the event-driven primary adapter and the rich orchestra of secondary adapters—you have a system that is robust, flexible, and ridiculously easy to test and maintain.

Bringing It All Home: Your Hexagonal Superpowers 🦸‍♂️

We started this journey by throwing some well-deserved shade at the overly simplistic "Hello, World" examples of Hexagonal Architecture. We argued that the real world is a chaotic mess of asynchronous events, a zoo of external services, and ever-changing business requirements. A simple, synchronous CRUD example just doesn't cut it.

And what did we do? We systematically built a system that tames that chaos:

We put our business logic in a protected Domain Core, a fortress of pure, technology-agnostic rules.
We introduced an Event Listener as a Primary Adapter, proving our application can react to its own internal drama, not just pokes from the outside world.
We assembled an army of diverse Secondary Adapters—for databases, email APIs, SMS gateways, template engines, and more—to handle all the messy, real-world integration details.

But what's the ultimate prize? Why go through all this trouble of defining ports and writing adapters?

You Build Future-Proof Software

Your application's identity is its purpose (a Notification System), not its tools (a Spring Boot app). When the next hot framework or cloud service comes along, you won't have a panic attack. You'll write a new adapter, plug it in, and have a celebratory well deserved drink. Your core logic, the most valuable part of your system, remains untouched and resilient.

You Gain Ludicrous Testability

You can test the absolute of your core business logic without a database, a mail server, or a full moon. Your unit tests are lightning-fast, ultra-reliable, and tell you if your logic is sound, not if your network connection is flaky.

You Embrace Change Instead of Fearing It

The business wants to add Slack notifications next quarter? You're not going to groan and schedule a three-week refactoring nightmare. You're going to say, "No problem," write a SlackNotificationSenderAdapter, and look like the hero you are. The system is designed for evolution.

Hexagonal Architecture, especially when supercharged with an event-driven mindset, isn't just an academic pattern. It's a practical, battle-tested strategy for writing professional, clean, and adaptable backend systems. It’s how you stop fighting your code and start building software that lasts.

Now go on, build something awesome!

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

Jose Javier Sanahuja — Thu, 08 Jun 2023 16:02:48 +0000

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.