Add a REST API to any SQL DB in Minutes

Have you got SQL data that you need to access from your mobile or web apps?   If so, DreamFactory provides an easy and secure way to add a REST API to any SQL database in minutes, and supports 18 popular databases, among them MS SQL Server, Oracle, MySQL, IBM DB2, Postgres, SAP SQL Anywhere, and MongoDB! All you have to do is use the DreamFactory REST API backend to create a service for your database, then use the auto-generated REST API to access that service. In this blog post we’ll show how to add additional services to REST-enable any SQL database (quick aside:  if you just need easy SQL reporting into Slack/email, check out SQLBot.co) with an easy to use API.   Then we’ll show some simple examples of how to use the REST API to manage your SQL schema and data. Do you need to create a REST API for MS SQL Server, Oracle, MongoDB, or another database? Using DreamFactory you can be up and running in minutes rather than months! Request a demo with one of our engineers and we’ll be happy to show you how it’s done!   Request a Demo   If you’re a video kind of person, we have some screencasts available. If you haven’t already signed up for a free, cloud-hosted instance of DreamFactory, sign up here. You can also download and install DreamFactory to run it on any server, cloud, or even your local Windows, Mac, or Linux desktop. In this blog post I’ll be using a Bitnami DreamFactory setup on OS X, but the process is similar for other installation types.

Setting Up Your Database Service

The first step is to go to your instance and set up a service for the SQL database you want to connect to. To do that, go to the admin console for your instance and click on the Services tab. Click the Create button in the upper left then complete the Info tab. SQL DB Info Screen Set the service type to ‘SQL DB’ and enter a name and a label. It’s best to keep the name short and lower case since it’s part of the URL for the REST API. We’ll use ‘sql’ for this example. Click the Config tab to enter connection information. This is where you enter the username and password for your database. Database credentials are stored securely on the instance and never exposed to the client-side app. Lastly you must enter the connection string. The connection string will be pre-populated based on the SQL type, then you can customize it to work for your database. Some setups require additional drivers to be installed. See the wiki for supported database types on the various host operating systems. mysql config page Click the Create Service button at the bottom of the page. The service you just created is for managing both SQL data and schema. To access the table records via the REST API you would use /api/v2/sql/_table/<table_name>. To access the schema you would use /api/v2/sql/_schema/<table_name>. You can grant or deny access to /api/v2/sql/_table and /api/v2/sql/_schema with user roles. For example you might want user A to have permission to perform CRUD operations on table X, but not modify the schema for table X in any way. User B, who is an admin, could have full access to the data and the schema.

Access Control

If you’re using an instance that is hosted by DreamFactory, you may need to provision your remote SQL database to allow access from our hosted system. The IP address for that system is 52.4.195.0.

Testing the Services

Now we’ve created the new service so let’s make sure it’s working. To try it out click the API Docs tab in the admin console. Here you can experiment with the REST API for the service. mysql api_docs To retrieve a list of available tables, find and click the GET button for ‘sql/_table’. Scroll down and click the ‘Try it out!’ button. It shows the full URL for the request which is
http://localhost:8085/api/v2/sql/_table
The response will be an array containing all tables in your database visible to the user provisioned in the service. If you get an error go back and check the settings on your service. If your database had a single table named ‘contact’ you would see
 {
    "resource": [
        {
            "name": "contact"
        }
    ]
}
You can also get a list of tables using the schema service by doing a GET on /api/v2/sql/_schema. In that case the URL is
http://localhost:8085/api/v2/sql/_schema

Adding a New Table

Ok great, so our new SQL service is working. Now let’s add a new table named ‘todo’ to the database. To create a table you POST the schema to /api/v2/sql/_schema. You can use any REST client for this or you can use the Schema tab in the admin console. We’ll be using the API Docs, so go there and click the button for ‘POST /sql/_schema’. To create a table named ‘todo’ the POST data would be
{
    "resource": [
        {
            "name": "todo",
            "label": "Todo",
            "plural": "Todos",
            "field": [
                {
                    "name": "id",
                    "label": "Id",
                    "type": "id"
                },
                {
                    "name": "name",
                    "label": "Name",
                    "type": "string",
                    "size": 80,
                    "allow_null": false
                },
                {
                    "name": "complete",
                    "label": "Complete",
                    "type": "boolean"
                }
            ]
        }
    ]
}
Paste the above schema into the text area and click ‘Try it out!’. If the result indicates success click the button for ‘GET /sql/_schema’, click ‘Try it out!’, and you should see your new table. Now that your table exists you can perform other schema operations such as creating, updating, and deleting fields or deleting the table. This can be done through the API or from the Schema tab in the admin console. The API Docs show the syntax and options for these operations.

Adding Records to a Table

To add a record to the new table you can POST the following JSON data to /api/v2/sql/_table/todo.
{
  "resource": [
    {
      "name": "item 1",
      "complete": false
    }
  ]
}
You can try it out in the POST /sql/_table section of the API Docs. If the ‘table_name’ menu does not show your new table, go to the Config tab in the admin console and flush the system cache.

Retrieving Records from a Table

To retrieve records from the table click on GET sql/_table and set the table name to ‘todo’. Update and delete are very similar, except you must specify the id(s) of the records. These are simple CRUD operations, but we also support relationship queries where you can perform CRUD operations on parent and child tables in a single API call. See the API Docs for examples of how to do this.

Changing Databases

It’s easy to change to an entirely different type of database such as MongoDB just by changing the service name from ‘sql’ to another service. DreamFactory takes care of translating the data to a consistent JSON format allowing the client code to be essentially the same for many different database types. Pretty cool!

Customizing APIs with Server-Side Scripting

In addition to providing the REST API DreamFactory allows you to customize your APIs with server-side scripting. For each database table there’s a pre-process and a post-process script for each HTTP verb. These scripts are created and edited from the Scripts tab in the admin console. They can be written in JavaScript (V8js or Node.js) or PHP. They can be used for things like field validation, business logic, and service orchestration. See the wiki for some examples. Hopefully this has given you a good introduction to using DreamFactory to REST-enable your own SQL databases. It’s easy and secure, and open source. Please add any questions or comments below or contact our support team if you need help!


Get started with DreamFactory with a free hosted DreamFactory development environment. Or, download and run it on the server, cloud, or desktop of your choice.