Add REST to a SQL Database

Add REST to a SQL Database

June 6, 2016 0 By Eric Shanks

If you do a lot of work with orchestration, you’re almost certain to be familiar with working with a REST API. These REST APIs have become the primary way that different systems can interact with each other. How about database operations? How about the ability to use a generic database to house CMDB data, change tracking or really anything you can think of.

I came across a nifty program called DreamFactory that allows us to add an API to our databases. The examples in this post are all around MS SQL Server, but it also has support for PostgreSQL, NO SQL, SQL Lite, DB2, Salesforce and even Active Directory or LDAP.

So my environment has a basic SQL Server with a custom database housing a custom table. Assume that I wanted to be able to create, update, delete entries in this database by using our go to REST API tools.

I signed up for an account with DreamFactory and got my download. After this I spun up my CentOS 7 instance copied over the file and ran it.

Once the package is installed, I was able to login. The first time logging in, I was asked to setup an account. And then login.

DatabaseAPI1

The program has the RBAC stuff you’d expect, but for this post we’re just worried about setting up my API for my SQL Database. Go to Services and create an API. My Service type is SQL DB and then entered the name of the service, a label and description. When done, go to the Config tab.

DatabaseAPI2

Under the Config tab, select the SQL Server driver. After this, replace the Connection String entries to match your environment. The connection string was really easy since they gave you an example to use and you just change the parameters to match your settings. Then enter login credentials to your database.

DatabaseAPI3

Save your service! Now if you go to the API Docs, tab, you’ll be able to browse your service in a Swagger-UI looking browser.

DatabaseAPI4

You can see that my database shows up and I can run test API calls against it.

DatabaseAPI5

 

Now I can go back to my orchestrator appliance or custom web applications to make API calls into my database. This is a pretty neat tool that I wanted to share with the rest of the community. Check this out on your own when you have time.