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!
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.
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.
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.
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 126.96.36.199.
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.
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
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
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
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
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.
"name": "item 1",
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.
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
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!