PL/SQL Microservice - Simple, Concise, Fast

In his excellent blog Developing Microservices in Java, JavaScript, Python, .NET, and Go with the Oracle Converged Database, Paul Parkinson describes how we created an inventory microservice in five different languages: Java, JavaScript (NodeJS), Python, .NET, and Go for the Grabdish application that we showcase in our workshop Simplifying Microservices with converged Oracle Database.  In this blog we show how the same microservice can be created using the PL/SQL language.

PL/SQL is a programming language for executing procedural logic in the Oracle database server.  Executing in the database server allows near instantaneous access to database data improving performance and reducing contention.  PL/SQL has tight integration with SQL, the declarative database language for querying and manipulating data, allowing clear and concise coding.  It also has many powerful and modern packages, such as the JSON_OBJECT package that we used for developing our service. 

Grabdish Architecture

The architecture of the Grabdish application is shown in the following diagram.  The inventory microservice is one component of the application.  When the microservice was implemented in Java, JavaScript (NodeJS), Python and .Net, it ran in a Kubernetes cluster:

When the microservice was implemented in PL/SQL it ran in the database server:

The Inventory Microservice Algorithm

The inventory microservice waits for messages on the order queue and for each message it:

1. Extracts the payload expressed as a JSON object
2. Checks for and updates the inventory
3. Constructs an inventory message in JSON object
4. Places the message on the inventory queue
5. Commits

The PL/SQL Implementation

The code was very simple to write, and it was possible to represent the logic clearly and concisely.  Here is a breakdown of the code logic:

The service was deployed directly into the database server using the DBMS_SCHEDULER PL/SQL package.  This meant the code was always running when the database was up and would failover to a Data Guard standby if that occurred.  

There was no need to compile or build the code and all the required packages were already installed and available in the database.  

Performance

In tests, the PL/SQL implementation processed the order messages at least 3 times faster than any of our other implementations.  It was faster because it was running in the database server, eliminating the network communication at each stage of processing that was required by the other implementations running in separate Kubernetes server.

Conclusion

The code was simple to write, and represented the logic clearly and concisely.  PL/SQL was found to be our fastest implementation, at least three times faster than any other implementation.  Deployment in the database was simple.  The service automatically starts with the database and runs wherever the database is running.  There was no need to build the code and all the required packages were already installed and available.  

References