by Jeremy H
• June 30, 2020
Let’s dive into how to code a REST API for Microsoft SQL Server. You have the perfect application in mind. It has an attractive UI and achieves everything the customer wants. However, it needs to connect with a Microsoft SQL Server database, so you have to start working on the data model and API.
Will you spend a month hand-coding your SQL Server API the hard way? Or will you choose an affordable solution that auto-generates the API in minutes?
Did you know you can generate a full-featured, documented, and secure 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 MySQL database provided to you as part of the trial!
Create a REST API Now
In this article, we’ll look at the steps, timing, and cost of hand-coding a bespoke SQL Server API. Then we’ll show you how to automatically generate a SQL Server API in minutes with DreamFactory. Believe it or not, DreamFactory can automatically cook up a fully-documented REST API faster than you can prep a bowl of ramen noodles.
Use these links to navigate the article:
Based on our experience hand-coding APIs – and using the DreamFactory API gateway to auto-generate APIs – we’ve summarized labor cost and timing estimates for both solutions in the table below. For a more specific estimate based on your unique project details, try our API cost calculator.
*Outsourcing development of a bespoke REST API can cost from $25,000 to $35,000 per API.
Hand-coding an API is more involved than simply developing an interface for your database. In this section, we’ll look at the steps developers take when creating a bespoke REST API for SQL Server. This process takes approximately 30 days in total. The total project (and each step below) could take more or less time than our estimates depending on complexity.
The first step is research. Gather specific information about the project. What databases or data sources will the API expose? Does the API need to integrate with third-party services? What else do you need to know for the API? Reserve five (5) days for researching the API.
If the SQL Server database doesn’t already exist, you’ll need to design the database schema and upload data. Complex databases could take two weeks or longer to build. Simpler databases take one or days. Consider reserving at least two working weeks (10 – 12 days) for designing a relatively complex database.
Develop your first draft of the API. This will serve as a basic prototype for testing. The results of testing will provide valuable information for refinements and adjustments. Reserve five (5) days for prototyping your SQL Server API.
From the API prototype, finalize a minimum viable product (MVP) for alpha testing. At this stage, the API should have its essential functions in place. Reserve six (6) days for completing the MVP.
Here you will secure access to the API and prevent the chance of a data breach. Generally, modern API security includes API keys and role-based access control (RBAC) at a minimum. Reserve one (1) day for adding API keys and RPAC to the API.
We recommend documenting your API with Swagger for interactive documentation that syncs with your API inputs, outputs, and endpoint descriptions. Interactive documentation is especially valuable because developers can refer to it while building apps that consume the API. Reserve two (2) days for creating your interactive documentation.
Here’s a screenshot of the Swagger UI. Note the interactive documentation on the right:
Restricting API interactions with rate limiting and other strategies saves on data access costs, prevents hackers from attacking the system, and circumvents malicious bot activity. Reserve one (1) day for configuring API rate limiting and other API restrictions.
Meeting your industry’s data compliance standards – like GDPR, CCPA, etc. – generally requires an API traffic logging strategy so industry regulators can audit API activity. Reserve two (2) days for setting up API logging.
Setting up real-time monitoring is vital to ensure optimum performance and reliability. You’ll want to configure an API monitoring dashboard that displays:
You’ll also want to configure alerts that notify you when problems arise. Reserve three (3) days for setting up monitoring and alerting.
An in-house API developer earning $40/hour will cost approximately $6,400 for the month of labor required to build a custom SQL Server API. Outsourcing the job to a contractor costs more – anywhere from $15,000 to $25,000 for the average REST API.
Costs will vary based on the complexity of your API, as this developer cleverly notes on Quora:
If you have a clear picture of your API project – and you want to know more precisely how much time and money it will cost to hand-code your API – try out our API development calculator.
When calculating expenses, remember that API development involves significant costs beyond labor. For example, adding a 30-day task to your development timeline represents a massive time-to-market delay. This interferes with business agility as it slows down your ability to respond to changing conditions. It also interferes with the number of projects your team can finalize each year, which slows down growth and debilitates your bottom line.
Software development companies that continue to hand-code APIs are currently competing against developers using automatic API generation tools (like DreamFactory). These tools completely eliminate the task of manual API development, so developers can offer dramatically lower prices and faster time to market. If your team continues to hand-code APIs the traditional way, you’re literally being left in the dust.
DreamFactory is an API Gateway that includes a stunning administrator panel for building and connecting modern microservices-based infrastructures. Most importantly, DreamFactory can automatically generate REST APIs with full Swagger documentation from any database.
Just feed your Microsoft SQL Server database credentials into DreamFactory and the platform instantly generates the API you need. This eliminates weeks of developer hours and tens of thousands of dollars in labor costs from your API development workflow. In most cases, you can generate a fully-documented REST API secured by API keys in less than 15 minutes.
Here’s what one user says about DreamFactory’s automatic REST API generation feature:
“DreamFactory REST API in seconds? Yeah, that’s true!! Live documentation of REST API’s is a pain for developers. DreamFactory solved this using Swagger-UI. This is the most interesting and lovable thing done by DreamFactory.” –Nagarjuna Y.
Here are the steps required to generate a SQL Server API with DreamFactory:
1. Install DreamFactory (5 Minutes)
DreamFactory includes pre-built connectors for the most popular databases – such as Microsoft SQL Server – and a drag-and-drop administrator dashboard. All you need is a local computer or hosted environment to install DreamFactory, and you’re ready to go.
Click here for a free, 14-day hosted trial of DreamFactory (no credit card required). We’ll set you up with a hosted environment to run DreamFactory, send you a link and password, and you’ll be spitting out SQL Server APIs faster than you can brew a cup of coffee.
2. Generate Your Microsoft SQL Server API (2 Minutes)
After logging into your DreamFactory instance, you’ll find the administrator dashboard. Next, find the Services tab. Select SQL Server from the dropdown, fill out the fields, and connect to the database.
Save your entries. Look for the pop-up that says “Service Saved Successfully,” and you’re done! As simple as that, you have a fully-documented SQL Server Rest API. Click the API Docs tab to find the Swagger documentation. Click the “Try It Out” button to test the API immediately.
Users rave about the speed and convenience of DreamFactory’s automatic REST API generation feature:
“APIs are built for you – and not just any APIs. Fully documented, fully secured (OAuth, LDAP, Active Directory, SAML Integration…) and accessible through a 3rd party client.” –Steve W.
“Fastest and easiest API Management tool my department has ever used. We used DreamFactory to build the REST APIs we needed for a series of projects and were able to save tens of thousands on the developer hours it would have taken otherwise.” –Mark A.
“We are a logistics company that leverages technology to stay ahead of the competition. We used to develop applications from scratch and it used to take a lot of time. But after using DreamFactory the work became extremely simple and APIs could be developed in just almost no time. It helped us move ahead especially with online forms filing work and saved a lot of fines for the organization.” –Astha S.
3. Secure the API with API Keys and RBAC (3 Minutes)
Next, we’ll secure the API with an API Key and Role-Based Access Control (RBAC). Click the Roles tab and select “Create” to set up a Role for the API. Under the Service column, point the Role to your newly-made SQL Server Service. Under the access column, limit access to GET calls. This allows the Role to read the database but not make any changes. Save the Role and you’re ready to create the API Key.
Click the Apps tab and select “Create” to generate an API Key. Complete the fields, assign the Key to the Role, save, and the API Key is ready!
4. Try Out the API
To demonstrate the API in action, we’ll use an HTTP service, Insomnia, to call the DreamFactory API. In the example below, we called the customers table. Notice how the API key is identified in the headers for authentication.
Look at that! A fully-documented, fully-secured, ready-to-use Microsoft SQL Server REST API in 11 minutes flat! Not only is DreamFactory fast, but it’s a joy to use. Does it get any better?
One of our primary missions at DreamFactory is to eliminate the costs and delays associated with traditional API development. Not convinced? Start a free hosted trial and see why we find it hard to believe that anyone would still choose to hand-code an API when they can use DreamFactory to generate one in minutes.
While some use-cases for hand-coding persist, in most scenarios, DreamFactory’s automatic API generation tools can dramatically speed up your time to market, lower your app development costs, and help you stay competitive in a market where rapid API development is essential.
Here we’re generating a REST API for SalesForce:
We know you’re going to love saving time and money with DreamFactory’s automatic API generation capabilities – but you don’t have to take our word for it. Use your free hosted trial of DreamFactory (no credit card required) and build your API now! Contact us with any questions. Our engineers are happy to give you a personalized tour.
Join the DreamFactory newsletter list.