Sunday, September 17, 2017

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

You can download complete documentation from the link : Download

Happy Coding :)

1 comment: