With 52% of professional developers rating it as the most popular database, wouldn’t it be ideal if you could create a MySQL REST API in minutes? Karl Hughes recently penned a blog post titled “The Bulk of Software Engineering in 2018 is Just Plumbing“. Notably he stated, “Just like plumbers, we are paid to know our tools and understand how they work together to make a usable piece of equipment, not to reinvent working technology…”. As programmers we should not be bothered with repeatedly writing code which is otherwise readily available, robust, and well-tested.
Yet this problem remains persistent in the REST API space, despite the implementation process being by this point in time rote, repetitive, and prone to error and oversight. This oversight is costly for several reasons:
- End users just *do not care* how the API was implemented, meaning there is no competitive advantage to be had by hand-crafting a new API for each project.
- Error and oversight in the API implementation and deployment phase can come at a very steep price due to security lapses and performance issues.
- Repeatedly building one-off APIs means they can’t be managed via a single platform or interface; unless the team decides to devote even more time and effort to building a custom management solution.
Fortunately, the DreamFactory platform can easily absolve your team from all of these hassles and much more by offering a centralized solution for the API documentation, generation, and security. In this tutorial you’ll learn just how easy it is to build, secure, and deploy a REST API for your MySQL database.
Related Reading: Learn how to generate a REST API for your SQL database
Generating the MySQL REST API
DreamFactory can generate REST APIs for a multitude of databases, among them MySQL, Microsoft SQL Server, Oracle, PostgreSQL, and MongoDB. To do so, you’ll login to the DreamFactory administration interface, navigate to Services and then enter the service creation interface by clicking on the Create button located to the left of the screen. From there you’ll select the MySQL service type by navigating to Database > MySQL (see below screenshot).
Next you’ll be prompted to provide a name, label, and description (below screenshot). The latter two are used just for reference purposes within the administration interface, however the name value is particularly important because as you’ll soon see it will comprise part of the API URL.
Finally, click on the Config tab. Here you’ll be prompted to provide the database connection credentials (see below screenshot). This should really be nothing new; you’ll supply a host name, username, password, and database. Additionally, you can optionally specify other configuration characteristics such as driver options, the timezone, and caching preferences. For the purpose of this tutorial I’ll stick to the required fields and leave the optional features untouched.
With the credentials in place, just press the Save button at the bottom of the screen, and believe it or not the REST API has been generated!
Viewing the Swagger Documentation
Along with the API, DreamFactory will also auto-generate an extensive set of interactive Swagger documentation for your API. You can access it by clicking on the API Docs tab located at the top of the administration interface, and then selecting the newly generated service by name. You’ll be presented with 44 endpoints useful for executing stored procedures, carrying out CRUD operations, querying views, and much more. For instance the following screenshot presents just a small subset of newly generated MySQL REST API endpoints!
Creating a Role and API Key
All DreamFactory-generated APIs are automatically protected by (at minimum) an API key. You can optionally authenticate users using basic authentication, SSO, or Directory Services (LDAP and Active Directory). Furthermore, you can associate each API key and/or user with a *role* which determines exactly what services the user is allowed to access. Not only that, you can restrict interactions to a specific database table or set of tables, a specific endpoint(s), and even restrict which HTTP methods are allowed.
Securing the users responsible for interacting with the MySQL database on behalf of your APIs is an incredibly important part of the API development and integration process. See our other recommendations on Improved Data Security with MySQL Privileges and DreamFactory
As an example, let’s create a new role which restricts the associated API key to interacting with a single table in a read-only fashion within the newly created MySQL API. To do so, navigate to the Roles tab, and click the Create button. You’ll be presented with the interface found in the below screenshot. In the screenshot you’ll see I’ve already assigned a name and description for the role, and made it active by selecting the Active checkbox.
Next, click the Access tab. This is where you’ll define what the role can do. In the below screenshot you’ll see I’ve limited the role to interacting with the MySQL service, and within that service the role can only interact with the _table/employees* endpoint via the GET method. We’re on lockdown baby!
Save the role by clicking the Save button. Now we’ll create a new API key and associate the key with this role. To do so, click on the Apps tab located at the top of the screen, and then click the Create button. Assign your new App a name and description, ensure it is set to Active, and then assign it the default role of MySQL just as I’ve done in the below screenshot. Regarding the App Location setting, presuming you plan on interacting with the API via a web or mobile application, or via another web service, then you’ll want to select “No storage required”.
Press the Save button and you’ll be returned to the Apps index screen where the new API key can be copied! Copy the key into a text file for later reference.
Configuring CORS for your MySQL API
We have one final configuration step before being able to test the API from outside the DreamFactory administration interface. You’ll need to enable CORS (Cross-Origin Resource Sharing) for the new API. For purposes of demonstration, you can set the default CORS setting as I’ve done in the below screenshot, which will allow API-restricted traffic from all network addresses:
Testing the MySQL REST API
With the API generated, API key and associated role created, and CORS configured, you’re ready to begin interacting with the API via a client! I like to use Insomnia for HTTP testing on MacOS, however another popular solution is Postman.
In the following screenshot I’m using Insomnia to contact the /api/v2/_table/employees endpoint using a GET request.
Recall that we’ve locked down this API key to only interact with the /api/v2/_table/employees/* endpoints using the GET method. So what happens if we try to POST to this table? A 401 (Unauthorized) status code is returned, as depicted in the following screenshot:
Alternative Approaches to MySQL API Creation
Obviously DreamFactory isn’t the only solution available. Check out these other popular tutorials for different perspectives on the topic:
- Building REST APIs with MySQL and Node.js: This is a solid tutorial however as the title indicates it focuses on Node.js-specific development.
- PHP 7 Tutorial with MySQL: CRUD REST API: This lengthy tutorial focuses on building PHP-specific REST APIs, but leaves other required API-related features such as security, API limiting, and logging as an exercise for the reader.
Where to From Here?
Believe it or not, we’ve only scratched the surface in terms of what DreamFactory can do for you. If you’d like to see our SQL Server, Oracle, or MongoDB connectors in action, or would like to watch how easy it is to convert a SOAP service to REST without writing any code, why not schedule a demo with our engineering team! Head over to https://www.dreamfactory.com/products and schedule a demo today!