NodeJs and PostgreSQL CRUD Example Application (Part 1)

Written by Data Pilot
November 11, 2019
  • PostgreSQL

NodeJs and PostgreSQL CRUD example introduction

Welcome to “NodeJs and PostgreSQL CRUD Example Application (Part 1),” a multi-part tutorial series. Every application must be able to perform CRUD at an elemental level. This tutorial will explain the process of setting up a NodeJs app and also give a NodeJs PostgreSQL CRUD example.

If you’ve previously learned how to complete steps to achieve CRUD with NodeJs and PostgreSQL, go to Just the Code.

Prerequisites to using PostgreSQL and NodeJs

  • Install NodeJs. The NPM package manager should be included. If you have a macOS, the Homebrew package manager is a good way to install NodeJs too. Read further in this tutorial for specific instructions on how to use Homebrew on macOS to install NodeJs. There are also steps to complete the NodeJs installation on a Linux machine.

  • Confirm the NodeJs version with the node -v command.

  • Next, check that you have NPM installed with the npm -v command.

  • Install PostgreSQL. Determine the version with the postgres -V command.

  • Check that PostgreSQL is running with the psql -V command. If your server is a Linux, input the command service postgresql status. Be sure to exit properly by typing CTRL + C when you’re finished.

  • The ps auxwww | grep postgres command also verifies the Postgres processes.

Screenshot of Postgres psql Node version numbers

Accessing the PostgreSQL using the ‘psql’ command-line interface

From your server, the localhost, go into the PostgreSQL database. Use the command psql:

1
sudo su - postgres

  • Enter your password at the prompt, and then hit the RETURN button to get into PostgreSQL.

  • Open your database with this command:

1
psql some_username -h 127.0.0.1 -d some_database
  • Enter your password and the RETURN button once more.

Install the NodeJs and the ‘Node Package manager’

  • If you haven’t already, go ahead and install NodeJs so you can complete the NodeJs PostgreSQL CRUD example in this tutorial.

Install the NodeJs on Linux

  • Input the APT-GET Ubuntu repository command for Linux distros:
1
sudo apt-get install nodejs
  • Input the YUM install command for Linux distros of Fedora, CentOS or other Red Hat distros:
1
sudo yum install nodejs

Install NodeJs on macOS using Homebrew

  • Here’s the brew command Homebrew method of installing NodeJs for your macOS if you haven’t already done so.
1
brew install node

Verify that NodeJs and the NPM is installed

  • If you have yet to confirm the version of NodeJs on your system, do it now wIth the node -v from a terminal window:

  • You should see a result something like this one here:

1
v12.10.0
  • This example shows you precisely how to check your Node package manager version. Be sure you update your installation globally with the npm -v command like this:
1
sudo npm install npm --global

Setup and initialize the NodeJs project

  • Input the command mkdir at a terminal window or command line. You’re making a directory for your new application in NodeJs.

  • Next access it with cd.

  • Once there, setup NodeJs with the npm init -y command.

  • The package.json will be automatically constructed.

Install all of the necessary Node packages for the PostgreSQL app

The list of modules required for your NodeJs project are the following:

  • pg – Gives NodeJs. PostgreSQL database access.

  • body-parser – Middleware for parsing for reading requests with JSON.

  • path – The module for the exact directory path.

  • ejs – Embedded templates for developing code in JavaScript.

  • express – A mobile and web application framework that is simple, flexible, and strong.

Install the EJS, Express, and body-parser Node modules using NPM

  • Use the npm install [npm_module] Node package manager installation command to install the NodeJs libraries.

  • Input the NodeJs package manager i command to get the essential modules for Postgres.

1
npm i path ejs express body-parser pg

NOTE: The client NodeJs Postgres uses the pg module.

Create a database for the NodeJs PostgreSQL CRUD example

Construct a PostgreSQL database and a table within it. You’ll need it to complete the NodeJs PostgreSQL CRUD example.

  • Use this command to make a sample database and name it some_db.
1
CREATE DATABASE some_db;
  • Give database user privileges with this command:
1
GRANT ALL PRIVILEGES ON DATABASE some_db TO some_username;
  • Connect the server and database with the command c:
1
d some_db

NOTE: You may need to use the GRANT CONNECT ON DATABASE some_db TO some_username; to grant database creation privileges if your Role doesn’t have them.

Screenshot of CREATE DATABASE and CREATE USER ROLE in psql

Node script that will create a PostgreSQL table for the database

Make a table for the database to use in the NodeJs PostgreSQL CRUD example. You’ll use PostgreSQL’s client library pg and the DROP and CREATE commands for this tutorial.

Create a pool instance of the pg client connection

  • Make a constant declaration for the library Pool of the pg client. Next, use the parameters you previously made for your PostgreSQL database like this:
1
2
3
4
5
6
7
8
9
10
const Pool = require("pg").Pool;

