Authentication with Node.js, JWTs, and Oracle Database

By June 3, 2015 Uncategorized

Authentication isn’t exactly hard, it’s just that there are a lot of moving pieces to consider. Should you provide local authentication or 3rd party authentication via identity providers such as Google and Facebook? Can you reuse an existing credential store like Active Directory? What libraries should you use to help you encrypt passwords and manage logins? Should you use cookie based authentication with sessions or use the emerging JSON Web Token (JWT) standard instead. Are there libraries for JWTs? Which is the best? Okay fine, authentication is hard. But it’s also necessary.

Authentication strategies should be tailored to the application being built. If it’s an internal application you’ll likely want to reuse an existing credential store. Public facing applications will probably use a combination of local credentials and identity providers. In this post I’ll show you how to create a very basic API that authenticates users via local credentials stored in an Oracle Database table and uses JWTs to identify users after they’ve been authenticated. Once understood, the concepts used here could be adapted to better fit your specific authentication needs.

What’s a JWT (pronounced “jot”) you ask? That’s a great question, but it’s not one I’m going to attempt to answer here in full. Suffice it to say, JWTs are an alternative to cookies that can be used to identify users post authentication. With JWTs, claims (such as ones identity) can be encrypted and signed before being encoded and then transferred to and from the client. This avoids the need to create and lookup sessions making JWTs stateless in nature and easy to scale. Also, because JWTs use HTTP headers rather than cookies, RESTful APIs that use JWTs can be used by mobile apps as well as web applications (or anything else that can make HTTP requests). For more information on JWTs, check out these resources:


To complete this tutorial you’ll need to have an Oracle Database, Node.js, and the Oracle Database Node.js driver (v0.5 or better) ready to go. Checkout Up and Running with Node.js and Oracle if you are interested in getting a VM setup with these requirements.

There will not be a front-end application included with this post (though I may release one in a future post), so you’ll need to have access to a REST client that allows you to test making requests and examine the responses. If you use the Chrome web browser and prefer a GUI, I recommend either Postman or Advanced REST client, both of which are available for free in the Chrome Web Store. If you prefer command line tools I recommend cURL.

Database Objects

Let’s kick things off by creating three tables and some supporting objects in the database. Execute this code in the schema that you’ll be connecting to from Node.js using a tool like SQL Developer.

The jsao_users table will contain our users and we’ll use the email and password fields for authentication (passwords will be stored hashed). The other tables, jsao_protected_things and jsao_public_things, will be used to demonstrate creating API routes that do and do not require authentication respectively.

Starting the API

Create a new directory named auth-api where you’re running Node.js. This will be the API’s top level directory. Then create the following package.json file within the auth-api directory.

The package.json is used by NPM to keep track of the dependencies in this project, of which we have 5 (excluding oracledb which should be installed globally):

  1. express is a popular webserver for Node.js.
  2. morgan is a logger for express.
  3. body-parser is an express module that handles parsing the body portion of incoming HTTP requests.
  4. bcrypt is a module used for encrypting and decrypting things in Node.js. If you’re running Node.js on Windows you may run into some compilation errors. If this happens have a look at this GitHub post.
  5. jsonwebtoken is fairly standard module for working with JWTs in Node.js.

Once you’ve created this file you can open the auth-api directory in a terminal window and run:

This will instruct NPM to begin installing the dependancies, which it will place in a new directory named node_modules.

Next we’ll create the server.js file in the auth-api directory.

The server.js file can be thought of as our “main” API file. This is the file we’ll actually be executing with Node.js. As you can see, it brings in a number of dependencies and then starts a web server that listens for requests on port 3000. A router is created and used to listen to GET requests on /api/public_things. The logic that handles the GET request on that route simply responds with a JSON message. Later, we’ll create logic that gets data from Oracle Database. But before we get to that, let’s test everything up to this point.

Open the auth-api directory in a terminal window and run:

If you see the “Web server listening on localhost:3000” message then your web server should be running. Open your REST client and issue a GET request on http://localhost:3000/api/public_things. Here’s an example in Postman:

auth-api test 1

If you see the JSON response with the message “Here are the public things…” then everything should be working.

Creating a Public Route

Time to bring in the database! Create the following config.js file in auth-api. Note that this tutorial will not be using a connection pool. If you plan on running a web server in production you should look into using a connection pool. Also, see my earlier post, Making a Wrapper Module for the Node.js Driver for Oracle Database, for ways to make that easier.

The config.js is used to hold our API’s configuration data. You will likely need to update the connection information to work with your database instance.

Create a subdirectory in auth-api named routes, then add the following publicThings.js file.

publicThings.js requires in the Oracle Database driver and uses it to execute a query and send the results out through the response. The get function is exported from the module which allows us to use it in server.js.

Let’s run another test to tryout the new logic. Stop your previous Node.js process if it’s still running with ctrl + c and then start it back up. Use your REST client to issue the same GET request as before. You should see results like the following:

auth-api test 2

Now users of our API can access the data from the /api/public_things route, awesome!

