Stop 06 · Databases (SQL & NoSQL)Topics · 2/8

PostgreSQL/MySQL

Part of the Full Stack Developer Roadmap

In short

Arrays in memory vanish when your server restarts. PostgreSQL and MySQL keep data safe on disk, enforce your rules and answer questions in SQL. Most full stack jobs expect you to know one well.

The two most widely used open-source relational databases — install one locally and get comfortable with basic SQL before reaching for an ORM to abstract it.

The mascot hands a slip to a strict clerk writing in a large ruled register.

Think of it like this

The strict school office clerk

The office clerk keeps registers with fixed columns: admissions, fees, attendance. He refuses an entry with a missing roll number and can tell you in a minute who hasn't paid fees. SQL is how you ask him. The database is the clerk.

Watch it happen

Your API talking to Postgres

React form

Express API

PostgreSQL

POST /api/students

1/6The admin submits a new student from the frontend.

The big ideas

What you need to know

Tables with fixed columns

Each table has typed columns. Every row must fit, so you never find a price stored as 'abc'.

Foreign keys link tables

fee_payments.student_id REFERENCES students(id) means a payment can't point to a student who doesn't exist.

Postgres or MySQL?

Both are solid. Postgres has richer types like JSONB and arrays. MySQL is everywhere on shared hosting. The SQL is mostly the same.

Always use parameters

Pass values as $1, $2 or via an ORM. Building SQL with string concatenation invites SQL injection.

See it in code

Walk through the code

sql

1CREATE TABLE students (2  id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,3  name       TEXT NOT NULL,4  email      TEXT UNIQUE NOT NULL,5  created_at TIMESTAMPTZ DEFAULT now()6);7 8CREATE TABLE fee_payments (9  id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,10  student_id INT NOT NULL REFERENCES students(id),11  amount     INT NOT NULL CHECK (amount > 0)12);13 14INSERT INTO students (name, email) VALUES ('Riya', 'riya@example.com') RETURNING id;15SELECT s.name, COALESCE(SUM(f.amount), 0) AS paid FROM students s16LEFT JOIN fee_payments f ON f.student_id = s.id GROUP BY s.id, s.name;

1/4A students table with an auto-generated id, required name and a unique email.

This is PostgreSQL. In MySQL you'd write INT AUTO_INCREMENT PRIMARY KEY and use a separate SELECT instead of RETURNING.

Save yourself the pain

Common mistakes

Oops

Building queries like "SELECT * FROM users WHERE email = '" + email + "'".

Do this instead

Use parameters: query('SELECT * FROM users WHERE email = $1', [email]). The driver escapes it safely.

Oops

Doing the totals in JavaScript after fetching every row.

Do this instead

Let the database SUM, COUNT and GROUP BY. It's faster and sends far less data to your API.

Your mission

Set up your first real database

~40 min

Quick check

You try to insert a fee payment with student_id = 999, but no such student exists. What happens?

Go deeper

1Install PostgreSQL (or MySQL) locally and connect with a GUI client.
2Practice SELECT, INSERT, UPDATE and DELETE against sample data.
3Learn `psql` or the equivalent CLI for quick queries and debugging.