Yesterday, my React frontend finally talked to my Node.js backend. But there was one problem...
The users were FAKE. Hardcoded in server.js. Every time the server restarted, any new users would disappear forever.
Today, I fixed that permanently.
I installed PostgreSQL, created a database, built a users table, and stored real data that survives server restarts. And I learned WAY more about databases than I expected.
First: What Even IS a Database?
Before writing a single command, I needed to understand what a database actually IS.
Think of it like this:
| Real World | Database |
|---|---|
| A filing cabinet | The database itself |
| A drawer in the cabinet | A table |
| A folder in the drawer | A row (record) |
| Information on the folder | Columns (fields) |
For my users:
- One drawer called
users - Each folder has: an ID number, a name, an email address, a creation date
- I can open any folder, read it, change it, or throw it away
The magic: Even if I turn off my computer, the filing cabinet (database) still has all my folders.
Step 1: Installing PostgreSQL
I'm on Ubuntu Linux, so installation was straightforward:
# Update my package list (like refreshing the app store)
sudo apt update
# Install PostgreSQL
sudo apt install postgresql postgresql-contrib -y
# Verify it's running
sudo systemctl status postgresql
What I learned about each command:
| Command | What it actually does |
|---|---|
sudo |
"Super user do" — gives admin permissions |
apt update |
Refreshes the list of available software |
apt install postgresql |
Downloads and installs the database |
-y |
Automatically answers "yes" to confirmation prompts |
The moment I knew it worked: Seeing Active: active (running) in green text.
Step 2: Understanding PostgreSQL Users
When PostgreSQL installs, it automatically creates:
- A system user called
postgres(the admin/manager) - A default database also called
postgres
Analogy:
| Concept | Analogy |
|---|---|
| PostgreSQL | A bank vault |
postgres user |
The bank manager (has all the keys) |
| My job | Ask the manager to create a vault for MY project |
To become the manager:
sudo -u postgres psql
My prompt changed from: mkangeth@LAP-018:~$
To: postgres=#
Note: That
postgres=#means I'm INSIDE PostgreSQL now. Different commands work here.
Step 3: My First PostgreSQL Commands
Seeing what databases already exist:
\l
This showed me three databases: postgres, template0, template1. None were mine.
Creating MY database:
CREATE DATABASE microservices_platform;
Response: CREATE DATABASE — it worked!
Creating a user for my app:
CREATE USER app_user WITH PASSWORD 'mysecretpassword';
Response: CREATE ROLE
Giving my user permission:
GRANT ALL PRIVILEGES ON DATABASE microservices_platform TO app_user;
Response: GRANT
Connecting to MY database:
\c microservices_platform
Response: You are now connected to database "microservices_platform"
Notice: My prompt changed to
microservices_platform=#
Step 4: Creating My First Table
Now for the exciting part — creating a table to store users.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Let me explain EVERY piece:
| Part | What it means |
|---|---|
CREATE TABLE users |
Make a new table called "users" |
id SERIAL PRIMARY KEY |
Auto-number each user (1,2,3...) and use it as the unique identifier |
name VARCHAR(100) NOT NULL |
Name column, max 100 characters, can't be empty |
email VARCHAR(100) UNIQUE NOT NULL |
Email column, max 100 chars, no duplicates, can't be empty |
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
Automatically sets the current time when a user is added |
Response: CREATE TABLE — my table exists!
Step 5: Inserting Data
Time to add some users:
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
Response: INSERT 0 3 — three users added successfully.
Step 6: Reading My Data
The moment of truth — seeing what's actually in my database:
SELECT * FROM users;
Output:
id | name | email | created_at
----+---------+---------------------+----------------------------
1 | Alice | alice@example.com | 2026-04-08 09:57:56.360654
2 | Bob | bob@example.com | 2026-04-08 09:57:56.360654
3 | Charlie | charlie@example.com | 2026-04-08 09:57:56.360654
(3 rows)
I literally cheered. My data was stored permanently in a real database!
Step 7: Exiting PostgreSQL
\q
Back to my normal terminal prompt.
What I Learned About Databases (Deep Dive)
Why PostgreSQL Specifically?
| Feature | Why It Matters |
|---|---|
| ACID compliance | Transactions are atomic — either fully complete or fully fail (no partial updates) |
| MVCC | Multiple users can read/write simultaneously without locking each other |
| Advanced data types | JSON, arrays, hstore, geometric data — not just strings and numbers |
| Standards compliant | Learning PostgreSQL teaches you SQL that works on Oracle, DB2, etc. |
The Data Types I Used
| Type | What it stores | Example |
|---|---|---|
SERIAL |
Auto-incrementing integer | 1, 2, 3, 4... |
VARCHAR(100) |
Text up to 100 characters | "Alice" |
TIMESTAMP |
Date and time | "2026-04-08 09:57:56" |
The Constraints I Used
| Constraint | What it does |
|---|---|
PRIMARY KEY |
Uniquely identifies each row (no duplicates, never null) |
NOT NULL |
This field must have a value |
UNIQUE |
No two rows can have the same value in this column |
DEFAULT |
If no value is provided, use this automatic value |
Mistakes I Made (And Fixed)
Mistake 1: Typing PostgreSQL commands in the normal terminal
What happened: I typed \c at mkangeth@LAP-018:~$ and got command not found
Why: \c is a PostgreSQL command, not a Linux command
Fix: Enter PostgreSQL first with sudo -u postgres psql
Mistake 2: Forgetting the semicolon
What happened: I typed CREATE DATABASE microservices_platform and nothing happened
Why: PostgreSQL waits for ; to know the command is complete
Fix: Always end SQL commands with ;
Mistake 3: Incomplete CREATE DATABASE
What happened: I typed CREATE DATABASE microservices and got stuck at a postgres-# prompt
Why: I didn't finish the command
Fix: Press Ctrl+C to cancel, then retype the full command
PostgreSQL vs Other Databases (What I Learned)
| Database | Best For | Why |
|---|---|---|
| PostgreSQL | Complex apps, data integrity | Full SQL support, ACID compliant |
| MySQL | Simple web apps, WordPress | Fast reads, easier to learn |
| SQLite | Mobile apps, local storage | No server needed, single file |
| MongoDB | Flexible schema, JSON data | No fixed structure, horizontal scaling |
| Firebase | Real-time apps, prototyping | Built-in auth, real-time updates |
| Supabase | PostgreSQL + convenience | Open source, real-time, auto-API |
Why I chose PostgreSQL for this project: It's the industry standard for serious applications. Apple, Netflix, and Uber all use it.
Key Takeaways
- Databases are permanent storage — unlike server memory, data survives restarts
- Tables have structure — columns define what data looks like (types, constraints)
- PostgreSQL is powerful — ACID compliance, advanced data types, concurrency handling
-
SQL is the language —
CREATE,INSERT,SELECT,UPDATE,DELETE -
The prompt tells you where you are —
$= terminal,=#= PostgreSQL
Quick Reference: PostgreSQL Commands I Learned
| Command | What it does |
|---|---|
sudo -u postgres psql |
Enter PostgreSQL as admin |
\l |
List all databases |
\c database_name |
Connect to a specific database |
\d |
List all tables in current database |
\d table_name |
Show table structure |
CREATE DATABASE name; |
Create a new database |
CREATE USER name WITH PASSWORD 'pass'; |
Create a new user |
GRANT ALL PRIVILEGES ON DATABASE db TO user; |
Give user permissions |
CREATE TABLE ... |
Create a new table |
INSERT INTO ... VALUES ... |
Add data to a table |
SELECT * FROM ... |
Read data from a table |
\q |
Exit PostgreSQL |
Resources
Let's Connect!
Have you worked with databases before? SQL or NoSQL? What's your preference and why?
Drop a comment or connect on LinkedIn. Let's learn together!
Top comments (0)