Creating a Protected Route

Of course not all of our data should be accessed so easily! Let’s create a /api/protected_things route that requires a valid JWT to return data. We’ll start by updating the config.js to include a secret key (shhhh, don’t tell anyone!).

Now we can create our /api/protected_things route logic in a new file named protectedThings.js in the routes directory.

protectedThings.js is different from publicThings.js in two ways. Of course it fetches data from a different table, but in addition, it uses the jsonwebtoken module to ensure that the request has a valid JWT.

With the protectedThings.js in place we just need to update server.js to map the new route through.

Restart your server and try calling this route like we did the last…

auth-api test 3

Ouch, that hurts! 🙁 But at least we know our data is safe… You can try sending fake authorization headers through – they should fail.

Creating Users

This tutorial is about authentication, but before we can authenticate users we have to create some. Add the following users.js file in our routes directory.

The users.js uses bcrypt to first generate a salt and then use it to hash the password. Once the password is hashed the insertUser function is invoked to do the insert into Oracle Database. The insertUser function demonstrates several features of the Oracle Database Node.js driver, such as the ability to use the returning clause (which always returns arrays) and the autoCommit setting (which was set to true). After the user is inserted, the user data is sent to the client along with a JWT access token that was generated with the jsonwebtoken module.

As we did before, we need to update server.js to map new the route through.

Testing this route is a little tricker than before as we are going to be sending some data to the server. The body of the request will be JSON and we’ll need to include the appropriate header to indicate that. Restart your server and give it a shot.

auth-api test 4

Notice that, when done correctly, we get both the user back (with the new id) and a token. A token! You know what we can do with that! Let’s re-test our /api/protected_things route, this time we’ll supply the correct authorization header. Note that the value of the authorization header is the word “Bearer”, followed by a space, and then the token value.

auth-api test 5

It’s bacon!!! 🙂 Typically the token returned would be saved to local storage, assuming the application is a web app, and then added to the HTTP headers of all subsequent requests to the API.

Just incase you were wondering, this is what our new record looks like in our table.

user with hashed password in db

Authenticating Users

The token generated when creating a user will only be valid for 60 minutes (that can be adjusted), after which time requests will receive an error with the message “Token Expired”. At that point the application can redirect the user to a login screen where they can authenticate themselves to get a shiny new token. We’ll create the logins.js in our routes directory to contain this logic.

logins.js fetches the user information, including the hashed password, from the database. It then uses the brcypt module to compare the hashed password to the password that was sent in the POST to /api/logins route. If a match is found then a new token is generated and sent to the client.

As before, we need to update server.js to map the new route through.

If you restart your server and test a POST to the /api/logins route with the correct email and password, you should get the user data and a new token. However, if the email or password are wrong you should get an error message.

auth-api test 6

Streamlining the Authentication and Authorization

Currently the authentication code for the /api/protected_things route is embedded in the route’s logic. This makes it hard to reuse. Also, it’s currently only ensuring that users are authenticated, it’s not doing any authorization. We can fix both issues pretty easily. First, we’ll remove the authentication logic from protectedThings.js. That’s right, strip it out!

Now we’ll create an auth.js file in the routes directory to contain our new, beefier authentication and authorization logic.

Notice that the auth function returns a function (middleware) that handles the authentication and authorization. If both checks pass then next is invoked which executes the next handler on the route. How can we use this new middleware? All we have to do is require it into server.js and invoke it as the first handler for a route that should be protected.

Here you see that the original /api/protected_things route still requires authentication (auth is used but not passed a role). There’s also a new route, /api/protected_things2 that demonstrates how easy it is to ensure that a user has a certain role. Of course this logic could be modified to better meet your authentication and authorization needs.

I hope this helps answer some questions regarding local authentication with Oracle Database and JWTs!


  • Kaelon Egan says:

    Nice work Dan!

  • John Scott says:


    Fantastic, awesome work!

  • Valentino says:

    Actually followed the tutorial from start to finish. Amazing learning experience !
    Dan , it’s been almost a year since you wrote this. Did you get any new experiences that you would improve authorization and that is not mentioned in this guide.

  • danmcghan says:


    Almost a year, wow! Honestly I haven’t revisited this topic for a while, so nothing new to add so far…

  • Femi says:

    Hi Dan,
    This is a very useful article and has surely put my on the right track after a few baby steps.
    I do want to ask though, is there any library you’re aware of that helps create/verify JWT’s… In PL/SQL (I forgot to add)

    • danmcghan says:

      Hi Femi,

      I’m glad it helped!

      Sorry, I’m unaware of a PL/SQL based solution for this. But I’d say the correct place for this logic is in the middle tier since you’re trying to secure routes there, no?


  • jordan says:

    When give a shot, i received “Cannot GET /api/users” .
    What is the probelm?

    • danmcghan says:

      Hi Jordan,

      This tutorial doesn’t have a route handler for GET on /api/users. There’s a handler for POST on /api/users. Perhaps that’s what you meant to do?


Leave a Reply