Knex.js with Azure SQL/MSSQL Server

Knex.js is a SQL query builder for MSSQL, Oracle, MySQL, Postgres and SQLite3. It supports both callbacks as well as a promise interface for streaming data.

The following features are supported by knex.js
a) SQL Query builder
b) Schema builder
c) Joins, Groping, Select, Where and etc
d) Transactions & Savepoint
e) Schema Migrations

f) SQL Raw query

g) Batch Insert

You can find complete details about knex.js @ http://knexjs.org/

Steps to connect to Azure SQL / SQL Server

1. Install Node.js from https://nodejs.org/en/download/
2. Create folder “Knexmssql”
3. Navigate to folder “knexmssql”
     Ex: d:\knexmssql >
4. Install following npm modules
         a) d:\knexmssql > npm install –save express
         b) d:\knexmssql > npm install –save body-parser
         c) d:\knexmssql > npm install –save knex
5. Install “mssql@3.3” node module
         a) d:\knexmssql > npm install –save mssql@3.3
6. see the following knex mssql configuration for Microsoft azure

1.      var knex = require('knex')({

2.      client: 'mssql',

3.      connection: {

4.        server : 'xxxx.database.windows.net',

5.        user : 'xxxx',

6.        password : XXXX',

7.        options: {

8.            port: 1433,

9.            database : 'xxxx',

10.          encrypt: true  // mandatory for microsoft azure sql server

11.      } 

12.    }

13.  });

7. create server.js file under d:\knexmssql and copy following code

 var express=require('express');

 var bodyParser=require('body-parser');

 var port=process.env.PORT || 8100;

 var knex = require('knex')({

    client: 'mssql',

    connection: {

      server : 'xxxx.database.windows.net',

      user : 'xxxx',

      password : xxxx',

      options: {

          port: 1433,

          database : 'xxxx',

          encrypt: true  // mandatory for microsoft azure sql server

      } 

    }

  });

 var app=express();

 app.use(bodyParser.json());

 app.use(bodyParser.urlencoded({extended:false}));

 app.get('/getdata',function(req,res){

    // knex.raw('select * from Users').then(function(todos){

    //     res.send(todos);

    // })

    knex.select().table('Users').then(function(todos){

            res.send(todos);

        })

})

app.listen(port,function(){

    console.log("listen to port:",port);

});

8. Start node server using following command

    d:\knexmssql> node server.js

9. Go to Browser/Postmen with following url

    http://localhost:8100/getdata

You can download complete documentation from the link : Download

Happy Coding :)