Create a MySQL REST API in Minutes Using DreamFactory

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 generation, documentation, and security. In this tutorial I’ll show you just how easy it is to build, secure, and deploy a REST API for your MySQL database.

Follow Along!

DreamFactory’s MySQL service connector is part of our open source version. You can download an installer or clone directly from GitHub via our downloads page.

Generating the MySQL REST API

DreamFactory can generate REST APIs for 18 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. 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. 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

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 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:   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!

Free DreamFactory Developer Sandboxes For Oracle Cloud

Oracle Cloud and DreamFactoryWhat is one the biggest issue facing developers these days?  Time and cost.  With the Forrester Report finding that 80% of the development cost for a typical app is on the backend vs. frontend, and API engineering forming the biggest component of this backend burden – DreamFactory was founded by developers, for developers, to provide an easy solution for this problem in a heartbeat.

DreamFactory’s Co-Founder, Eric Rubin, introduces Free Developer Sandboxes for Oracle Cloud. Experience DreamFactory’s Instant APIs and Enterprise Data Gateways for yourself at www.dreamfactory.com/Oracle to see how you can automate your backend and focus your engineering expertise on projects that are needed to grow revenue.

Continue reading “Free DreamFactory Developer Sandboxes For Oracle Cloud”

Running DreamFactory as a Docker Container

large_h-dark 

Note: We have updated the instructions here to match our DF-Docker repo instructions.  This will pull the latest GitHub repo now.

DreamFactory can be run as a Docker container, which makes it easier than ever to get the backend for your apps up and running. The DreamFactory Docker image is available on Docker Hub, or you can build your own image from the GitHub repo. Using these two methods, I’ll show you how to use Docker to fire up your own DreamFactory instance in just a few steps. This setup uses MySQL for the system database and Redis for the system cache. The basic idea is that you first start the containers for MySQL and Redis, then a container for DreamFactory which links to the others. 

Continue reading “Running DreamFactory as a Docker Container”

Changing An API Key For One of Your Apps In DreamFactory

So what happens if you make a mistake and expose your admin app api_key or just need to change api_key associated with one of your apps?  We have an easy workaround that doesn’t require you to have to change any of your endpoints or having to recreate an app, etc.  This article shows you how to access all of your app API keys via MySQL or, if you haven’t fully started exploring DreamFactory yet, the default SQLite database.

Continue reading “Changing An API Key For One of Your Apps In DreamFactory”

The importance of loose coupling in REST API design

One of the most important ideas in the world of software engineering is the concept of loose coupling. In a loosely coupled design, components are independent, and changes in one will not affect the operation of others. This approach offers optimal flexibility and reusability when components are added, replaced, or modified. Conversely, a tightly coupled design means that components tend to be interdependent. Changes in a single component can have a system wide impact, with unanticipated and undesirable effects.

Continue reading “The importance of loose coupling in REST API design”

Connecting MySQL with JavaScript;  DreamFactory as a BaaS

The DreamFactory REST API enables database connections using a wide variety of front end scenarios. This simple sample app demonstrates how DreamFactory easily can be used as a backend for a JavaScript application. It’s a simple address book, where contacts can be created, shown, updated, deleted and grouped: basically, CRUD operations.

Continue reading “Connecting MySQL with JavaScript;  DreamFactory as a BaaS”

How To Configure An ELK Stack With DreamFactory

DreamFactory has had support for Logstash since version 2.3 for our Gold Tier version.  Elastic makes some great tools to support very robust logging.  Incorporating Elasticsearch, Logstash and Kibana into your powerful, scalable DreamFactory instance is a no brainer, especially for users who have a lot of data being pushed and pulled through various endpoints.  This will make the lives of your admins so much easier with the amount of detail they can grab to troubleshoot issues. Continue reading “How To Configure An ELK Stack With DreamFactory”

What if Chuck Norris Wanted to Create a Service That Automated APIs?

chuck_facts-467532-edited

Chuck Norris Joke Enthusiasts Trust DreamFactory to Automate  APIs

Thanks to amusing Chuck Norris API database site The Internet Chuck Norris Database, you can have some fun and keep the Chuck Norris jokes flowing.  With the help of DreamFactory and our API automation tools, you will always have life-changing insights making those around you just a bit more intelligent, good looking and successful.  Who doesn’t need to understand such nuggets as:

Contrary to popular belief, the Titanic didn’t hit an iceberg. The ship was off course and ran into Chuck Norris while he was doing the backstroke across the Atlantic.

Continue reading “What if Chuck Norris Wanted to Create a Service That Automated APIs?”