Posted on Fri Feb 22 2019

A beginner friendly step by step tutorial aimed to teach you how to build a Node.js API connected to a sql database. And a frontend react application.

What you need to know?

This tutorial is aimed for beginners with some knowledge in the following fields:

Basic understanding of javascript

Basic understanding of HTML elements and markup

SQL syntax (INSERT, SELECT, UPDATE, DELETE)

If you don't have any knowledge at all about javascript in general or nodejs, you can still follow all the steps and get the app to work.

But I strongly advise you to learn the basics of javascript before learning any framework or library.

What you will learn?

Pre-requisites

To follow along with this tutorial you need to have nodejs installed on your system.

What is Node.js?

According to Node.js official website

Node.js is an asynchronous event-driven JavaScript runtime, Node is designed to build scalable network applications.

Node can be used for anything from servers to web apps, mobile apps, desktop apps and even as a CLI. That's due to the huge amount of libraries and frameworks made by the awesome node community, and today we will use a couple of them.

Installing Node.js

To install Node.js head over to the official website and download the LTS version (At the time of writing this article it was 10.15.1 LTS).

Installation is straight forward, just execute the installer and follow the on-screen instructions.

For Linux and macOS users you can also install nodejs using your package manager.

After that make sure Node is installed correctly by running the following command in your terminal (CMD on windows):

node --version

Installing node also installs NPM (Node Package Manager) on your system, you can verify this by running the following command:

npm

The output of the above commands should look like this but the versions might be different:

$ node --version v8.11.2 $ npm --version 6.7.0

Running your first nodejs application

Node.js can run any javascript file that doesn't use the browser api (document, window, etc...).

Let's create a new folder for our nodejs app, you can call whatever you want and also create a file called app.js inside it. Our project structure should look like this now:

project -folder/ app.js

Open app.js with your favorite text editor and insert the following code:

console .log( 'Hello World!' );

console.log is a built-in function that takes a message as an argument and prints it to the console (In this case the terminal or CMD).

Now open a terminal (CMD on Windows) and navigate to your directory:

cd /path/to/project-folder/

To run this file we will use the node command. To use it just type node followed by the file name. In our case:

node app.js

You will see Hello World! printed on your terminal and the app exits.

Creating your first nodejs project

You now know how to run javascript files using node , but in order to use packages, frameworks, and libraries you should initialize a new project.

To do so, we will use npm which has a command called init that will initialize a new node project. To use it type npm init in your terminal (or CMD) and you will be asked to answer a couple of questions about your project. You can skip them by either leaving them empty and pressing enter or by running npm init -y instead of npm init .

The output should like this:

