Automatically Mapping Tables to a REST API

Like all good projects, this one started with a need.  Specifically, I needed a way to communicate between the front-end of my Bubble map editor and the MySQL database on the back-end.  The editor was designed to create, edit and save bubble maps all without having to submit a form or reload the page.  No one wants to submit a form and reload a page to make a save to something you’re working on.  Imagine if every time you wanted to save a change in Google Docs, you had to click a ‘Save’ button, which would take a couple seconds to reload the page before you can continue working (ewww).  Instead, the document is constantly communicating with Google’s servers so that you don’t even have to click a button, it is automatically saving as you edit.  Way better.  My goal was to bring this type of magic to my bubble map form.

Why use REST?

The reasons are that (1) REST API’s provide a simple, elegant way to communicate between the frontend and backend and (2) they’ve become popular recently. This popularity means that many people will quickly understand how your API works if you make it a RESTful one.  I’ve used several REST API’s in the past (including DocuSign and Habitica) and they’ve tended to been intuitive and easy to use.

There are no formal specifications for how a REST API operate. Instead, there is just a series of best-practices and design principles that implementers follow. Communication consists of the client requesting a CRUD operation for a resource and the service performing the operation.  URL paths are used to specify the resource and HTTP Methods (POST, GET, PATCH, PUT, DELETE) are used to specify the type of operation.

Early on, when I was first figuring out how to tackle this project, I found a very helpful article on implementing REST API’s in Symfony (which is what all my web projects are being created in these days).

Why automatically map tables to a REST API?

I could have just followed the tutorial above and created a one-off API for the bubble map resources, but I wanted to easily extend the API to other resources in the future.  Since all my web app data tends to be contained databases, I decided to take this project one step further and create a REST API library that would automatically handle requests based on the structure of tables in a MySQL database.  This would allow me to quickly expose the data in the back-end to the front-end in future projects.  Plus, I already had experience building an ORM for MySQL, so I have a bit of understanding about how the mapping could be accomplished.

How it works

The controller handles five types of API requests:

1) Get List

HTTP Request: GET /api/v1/{TableName}

This returns a list of all the items in the table (limit 100)

2) Get Single

HTTP Request: GET /api/v1/{TableName}/{id}

3) Update

HTTP Request: PATCH|PUT /api/v1/{TableName}/{id}

4) Create

HTTP Request: POST /api/v1/{TableName}

5) Delete

HTTP Request: DELETE /api/v1/{TableName}/{id}

The Code

GitHub repository

Future improvements:

  1. Auto-created doc page
  2. Authentication
  3. Non-JSON format
  4. Add filtering for get list

Leave a Reply

Your email address will not be published. Required fields are marked *