// Declare a new client instance from Pool()
const pool = new Pool({
  user: "some_username",
  host: "localhost",
  database: "some_db",
  password: "1234",
  port: "5432"
});

Concatenate a string for the ‘CREATE TABLE’ SQL statement

  • Make a declaration for the constant tableName.

  • Do a method query() Pool instance string concatenation for the Postgres database API request.

1
2
3
4
5
6
7
8
9
10
11
// Declare a constant for the CRUD Postgres table
const tableName = "employee";

// Declare a string for the CREATE TABLE SQL statement
const newTableSql = `CREATE TABLE ${tableName} (
id SERIAL NOT NULL,
name VARCHAR(255) NOT NULL,
address TEXT NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL
);`;

NOTE: The above code uses the ${tableName} value substitution for the table name. Make sure that all of your SQL statements end with a semicolon (;) so that it doesn’t throw any errors.

Connect to the PostgreSQL database in the Node app

  • Use async to make a function that’s asynchronous where some code lines must complete before the next set of code lines are allowed to continue. The await syntax is used in this async example:
1
2
3
4
5
6
7
8
9
async function createTable() {

// Promise chain for pg Pool client
const client = await pool
.connect()

.catch(err => {
console.log('pool .connect ->', err)
})

Use the Pool client instance to drop the Postgres table

  • An existing database table will DROP; otherwise, if there’s no table, the error is logged.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Check that the pg client is valid
if (client !== undefined) {

await client.query(`DROP TABLE ${tableName};`, (err, res) => {

// client is ready for the query() API call
console.log("nclient ready:", client.readyForQuery, "n")

// check for errors with client.query()
if (err) {
console.log('DROP TABLE ->', err)
}
if (res) {
console.log('DROP TABLE result:', res)
}
})

Use the Pool client instance to create the Postgres table

The function ends after when the concatenated CREATE TABLE string is passed to the method client.query(). After this API call is made, the client will be released.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
await client.query(newTableSql, (err, res) => {
// check for errors with client.query()
if (err) {
console.log('nCREATE TABLE ->', err)
}
if (res) {
console.log('nCREATE TABLE result:', res)
}

// Release the pg client instance after last query
client.release()
console.log("Client is released")
})
}
}

createTable()

NOTE: Remember to call the function after you add the last curly-bracket.

Conclusion to the NodeJs and PostgreSQL to create a sample CRUD application

  • Use the command node to execute your NodeJs PostgreSQL CRUD example. Notice that the file name goes after the command node.
1
node create-table.js

Screenshot of a Node app that executes Postgres SQL DROP TABLE & CREATE TABLE queries

This completes the tutorial, “NodeJs and PostgreSQL CRUD Example Application (Part 1),” the first lesson in the multiple-series. You learned how to create a NodeJs and PostgreSQL application and use CRUD, which is to create, read, update, and delete. All computer programs must enable the user or programmer to use CRUD at a very basic level.

You’re now prepared for Part 2 of the NodeJs and PostgreSQL CRUD Example Application. The code below shows what should be entered at the command-line in psql for identical results of that lesson. Start Part 2 to complete the examples and read the detailed instructions.

1
2
3
4
5
6
7
CREATE TABLE employee(
id SERIAL NOT NULL,
name VARCHAR(255) NOT NULL,
address TEXT NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL
);

Just the Code

Here’s the entire code sample the NodeJs PostgreSQL CRUD example shown in this part of the multiple-series tutorial.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
const Pool = require("pg").Pool;

// Declare a new client instance from Pool()
const pool = new Pool({
  user: "some_username",
  host: "localhost",
  database: "some_db",
  password: "1234",
  port: "5432"
});

// Declare a string for the CRUD Postgres table
const tableName = "employee";

// Declare a constant for the CREATE TABLE SQL statement
const newTableSql = `CREATE TABLE ${tableName} (
id SERIAL NOT NULL,
name VARCHAR(255) NOT NULL,
address TEXT NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL
);`;

async function createTable() {
  // Promise chain for pg Pool client
  const client = await pool
    .connect()

    .catch(err => {
      console.log("pool .connect ->", err);
    });

  // Check that the pg client is valid
  if (client !== undefined) {
    await client.query(`DROP TABLE ${tableName};`, (err, res) => {
      // client is ready for the query() API call
      console.log("nclient ready:", client.readyForQuery, "n");

      // check for errors with client.query()
      if (err) {
        console.log("DROP TABLE ->", err);
      }
      if (res) {
        console.log("DROP TABLE result:", res);
      }
    });

    await client.query(newTableSql, (err, res) => {
      // check for errors with client.query()
      if (err) {
        console.log("nCREATE TABLE ->", err);
      }
      if (res) {
        console.log("nCREATE TABLE result:", res);
      }

      // Release the pg client instance after last query
      client.release();
      console.log("Client is released");
    });
  }
}

createTable();
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.