Introduction to Databases

Learn the fundamentals of relational databases with PostgreSQL.

Installing PostgreSQL

With Docker (recommended)

docker run --name postgres \  -e POSTGRES_USER=postgres \  -e POSTGRES_PASSWORD=postgres \  -e POSTGRES_DB=ecommerce \  -p 5432:5432 \  -d postgres:15

Relational Concepts

Tables

Store records (rows) with fields (columns).

Primary Key (PK)

Unique field that identifies each record.

Foreign Key (FK)

Field that references another table.

Relationships

• One-to-One: User → Profile
• One-to-Many: Category → Products
• Many-to-Many: Products ↔ Orders

Basic SQL

-- CreateINSERT INTO products (name, price) VALUES ('iPhone', 999.99); -- ReadSELECT * FROM products WHERE price > 100; -- UpdateUPDATE products SET price = 899.99 WHERE id = 1; -- DeleteDELETE FROM products WHERE id = 1;

Relationships

-- One-to-ManySELECT p.name, c.name as categoryFROM products pJOIN categories c ON p.category_id = c.id; -- Many-to-Many (with junction table)SELECT o.id, p.nameFROM orders oJOIN order_items oi ON o.id = oi.order_idJOIN products p ON oi.product_id = p.id;

Why Use an ORM?

• Write code instead of SQL
• Type safety with TypeScript
• Automatic migrations
• Protection against SQL injection

Summary

• ✅ PostgreSQL as main database
• ✅ Tables, PKs and FKs concepts
• ✅ Basic SQL operations
• ✅ Relationships between tables

Next lesson: Prisma ORM! 🚀