by Kevin McGahey • June 3, 2019
Do you have a ton of data sources and do not know how to expose them? Do you know you need a Microsoft SQL Server API but don’t know where to begin to build it? Look no further, DreamFactory can take any database and generate a fully documented and secure REST API faster than making a sandwich. All you need is your database credentials and DreamFactory will handle the rest, instantly generating Swagger API documentation and securing your API by way of API keys. Follow along with the blog or our video below!
In order to get started you must install DreamFactory, which is Open Source and gives you the ability to try out a numerous amount of popular databases. To spin up your own hosted environment for free click here.
Did you know you can generate a full-featured, documented, and secure Microsoft SQL Server REST API in minutes using DreamFactory? Sign up for our free 14 day hosted trial to learn how! Our guided tour will show you how to create an API using an example database provided to you as part of the trial!
Create a Microsoft SQL Server REST API Now
Now the fun part. Once you have DreamFactory up and running you will be on the Admin panel. From there you will navigate to the Services tab to connect your database for your API. For example I have selected a Microsoft SQL Server database but it is nearly identical for any database you will be connecting to. Connecting your database typically only requires filling out these 5 fields as shown below.
Upon saving there will be a success window pop-up saying “Service saved successfully”. What it doesn’t tell you is all the magic it just did behind the scenes. In just that short amount of time, it generated your REST API. So now if you navigate to the API Docs tab you can see your new documentation for the API and actually interact with it via the “Try it out” button.
I can end it here now that you have generated your API, but where is the fun in that? Now let’s actually see the API in action! First things first, let’s generate an API key to be paired with the API for security purposes. DreamFactory does not allow access to the API without being authenticated. Let’s navigate to the Roles tab and create a Role for our API. This Role with correspond with the API key so different users can have different privileges based off different keys.
For my Role I have pointed it to the SQL Server Service we just created and told it to only allow GET calls on the endpoints. This will ensure anyone using this API key will not be able to, for example delete data from the database. We must now link this Role to an API key. If we now go to the Apps tab we can create a new API key with the corresponding Role.
Once we hit save we are able to see the API key generated for our use. Just to show how it works I will be using Insomnia, a popular HTTP service, to call our DreamFactory API. I will call the customers table, passing the API key in the headers for authentication.
As you can see I have access to our new API and how much time did that take? Way less time than building this API yourself! If you would like to find out exactly how much time and money DreamFactory can save you, check out our API calculator. Otherwise what are you waiting for? Go build your next application using DreamFactory already!
DreamFactory 4.8.0 has been released! This release focuses on user experience, notably with regards to database API generation. The most popular database connectors (MySQL, MariaDB, PostgreSQL, MS SQL Server, and Oracle) have long included a lengthy list of options, and it hasn’t been obvious which are required and which are optional. To remedy this we’ve broken the service creation form into three sections: Basic, Caching, and Optional Advanced Settings. Additionally, because the Services tab is the natural first tab new users should be clicking on after logging in, we’ve moved the tab to the second position in the navigational bar directly following the Home tab.
In upcoming releases users will see a stream of additional UX improvements intended to more effectively guide new users through the API generation process. Notably, for most basic use cases the administrator completes three tasks: generate the API, create a role-based access control (RBAC), and then associate the RBAC with a newly generated API key. Therefore after successful API generation users will be presented with a new view enumerating typical next steps. We’re also working on improving the service profile detail page, providing admins with a link list taking them directly to the service’s relevant other administrative features, such as API Docs and associated roles.
If you have any questions about the platform, or just APIs in general, we’d love to hear from you! Contact us.
Related reading: MySQL or MS SQL? Which Reigns Supreme?
Join the DreamFactory newsletter list.