Build a Secure Node.js App With SQL Server
SQL Server is an awesome relational database. Here’s how to use it from Node.js.
J. Scott Cromie
. Hey there - do you have a working repo for this? I totally understand where you’re going with it and it looks like a lot of thought was put into it, but I am having a problem trying to connect to the DB. It’s almost as if it’s not registering the getConnection() function.
Thanks in advance!
J. Scott Cromie
Hey - I see the repo here at the bottom of the post…so never mind! I’ll have a look at that and see where my code differs. Thanks again for the post - it’s great!
Adan
I am having the exact same issue… getConnection() is not defined. Not sure what’s going on here…
Adan
Solved after days of debugging… Had my input as getPool not getConnection. smh. For some reason I thought that getConnection wasn’t being defined because of a failure to connect to SQL server but was instead a simple reference error. Sigh… #learningsql
// getConnection was getPool -____-
const register = async ({ sql, getConnection }) => {
// read in all the .sql files for this folder
const sqlQueries = await utils.loadSqlQueries(“events”);
const getEvents = async userId => {
// get a connection to SQL Server
const cnx = await getConnection();
// create a new request
const request = await cnx.request();
// configure sql query parameters
request.input(“userId”, sql.VarChar(50), userId);
// return the executed query
return request.query(sqlQueries.getEvents);
};
return {
getEvents
};
};
David Neal
Apologies for the frustration! This tutorial has been corrected to use getConnection.
remaind
This is a great walkthrough, at least as a start…The beginning of it works great, however, I’m running into an issue integrating Okta in the last part…I’m getting a CORS error after authenticating initially:
Access to XMLHttpRequest at 'https://[myurl].okta.com/oauth2/v1/authorize?client_[stuff] (redirected from ‘https://localhost:8080/api/events’) from origin ‘https://localhost:8080’ has been blocked by CORS policy: No ‘Access-Control-Allow-Origin’ header is present on the requested resource.
In chrome, so I think it has to do with the headers being sent by the hapi server rather than Okta. (I added a TLS cert in the hopes that maybe that was the issue). I added routes:{cors:true} to the Hapi.server config object in server.js…but it doesn’t seem like it’s doing anything.
Any insight into why this might be coming up?
Thanks again for the walk through!
David Neal
Hi @remaind ! Thanks for taking the time to walk through the tutorial! I just stepped through the tutorial from scratch to make sure I didn’t miss anything, and it’s working for me. I believe it might be a configuration issue. Check your Okta Application Settings to see if the correct URIs are listed that match your local web application. For example: https://www.screencast.com/… Also, check your .env file settings. The Okta org url should look like https://dev-123123.oktaprev… with “oktapreview” instead of “okta.” The client ID and secret should be alphanumeric strings without the curly braces. Hope this helps!
remaind
Following the instructions, I don’t have the “oktapreview” on my client dashboard…going there after following all the links included in the article takes me to a page that creates the account on .okta.com. When I set it up to go to oktapreview I get client_id not found and I can’t sign into oktapreview either. If I follow the link that gave me issues it bounces me back to localhost. I’ve triple checked my client_ids and app ids, they’re correct (with no quotes around them). when I click on the “log in” it takes me to the log in screen, when I authenticate, I bounce to localhost without the appearance of having logged in.
This was a great starting point for hapi and mssql, but I’ll probably just remove the okta portion and make my own provider.
Nick LaHam
Very nice tutorial. I’m new to js but would like to try this. Any reason this can’t work with MSSQL Server 2008 R2? I’m in the process of upgrading our db but it’s a big undertaking at my company and I would like to build an application that can query from 2008 R2 before we complete our transition to 2016.
Ravindar Chandrasekar
Hi David,
Thank you for the nice tutotrial. I have getting the error in conneting to the database below is the detail ofr the error can you please help me in resolving it.
in the browser
{“statusCode”:500,“error”:“Internal Server Error”,“message”:“An internal server error occurred”}
in the console
c:\Projects\okta>node .
Server running at http://localhost:8080…
TypeError: Cannot read property ‘request’ of undefined
at Object.getEvents (c:\Projects\okta\src\data\events\index.js:14:34)
at process._tickCallback (internal/process/next_tick.js:68:7)
Debug: internal, implementation, error
Error: handler method did not return a value, a promise, or throw an error
at module.exports.internals.Manager.execute (c:\Projects\okta\node_modules\hapi\lib\toolkit.js:48:29)
at process._tickCallback (internal/process/next_tick.js:68:7)
Thank you
hugo
I am having the exact same issue… getConnection(), but not sure…because @adan show one solution not working about
so: const cnx = await getConnection() → console.log ( cnx ) = undefined …next…-> const request = await cnx.request() → console error “Cannot read property ‘request’ of undefined”
David Neal
Hi @ravindarchandrasekar … this is probably a configuration issue with the SQL Server connection. The code has some logging, but unfortunately I did not cover setting up a logger, such as hapi-pino.
As a quick fix, edit the /src/data/index.js file and add console.log(err); inside the catch() block of the getConnection() function. Should be around line 45. Restart the application and try again. You should see a more detailed error message as to why the application is not able to connect to SQL Server.
Hope this helps!
David Neal
@hugo@disqus_PGNVpaKbow:disqus See the reply above to this question. Thanks!
hugo
Hi David,
Thank you for the nice tutorial. I have getting the error
…This happens at the end of the tutorial. I have the connection with https://developer.okta.com/ as you have indicated in tutorial.
after running npm run dev, it returns the error.
Any help is always welcome, can you please help me in resolving it.
console: --------------------------------------------------------------------
startup error: { ValidationError: child “password” fails because [“password” is required]
at Object.exports.process (/home/hcgalvan/geos-nuevo-sql/node_modules/joi/lib/errors.js:203:19)
at internals.Object._validateWithOptions (/home/hcgalvan/geos-nuevo-sql/node_modules/joi/lib/types/any/index.js:764:31)
at module.exports.internals.Any.root.validate (/home/hcgalvan/geos-nuevo-sql/node_modules/joi/lib/index.js:147:23)
at Object.internals.implementation [as cookie] (/home/hcgalvan/geos-nuevo-sql/node_modules/hapi-auth-cookie/lib/index.js:98:25)
at module.exports.internals.Auth._strategy (/home/hcgalvan/geos-nuevo-sql/node_modules/hapi/lib/auth.js:47:47)
at Object.module.exports.register (/home/hcgalvan/geos-nuevo-sql/src/plugins/auth.js:14:16)
isJoi: true,
name: ‘ValidationError’,
details:
[ { message: ‘“password” is required’,
path: [Array],
type: ‘any.required’,
context: [Object] } ],
_object:
{ password: undefined,
redirectTo: ‘/authorization-code/callback’,
isSecure: false },
annotate: [Function] }
[nodemon] clean exit - waiting for changes before restart
------------------
thank you.
David Neal
Hi @disqus_PGNVpaKbow:disqus ,
It sounds like you may not have the SQL Server connection values configured in the .env file. There should be the following values configured to match your SQL database.
SQL_USER=
SQL_PASSWORD=
SQL_DATABASE=
SQL_SERVER=
Hope this helps!
hugo
@dav@reverentgeek thanks for your quick response.
These indicated fields are with values, I will try to change them and evaluate the facts.
hugo
thank your Dave
hugo
–
hugo
Hi @da@reverentgeek solved!!,
The problem related to the non-database connection was because it did not initialize before connecting to the database.
Use the npm execution tasks: initdb and voila!
Thanks for the patience.