Node write & read data with Azure MS SQL database from routes

Question

I'm trying to insert data into my Azure database then read it. I created a separate data configuration and global connection then I can access it from anywhere within the app. The server was connected, however, when I wrote an insert function in a route, I met this error: db.Request is not a constructor in the route index.js. Here are my codes:

dbconfig.js:

const sql = require('mssql');

const config = {
  user: 'admin',
  password: 'admin',
  server: 'admin.database.windows.net',
  database: 'em-cryptolend-db',
  // If you are on Microsoft Azure, you need this:
  options: {encrypt: true}
}

const connection = sql.connect(config, err => {
  if(err) throw err;
  else console.log("SQL Server connected");
})

module.exports = connection;

app.js:

const http = require('http');
const express = require('express');
const exphbs = require('express-handlebars');
const path = require('path');
const bodyParser = require('body-parser');
const expressValidator = require('express-validator');
const sql = require('mssql');
const request = require('request');

// Express app setup
const app = express();

// BodyParser Middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

// Express Validator
app.use(expressValidator());

// Routes
const routes = require('./routes/index');
const users = require('./routes/users');

app.use('/', routes);
app.use('/users', users);

// View engines
app.set('views', path.join(__dirname, 'views'));
app.engine('handlebars', exphbs({defaultLayout:'layout'}));
app.set('view engine','handlebars');

// Static folder
app.use(express.static(path.join(__dirname, 'public')));

// Listen to the server
const port = process.env.PORT || 1337;
const server = app.listen(port, function(){
    console.log("Server started on port " + port);
});

routes/index.js:

const express = require('express');
const router = express.Router();
const db = require('../dbconfig');

// Get homepage
router.get('/', function(req,res) {
  var request = new db.Request();
  request.query("INSERT INTO dbo.lendbook (id, rate, amount, period, timestamp, type, frr) VALUES (8, 0.6, 5, 4, 2009-11-11 13:23:44.000, 'eth', 'bbb' )").then(function(recordset) {
    console.log('Recordset: ' + recordset);
    console.log('Affected: ' + request.rowsAffected);
  }).catch(function(err) {
    console.log('Request error: ' + err);
  });
});

module.exports = router;

versions: node: 6.9.1 mssql: 4.0.4

Thank you in advance.

Aaron Chen · Accepted Answer · 2017-09-27 09:56:11Z

1

Please change your code as below:

dbconfig.js

const sql = require('mssql');

const config = {
  user: 'admin',
  password: 'admin',
  server: 'admin.database.windows.net',
  database: 'em-cryptolend-db',
  // If you are on Microsoft Azure, you need this:
  options: {encrypt: true}
}

module.exports = config;

routes/index.js

const express = require('express');
const router = express.Router();
const config = require('../dbconfig');
const sql = require('mssql');

// Get homepage
router.get('/', function(req, res) {

  sql.connect(config).then(function(err) {  
    var request = new sql.Request().query("INSERT INTO dbo.lendbook (id, rate, amount, period, timestamp, type, frr) VALUES (8, 0.6, 5, 4, 2009-11-11 13:23:44.000, 'eth', 'bbb' )").then(function(recordset) {
      console.log('Recordset: ' + recordset);
      console.log('Affected: ' + recordset.rowsAffected);

      res.status(200).send('Inserted successfully.');
    }).catch(function(err) {
      console.log('Request error: ' + err);

      res.status(500).send('Request error: ' + err);
    });
  })      

});

module.exports = router;

answered Sep 27, 2017 at 9:56

Aaron Chen

9,9701 gold badge20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hoàng Nguyễn Over a year ago

Thank you for your solution. The data was inserted into my table and the terminal console looks fine, but on browser there is this error: "Global connection already exists. Call sql.close() first."

Hoàng Nguyễn Over a year ago

I figured it out that I have to input the sql.close() after the request. Nevertheless, my further goal is to create a global connection pool in a separate file then I can access it and just make requests from different routes, so I don't have to make single connection in different routes.

Collectives™ on Stack Overflow

Node write & read data with Azure MS SQL database from routes

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related