Connecting Nodejs to SQL Server with Knex ORM - Part 1

Normally we connect Nodejs to the databases like SQLite, MongoDB, Postgres etc. In this multiple part post, I am going to show you, how we can connect Nodejs with Microsoft's SQL Server using Knex and run CRUD operations.

1. Installation of required packages

In order to connect Nodejs to SQL Server with Knex, we will need following node packages installation:

i. npm install knex

ii. npm install mssql

Once the packages are installed, we can write the following code to connect:

 //Code to KNEX connection settings.

 var knex = require('knex')({  
  client: 'mssql',  
   connection: {  
    user: 'sa',  
    password: 'pwd',  
    server: 'localhost',  
    database: 'Test'  
   }  
 });  

 //Code to query the Table DEPT
 knex.select("*").from("dept")  
 .then(function (depts){  
   depts.forEach((dept)=>{ //use of Arrow Function  
     console.log({...dept});  
   });  
 }).catch(function(err) {  
 // All the error can be checked in this piece of code  
   console.log(err);  
 }).finally(function() {  
   // To close the connection pool  
   knex.destroy();  
 });  

The above example has two parts. Part one does the connection string settings for Knex to connect to SQL Server. Part two actually runs a Select query to select all the rows from table named "Dept". Once the program runs successfully, the output will be, based on table data.
{ DEPTNO: 10, DNAME: 'ACCOUNTING', LOC: 'NEW YORK' }
{ DEPTNO: 20, DNAME: 'RESEARCH', LOC: 'DALLAS' }
{ DEPTNO: 30, DNAME: 'SALES', LOC: 'CHICAGO' }
{ DEPTNO: 40, DNAME: 'OPERATIONS', LOC: 'BOSTON' }
The select query was able to fetch all 4 records from the table named "Dept" in my SQL Server database. The result will vary based on your data and table.
Happy Coding!. | dhandrohit@gmail.com