Introduction

Before We Start

We already explored MongoDB, a NoSQL document database. Now we'll look at the other side of the database world — relational databases — and specifically PostgreSQL, one of the most popular and feature-rich options available today.

Relational databases are the foundation of most enterprise applications. If you're building something with structured data, complex relationships, or strict data integrity requirements, a relational database is likely the right choice.

What is a Relational Database?

A relational database organizes data into tables (also called relations). Each table has a fixed set of columns that define what kind of data it holds, and each row in the table represents a single record.

Think of it like a spreadsheet:

id	name	age	email
1	Rizwan Ashiq	25	rizwan@example.com
2	Jane Doe	30	jane@example.com

The key strength of relational databases is that tables can be related to each other. For example, a posts table can reference a users table to say which user wrote which post.

Relational vs. Document Databases

Feature	Relational (PostgreSQL)	Document (MongoDB)
Data format	Tables with rows & columns	Collections of JSON documents
Schema	Strict, defined upfront	Flexible, schema-optional
Relationships	Foreign keys & JOIN queries	Embedded documents or refs
Transactions	Full ACID support	Multi-document transactions
Query language	SQL	MongoDB Query Language (MQL)

What is PostgreSQL?

PostgreSQL (often called "Postgres") is a powerful, open-source relational database management system with over 35 years of active development. It is written in C and is known for:

• ACID compliance: Every transaction is Atomic, Consistent, Isolated, and Durable.
• Advanced data types: JSON, arrays, UUID, hstore (key-value), geometric types, and more.
• Extensibility: You can add custom functions, operators, and data types.
• Performance: Handles millions of rows with the right indexes and query planning.
• Full SQL support: Window functions, CTEs, subqueries, stored procedures, triggers, etc.

PostgreSQL is used by companies like Apple, Instagram, Reddit, and Twitch.

Key Terms

Before we start using PostgreSQL, let's get familiar with the terminology.

Database

A logical container that holds all your tables, indexes, and other database objects. You typically have one database per application.

Table

The fundamental unit of storage in a relational database. A table has a fixed set of columns and stores data as rows. It is analogous to a collection in MongoDB.

Creating a table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255) UNIQUE
);

Row

A single record in a table. It is analogous to a document in MongoDB. Every row in the same table has the same columns.

| id | name  | email            |
|----|-------|------------------|
| 1  | Rizwan| rizwan@email.com |

Column

A single field in a table. Every row has the same columns, and each column has a defined data type (e.g., VARCHAR, INTEGER, BOOLEAN, TIMESTAMP).

Primary Key

A column (or combination of columns) that uniquely identifies each row. In PostgreSQL it is common to use SERIAL (auto-incrementing integer) or UUID.

id SERIAL PRIMARY KEY

This is analogous to the _id field in MongoDB.

Foreign Key

A column that references the primary key of another table, creating a relationship between the two tables.

author_id INTEGER REFERENCES users(id)

This is analogous to ref in Mongoose schemas.

Index

A data structure that speeds up data retrieval at the cost of extra storage. The primary key always has an index automatically. You can add indexes to other columns you frequently query or filter by.

Getting Started

Installation

You can install PostgreSQL locally from the official website.

Choose your operating system and follow the installer. The installer also includes pgAdmin, the official GUI.

PostgreSQL Cloud Options

You can also use PostgreSQL as a managed cloud service:

• Neon — serverless Postgres, free tier available
• Supabase — Postgres with a generous free tier
• Railway — one-click Postgres provisioning
• AWS RDS — enterprise-grade managed Postgres

pgAdmin GUI

pgAdmin is a web-based GUI for PostgreSQL. It allows you to browse databases, run SQL queries, view table data, and manage users.

After installation, you can open it in your browser at http://localhost/pgadmin4.

It is to PostgreSQL what MongoDB Compass is to MongoDB.

What's Next?

Now that you understand what PostgreSQL is, in the next section we'll learn about TypeORM — the library we'll use to work with PostgreSQL from Node.js — and how to connect our Express application to a PostgreSQL database.

Conclusion

In this article, we learned about relational databases and PostgreSQL. We covered the key terms: database, table, row, column, primary key, foreign key, and index. We also compared PostgreSQL with MongoDB to help you understand when to reach for each.