Effortless MySQL REST API Creation with Low- Code: Boosting Efficiency and Productivity

In this article, we will be looking into a low-code REST API creation using the MySQL database. The tutorial will be covering the following topics.

  • Prerequisite
  • MySQL Rest Service (MRS) Architecture for a standalone machine
  • REST API Creation
  • Adding DB objects to the service
  • Starting MySQL router
  • Testing the rest endpoint

Prerequisite

Before getting started with the API development we will be needing below setup

Once we have these applications ready, we are ready to dive in further.

MRS Architecture

In this article, we are talking about a bare minimum or stand-alone system for API development using MySQL. So, moving forward we have mainly 4 components at a high level

  • Client: A web browser / App or any rest client
  • Middleware: HTTP server, Authentication, MRS plugin and MySQL router. Each of these is very easy to configure and does not require any coding experience
  • Database: MySQL
  • Utility tools: MySQL Shell and MySQL Shell for VS Code.

Note: We will not be covering MySQL Shell in this article as we are following a low-code approach.

REST API Creation

REST API Creation

Assuming, you have already downloaded the applications mentioned in the previous section. If not, do already follow the below instructions :

  • Install the MySQL community server
  • Install VS Code application
  • Run both applications and confirm you are able to connect to MySQL using the command prompt
  • Open VS Code, search and install the “MySQL Shell for VS Code” extension
  • Once it is installed, VS Code will restart and you will get a new icon like below
REST API Creation
  • Click on it and you will get an option for creating a new DB connection as below
REST API Creation

Database type: MySQL as we’re working with it

Hostname: localhost (denotes our local machine)

Username: root (system default, or you can use any other user you have created using MySQL installation).

Schema: The default schema selected. This can be left blank also.

After pressing ok, it will ask for a password for the username provided. ( which you must have created during MySQL installation)

Once you are done with connection creation. A list of existing Databases will be displayed under the Database Connection

  • Right Click on MySQL REST Service and you will get an option to add a new rest service
  • A rest service configuration will appear
  • provide a desired service_path and supported protocols
REST API Creation
  • once it’s done you will be able to see the rest end-point under MySQL REST Service
REST API Creation

Congratulation !!!! you have configured your first rest endpoint. Now we need to flow data through it.

Adding DB objects to the service

  • Right-click on any database and choose to Add Schema to Rest Service
REST API Creation
  • A new configuration will open and you need to uncheck the Required Authentication option ( we can talk about authentication sometime later) and press ok.
REST API Creation
  • In the next step, we will be mapping the Schema object to the rest service.
  • To map the actual data to the rest endpoint we need to select the table for which data need to be exposed
REST API Creation
  • Select Add Database Object to Rest Service
  • A new window will open to select the fields that can be exposed with the operations that can be performed. (Create, Read, Update and Delete operations)
REST API Creation
  • After completion of this step, the rest endpoint with table data is ready
REST API Creation

Starting MySQL router

Now as the last step of our setup, we need to start the MySQL Router

  • Right Click MySQL Rest Service
  • Select BootStrap Local MySQL Router Instance
  • It will start the router and will ask to enter the Jwt token passphrase. (Please note that this JWT secret always needs to be the same for every MySQL Router instance when deploying multiple routers for the same MySQL solution.) and we are done now
REST API Creation
REST API Creation

Testing the rest endpoint

For testing your rest endpoint right-click Rest Object and select the option Open Rest Object Request path in Web Browser. In case no hostname is provided the default link is formed in the below format

localhost:8443/<service_name>/<schema_name>/<db_object>

REST API Creation
REST API Creation

Conclusion

MRS provides a low-code REST API Creation interface for creating an application. This can be leveraged to PWA, product prototyping, making fun projects, and setting up testing environments giving developers more time to work on actual problems.

We would like to hear about your experience with MRS REST API Creation. Let us know if you want to learn more about such articles.

Thanks for your precious time

Leave a Comment