Creating a Microsoft SQL Server API in Less Than 5 minutes with DreamFactory
by Kevin McGahey • February 19, 2024
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!
Installing DreamFactory
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.
Generating the SQL Server API
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.
Securing and Interacting with the API
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.
Creating a SQL Server API using Node.js and the Express framework
You can also create a REST API for SQL Server using Node.js and the Express framework. While it is not nearly as convenient as using DreamFactory, it is still a useful and practical way to create an API.
Prerequisites
Before we begin, make sure you have the following installed on your system:
- Node.js
- A code editor like Visual Studio Code
Step 1: Create a New Node.js Project
First, create a new directory for your project and initialize it with npm
Step 2: Install required packages
In this step, we will install the necessary npm packages for our project. Each package serves a specific purpose in our API:
express: A minimal and flexible web application framework for Node.js, providing a robust set of features for building web and mobile applications. We will use Express to create our API endpoints and manage HTTP requests.
mssql: A Microsoft SQL Server client for Node.js, which provides an easy-to-use, unified interface for working with SQL Server databases. We will use this package to connect to our SQL Server database and perform queries.
cors: A middleware package that handles Cross-Origin Resource Sharing (CORS) in our API, allowing clients from different domains to access our API. This package is crucial for enhancing security and ensuring that only authorized clients can interact with our API.
body-parser: A middleware package for parsing incoming request bodies in a middleware before handlers. This package simplifies the process of reading data from the client, making it easier to work with JSON payloads in our API.
To install these packages, run the following command in your project directory:
By installing these packages, we set up the foundation for building our SQL Server API. Each package will play a critical role in various aspects of our API, such as handling HTTP requests, connecting to the database, managing security, and parsing request payloads.
Step 3: Set up the project structure
A well-organized project structure is essential for maintaining and scaling your application. In this step, we will create a simple file structure for our API project.
- Create a
.gitignorefile: To prevent unnecessary files, like thenode_modulesfolder, from being included in your version control system, we will create a.gitignorefile in the root of your project. This file lists files and folders that should be excluded from version control. Add the following line to your.gitignorefile:node_modules/ - Create the main application file: In the root directory of your project, create a new file called
app.js.This file will contain the main logic for our API, such as setting up the server, connecting to the database, and defining the routes. We will write the code for this file in Step 4. - (Optional) Create folders for organizing your code: As your API grows, you may want to organize your code into separate folders. For example, you could create a
routesfolder to store your route handlers, acontrollersfolder to store your business logic, and amodelsfolder to store your database models. This organization can make it easier to navigate and maintain your code as your API evolves.
By setting up a clean project structure, you lay the groundwork for a maintainable and scalable API. In the next step, we will write the main application code in app.js.
Step 4: Create the main application file
In this step, we will write the main application code in the app.js file. We will import the necessary packages, set up middleware, configure the database connection, create API routes, and start the server.
Import required packages: At the beginning of the app.js file, import the necessary packages (express, body-parser, cors, and mssql) using the require function.
Configure the Express app and middleware: Create an instance of the Express app and set up the middleware. We will use the cors middleware to handle Cross-Origin Resource Sharing (CORS) and the body-parser middleware to parse incoming JSON payloads.
Set up the database connection: Create a configuration object containing your SQL Server connection details (username, password, server, and database). Use the mssql.connect() function to establish a connection to the database. You should also include error handling to log any connection issues.
Define API routes: Set up your API routes using Express's routing system. In this example, we create a single GET route that fetches data from a specific table in the SQL Server database. You can add more routes for different CRUD operations as needed.
Start the server: Use the app.listen() function to start the server on a specified port. This will make your API accessible on your local machine at the designated port.
Here's an overview of the app.js file with the main application code:
Don't forget to replace 'YourUsername', 'YourPassword', 'YourDatabase', 'your_endpoint', and 'YourTable' with your actual database details and desired endpoint and table names.
After completing Step 4, you should have a working API connected to your SQL Server database. The next step is to run your API and test its functionality.
Step 5: Run the API.
Your API should now be running on http://localhost:3000 (or another port if you changed it) and will be able to serve requests related to your SQL Server database.
You can add more routes to handle various CRUD operations and customize the code to match your specific requirements.
For more information on using the mssql package with Node.js, you can refer to the official documentation: https://www.npmjs.com/package/mssql.
Conclusion
In this blog post, we have shown you how to create a Microsoft SQL Server API in less than 5 minutes using DreamFactory, an effortless and time-saving method for generating APIs. With just a few clicks, DreamFactory automatically builds and manages the necessary endpoints for CRUD operations on your SQL Server database. The simplicity of this approach allows you to focus on building your application without getting bogged down in the intricacies of API development.
However, if you prefer a more hands-on approach or wish to create a custom API tailored to your specific needs, you might want to consider building your API from scratch using Node.js and the Express framework.
Whether you choose to use DreamFactory for a rapid API generation or build your own custom API with Node.js and Express, both methods offer powerful solutions for connecting your applications to a SQL Server database. Ultimately, the choice depends on your requirements, preferred development style, and the level of customization you need for your API.
If you have any questions about the platform, or just APIs in general, we'd love to hear from you! Contact us.
Kevin McGahey is an accomplished solutions engineer and product lead with expertise in API generation, microservices, and legacy system modernization, as demonstrated by his successful track record of facilitating the modernization of legacy databases for numerous public sector organizations.
