This is the 5th post of the tutorial series called Node Hero – in these chapters, you can learn how to get started with Node.jsNode.js is an asynchronous event-driven JavaScript runtime and is the most effective when building scalable network applications. Node.js is free of locks, so there's no chance to dead-lock any process. 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 PostgreSQL database, and teach you the basics of using it.
See all chapters of the Node Hero tutorial series:
- Getting started with Node.js
- Using NPM
- Understanding async programming
- Your first Node.js HTTP server
- Node.js database tutorial [ this article ]
- Node.js request module tutorial
- Node.js project structure tutorial
- Node.js authentication using Passport.js
- Node.js unit testing tutorial
- Debugging Node.js applications
- Node.js Security Tutorial
- How to Deploy Node.js Applications
- Monitoring Node.js Applications
Ways of storing data in Node.js Apps:
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.
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 database 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.
Storing data in a SQL database
Let’s start with SQL. It is a query language designed to work with relational databases. SQL databases have 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.
Advantages of an SQL database:
SQL enables communicating with the databases and receive answers to complex questions in seconds.
SQL views the data without storing the it in the object. It adheres to a long-established, clear standard.
Storing data in a NoSQL database
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.
Advantages of a NoSQL database:
- NoSQL can handle large volumes of structured, semi-structured, and unstructured data
- Interacts quickly
- Flexible and object-oriented
- Has an efficient, scale-out architecture
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 in our Node.js app.
Setting up your PostgreSQL database
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 PostgreSQL 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 PostgreSQL database we are going to use in our Node.js application. 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)
})
})
})
You just created a functioning PostgreSQL database in Node.js!
That wasn’t that hard, was it?
Now you can run any complex SQL query that you can come up with within your Node.js application.
With the technique you learned in this node.js database tutorial, you can store data persistently in your application, and thanks to the hard-working team of the node-postgres module, it is a piece of cake to do so.
We have gone through all the basics you have to know about using databases with Node.js. Now go, and create something yourself.
Try things out and experiment, because that’s the best way of becoming a real Node Hero! Practice and be prepared for the next Node.js tutorial on how to communicate with third-party APIs!
If you have any questions regarding this Node.js database tutorial, don’t hesitate to ask!
