How To Set up Nodejs with a Database
How To Set up Nodejs with a Database

How To Setting up Nodejs with a Database

I could have named this tutorial “setting up Nodejs with MySQL” however I wanted to make the name a little more encompassing because I think for most applications MySQL is a great choice for Nodejs.

MySQL has been around for 21 years and so in early Nodejs apps it was often overlooked for some new and shiny NoSQL choices such as MongoDB. However with the benefit of hindsight, MongoDB turned out to be more hype than substance and is well known for a few major security issues and performance issues. If you don’t believe me, search Hacker News for “mongodb”.

If you would like a DB, even for NoSQL, MySQL is a great choice. It has great support, decent performance and there are some great NPM modules for interfacing with it. Furthermore there are MySQL adapters for pretty much everything.

In this tutorial, I will show how to setup MySQL with Node using Knex in order to create a crude app for creating and logging in users.

We will

  • Install Nodejs
  • Install MySQL
  • Create a HTTP API for writing to the database
  • Create some HTML and JS to POST to the API
  • Use Knex migrations to create a user database schema (a similar process to Rails migrations)
  • Use a migration to setup password hashing
  • Create a login route to check the hashing works

In the next tutorial we will add more functionality to the app and explore some more complex querying. Eventually we will deploy the app somewhere.

Installing Nodejs

If you have completed the previous tutorials, you probably have Nodejs installed so you can skip this section.

We will be using NVM to manage nodejs so that we can easily install and switch between different versions.

Before installing NVM make sure you have a .bashrc file in your home directory (even if the file is empty).

To install NVM just run this command (same as in the NVM installation instructions).

This command pulls down a script from a remote URL and runs it.

You now have NVM! But if you run nvm ls you will notice it isn’t found. This is because NVM adds some code to your ~/.bashrc. This file is a special file that is run every time you log in to your instance, so to get NVM running you could logout and login again. However you can just run the file manually by using the source command.

Now running nvm ls works! But there aren’t any nodejs versions installed! To get the latest version, just nvm install <latest version number>.

You have nodejs installed! Check that it’s there by looking at the version

Installing MySQL

If you are using Mac, you should install MySQL using Homebrew. If you are using another platform just Google something like “installing MySQL on my platform” for instructions.

Install Homebrew on mac by running this in terminal

Once installed, update Homebrew and install MySQL

Run the MySQL daemon

Set a rudimentary password for MySQL locally, there is no need to worry about security because it will only be accessible from your machine (I used “password”).

Once installed and running, MySQL is accessible to any app running locally on your machine.

In this tutorial, we will use Sequel Pro to manage our database. Download and install Sequel Pro and open it. The database to connect to is on localhost so set “host” to “”. Username is “root” and password the same as chosen in the previous step, for me that’s “password”.

Connect and you will be presented with an empty MySQL. If the connection does not work, make sure you modified your password correctly in the previous step and that the mysql service is still running.

After successfully connecting, click Database > Add Database and name it, I chose the name “tutorial_node_database” (the MySQL naming convention is snake_case).

Great, MySQL is ready to go!

Project setup

I have added this whole project to a public github repo and I have broken down each step as a commit so it should be easy to follow. This is just as an aid, we will be creating the project from scratch so there is no need to clone the code.

Let’s start with a fresh directory, I have named the project tutorial-node-database. Run the following to create the file structure.

We are using Knex with MySQL and creating a small API using express, so you need to install knex and express. We’ll also use the body-parser module.

knex should be installed globally also

When creating a new project I like to add a minimal .gitignore file so that I can easily check everything in without including the node_modules folder or .DS_Store files that MacOS likes to create.

Don’t worry if you don’t understand the bash code above. All it does is put node_modules followed by a newline \n followed by .DS_Store into a file called .gitignore. It’s perfectly reasonable to use an editor to do this sort of thing if that’s more simple for you.


We’ll start with a simple express app, serving the public directory, with a /createUser endpoint to write some data to the database.

Save this to index.js. When we run this file, the server will listen on http://localhost:7555 for POST requests to /createUser and pass those requests to the store file. The server will then respond with a 200 status code (200 is the code that every HTTP server uses to say that the request was successful).

For the time being, we will mock the store in order to check that our API works.

Save this to store.js. Note that the addLog function returns a promise so that you know when it is done. If the use of promises here is a little confusing, it might be worth checking out some Promise tutorials, they are an essential concept in modern Javascript.

Now, lets write a simple index.html, including app.js. We add app.js at the bottom of the HTML here rather than the head so that we can easily query elements above the script, without needing to use something like jQuery.ready or the DOMContentLoaded event to wait for them to load.

The app.js script should look like the below and will be used to hit the server’s /createUser endpoint.

Save this to public/app.js and start your server.

Your server should start on http://localhost:7555/. Go to this link and you will be able to see createUser requests in the network panel when you click “Create User”.

Check your terminal for the console.log from store.js.

This is a good point to commit your code if you are using git.

Setting up knex

Let’s set up the knexfile.js to connect to the database.

