NodeJs and PostgreSQL CRUD Example Application (Part 1)
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 commandservice 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.
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 yourRole
doesn’t have them.
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 thepg
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. Theawait
syntax is used in thisasync
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 commandnode
.
1 | node create-table.js |
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(); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started