Blog

In an earlier blog post I showed you how to expose a relational database function as a REST API. In this blog post you are going to learn how to expose a database store procedure as a REST API.

If you have an existing stored procedure you can use it. If you don’t have a stored procedure, I will show you how to create two simple tables and then create a stored procedure.

The database used in this example is MySQL. The stored procedure will insert the same data into two different tables. The first step is to create two tables.

Here is SQL to create the first table:

CREATE TABLE `message` (
   `id` INT NOT NULL AUTO_INCREMENT ,
   `messageText` VARCHAR(255) NOT NULL,
   PRIMARY KEY ( `id` )
)

And here is SQL to create the second table:

CREATE TABLE `backup` (
   `id` INT NOT NULL AUTO_INCREMENT ,
   `messageBackup` VARCHAR(255) NOT NULL,
   PRIMARY KEY ( `id` )
)

This is the SQL for the stored procedure. The stored procedure inserts the same information into both tables – performing two inserts.

CREATE PROCEDURE `message_and_backup`(IN msg VARCHAR(255))
BEGIN
   INSERT INTO `message` (`messageText`) VALUES (msg);
   UPDATE `backup` (`message`) VALUES (msg);
END

It’s a simple but good example. You can make the stored procedure as complex as you need but the process of exposing it as a REST API is the same and is shown next.

To expose the stored procedure as a REST API, create a custom API Express service with one SQL component:

In the SQL component, call the message_and_backup stored procedure passing in the message input. That’s it.

Right away you can test the API Express service:

In this blog post you learned how to expose a stored procedure as a REST API. You can as easily expose a custom SQL query or a function as a REST API. To learn more about API Express please visit our YouTube channel.