NodeJS is a JavaScript-based programming language and you can run JavaScript code on your server and then create applications using it. In the real world, most applications will need a database to store and retrieve information. Postgres is a relational database that helps you store your data and perform actions over it.
What are you going to build?
You will create a NodeJS application and then use the Postgres.js npm module to connect to the Postgres database and then perform the below operations on the database
- Create
- Read
- Update
- Delete
Prerequisite
There are some prerequisite that needs to be fulfilled before you start coding.
- Postgres Database must be installed. You can follow the below links to install the Postgres database
- NodeJS and NPM must be installed
Initialize the NodeJS project
you can use the npm tool to create a new NodeJS project.
Create a new directory named nodejs-postgres-application and move inside the project directory
mkdir nodejs-postgres-application
cd nodejs-postgres-applicationonce you are inside the directory initialize the new NodeJS project using the below command.
npm initThe NPM tool will ask you a few questions. Just keep hitting the enter/return key and this will create a package.json file in the project directory.
package.json file holds all the configuration related to your NodeJS project like dependencies, the main entry file, etc.
You can open the directory inside any suitable IDE to start working on the project.
Install Postgres.js npm module
Postgres.js npm module will help your NodeJS application to connect to the Postgres database and then run the commands over it
$ npm install postgres --saveThis will install the postgre.js module and also save it as a dependency in the package.json file
Todo application Postgres database and table creation
You will create a to-do application database and perform different operations.
Run the below command inside the Postgres database and this will create the todo_application database
create database todo_application;
once the database is created then you need to create the tasks table
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
name text NOT NULL,
active boolean NOT NULL,
created_at DATE,
updated_at DATE DEFAULT now()
);
You can view the details of the table using the below command
\d <table-name>
Connect to the Postgres Database
Create a new file named index.js
This file is the entry point of our application. whenever you run the application then the code inside the index.js file will be executed.
touch index.jsPostgres database connection configuration
If you want to connect to any database then you need to provide some configuration to the database connect command. In Postgres, you need to provide the below configuration
const datbaseConfiguration = {
"host": "localhost",
"port": 5432,
"database": "todo_application",
"username": "postgres",
"password": "postgres"
}we have used Install Postgres using docker-compose tutorial to setup the Postgres server so the configuration value will be based on that.
- host = localhost
- This is the server IP address where your Postgres is running.
- port = 5432
- This is the default port for the Postgres server
- database = todo_application
- you have created this database in the above steps
- username = postgres
- This is the username used in the Postgres setup tutorial
- password = postgres
- This is the password used in the Postgres setup tutorial
For connecting to the Postgres database insert below lines in index.js file
const postgres = require("postgres")
const datbaseConfiguration = {
"host": "localhost",
"port": 5432,
"database": "todo_application",
"username": "postgres",
"password": "postgres"
}
const sql = postgres(datbaseConfiguration)- you import the Postgres npm module
- Then create a database configuration object as described above section
- Finally provide the configuration to the Postgres library to instantiate the database connection
- the sql variable stores the reference of the database connection.
- you can use the sql variable to execute the database commands
Syntax to run any query using Postgres.js npm module
The sql instance/connection object will be used to run any query in the Postgres database. The syntax to be used is
await sql`<put-your-query-here>`You need to replace <put-your-query-here> with the actual Postgres query.
Insert Task into tasks table
let’s try to insert some dummy tasks into our todo-application.
Columns in the tasks table are
- id : This is auto-generated so we will not provide this in our query
- name : This is mandatory and will be the name of our task
- active : All the tasks will be active when it is created so the value will be true
- created_at : This value will be the current timestamp
- updated_at : This will be handled at the database layer as the default value is now() so this is not mandatory to be present in the insert command
Create a new method named insertTasks that will try to insert few task into the database
async function insertTasks(){
await sql`insert into tasks ${sql({
"name": "create ten new videos on youtube",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`;
await sql`insert into tasks ${sql({
"name": "write 10 blog post",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
await sql`insert into tasks ${sql({
"name": "Buy a new car",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
}You will use the dynamic query for inserting the data. The dynamic query will be provided by ${sql())
- The first parameter will be the object that will contain the values that we want to insert
- The order of the keys is not important in the object provided
- The next parameters will be the actual column names
Let’s run the method and see the content in the table. The final index.js file will be
const postgres = require("postgres")
const datbaseConfiguration = {
"host": "localhost",
"port": 5432,
"database": "todo_application",
"username": "postgres",
"password": "postgres"
}
const sql = postgres(datbaseConfiguration)
async function insertTasks(){
await sql`insert into tasks ${sql({
"name": "create ten new videos on youtube",
"created_at": new Date().toISOString(),
"active": true
}, 'name','active','created_at')}`;
await sql`insert into tasks ${sql({
"name": "write 10 blog post",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
await sql`insert into tasks ${sql({
"name": "Buy a new car",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
}
insertTasks()The insert task method is run at the bottom of the index.js file.
The content in the tasks table will look something like this
Read Tasks from the tasks table
All the tasks are stored in the tasks table.
Create a new method named getAllTasks to read all the tasks from the database. we will print the tasks inside the method itself
async function getAllTasks(){
const tasks = await sql`select * from tasks`;
console.log(tasks)
return tasks;
}let’s put this method in the index.js file and execute the method
const postgres = require("postgres")
const datbaseConfiguration = {
"host": "localhost",
"port": 5432,
"database": "todo_application",
"username": "postgres",
"password": "postgres"
}
const sql = postgres(datbaseConfiguration)
async function insertTasks(){
await sql`insert into tasks ${sql({
"name": "create ten new videos on youtube",
"created_at": new Date().toISOString(),
"active": true
}, 'name','active','created_at')}`;
await sql`insert into tasks ${sql({
"name": "write 10 blog post",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
await sql`insert into tasks ${sql({
"name": "Buy a new car",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
}
async function getAllTasks(){
const tasks = await sql`select * from tasks`;
console.log(tasks)
return tasks;
}
getAllTasks()The output of this program will be
Update a task in the tasks table
we will try to change the status of the task and mark it as inactive
An inactive status of the task signifies that the task is completed. we will try to update the task with id=2
Create a new method updateTask and then run it
async function updateTask(){
let user = {
"id" : 2,
"active": false
}
await sql`update tasks set ${sql(user,'active')} where id= ${user.id}`
}You will use the dynamic query for inserting the data. The dynamic query will be provided by ${sql())
- The first parameter will be the user object that contains key with values.
- the other parameters are column names that will be updated
Now the index.js file will look something like this
const postgres = require("postgres")
const datbaseConfiguration = {
"host": "localhost",
"port": 5432,
"database": "todo_application",
"username": "postgres",
"password": "postgres"
}
const sql = postgres(datbaseConfiguration)
async function insertTasks(){
await sql`insert into tasks ${sql({
"name": "create ten new videos on youtube",
"created_at": new Date().toISOString(),
"active": true
}, 'name','active','created_at')}`;
await sql`insert into tasks ${sql({
"name": "write 10 blog post",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
await sql`insert into tasks ${sql({
"name": "Buy a new car",
"active": true,
"created_at": new Date().toISOString()
}, 'name','active','created_at')}`
}
async function getAllTasks(){
const tasks = await sql`select * from tasks`;
console.log(tasks)
return tasks;
}
async function updateTask(){
let user = {
"id" : 2,
"active": false
}
await sql`update tasks set ${sql(user,'active')} where id= ${user.id}`
}
updateTask()
The data in the table will be updated and task with id=2 will become inactive
If you want to learn more details and complex queries then you can visit the documentation of the Postgres.js library
Raj Ranjan