How to Expose a Database Stored Procedure as a REST API

Posted: April 3rd, 2017Author:

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:

And here is SQL to create the second table:

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

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:

apiexpress-storedprocedure

 

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:

apiexpress-storedprocedure-test

 

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.