Working With Databases
Querying SQL Server in Node.js Using Async/Await
A Cleaner Way To Query A Database
Since database operations (CRUD, Create, Read, Update, Delete) require time to perform, they require asynchronous capabilities of JavaScript. This could be done with callbacks, promises or async/await.
In this article we will focus on using async/await to query (the R in CRUD) a SQL Server database.
Async/await is a special syntax to work with promises.
Assumptions
In this article I will assume you already have access to a SQL Server database with at least one table that you want to query or can build the table we will use (script below.)
If needed,
- SQL Server Express and Developer Editions are free! and can be downloaded here.
- SQL Server Management Studio, SSMS, is also free! and can be downloaded here.
I will be writing this using Visual Studio Code (VSCode)
- VSCode is free! and can be downloaded here.
You will also need to have node.js installed.
- Node.js can be downloaded here.
To keep this article short and to the point, I will not be covering how to install and configure these items. Just what is needed for this article.
The Setup
My SQL Server Setup
On your SQL Server Instance we will build the Company database and Employees table.
- Create the database Company on your instance using SSMS or the script below.
USE [master]
GO
/****** Object: Database [Company] Script Date: 4/20/2020 7:48:00 AM ******/
CREATE DATABASE [Company]2. Create the Employees table using the following script.
USE [Company]
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Employees](
[EmpId] [char](6) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Title] [varchar](50) NULL,
[HireDate] [datetime] NULL,
[PerformanceRating] [int] NULL,
[Phone] [char](10) NULL,
[Manager] [char](6) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOALTER TABLE [dbo].[Employees] ADD CONSTRAINT [DF_Employees_PerformanceRating] DEFAULT ((0)) FOR [PerformanceRating]
GO3. Feel free to enter any data you wish in to the table or run the following script.
USE [Company]
GO
INSERT [dbo].[Employees] ([EmpId], [FirstName], [LastName], [Title], [HireDate], [PerformanceRating], [Phone], [Manager]) VALUES (N'000001', N'Fred', N'Jones', N'Lawyer', CAST(N'2019-01-01T00:00:00.000' AS DateTime), 4, N'8045551212', N'000005')
GO
INSERT [dbo].[Employees] ([EmpId], [FirstName], [LastName], [Title], [HireDate], [PerformanceRating], [Phone], [Manager]) VALUES (N'000002', N'Jane', N'Jones', N'Lawyer', CAST(N'2018-02-01T00:00:00.000' AS DateTime), 4, N'4567653434', N'000005')
GO
INSERT [dbo].[Employees] ([EmpId], [FirstName], [LastName], [Title], [HireDate], [PerformanceRating], [Phone], [Manager]) VALUES (N'000003', N'Jill', N'Hans', N'Paralegal', CAST(N'2018-03-01T00:00:00.000' AS DateTime), 3, N'4546667777', N'000006')
GO
INSERT [dbo].[Employees] ([EmpId], [FirstName], [LastName], [Title], [HireDate], [PerformanceRating], [Phone], [Manager]) VALUES (N'000004', N'Frank', N'Brown', N'Clerk', CAST(N'2019-02-01T00:00:00.000' AS DateTime), 4, NULL, N'000006')
GO
INSERT [dbo].[Employees] ([EmpId], [FirstName], [LastName], [Title], [HireDate], [PerformanceRating], [Phone], [Manager]) VALUES (N'000005', N'Jillian', N'Hill', N'Manager', CAST(N'2019-01-01T00:00:00.000' AS DateTime), 4, N'8886667777', NULL)
GO
INSERT [dbo].[Employees] ([EmpId], [FirstName], [LastName], [Title], [HireDate], [PerformanceRating], [Phone], [Manager]) VALUES (N'000006', N'Billy', N'Smith', N'Manager', CAST(N'2019-02-01T00:00:00.000' AS DateTime), 3, NULL, NULL)
GOThe Employees table should look as follows.
Our Objective
Our goal will be to retrieve the EmpId, FirstName, LastName and Title of Employees with a PerformanceRating of 4.
The SQL Query
We will use the following SQL Select Query shown here in SQL Server Management Studio (SSMS.)
Let’s Get Started
I will assume you are already setup to run node.js. If not, here is a great tutorial for getting set up using VSCode.
- Create a the folder in which you wish to work
- Open VSCode and from the File menu open that folder.
- Go to the Terminal Menu and choose New Terminal.
4. To create the default package.json and set index.js as the default startup file, type the following in the Terminal window.
npm init -y5. To work with SQL Server, we need to install the mssql drivers. These will be stored in the node_modules folder. In the Terminal window type,
npm install mssqlOur Code and Result
Since our package.json is using index.js as the main file, create a file called index.js.
First we will import the mssql drivers and set up our configuration required to connect to our SQL Server instance and database. My configuration is below. Adjust this for your environment. Place this at the top of index.js
const sql = require('mssql');const config = {
user: 'your user',
password: 'your password',
server: 'LENOVOLAPTOPW10\\MSSQLSERVER01',
database: 'Company',
port: 1433 };
About Async/await
- The word async before a function means the function always returns a promise.
- The await keyword works only inside async functions and makes JavaScript wait until the promise is resolved and returns its result.
Next let’s create our async function. It will be empty for now. We will be passing the desired PerformaceRating as a parameter to this function.
Place this below your config code.
async function queryDb (queryParm) {
// We will connect to SQL Server
// Query the database
// Return an Array of data}
As mentioned previously an async function, queryDb in this case, will return a promise. Therefore we can use .then() once the promise is resolved to work with the result.
Type the following below the async function. We will add a .catch() later.
queryDb(rating)
.then(result=>{
// Work with the result here
})The function call queryDb(rating) will invoke the async function queryDB(queryParm), passing a PerformanceRating, rating. Once the promise is resolved, the result will be returned. We can use .then() to accept the result and work with it.
Next, above the async function, let’s establish a few variables.
let employees=[];
let rating=4;The employees Array will store the records returned from the query. The variable rating is the PerformanceRating we are looking for.
Code So Far
To this point, your code should look as follows,
const sql = require('mssql');
const config = {
user: 'your user',
password: 'your password',
server: 'LENOVOLAPTOPW10\\MSSQLSERVER01',
database: 'Company',
port: 1433 };let employees=[];
let rating=4;async function queryDb (queryParm) {
}queryDb(rating)
.then(result=>{});Making The Connection and Querying
We are now ready to ,
- Establish a connection pool.
- Use the pool to request that a query be run.
- Handle the returned data.
Below is the complete code.
- Code has been added to the async function queryDB().
- A .catch() has been added to the queryDB() invocation
After running the code we will discuss it.
const sql = require('mssql');
const config = {
user: 'your user',
password: 'your password',
server: 'LENOVOLAPTOPW10\\MSSQLSERVER01',
database: 'Company',
port: 1433 };let employees=[];
let rating=4;async function queryDb (queryParm) {
let pool = await sql.connect(config);
let data = await pool.request()
.input('pr', sql.Int, queryParm)
.query("Select FirstName,LastName, Title from Employees where PerformanceRating=@pr"); // Store each record in an array
for (let i=0;i<data.rowsAffected;i++){
employees.push(data.recordset[i]);
} pool.close;
sql.close; return employees;}
// async function invocation
queryDb(rating)
.then(result=>{result.forEach(item=>{
console.log(item);
});})
.catch(err=>{
pool.close;
sql.close;
console.log(err)
})Run The Code
Use F5 or CTRL+F5 to run the code. You should get an array of Objects.
Viola! Data
{ FirstName: 'Fred', LastName: 'Jones', Title: 'Lawyer' }
{ FirstName: 'Jane', LastName: 'Jones', Title: 'Lawyer' }
{ FirstName: 'Frank', LastName: 'Brown', Title: 'Clerk' }
{ FirstName: 'Jillian', LastName: 'Hill', Title: 'Manager' }Explanation
Getting it started
- We imported the SQL Server drivers.
- Established the configuration needed to connect to our instance and database.
- We invoked the async function queryDB, passing the rating of 4.
Inside the async queryDB function
- We established a connection pool, passing it the config information needed to make the connection. We awaited the connection before executing the query.
- We created a transaction request, storing the rating in an input parameter named “pr” and specifying the query SQL. We awaited until the request returns the records to the variable data.
- We then accessed the data variable’s rowsAfftected and recordset to push each recordset object in to an Array.
- Finally we closed the connections and returned the Array, employees, to the queryDB invocation, resolving the promise.
Inside The queryDB invocation
- We use .then() to access the data returned from the async queryDB function. This is an Array and is stored in the result parameter of .then().
- We then output the contents of the array. This, of course, is where we might want to do more with the data.
That’s It!
Conclusion
We learned how to query a SQL Server database and a learned a bit about async/await.
As we can see using async/await gives us a very clean way of handling asynchronous operations, in our case with database transactions.
Could we do more? Certainly.
I encourage you to experiment and expand on this example
Thank you for taking the time to read and code along!
If you have problems or questions on any of the items mentioned, send a response. My goal is your success.
Read all you want and help me continue writing by joining Medium.
