CODEWITHSUNDEEP
javascriptsql-servernode.jshapi.js
josh
josh

Reputation: 398

I am trying to connect to connect to my SQL server database with node and hapi.js

I am trying to test out connection to my sql server using node and hapi.js and run a simple query on one of my tables. I am fairly new to node and Hapi so I am just trying to figure this out

so first my folder structure is as follows I have a folder called api which has routes in it and that is where I am doing my routes get_aosdata.js this works I tested it just passing back 'hello world' then I have a query folder under api this is where I am setting my db connections in connection.js

this is what my route currently looks like 

'use strict';
const query = require('../query/connection');

module.exports = {
    method: 'GET',
    path: '/api/query/{id}',
    config: {
        pre: [
            { method: query.getSqlConnection, assign: 'db' }
        ],
        handler: (request, reply) => {
                const request = new db.Request();
                request.query(`SELECT a.OrdNbr, a.sotypeid, a.user6, a.lupd_datetime, a.user3, a.crtd_user, a.S4Future01, a.slsperid, a.totmerch, a.CustOrdNbr from SOHeader a
                              join customer b
                              on a.CustID = b.CustId
                              join SOLine c
                              on a.OrdNbr = c.OrdNbr
                              where sotypeId = 'Q'`, ((err, recordset) => {

                                if (err) {
                                    console.log(err);
                                }
                                reply(recordset);
                              }));

        }
    }
}

I am calling to a prereq to do my connection this is what this code looks like 

const sql = require('mssql');

const dbConfig = {
    server: 'myserver',
    database: 'mydatabase',
    user: 'myuser',
    password: 'mypassword'
}

const getSqlConnection = (request, reply) => {
    sql.connect(dbConfig, ((err) => {
                if (err) {
                    console.log(err);
                }
    }));
            return reply();
}

module.exports = getSqlConnection;

this is my server 

'use strict';

const Hapi = require('hapi');

const server = new Hapi.Server();

server.connection({ port: 3001, host: 'localhost' });

server.route(require('./api/routes/get_AOSdata'));

server.start((err) => {
    if(err) {
        throw err;
    }

    console.log(`Server running at: ${server.info.uri}`);
});

now my error says pre at position 0 does not match any of the allowed types... I am clueless on this error and I cannot find a fix anywhere. I am using hapi and mssql to make the call any help would be appreciated I am new at node and hapi, however I have read a lot on hapi and would really like to use it

Upvotes: 2

Views: 1510

Answers (1)

Joshua Deshazer
Joshua Deshazer

Reputation: 193

this is how I am doing it so I hope it helps 

'use strict';
const sql = require('mssql');
const async = require('asyncawait/async');
const await = require('asyncawait/await');
const boom = require('boom');

const dbConfig = {
    server: 'myserver',
    database: 'mydb',
    user: 'myuser',
    password: 'mypass'
}

const conn = new sql.ConnectionPool(dbConfig);

module.exports = {
    method: 'GET',
    path: '/api/query/{id}',
    config: {
        handler: (request, reply) => {


            conn.connect().then(() => {
              req.query(``SELECT a.OrdNbr, a.sotypeid, a.user6, a.lupd_datetime, a.user3, a.crtd_user, a.S4Future01, a.slsperid, a.totmerch, a.CustOrdNbr from SOHeader a
                          join customer b
                          on a.CustID = b.CustId
                          join SOLine c
                          on a.OrdNbr = c.OrdNbr
                          where sotypeId = 'Q'`).then((data) => {
                  reply(data);
                  conn.close();
                })
              .catch((err) => {
               reply(boom.badRequest(err.message, err));
                conn.close();
              });
          })
          .catch((err) => {
              reply(boom.badRequest(err.message, err));
          });
        }
    }
}

in your case this would be in your routes I think

then your server would look almost the same like this 

'use strict';
const Hapi = require('hapi');
const boom = require('boom');

const server = new Hapi.Server();

server.connection({ port: 3001, host: 'localhost', routes: {cors: true} });

server.route(require('./api/routes/get_AOSdata'));

server.start((err) => {
    if(err) {
        boom.badRequest(err.message, err);
    }

    console.log(`Server running at: ${server.info.uri}`);
});

this could be totally the wrong way but this is how I got mine to work

Upvotes: 2

Related Questions