Key Takeaways
- PostgreSQL is a reliable, mature relational database that handles complex queries and relationships well, making it a solid default choice for Node.js projects.
- The pg package is the standard way to connect Node.js to PostgreSQL, handling connection pooling and query execution with minimal setup.
- Storing database credentials in environment variables keeps sensitive information out of your codebase and lets the same code run cleanly across local and production environments.
Storing app data somewhere reliable is one of those things that has to be sorted out early on any real Node.js project. PostgreSQL is a common pick for this, it’s been around forever, plays nicely with pretty much any Node.js setup, and doesn’t come with any weird surprises later.
Node.js talks to Postgres through a package called pg. It handles the connection for you, so all you really do is write your SQL, run it, and get the results back as regular JavaScript objects to use however you want.
In this blog, I’ll go into why Postgres pairs well with Node.js, walk through how the two actually connect, and then build a small book tracker app to bring it all together. Lastly, I’ll deploy the whole thing on a live Cloudways server.
Why PostgreSQL Works Well With Node.js
Honestly, the main thing I like about Postgres is how boringly reliable it is. It’s been in serious production use for decades. Queries stay fast even when they get complicated, relationships between tables work exactly how you’d expect, and I’ve almost never had it just… break on me.
On the Node side, the pg package is well-maintained and used just about everywhere. Install it, hand it your credentials, start running queries. Nothing more to it.
Postgres also has this handy thing where you can throw JSON straight into a column if some of your data doesn’t quite fit a normal table structure. So you get NoSQL-style flexibility when you need it, without switching databases.
The stack is popular too, which matters more than people think. When you inevitably hit some weird edge case at 11pm, there’s a decade of Stack Overflow threads waiting for you.
How Connecting Node.js to PostgreSQL Actually Works
At a high level, this is pretty simple. Your Node.js app uses the pg package to open a connection to Postgres, sends over some SQL, and gets rows back. That’s really the whole idea.
The one thing worth being careful with is where your credentials live. Hardcoding your database host, username, and password directly in your source is a bad habit, especially since the same code has to work locally and in production with totally different values. Environment variables solve this. You keep the credentials in a .env file locally, and set them separately on your live server.
One other thing pg does behind the scenes is connection pooling. Instead of opening a new database connection for every request, it holds onto a small pool of connections and hands them out as needed. Not something you have to think about much, but it saves you a lot of headaches once your app actually starts getting real traffic.
How to Build a Book Tracker With Node.js and PostgreSQL
To show you how this works in practice, I’ll build a mini project, a small book tracker where I can add books to a list, mark them as read, and delete the ones I don’t want anymore.
What I’ll Be Using
- Node.js
- PostgreSQL 17 (standalone binary)
- VS Code
- Express, EJS, and the pg Postgres client
Deploy Node.js Apps Without the Hassle
Cloudways Managed Node.js Hosting comes with PostgreSQL bundled in, so you can go from Git repo to live server without setting up a separate database.
Step 1: Setting Up PostgreSQL Locally
Before writing any code, I need Postgres itself on my machine.
I’m working on my office laptop, which has IT restrictions, so I’ll get the standalone binary version instead of running an installer. I’ll head over to enterprisedb.com/download-postgresql-binaries, pick version 17 for Windows x86-64, and download it.
Once it’s downloaded, I’ll unzip it into my Downloads folder.
Pointing Command Prompt to the PostgreSQL binary
Then I’ll open Command Prompt and point it to the Postgres bin folder so I can actually run Postgres commands:
set PATH=%PATH%;C:\Users\abdulrehman\Downloads\postgresql-17.10-2-windows-x64-binaries\pgsql\bin
To make sure it’s actually working, I’ll run:
postgres --version
If it prints a version number, I’m good to continue.
Initializing the data directory
Postgres needs a folder somewhere to store its data. I’ll create one and initialize it:
mkdir C:\Users\abdulrehman\pgdata initdb -D C:\Users\abdulrehman\pgdata -U postgres
The -U postgres part sets postgres as the admin username. On Windows, initdb creates this user without a password, so I’ll set one manually in the next step.
Starting the PostgreSQL server
Now I’ll actually start the server:
pg_ctl -D C:\Users\abdulrehman\pgdata -l C:\Users\abdulrehman\pgdata\logfile start
This runs it in the background so I don’t need to keep a terminal open for it.
Setting a password for the postgres user
With the server running, I’ll open the Postgres shell and set a password for the postgres user:
psql -U postgres
Once inside the shell, I’ll run:
ALTER USER postgres WITH PASSWORD 'postgres';
Then I’ll type \q to exit.
I’m going with postgres as the password since this is only for local dev.
Creating a database for the project
Last thing, I’ll create an empty database for this project:
createdb -U postgres booktracker
Then, to verify it got created, I’ll list all the databases:
psql -U postgres -l
If I see booktracker in the list, Postgres is set up and ready for the app to use.
Step 2: Setting Up the Node.js Project
Now for the app. Before I can do anything, I need Node.js on my machine.
Same as with Postgres, I’ll grab the standalone binary version of Node.js from nodejs.org instead of running an installer, and unzip it into my Downloads folder.
Opening project folder in Command Prompt
I’ll create a folder for this project on my desktop and just call it node-postgres-book-tracker.
Then I’ll open up Command Prompt and move to my project folder:
cd C:\Users\abdulrehman\Desktop\node-postgres-book-tracker
Pointing CMD to the Node binary
Since my Command Prompt doesn’t know where the Node folder is on my machine, I’ll have to tell it manually. To that I’ll run this command:
set PATH=%PATH%;C:\Users\abdulrehman\Downloads\node-v24.18.0-win-x64\node-v24.18.0-win-x64
To verify if everything is set up properly, I’ll run:
node -v npm -v
Both commands are returning version numbers, so I’m good to move forward.
Creating the Node.js project
I’ll create a plain Node.js project first using this command:
npm init -y
Then I’ll install everything this project actually needs: Express to handle routing, EJS to render HTML, the official pg package to connect to Postgres, and dotenv to manage my credentials locally.
npm install express ejs pg dotenv
Step 3: Building the Book Tracker App
Now I’ll open my project in VS Code. To do this, I’ll click on File > Open Folder and select my node-postgres-book-tracker folder.
Alright, my project’s now opened in VS Code, so it’s time to actually start building the app.
Creating the books table locally
First, I need a table in Postgres to store the books in. To do this, back in Command Prompt, I’ll open an interactive Postgres shell:
psql -U postgres -d booktracker
Then I’ll paste in the SQL to create the table:
CREATE TABLE books ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, is_read BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Then I’ll type \q to exit the shell.
The table has an auto-incrementing ID, title and author fields, a boolean for whether I’ve read the book, and a timestamp for when I added it.
Securing the Database Credentials
Next, I want to get my Postgres credentials into the project somewhere safe. To do this, in the root of my project folder, I’ll create a new file called .env and paste in:
DB_USER=postgres DB_PASSWORD=postgres DB_HOST=localhost DB_PORT=5432 DB_NAME=booktracker
These are the local values that match what I set up in Step 1. I’ll swap them out for the real production values later when I deploy.
Now, since these are my actual credentials, I don’t want them in my public GitHub repo. To prevent this, I’ll create one more file in my project root called .gitignore, and paste in:
node_modules .env
This tells Git to skip both whenever I push my project. node_modules because it’s just downloaded packages, no reason to upload those. And .env because it holds my actual credentials, so leaving it out means they never end up somewhere public.
Setting up the Database Connection
Next up, I need something that actually connects my app to Postgres. To do this, I’ll create a db folder in the project root, and inside it, a file called index.js.
require('dotenv').config();
const { Pool } = require('pg');
const pool = new Pool({
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
host: process.env.DB_HOST,
port: process.env.DB_PORT,
database: process.env.DB_NAME,
});
module.exports = pool;
Nothing fancy in here. Just a connection pool set up once so any file in the project can import it and start querying.
Building the Server
This is where I’ll build the actual app: it fetches the books from Postgres, renders them out, and handles adding, updating, and deleting.
I’ll create a file called server.js in my project root, and paste this in:
const express = require('express');
const path = require('path');
const pool = require('./db');
const app = express();
const PORT = process.env.PORT || 3000;
app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, 'views'));
app.use(express.static(path.join(__dirname, 'public')));
app.use(express.urlencoded({ extended: true }));
app.get('/', async (req, res) => {
try {
const result = await pool.query('SELECT * FROM books ORDER BY created_at DESC');
res.render('index', { books: result.rows });
} catch (err) {
console.error(err);
res.status(500).send('Something went wrong loading books.');
}
});
app.post('/add', async (req, res) => {
const { title, author } = req.body;
try {
await pool.query('INSERT INTO books (title, author) VALUES ($1, $2)', [title, author]);
res.redirect('/');
} catch (err) {
console.error(err);
res.status(500).send('Something went wrong adding the book.');
}
});
app.post('/toggle/:id', async (req, res) => {
try {
await pool.query('UPDATE books SET is_read = NOT is_read WHERE id = $1', [req.params.id]);
res.redirect('/');
} catch (err) {
console.error(err);
res.status(500).send('Something went wrong updating the book.');
}
});
app.post('/delete/:id', async (req, res) => {
try {
await pool.query('DELETE FROM books WHERE id = $1', [req.params.id]);
res.redirect('/');
} catch (err) {
console.error(err);
res.status(500).send('Something went wrong deleting the book.');
}
});
app.listen(PORT, () => {
console.log(Server running on port ${PORT});
});
So the routes handle everything the app needs, listing the books, adding one, toggling the read status, and deleting. I’ve wrapped each query inside try-catch so nothing crashes the whole app if a query goes bad.
That server.js points to a template called index that doesn’t exist yet, so next I’ll create the page it needs.
Building the UI
I’ll create a folder called views in my project root, and inside it, a file called index.ejs. I’ll paste this in:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Book Tracker</title>
<link rel="stylesheet" href="/style.css">
</head>
<body>
<main>
<div class="card">
<h1>Book Tracker</h1>
<form action="/add" method="POST" class="add-form">
<input type="text" name="title" placeholder="Book title" required>
<input type="text" name="author" placeholder="Author" required>
<button type="submit">Add Book</button>
</form>
<ul class="book-list">
<% books.forEach(book => { %>
<li class="<%= book.is_read ? 'read' : '' %>">
<div class="book-info">
<strong><%= book.title %></strong>
<span>by <%= book.author %></span>
</div>
<div class="actions">
<form action="/toggle/<%= book.id %>" method="POST">
<button type="submit"><%= book.is_read ? 'Unread' : 'Read' %></button>
</form>
<form action="/delete/<%= book.id %>" method="POST">
<button type="submit" class="delete">Delete</button>
</form>
</div>
</li>
<% }) %>
</ul>
</div>
</main>
</body>
</html>
And last thing for this step, I’ll add one more file, a public folder with a style.css file inside it. I’ll paste this in:
* {
box-sizing: border-box;
}
body {
font-family: -apple-system, BlinkMacSystemFont, sans-serif;
background: linear-gradient(135deg, #4f46e5, #7c3aed);
margin: 0;
min-height: 100vh;
display: flex;
align-items: center;
justify-content: center;
padding: 20px;
}
main {
width: 100%;
max-width: 600px;
}
.card {
background: #ffffff;
border-radius: 12px;
padding: 40px;
box-shadow: 0 20px 60px rgba(0, 0, 0, 0.2);
}
h1 {
margin-top: 0;
color: #1f2937;
}
.add-form {
display: flex;
gap: 8px;
margin: 20px 0;
}
.add-form input {
flex: 1;
padding: 10px 12px;
border: 1px solid #d1d5db;
border-radius: 6px;
font-size: 14px;
}
.add-form button {
background: #4f46e5;
color: #ffffff;
border: none;
padding: 10px 18px;
border-radius: 6px;
cursor: pointer;
font-size: 14px;
}
.book-list {
list-style: none;
padding: 0;
margin: 0;
}
.book-list li {
display: flex;
justify-content: space-between;
align-items: center;
padding: 14px;
background: #f9fafb;
border-radius: 8px;
margin-bottom: 10px;
}
.book-list li.read {
opacity: 0.6;
text-decoration: line-through;
}
.book-info {
display: flex;
flex-direction: column;
}
.book-info span {
color: #6b7280;
font-size: 13px;
}
.actions {
display: flex;
gap: 6px;
}
.actions button {
padding: 6px 12px;
border: none;
border-radius: 5px;
cursor: pointer;
font-size: 12px;
background: #e5e7eb;
}
.actions .delete {
background: #fee2e2;
color: #b91c1c;
}
Testing What We Built
Time to actually test this. I’ll add a start script to my package.json so I don’t have to keep typing the full node command:
"scripts": {
"start": "node server.js",
"test": "echo "Error: no test specified" && exit 1"
}
Then, I’ll go back in Command Prompt and run:
npm start
And I’ll open http://localhost:3000 in the browser. I should see the book tracker with an empty list. I’ll add a book, click Read to mark it as read, and then hit Delete to remove it.
Since everything’s flowing exactly the way I want, the connection’s doing its job. Data’s going into Postgres and coming back out into the HTML this little Express server is putting together.
Step 4: Pushing the Project to GitHub
Before I deploy my nodejs postgresql project on Cloudways, I want this pushed to GitHub first.
I’ll head over to GitHub and create a new repository called node-postgres-book-tracker.
Then, back in Command Prompt, I’ll run these one at a time to upload my project:
git init git add . git commit -m "First commit" git branch -M main git remote add origin https://github.com/abdulrehman293/node-postgres-book-tracker git push -u origin main
I’ll head back to the repo and give it a refresh, and yep, everything’s there, minus the .env file and node_modules, which is exactly how it should be.
Step 5: Deploying to Cloudways
At this point, my project is working on the local setup and it’s also pushed to GitHub. Now I’ll move it from local to a live server. For this, I’ll use Cloudways’ Managed Node.js Hosting, which comes with a PostgreSQL database bundled in, so I don’t need to set up any external database service.
Inside the Cloudways dashboard, I’ll click Node.js from the left-side menu, then hit Launch Now.
From here, I’ll choose a plan. This project doesn’t need too many resources, so the Starter plan is good enough.
Next, on the Deploy Your Node.js Web App screen, I’ll click on Connect Via Git. Once my Git is connected, I’ll select my node-postgres-book-tracker repo and continue.
Step 6: Configuring and Going Live
Now, I’ll tell Cloudways how to run the project. To do this, I’ll set Framework Preset to Express. And for the Node Version, I’ll pick Node 24 (LTS). For Root Directory, I’ll leave it as default.
Next, I’ll click Change under Build and output settings to expand it, then set Package Manager to npm, and Entry File to server.js.
Now, since my .env file intentionally never made it into GitHub, I need to add my database values back in manually here. But the thing is, the Cloudways database will only get created once the app itself is created. So at this point, I don’t have the real values.
To work around this, I’ll deploy first with placeholder values, then come back and update them once the app exists and the real database credentials are available.
I’ll go to Environment Variables and click Add. This opens a modal where I can add my variables one at a time.
I’ll add all five, using placeholders for now:
- DB_USER — set to placeholder
- DB_PASSWORD — set to placeholder
- DB_HOST — set to localhost
- DB_PORT — set to 5432
- DB_NAME — set to placeholder
Once all five are in, I’ll click Save, then hit Deploy Now.
Once I hit Deploy Now, Cloudways will start pulling the code straight from GitHub, install the dependencies, and start the app using the entry file I pointed it to.
But after the deployment completes, the app itself will start up fine, but any database queries will fail for now, which is expected since the credentials are placeholders.
After the deployment completes, I’ll open the Database tab and grab the auto-generated DB Name, Username, and Password for this app.
Then I’ll head back to Environment Variables, update DB_USER, DB_PASSWORD, and DB_NAME with the real values.
Lastly, what I’ll do is a manual Redeploy.
Step 7: Creating the Books Table on Cloudways
At this point, the app is deployed and the database credentials are hooked up, but the production database itself is still completely empty. It doesn’t have the books table yet, so if I open the live URL right now, it’ll error out trying to fetch books that aren’t there.
To fix this, I’ll head back into the Database section on Cloudways and click Launch Database Manager.
This opens up a browser-based Postgres client.
In the small panel on the left, I’ll click SQL command, and paste in the same CREATE TABLE statement I ran locally:
CREATE TABLE books ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, is_read BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
I’ll hit Execute, and the table gets created on the production DB.
Moment of Truth
Now, I’ll open the temporary Cloudways URL for my app.
The book tracker should load up empty, just like it did locally. I’ll add a book, mark it as read, and then delete it.
So, everything is working perfectly. The data is getting stored in the Cloudways-managed Postgres database. Any book I add from here on out sticks around as long as the app’s running.
Wrapping Up
And that’s it. Postgres set up locally, small book tracker app built on top, and the whole thing deployed to a live server.
The finished project is up on my GitHub if you want to clone it and mess around with the code yourself.
And if you’re planning on hosting a Node.js app of your own somewhere, worth knowing our Managed Node.js Hosting ships with PostgreSQL out of the box. So you can go straight from your Git repo to something live without lining up an external database service first.
Ready to Deploy Your Own Node.js Project?
Spin up a server with Managed Node.js Hosting and PostgreSQL bundled in, and get your app live in minutes.
Q. Can I use an ORM like Sequelize or Prisma instead of pg?
Yeah, totally. pg is nice when you’re happy writing raw SQL yourself. But on a bigger project, or if you’d rather work with data as objects than raw rows, something like Sequelize or Prisma will save you some time. It’s another thing to learn, though.
Q. Do I need to install PostgreSQL locally to develop with it?
Not required, but honestly, I’d recommend it. If you point your local app at a remote Postgres, every tiny code change has to make a network trip before you know it’s working. Local Postgres cuts all of that out.
Q. How do I keep my database credentials secure?
Environment variables. Never put them in your actual code. Locally that means a .env file that’s in your .gitignore so it doesn’t get pushed anywhere. On a live server, the hosting platform stores those values on its end and passes them into your app when it runs.
Q. Why PostgreSQL over MySQL or MongoDB for a Node.js project?
Depends on your use case. For most Node.js projects, Postgres is a safe pick because it does relational data well, deals with complicated queries, and supports JSON if you want a bit of NoSQL flavor. MySQL is comparable but slightly less capable. MongoDB works on a completely different model (document-based) that’s a great fit for some things and a really awkward one for others.
Start Growing with Cloudways Today.
Our Clients Love us because we never compromise on these
Abdul Rehman
Abdul is a tech-savvy, coffee-fueled, and creatively driven marketer who loves keeping up with the latest software updates and tech gadgets. He's also a skilled technical writer who can explain complex concepts simply for a broad audience. Abdul enjoys sharing his knowledge of the Cloud industry through user manuals, documentation, and blog posts.
