Node.js Database Tutorial

This is the 5th post of the tutorial series called Node Hero - in these chapters, you can learn how to get started with Node.js and deliver software products using it.

In the following Node.js database tutorial, I’ll show you how you can set up a Node.js application with a database, and teach you the basics of using it.

Upcoming and past chapters:

1. Getting started with Node.js
2. Using NPM
3. Understanding async programming
4. Your first Node.js server
5. Node.js database tutorial [you are reading it now]
6. Node.js request module tutorial
7. Node.js project structure tutorial
8. Node.js authentication using Passport.js
9. Node.js unit testing tutorial
10. Debugging Node.js applications
11. Node.js Security Tutorial
12. How to Deploy Node.js Applications
13. Monitoring Node.js Applications

Storing data in a global variable

Serving static pages for users - as you have learned it in the previous chapter - can be suitable for landing pages, or for personal blogs. However, if you want to deliver personalized content you have to store the data somewhere.

Let’s take a simple example: user signup. You can serve customized content for individual users or make it available for them after identification only.

If a user wants to sign up for your application, you might want to create a route handler to make it possible:

const users = []

app.post('/users', function (req, res) {  
    // retrieve user posted data from the body
    const user = req.body
    users.push({
      name: user.name,
      age: user.age
    })
    res.send('successfully registered')
})

This way you can store the users in a global variable, which will reside in memory for the lifetime of your application.

Using this method might be problematic for several reasons:

• RAM is expensive,
• memory resets each time you restart your application,
• if you don't clean up, sometimes you'll end up with stack overflow.

Need help with enterprise-grade Node.js Development? 
Hire the experts of RisingStack! 

Storing data in a file

The next thing that might come up in your mind is to store the data in files.

If we store our user data permanently on the file system, we can avoid the previously listed problems.

This method looks like the following in practice:

const fs = require('fs')

app.post('/users', function (req, res) {  
    const user = req.body
    fs.appendFile('users.txt', JSON.stringify({ name: user.name, age: user.age }), (err) => {
        res.send('successfully registered')
    })
})

This way we won’t lose user data, not even after a server reset. This solution is also cost efficient, since buying storage is cheaper than buying RAM.

Unfortunately storing user data this way still has a couple of flaws:

• Appending is okay, but think about updating or deleting.
• If we're working with files, there is no easy way to access them in parallel (system-wide locks will prevent you from writing).
• When we try to scale our application up, we cannot split files (you can, but it is way beyond the level of this tutorial) in between servers.

This is where real databases come into play.

You might have already heard that there are two main kinds of databases: SQL and NoSQL.

SQL

Let's start with SQL. It is a query language designed to work with relational databases. SQL has a couple of flavors depending on the product you're using, but the fundamentals are same in each of them.

The data itself will be stored in tables, and each inserted piece will be represented as a row in the table, just like in Google Sheets, or Microsoft Excel.

Within an SQL database, you can define schemas - these schemas will provide a skeleton for the data you'll put in there. The types of the different values have to be set before you can store your data. For example, you'll have to define a table for your user data, and have to tell the database that it has a username which is a string, and age, which is an integer type.

NoSQL

On the other hand, NoSQL databases have become quite popular in the last decade. With NoSQL you don't have to define a schema and you can store any arbitrary JSON. This is handy with JavaScript because we can turn any object into a JSON pretty easily. Be careful, because you can never guarantee that the data is consistent, and you can never know what is in the database.

Node.js and MongoDB

There is a common misconception with Node.js what we hear all the time:

"Node.js can only be used with MongoDB (which is the most popular NoSQL database)."

According to my experience, this is not true. There are drivers available for most of the databases, and they also have libraries on NPM. In my opinion, they are as straightforward and easy to use as MongoDB.

Node.js and PostgreSQL

For the sake of simplicity, we're going to use SQL in the following example. My dialect of choice is PostgreSQL.

To have PostgreSQL up and running you have to install it on your computer. If you're on a Mac, you can use homebrew to install PostgreSQL. Otherwise, if you're on Linux, you can install it with your package manager of choice.

For further information read this excellent guide on getting your first database up and running.

If you're planning to use a database browser tool, I'd recommend the command line program called psql - it's bundled with the PostgreSQL server installation. Here's a small cheat sheet that will come handy if you start using it.

If you don't like the command-line interface, you can use pgAdmin which is an open source GUI tool for PostgreSQL administration.

Note that SQL is a language on its own, we won't cover all of its features, just the simpler ones. To learn more, there are a lot of great courses online that cover all the basics on PostgreSQL.

Node.js Database Interaction

First, we have to create the database we are going to use. To do so, enter the following command in the terminal: createdb node_hero

Then we have to create the table for our users.

CREATE TABLE users(  
  name VARCHAR(20),
  age SMALLINT
);

Finally, we can get back to coding. Here is how you can interact with your database via your Node.js program.

'use strict'

const pg = require('pg')  
const conString = 'postgres://username:password@localhost/node_hero' // make sure to match your own database's credentials

pg.connect(conString, function (err, client, done) {  
  if (err) {
    return console.error('error fetching client from pool', err)
  }
  client.query('SELECT $1::varchar AS my_first_query', ['node hero'], function (err, result) {
    done()

    if (err) {
      return console.error('error happened during query', err)
    }
    console.log(result.rows[0])
    process.exit(0)
  })
})

This was just a simple example, a 'hello world' in PostgreSQL. Notice that the first parameter is a string which is our SQL command, the second parameter is an array of values that we'd like to parameterize our query with.

It is a huge security error to insert user input into databases as they come in. This protects you from SQL Injection attacks, which is a kind of attack when the attacker tries to exploit severely sanitized SQL queries. Always take this into consideration when building any user facing application. To learn more, check out our Node.js Application Security checklist.

Let's continue with our previous example.

app.post('/users', function (req, res, next) {  
  const user = req.body

  pg.connect(conString, function (err, client, done) {
    if (err) {
      // pass the error to the express error handler
      return next(err)
    }
    client.query('INSERT INTO users (name, age) VALUES ($1, $2);', [user.name, user.age], function (err, result) {
      done() //this done callback signals the pg driver that the connection can be closed or returned to the connection pool

      if (err) {
        // pass the error to the express error handler
        return next(err)
      }

      res.send(200)
    })
  })
})

Achievement unlocked: the user is stored in the database! :) Now let's try retrieving them. Next, let’s add a new endpoint to our application for user retrieval.

app.get('/users', function (req, res, next) {  
  pg.connect(conString, function (err, client, done) {
    if (err) {
      // pass the error to the express error handler
      return next(err)
    }
    client.query('SELECT name, age FROM users;', [], function (err, result) {
      done()

      if (err) {
        // pass the error to the express error handler
        return next(err)
      }

      res.json(result.rows)
    })
  })
})