Nodejs and PostgreSQL, Practical Introduction (Connection and Basic Queries with Javascript)

In this tutorial, I will show you how to create a Node Server and the basic queries with PostgreSQL.

  • NodeJS installed
  • PostgreSQL installed
  • Basic JavaScript
  • Terminal
  • Text Editor
  • Postman

To start it is necessary to first create a new folder called as you want, for this tutorial I am going to call it node-postgres, and then inside the folder open a new terminal with the folder path and initialize our project, for this, just write the following command in the terminal.

npm init -t

This will create a file called package.json with default values, for our project which contains all the project references such as the author, repository, version, and most important of all the project dependencies.

example of package.json
example of package.json

Before starting to write our code, we are going to install the dependencies, for this tutorial only “express” and “pg” are necessary. So we are going to write the following command in the console:

npm i express pg

And finally, for dev dependencies:

npm i -D nodemon

Now we are going to give our project a bit of structure, first, we are going to create a folder called “src” where we will place all our files.

Inside the src folder, we are going to create a file called index.js, this file will be the initial configuration of our server so we are going to add the following lines of code.

const express = require('express')const app = express()app.use(express.json())app.use(express.urlencoded({extended:false}))const PORT = 3000app.listen(PORT);console.log(`Running on port ${PORT}`)

This is enough for now to have 👌.

Now we are going to create our database in PostgreSQL, for this it is enough to have a database and a table called users. It is only enough to execute the commands either by console or through a graphical interface.

CREATE DATABASE api;
CREATE TABLE users(id SERIAL PRIMARY KEY,name VARCHAR(40),email TEXT)

And insert some data for testing.

INSERT INTO users(name,email)
VALUES ('Alex','alex.example1.com'),
('Fer','fer.example2.com'),
('Jonny','jonny.example3.com')

At this point we are going to separate the logic of our project, inside the “src” folder we are going to create two new folders, “routers” and “controllers”, and in each one of these, we will create an index.js file for routers and index.controller.js for the controllers.

example of folders structures
example of folders structures

Connections and Queries

First, we are going to create a basic query to our database with a query to obtain all users, for this in our index.controller.js file we are going to add the following lines of code.

const {Pool} = require('pg');const pool = new Pool({host: 'localhost',port:'5432',user:'postgres',password:'password',database:'api'})const getUsers = async (req,res) =>{const response = await pool.query('Select * from users')res.status(200).json(response.rows)}module.exports = {getUsers}

With this, we are creating a new connection to our database locally with its credentials, and a function with a query to obtain all the users of our database and finally, export it.

Once we have our controller we will move on to the Router part.

Routers

For our router, we are going to add the following code in the index.js file of the routers folder.

const {Router} = require('express')
const router = Router()
const {getUsers} = require('../controller/index.controller')router.get('/users',getUsers)module.exports = router;

Here we are only going to require our controller that we already defined. Add the route through which we are going to consult our users, as well as the type of request, in this case, GET petition, and in the same way export it to use it in the index.js of our initial configuration.

Now we just have to add the following lines of code to our index.js file

...const routers = require('./routes/index')...app.use(routers)...

We have everything ready to test our project, for this, we have to make some adjustments to the package.json

dev example dependencies
dev example dependencies

We just add nodemon dependency to our scripts

“dev”: “nodemon src/index.js”

Nodemon” is a command-line interface (CLI) utility developed by @rem that wraps your Node app, watches the file system, and automatically restarts the process. With this, you can eliminate this extra step by using to restart the process automatically.

Now we are going to build our server, which with all the adjustments we make, it will only be necessary to execute the following command once:

npm run dev

Once we execute all of the above we will have something similar to this in the console:

console output example
console output example

And in the same way, we can access the service with the URL http:localhost:3000/users in the browser and we are going to observe the users as follows:

users output
users output

Now that we can access the database users, now we need a way to register new users, for this we will use the same request but with a different POST method, to create new users.

For this, it is necessary to add new lines of code first in our index.controller.js file add the following

...const createUser = async (req,res) =>{const {name,email} = req.bodyconst response = await pool.query('INSERT INTO users(name,email) VALUES($1,$2)',[name,email])res.status(200).json({message:'User Added',body: {user:{name,email}}})}...module.exports = {getUsers,createUser}

The important thing to highlight in this new section of code is the execution of the query where we insert the new user INSERT INTO users (name, email) VALUES ($ 1, $ 2) with the values that we send in the request POST “name” and “email” that we take from req .body and at the end where we only give a structure to the output res.status (200) in the way that best suits

With our controller ready, it is only necessary to import it into our Routes file with the following lines of code.-


...
const {getUsers,getUser} = require('../controller/index.controller')...router.post('/users',createUser)

Once we have all the elements joined it is time to test the new request with Postman. Where we are going to observe if everything is done correctly the JSON of the output that we have structured for this.

postman petition example
postman petition example

And if we make the same request to obtain all the users now we can see the new user that we have created http:localhost:3000/users

Example of output of new users added
Example of output of new users added

We already have a way to consult our users and add new ones, now we are going to create a method to consult a single user based on the id we provide, for this first in the index.controller.js file we are going to add the following lines of code.

...const getUser = async (req,res) =>{const id = req.params.idconst response = await pool.query(`Select * from users where id='${id}'`)res.status(200).json(response.rows)}...module.exports = {getUsers,createUser,getUser}

Where this time the remarkable thing in the same way as the previous one is the query that we execute Select * from users where id = ‘$ {id} and the parameter that we add dynamically for the execution of our query const id = req.params.id, It should be noted that each new function that we have added is important to export it to use it in the part of our Routers module.exports ={getUsers,createUser,getUser}

And add this to the index.js file of our Routers

...const {getUsers,createUser,getUser} = require('../controller/index.controller')...router.get('/users/:id',getUser)

Now we are going to test in the same way with the following URL http://localhost:3000/users/1, either in the browser or Postman

example of output of the user with the id = 1
example of output of the user with the id = 1

And so for each of the users, where the difference is the id of the user we want to know.

And now to finish it is necessary a way to eliminate the users based on the id that we provide, for which we are going to add the following code in the index.controller.js file again

...const deleteUser = async (req,res) =>{const id = req.params.idconst response = await pool.query(`DELETE from users WHERE id=${id}`)res.json(`User ${id} deleted`)}...module.exports = {getUsers,createUser,getUser,deleteUser}

That again the most remarkable thing in this part is the query that we execute Select * from users where id = ‘$ {id} the id that we provide through the request const id = req.params.id and for this case a response in JSON to know that the user has been deleted and how we have been doing it, export this function to be able to use it in our routers module.exports = {getUsers,createUser,getUser,deleteUser}

And add this to the index.js file of our Routers

...const {getUsers,createUser,getUser,deleteUser} = require('../controller/index.controller')...router.delete('/users/:id',deleteUser)

And now we are going to test with Postman

Example of Detele petition
Example of Detele petition

And we execute the request again to obtain all the users and verify that this user is no longer found

users output
users output

Conclusion

Now that you have the basic knowledge to perform the main queries in a database as well as the types of requests to them, along with their essential elements for a node server, you can now move towards more complex things using this knowledge such as base hoping that they will be of help to create future complete Front-End projects with Node and PostgreSQL database

https://github.com/AbrahamMurillo/node-postgres

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store