SQL DB REST APIs in Minutes, not Months

Would you like to access SQL data from your mobile, web or IOT apps?

If so, DreamFactory offers 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, SAP Hana, MemSQL and MongoDB! All you have to do is use the DreamFactory platform to connect your database, then use it to auto-generate a REST API for your database – it’s that simple!

In this blog post we’ll show how to REST-enable any SQL database, which is free forever for the databases and other services covered by our open source software. Then we’ll show some simple examples of how to use the REST API to manage your SQL schema and data.

Interested in a live demo with one of our engineers? We’ll be happy to show you how it’s done for your particular use case! If you’d rather watch a video, check out DreamFactory Academy. Alternatively, you might want to check out our new guide, Getting Started with DreamFactory. It offers a comprehensive walkthrough of generating a database-backed API. You’ll learn how to generate the API, define a role and API key, and then interact with the API using a variety of queries. Finally, if you haven’t already checked out our free open source software, you can download it here.

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 your instance’s administration console and click on the Services tab. Click the Create button in the upper left then complete the Info tab.

The DreamFactory info tab

Set the service type to the desired database type 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 application. Keep in mind some additional drivers may need be installed. See the wiki for supported database types on the various host operating systems.

The DreamFactory config tab

Click the Save button at the bottom of the page. The service you just created is for managing both SQL data and schema. Once created, you can access the table records via the REST API using the endpoint /api/v2/sql/_table/table_name. To access a table schema you would use /api/v2/sql/_schema/table_name. Further, using DreamFactory’s role manager you can grant or deny access to any exposed part of the API. 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.

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.

The DreamFactory API Docs interface

To retrieve a list of available tables, find and click the GET button for /_table. Scroll down and click the Try it out button. It shows the full URL for the request which is /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 consisted of a single table named contact you would see:
{
    "resource": [
        {
            "name": "contact"
        }
    ]
}
You can also get a list of tables using the schema service by executing a GET request against /api/v2/sql/_schema. In that case the URL is:
/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 /_schema. To create a table named todo the POST request payload 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"
            }
        ]
    }]
}
Click Try it out, paste the above code into the text area, and press Execute. The table will be created and you’ll receive the following response in return:
{
    "resource": [
        {
            "name": "todo"
        }
    ]
}
You can view the newly created table schema by navigating to the GET /_schema/{table_name} endpoint, pressing Try it out, and entering todo into the table_name field.

Adding Records to a Table

To add a record to the new table you can POST the following JSON payload to /api/v2/sql/_table/todo:
{
    "resource": [
        {
            "name": "item 1",
            "complete": false
        }
    ]
}
You can try it out in the POST /_table/{table_name} section of the API Docs. Be sure to enter the todo table name in the table_name field.

Retrieving Records from a Table

To retrieve records from the table click on GET /_table/{table_name} and set the table name to todo. The update and delete methods are very similar, except you must specify the id(s) of the records. These are simple CRUD operations, but DreamFactory also supports relationship queries where you can perform CRUD operations on parent and child tables in a single API call. See the API Docs section 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 your currently chosen database option 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.

Conclusion

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.


Get started with DreamFactory commercial edition by starting a free trial. Or, download and run it on the server, cloud, or desktop of your choice.