Wrote to / path / to /project-folder/package.json: { "name": "project-name", "version": "1.0.0", "description": "", "main": "app.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "keywords": [], "author": "", "license": "ISC" }

NPM created a new file in our project folder called package.json, this file contains info about our project, how to run it and what dependencies it use.

We will be making some changes to our package.json file throughout this tutorial to simplify development and build processes.

First change the test script "test": "echo \"Error: no test specified\" && exit 1" to "start": "node app.js"

This means that whenever we run npm start the command node app.js will be executed, which will run our app.

You can now change the name and description of your project and also set the author to your full name.

The content of package.json should now be:

{ "name" : "my-node-aserver" , "version" : "1.0.0" , "description" : "Best node server ever." , "main" : "app.js" , "scripts" : { "start" : "node app.js" }, "keywords" : [], "author" : "Hassan Saleh" , "license" : "ISC" }

Installing node packages

Now that you have a node project, you can install third-party packages as dependencies using npm install . To install a package run npm install package-name and npm will download a copy of this package and place it in a folder called /node_modules inside your project directory.

Useful npm install commands:

npm install --save package-name npm install --save-dev package-name npm install -g package-name

Why we want our packages as dependencies in package.json?

When installing new packages using npm install --save , npm install --save-dev or even just npm install (in the latest versions of npm), these packages names get saved in our package.json as project dependencies and this is very useful when moving the project from one machine to another, or when working with a team and using version control. In these situations, you don't want to move the node_modules folder which contains all your installed packages files, because of the size of this folder and the number of files it contains. And also because some dependencies have platform specific builds and must be installed on the system you're going to use it on.

So all you need to do in this case is to move all your files and folders except the /node-modules folder and run npm install with no arguments to the other machine. This will get the list of dependencies from the package.json file and installs all your packages.

Installing your first package

We are going to create an API which will handle http requests from the react app we will build later. To do so we need to create a nodejs server.

There are multiple ways to create a server in node, you can use the built-in http module, but it's very complex and requires a lot of manual configurations. Luckily the node community created lots of packages for creating servers and the most widely used package is express.

First, let's install express. Run the following command in your terminal:

npm install --save express

Creating a simple server

Now since you installed express replace console.log('Hello World!'); in app.js with the following code:

const express = require ( 'express' ) const app = express() app.get( '/' , function ( req, res ) { res.send( 'Hello World' ) }) app.listen( 3000 , function ( ) { console .log( 'Server started' ) })

Let me explain what we did here.

First, we imported express module from the express package and stored it in a constant called express so we can use it.

Then we created a new instance of express by calling the express function express() and stored it in a constant called app.

We can simplify the first two lines by using only one line like so:

const app = require ( 'express' )();

Then we used this instance to call 2 functions inside express (get and listen). Most express functions are asynchronous and their last argument is a callback function which runs whenever the express function calls it.

For more info on callbacks check out this medium post.

The first express function we used is get. This function takes a path (in this case / which stands for the root directory) and a callback function that runs when a GET request to this path is made. Inside the callback function, we're using the response parameter res to send hello world to whoever sent the request.

The second express functions is listen. This function takes the port number the server should listen on and a callback function to run when the server is started successfully (In this case it will print Server started to the console).

Save the file and run npm start again, Server started should be printed in the terminal but this time the app didn't exit and that's because the express server is always listening for requests. If you want to manually stop the server press control + C ( command + C on macOS) in the terminal.

Now we need to test our server, to do so run the server again if you stopped it, open a new terminal and type the following command:

curl http://localhost:3000

If you are on windows or you want to test it in the browser, open your browser and navigate to http://localhost:3000

Both of the above methods will send an HTTP get request to localhost (which is your system's hostname) on port 3000, where your nodejs server is listening. The app.get function will then run its callback and return a response containing the word Hello World.

Connecting your server to a database

In a real server, we will not be sending Hello World to our users, instead, we should have a database to store, retrieve, update and delete data. For this app, I will be using Postgres as my SQL database.

Running Postgres on your machine

You can download and install Postgres from the official website, but you shouldn't be doing this because you will be using it on your local machine for development and testing only. What I like to do in this case is to use docker.

Wait a minute, what's docker?

You can think of docker as a virtual machine that is running on your system, but instead of running a whole operating system with its own kernel it runs a specific software and allow it to use the same kernel the system is running on.

That's not the only reason why I chose to use docker, it's because docker can download and run a Postgres database in one command.

Note: I am not sponsored by docker, I just love how simple it is to use and how powerful it can be (I use it in most on my web apps).

Installing Docker on your machine

We are going to install Docker Community Edition.

Head over to Docker Desktop page and download the community edition of Docker Desktop. You might need to create an account to be able to download.

If you're on Linux follow the official guide for installation.

After installing Docker make sure it's running by opening a new terminal and typing the following command:

docker -v

The output should be like this (versions might be different):

$ docker -v Docker version 18.09 .2 , build 6247962

Running Postgres using Docker

To run Postgres on your machine simply run the following command:

docker run --name postgres-container -p 5432:5432 -e POSTGRES_PASSWORD =mysecretpassword postgres

This command will download Postgres image if it's the first time you run it, run this image on the port 5432 in a container called postgres-container as specified in the command and set the password to mysecretpassword .

The output should look similar to this:

PostgreSQL init process complete; ready for start up. 2019-02-20 19:55:57.052 UTC [1] LOG: listening on IPv4 address "0.0.0.0" , port 5432 2019-02-20 19:55:57.052 UTC [1] LOG: listening on IPv6 address "::" , port 5432 2019-02-20 19:55:57.054 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2019-02-20 19:55:57.063 UTC [53] LOG: database system was shut down at 2019-02-20 19:55:56 UTC 2019-02-20 19:55:57.066 UTC [1] LOG: database system is ready to accept connections

That's it, now we have a database.

This container will continue to run until the terminal is closed or until you stop the container by pressing control + C ( command + C on macOS).

Docker commands

Useful docker command you might need:

docker ps docker ps -a docker start container-name docker stop container-name docker restart container-name docker rm container-name

Connecting to postgres

The next step is to connect our nodejs server to the database we just created with docker. To do so we need to install a new package called pg.

npm install --save pg

Next, we need to import it at the top of our app.js file and connect to postgres like so:

const pg = require ( 'pg' ); const Pool = pg.Pool; const pool = new Pool({ user : 'postgres' , host : 'postgres.localhost' , database : 'postgres' , password : 'mysecretpassword' , port : 5432 , }) pool.query( 'SELECT NOW()' , function ( err, res ) { console .log(res) pool.end() })

Let me explain what we did here:

First, we import pg module and store it in a constant called pg.

Then we get the Pool function from pg and store it in a const called Pool.

Then we create a new Pool instance, give it the database configuration and store it in a constant called pool.

The default user for postgres is postgres.

The default database is the user which is in our case postgres.

The host is database.hostname which is postgres.localhost in our case.

Then we run an SQL query which returns the current time to test if our connection works.

Make sure postgres is still running by running docker ps .

If your server is still running in the terminal stop it by pressing control + C ( command + C on macOS) and start it again by running npm start .

The server should start and you should see the following output in the terminal:

Server started Result { command: 'SELECT' , rowCount: 1 , oid: null , rows: [ { now: 2019 -02 -20 T20:37:06.421Z } ], fields: [ Field { name: 'now' , tableID: 0 , columnID: 0 , dataTypeID: 1184 , dataTypeSize: 8 , dataTypeModifier: -1 , format: 'text' } ], _parsers: [ [Function: parseDate] ], RowCtor: null , rowAsArray: false , _getTypeParser: [Function: bound ] }

Server started and the result of the query is printed in the console, this means we have successfully connected to Postgres.

You can now remove the query, we only needed it to test the connection.

We can simplify the first two lines as we did with express by replacing them with const Pool = require('pg').Pool;

The final content of app.js is:

const app = require ( 'express' )(); const Pool = require ( 'pg' ).Pool; const pool = new Pool({ user : 'postgres' , host : 'postgres.localhost' , database : 'postgres' , password : 'mysecretpassword' , port : 5432 , }) app.get( '/' , function ( req, res ) { res.send( 'hello world' ) }) app.listen( 3000 , function ( ) { console .log( 'Server started' ) })

API routing and CRUD operations

Now that your server is connected to a database you can start making CRUD operations (create, read, update and delete).

Our app will be a small to-do with no user authentication, so we just need one table called tasks. Let's create this table:

const createTabelString = ` CREATE TABLE IF NOT EXISTS tasks( task_id SERIAL PRIMARY KEY, task_name VARCHAR(255) NOT NULL, is_done boolean NOT NULL DEFAULT false ); ` ; pool.query(createTabelString, function ( err, res ) { if (err) { console .error(err); } else { console .log(res); } });

Next, we will create all the required functions to manage our to-dos.

function createTask ( name, callback ) { pool.query( 'INSERT INTO tasks (task_name) VALUES ($1) RETURNING *;' , [name], function ( err, res ) { if (err) { callback(err, null ); } else { callback( null , res.rows[ 0 ]); } }); } function getAllTasks ( callback ) { pool.query( 'SELECT * FROM tasks;' , function ( err, res ) { if (err) { callback(err, null ); } else { callback( null , res.rows); } }); } function deleteTask ( id, callback ) { pool.query( 'DELETE FROM tasks WHERE task_id = $1' , [id], function ( err, res ) { if (err) { callback(err); } else { callback( null ); } }); } function updateTask ( id, isDone, callback ) { pool.query( 'UPDATE tasks SET is_done = $1 WHERE task_id = $2 RETURNING *;' , [isDone, id], function ( err, res ) { if (err) { callback(err, null ); } else { callback( null , res.rows[ 0 ]); } }); } }

Now we need to define the API routes using express.

app.put( '/api/tasks/' , function ( req, res ) { const name = req.body.name; createTask(name, function ( err, task ) { if (err) { res.sendStatus( 500 ) } else { res.send( JSON .stringify(task)) } }) }) app.get( '/api/tasks/' , function ( req, res ) { getAllTasks( function ( err, tasks ) { if (err) { res.sendStatus( 500 ) } else { res.send( JSON .stringify(tasks)) } }) }) app.delete( '/api/tasks/:id' , function ( req, res ) { const id = req.params.id; deleteTask(id, function ( err ) { if (err) { res.sendStatus( 500 ) } else { res.sendStatus( 200 ) } }) }) app.post( '/api/tasks/:id' , function ( req, res ) { const id = req.params.id; const isDone = req.body.isDone; updateTask(id, isDone, function ( err, task ) { if (err) { res.sendStatus( 500 ) } else { res.send( JSON .stringify(task)) } }) })

Our server can now handle 5 requests

GET / this is the root route which will serve the react app at the end. PUT /api/tasks/ creates a task. It requires a value called name to be passed in the request body. GET /api/tasks/ returns all tasks in the database as a json string. DELETE /api/tasks/task_id/ deletes the task with the id task_id . POST /api/tasks/task_id/ sets the is_done column of the task task_id to the value sent in the request body.

Now we need to do is allow CORS (Cross-Origin Resource Sharing) so that the application we will build can send requests to this server. We have to install a new package called cors.

npm install

Add the following two lines at the top of the page just bellow const Pool = require('pg').Pool

const cors = require ( 'cors' ); app.use(cors())

We also want to receive the request body as JSON. To do so we need another package called body-parser.

npm install

And in app.js

const bodyParser = require ( 'body-parser' ); app.use(bodyParser.json())

And that's it, you now have a nodejs backend application.

What's next

In the next article you will learn how to create a simple react app and communicate with the node server.

Thank you for reading and if you have any questions or you have found any issues in this article don't hesitate to contact me.

Code avalilable at github.