I have used default credentials to connect locally to MySQL, you may need to change this if yours are different.

For the tutorial we will need a user table with a username and password. These all need to be defined in schema. To create or modify schemas, we will use Knex migrations.

Create a new migration and name it something meaningful

This will create a file like migrations/20170504155313_create_user_table.js. The name of the file is the current datetime followed by the name you specified. The datetime keeps the migration files in order in the filesystem, making them easy for you to read in chronological order.

Every time we need to add, remove or modify a table or column in order to change how our data will be represented, we’ll write a new migration file. This allows us to

  • Make schema changes programmatically that can be checked into git and reviewed (far better than updating schema using MySQL directly)
  • Create a history of schema updates
  • Use JS rather than SQL to transform values (with the ability to use NPM modules and functions internal to the project)
  • Keep all the project collaborators’ database schema up to date, they can run the latest migrations to get the latest changes
  • Update staging and production databases when deploying code changes
  • Rollback schema changes

The created file contains code which exports an up method and downmethod. We will create our table schema in the down method (used when we migrate) and delete it in the up method (when we roll back).

The API for Knex is pretty straightforward and the docs are very simple to read. It is important to realize that Knex is actually constructing SQL from the calls you make. Although you are using a JavaScript API, a lot of queries are built with the underlying SQL in mind which can be very important when it comes to query performance and optimization.

Note that we add an id fields here that will automatically increment as well as updated_at and created_at fields using the t.timestamps method.

To run this migration

Check SequelPro to see the new table with the schema specified

Now we have a table, we can edit store.js so that it writes data to the table.

Using Knex.js to write data

All we need our store to do is load in knex using the knexfile.js config and then write data to the user table whenever a createUser request is made.

Restart your server and browse to http://localhost:7555/ to create a user. If you go to Sequel Pro you should see the new user show up in the database.

Storing the password safely

Let’s make sure the password is encrypted in the DB before going any further. The flexibility of using database migrations really comes in handy.

To safely store passwords, you want to create a random salt for each new user and use crypto to encrypt the password (this is standard practice and safeguards against leaking your user’s passwords if your database is compromised).

Before the migration, let’s add the encryption logic into the store.js file

Note that crypto is a native node module and need not be installed using npm.

Let’s make a migration file

For the migration, we are going to add encrypted_password and saltcolumns, then we are going to borrow the saltHashPassword method that we exported from store.js in order to migrate users already in the database. Finally, we will remove the password column.

We can’t get the original password back in the down function because it is encrypted, hence we just try our best by putting the schema back as it was.

There is a tonne of promises being used here which is common when writing migrations. Using async/await we can greatly simplify this code.

If you want to use async await, ensure you have the latest Nodejs version (it only recently became supported)

When you install a new Nodejs version, be aware that global packages will need to be reinstalled.

Let’s run the migration and check that it works.

If everything runs smoothly, your password should have been encrypted.

Restarting your server and creating new users should encrypt the passwords. Go to http://localhost:7555/ and create some more users to check it works.

Add a login route

At this point, let’s install nodemon to restart the server automatically when we make changes.

Now run the server using nodemon, rather than Nodejs

Next time we make changes, the server will restart for us. Usually you would leave this running and then run bash operations in a new terminal tab.

To authenticate a user we create a /login route which calls the store’s authenticate method with the username and password.

If the authentication is successful, we respond with 200, else we respond with 401 (unauthorized).

In the store, we need to get the user’s salt, use it to encrypt the password and then check it against the encrypted_password in the database.

We have also modified the saltHashPassword function to accept a salt, only generating one if none is supplied.

Notice that we changed the function declaration here so that the first argument is now an object. We could have easily had the hash passed in as a second argument however it is good practice to pass options objects because it makes it more obvious what is happening in the function call.

Consider for instance if saltHashPassword ends up at the bottom of the file, or is abstracted into another file. Which call is easier to understand?

Having the argument names in the call is nice.

To call /login, let’s add a login form to the index.html file.

and let’s add logic to the app.js to call the login endpoint

By checking the status, we are now able to tell whether the user is authenticated or not.


Inspecting the SQL

Before wrapping up, let’s just take a look at some of the SQL queries being run under the covers here. In order to log a Knex query to the console, just add a .debug() to the query chain.

For example, in store.js you could add it to createUser

Now, when this method is called, you will see the SQL output to the console.

By looking at the sql key and replacing the ? values with the values in the bindings array you can reconstruct the underlying SQL query that Knex runs to add a user.


In this tutorial we created a crude app for creating and logging in users using Nodejs + Knex + MySQL. In the next tutorial we will add more functionality to the app and explore some more complex querying.

Eventually I hope to show how we can deploy the app to EC2 with a remote database.

If you liked this tutorial, then you might want to take a look at a couple of my earlier tutorials

Recommended Courses:

☞ Getting Started with Nodejs for Beginners

☞ Learn Nodejs by building 12 projects

☞ Supreme Nodejs Course – For Beginners

☞  Node.js for beginners, 10 developed projects, 100% practical

via :



Please enter your comment!
Please enter your name here