PHASE 2 — BACKEND ENGINEERING

PostgreSQL & FastAPI

The Complete Guide to Databases and

Backend Connection Management

Covers: Lesson 16 → Lesson 21

Topics: Why Databases · PostgreSQL Setup · SQL Fundamentals
CRUD Operations · SQLAlchemy ORM · Session & Pool Management

Includes: A complete, runnable FastAPI + PostgreSQL project

How to Use This Document

This document is written in teaching order. Every section follows the same pattern:

1. The Problem — why this concept exists at all

2. The Idea — explained in plain language with a real-life analogy
3. The Architecture — a diagram showing where it fits
4. The Practical — commands and code you type yourself
5. Interview Questions — how to say it out loud in an interview

If you can explain each Architecture diagram from memory, you have understood that section.

Table of Contents

Part Topic Source Lesson

0 The Big Picture —

1 Why Do We Need a Database? Lesson 16

2 Installing PostgreSQL on Ubuntu Lesson 17

3 PostgreSQL Architecture & SQL Fundamentals Lesson 18

4 SQL Basics & Your First Database Lesson 19

5 SQL CRUD Operations Lesson 20

6 Connecting FastAPI to PostgreSQL with SQLAlchemy Lesson 21

7 Proper Connection Management (Production Level) Extension

8 Complete Working Project Extension

9 Consolidated Reference & Cheat Sheets —

PostgreSQL & FastAPI — Phase 2 Guide Page 2 of 56

 Part 0 — The Big Picture

0.1 Where You Were

Until now your backend stored data like this:

students = [
{"id": 1, "name": "Vikas", "age": 20}
]

This works. You can GET , POST , PUT , DELETE on it. But there is one fatal flaw.

Stop the server:

Ctrl + C

Start it again:

uvicorn main:app --reload

All data is gone.

This is because a Python list lives in RAM (temporary memory). RAM is wiped the moment the process ends.

0.2 Where You Are Going

BEFORE AFTER
------ -----

React React
│ │
▼ ▼
FastAPI FastAPI
│ │
▼ ▼
Python List Pydantic (validation)
│ │
▼ ▼
RAM SQLAlchemy (ORM)
│ │
▼ ▼
LOST ON RESTART psycopg2 (driver)
│
▼
PostgreSQL
│
▼
DISK
│
▼
PERMANENT DATA

0.3 The One-Line Summary of Phase 2

FastAPI handles the request. PostgreSQL handles the data. SQLAlchemy is the translator between them, and connection
management is how you make that translation safe and efficient.

PostgreSQL & FastAPI — Phase 2 Guide Page 3 of 56

 Part 1 — Why Do We Need a Database?

(Lesson 16)

1.1 The Problem

Imagine you built a Notes App.

A user creates:

My Notes
↓
Learn Linux
↓
Learn FastAPI
↓
Learn Docker

Everything is stored inside:

notes = []

Looks fine. Now restart the server.

Question: Where are the notes? Answer: Gone.

This is not how real applications behave:

Instagram does not lose all users when its server restarts.
Amazon does not lose products.
ChatGPT does not lose conversations.

Why? Because they store everything in a Database.

1.2 Real-Life Analogy — The Whiteboard vs The Register

Imagine a teacher writing marks on a whiteboard:

Math
↓
Vikas 95
Rahul 89

What happens if someone cleans the board? Everything disappears.

That whiteboard is your Python list — fast, easy, temporary.

Instead, teachers keep records in a Register:

Register
↓
Permanent
↓
Safe

A database is that permanent register.

1.3 What Is a Database?

A Database is software used to permanently store, organize, retrieve, and manage data.

Think of it as a digital warehouse for your application's information.

Notice the four verbs — they are the whole job description:

PostgreSQL & FastAPI — Phase 2 Guide Page 4 of 56

 Verb Meaning

Store Save data permanently on disk

Organize Keep it structured in tables

Retrieve Find exactly what you need, fast

Manage Update, delete, secure, back up

1.4 Without a Database vs With a Database

Without Database

FastAPI
↓
Python List
↓
RAM

Problems:

✘ Lost after restart

✘ Cannot store millions of records
✘ Cannot support multiple users safely
✘ No searching, sorting, or filtering at scale
✘ No backup or recovery

With Database

FastAPI
↓
PostgreSQL
↓
Disk
↓
Permanent

Data survives:

Restart
Shutdown
Tomorrow
Next year

1.5 What Can a Database Store?

Almost everything.

Users Students Products Orders

Chats PDFs Embeddings Research Papers
Images Transactions Logs Settings

Every real application uses a database.

1.6 Real Examples

Instagram stores

Users → Posts → Likes → Comments → Followers

Amazon stores

Products → Customers → Orders → Payments → Inventory

PostgreSQL & FastAPI — Phase 2 Guide Page 5 of 56

ChatGPT stores

Users → Chats → Conversation History → Subscription Plan

Your future AI platform will store

Users → Projects → PDFs → Embeddings → Chat History → AI Agents → Tasks

1.7 Database Architecture (Where It Sits)

React
↓
FastAPI
↓
PostgreSQL
↓
Disk

Notice: FastAPI does not store data itself. It asks PostgreSQL.

1.8 Why Not Just Use Excel?

A very common beginner question.

Imagine 100,000 users in an Excel file. Excel becomes:

✘ Slow — no indexes, it scans everything

✘ Unsafe — one wrong click destroys the file
✘ Difficult to search — no query language
✘ No concurrency — two people cannot write at the same time safely
✘ No transactions — a half-finished payment cannot be rolled back

Databases solve every one of these problems.

1.9 Why PostgreSQL?

There are many databases:

MySQL PostgreSQL MongoDB Oracle SQLite SQL Server

Why PostgreSQL for us?

Reason Meaning

✔ Free No licence cost

✔ Open Source You can read the source, huge community

✔ Extremely reliable ACID compliant, trusted with financial data

✔ Used by startups and large companies Skill is directly employable

✔ Excellent with FastAPI First-class SQLAlchemy + psycopg support

✔ Advanced SQL features JSON columns, full-text search, window functions, extensions like pgvector for AI embeddings

That last point matters for your roadmap: when you build an AI application, PostgreSQL can store your vector embeddings too, using the
pgvector extension. You will not need a separate vector database on day one.

1.10 PostgreSQL in Your Roadmap

PostgreSQL & FastAPI — Phase 2 Guide Page 6 of 56

 FastAPI
↓
PostgreSQL ← you are here
↓
Redis
↓
Docker
↓
Kubernetes
↓
AWS

PostgreSQL is the foundation:

Redis will cache PostgreSQL.

Docker will containerize PostgreSQL.
Kubernetes will orchestrate it.
AWS will host it (usually as RDS).

Everything above builds on this layer.

1.11 Database vs Backend — Two Different Employees

Beginners constantly confuse these. Keep the responsibilities separate in your head.

FastAPI (Backend) PostgreSQL (Database)

Receives HTTP requests ✔ ✘

Validates input ✔ (Pydantic) Partially (constraints)

Business logic / rules ✔ ✘

Authentication ✔ ✘

Saving data permanently ✘ ✔

Searching / filtering at scale ✘ ✔

Updating & deleting records ✘ ✔

Returning JSON ✔ ✘

FastAPI is the manager. PostgreSQL is the storekeeper.

1.12 Full Visual Architecture

React
↓ HTTP Request
FastAPI
↓ Business Logic
PostgreSQL
↓ Rows
FastAPI
↓ JSON Response
React

1.13 Practical — Verify PostgreSQL Is Not Installed Yet

Open your terminal and run:

psql --version

If it says command not found , that is perfectly fine — it just means PostgreSQL is not installed yet.

Also check:

which psql

PostgreSQL & FastAPI — Phase 2 Guide Page 7 of 56

If nothing is returned, PostgreSQL is not installed.

And:

systemctl status postgresql

Expect Unit [Link] could not be found. — again, expected.

1.14 Interview Questions

Q1. What is a database? A database is software used to permanently store, organize, retrieve, and manage application data efficiently.

Q2. Why can't we store data in Python lists? Python lists exist only in memory (RAM). When the application stops, all data is lost. They
also cannot efficiently handle large amounts of data, concurrent users, searching, or crash recovery.

Q3. Why is PostgreSQL widely used? Because it is open-source, reliable, ACID-compliant, scalable, standards-compliant, supports
advanced SQL features, and integrates very well with modern backend frameworks like FastAPI.

Q4. What is the difference between the backend and the database? The backend (FastAPI) handles requests, validation, and
business logic. The database (PostgreSQL) is responsible for persistent storage and efficient retrieval of data. The backend never stores
data itself — it delegates that to the database.

PostgreSQL & FastAPI — Phase 2 Guide Page 8 of 56

 Part 2 — Installing PostgreSQL on Ubuntu

(Lesson 17)

Goal: Install PostgreSQL, understand every component being installed, and verify that it is running correctly.

2.1 First, an Analogy

When you ran:

pip install fastapi

Python downloaded FastAPI plus its dependencies:

FastAPI → Pydantic → Starlette → ...

Similarly, when you install PostgreSQL, Ubuntu installs several components.

2.2 What Actually Gets Installed?

PostgreSQL Server
↓
Database Engine
--------------------
PostgreSQL Client
↓
psql Command
--------------------
systemd Service
↓
Runs PostgreSQL automatically

1. PostgreSQL Server ★
This is the actual database. It is a background process that:

Listens on port 5432

Accepts connections
Reads and writes files on disk
Enforces rules, permissions, and transactions

It stores your Users, Products, Chats, Orders, Research Papers. Without the server, there is no database.

2. PostgreSQL Client ( psql )

psql is a terminal for PostgreSQL.

python → opens Python

psql → opens PostgreSQL

It is not the database. It is a tool that talks to the database. Later, SQLAlchemy will do the same job programmatically.

3. PostgreSQL Service (systemd)

You already learned Linux services:

systemctl start
systemctl stop
systemctl status

PostgreSQL is one of those services — exactly like Docker, Redis, or Nginx.

2.3 Architecture

PostgreSQL & FastAPI — Phase 2 Guide Page 9 of 56

 Ubuntu
↓
systemd
↓
PostgreSQL Service
↓
Database Engine
↓
Your Data (on disk)

2.4 Step-by-Step Installation

Step 1 — Update Ubuntu

sudo apt update

This refreshes Ubuntu's package list so you install the latest available version.

Step 2 — Install PostgreSQL

sudo apt install postgresql postgresql-contrib

Package What it gives you

postgresql The database server + psql client

postgresql-contrib Additional official extensions and utilities ( uuid-ossp , pg_stat_statements , etc.)

Always install both.

Step 3 — Verify the Installation

psql --version

Example output:

psql (PostgreSQL) 16.x

If you see a version number, installation succeeded.

Step 4 — Check the Service Status

systemctl status postgresql

You should see:

Active: active (running)

Meaning PostgreSQL is currently running. Press q to exit the status screen.

Step 5 — Start the Service (if not running)

sudo systemctl start postgresql

Step 6 — Enable Auto-Start ★ (Very Important)

sudo systemctl enable postgresql

Meaning: every time Ubuntu boots, PostgreSQL starts automatically. Without this, your FastAPI app will fail to connect after every reboot.

Step 7 — Check Again

PostgreSQL & FastAPI — Phase 2 Guide Page 10 of 56

 systemctl status postgresql

Should show active (running) .

2.5 What Actually Happens on Boot

Ubuntu Starts
↓
systemd Starts Services
↓
PostgreSQL Starts
↓
Listens on Port 5432
↓
FastAPI Connects
↓
Reads / Writes Data

This is exactly how a real production server behaves.

2.6 Useful Service Commands

sudo systemctl start postgresql # Start

sudo systemctl stop postgresql # Stop
sudo systemctl restart postgresql # Restart
sudo systemctl enable postgresql # Start automatically on boot
sudo systemctl disable postgresql # Do not start on boot
systemctl status postgresql # Check status

2.7 Step 8 — Log In as the postgres User

During installation, Linux created a special system user called postgres .

This is not your Ubuntu user. It is the PostgreSQL administrator account.

Switch to it:

sudo -i -u postgres

Your prompt changes from:

vikaskumar@Ubuntu:~$

to:

postgres@Ubuntu:~$

You are now acting as the PostgreSQL administrator.

Why does this special user exist?

PostgreSQL uses peer authentication by default on Linux. That means:

"If your Linux username matches the PostgreSQL username, let you in without a password."

Since the default database superuser is named postgres , you must become the Linux user postgres to log in as the database superuser
postgres . This is a security design — the database is not exposed with a default password.

2.8 Step 9 — Open PostgreSQL

psql

PostgreSQL & FastAPI — Phase 2 Guide Page 11 of 56

Now you will see:

postgres=#

Congratulations — you are inside PostgreSQL.

Ubuntu Terminal
↓
postgres Linux User
↓
psql (client)
↓
PostgreSQL Server

2.9 Step 10 — Exit

Leave PostgreSQL:

\q

Leave the postgres user:

exit

Now you are back as your normal user.

2.10 Step 11 — Set a Password and Create an Application User ★

(This step is not in the original lesson, but you cannot connect FastAPI without it.)

Peer authentication works for the terminal, but FastAPI connects over TCP with a username and password. So you need a password.

Option A — Quick (set a password for postgres )

sudo -i -u postgres
psql

Inside psql:

ALTER USER postgres WITH PASSWORD 'your_strong_password';

\q

exit

Now this connection string will work:

postgresql://postgres:your_strong_password@localhost:5432/college_db

Option B — Professional (dedicated application user) ✔ Recommended

Never let your application log in as a superuser. Create a user with only the rights it needs:

-- inside psql as the postgres user

CREATE USER college_user WITH PASSWORD 'strong_password_here';
CREATE DATABASE college_db OWNER college_user;
GRANT ALL PRIVILEGES ON DATABASE college_db TO college_user;

Connection string:

postgresql://college_user:strong_password_here@localhost:5432/college_db

Why this matters: if your application is ever compromised, the attacker gets only the permissions of college_user — not the ability to
drop every database on the server. This is the principle of least privilege, and interviewers like hearing it.

PostgreSQL & FastAPI — Phase 2 Guide Page 12 of 56

Verify the password login works

psql -h localhost -U college_user -d college_db

The -h localhost forces a TCP connection, which is exactly what FastAPI will do. If this works, FastAPI will work.

2.11 The Linux Connections

Notice how everything you already learned is being reused:

Linux Concept PostgreSQL Usage

Users The postgres system user

Services systemctl status postgresql

Processes ps aux \| grep postgres shows the running backends

Ports PostgreSQL listens on 5432

Files PostgreSQL stores data in /var/lib/postgresql/<version>/main

Permissions Only the postgres user can read the data directory

Try this to see the processes:

ps aux | grep postgres

And this to confirm the port is open:

ss -tlnp | grep 5432

2.12 Complete Practical Flow

Run every command yourself. Understanding comes from doing, not reading.

sudo apt update

sudo apt install postgresql postgresql-contrib
psql --version
systemctl status postgresql
sudo systemctl enable postgresql
sudo -i -u postgres
psql
\q
exit

2.13 Interview Questions

Q1. Why do we install both postgresql and postgresql-contrib ? postgresql installs the database server and client, while postgresql-
contrib provides additional official extensions and utilities commonly used in development and production.

Q2. What is psql ? psql is PostgreSQL's interactive command-line client, used to connect to databases, execute SQL commands, and
administer PostgreSQL.

Q3. Why does PostgreSQL run as a Linux service? Running as a systemd service allows PostgreSQL to start automatically with the
operating system, stay available for applications, restart on failure, and be managed uniformly with systemctl .

Q4. What is the postgres user? It is a dedicated Linux system user created during installation that owns the PostgreSQL data directory
and maps to the default database superuser. PostgreSQL uses peer authentication, so you switch to this Linux user to log in as the database
superuser.

Q5. Which port does PostgreSQL use by default? Port 5432 .

Q6. Why should an application not connect as the postgres superuser? Because of the principle of least privilege — if the
application is compromised, a limited user restricts the damage an attacker can do.

PostgreSQL & FastAPI — Phase 2 Guide Page 13 of 56

 Part 3 — PostgreSQL Architecture & SQL Fundamentals

(Lesson 18)

Goal: Understand how PostgreSQL stores data internally before writing any SQL.

This is one of the most important lessons in the entire journey. Whether it is PostgreSQL, MySQL, Oracle, or SQL Server — they all use
almost the same concepts. Learn it once, use it everywhere.

3.1 First Question

Suppose you are the principal of a college with student information. Would you store it like this?

Vikas AI 20
Rahul ML 21
Aman DL 22
Neha DS 20

✘ Very difficult to read, search, or update.

Instead, you create a table:

+----+--------+-----+------------------+
| ID | Name | Age | Course |
+----+--------+-----+------------------+
| 1 | Vikas | 20 | AI |
| 2 | Rahul | 21 | Machine Learning |
| 3 | Aman | 22 | Deep Learning |
+----+--------+-----+------------------+

PostgreSQL works exactly like this. This is why it is called a relational database — data lives in relations (tables).

3.2 The Hierarchy — Think of an Office Building

PostgreSQL Server ← the building

│
▼
+----------------------+
| Database | ← a floor
+----------------------+
│
▼
+----------------------+
| Tables | ← rooms on that floor
+----------------------+
│
▼
Rows + Columns ← furniture inside the room

Memorise this chain:

Server → Database → Table → Row → Column

3.3 What Is a Database?

A Database is a container that stores related tables.

Think of it as a folder.

College Database
├── Students Table
├── Teachers Table
├── Courses Table
└── Attendance Table

PostgreSQL & FastAPI — Phase 2 Guide Page 14 of 56

Everything related to the college lives in one database.

Instagram Database

Instagram Database
├── Users
├── Posts
├── Comments
├── Likes
└── Followers

Your future AI application

AI Study Assistant
├── Users
├── Chats
├── Notes
├── PDFs
├── Agents
├── Research
└── Tasks

One database. Many tables.

3.4 What Is a Table?

A Table stores exactly one type of data.

Students Table

+----+--------+-----+--------+
| ID | Name | Age | Course |
+----+--------+-----+--------+
| 1 | Vikas | 20 | AI |
| 2 | Rahul | 21 | ML |
+----+--------+-----+--------+

Teachers Table

+----+--------+------------+
| ID | Name | Subject |
+----+--------+------------+
| 1 | Sharma | DBMS |
| 2 | Gupta | Networks |
+----+--------+------------+

Real-Life Analogy — The Cupboard

Cupboard
├── Clothes Shelf
├── Books Shelf
├── Files Shelf
└── Electronics Shelf

Each shelf stores one type of item. Each table stores one type of data. You would never mix shirts and laptop chargers on the same shelf —
and you never mix students and teachers in the same table.

3.5 What Is a Row?

A Row is one complete record.

+----+--------+-----+--------+
| ID | Name | Age | Course |
+----+--------+-----+--------+
| 1 | Vikas | 20 | AI | ← this entire line is ONE row
+----+--------+-----+--------+

Easy Rule

PostgreSQL & FastAPI — Phase 2 Guide Page 15 of 56

 One Row = One Object

Examples:

One User One Student One Product One Chat One Order

In Python terms: a row is one dictionary; a table is the list of those dictionaries — except it lives on disk instead of RAM.

3.6 What Is a Column?

A Column is one property that every row has.

Columns
↓
ID | Name | Age | Course
--------------------------
1 | Vikas | 20 | AI ← Rows
2 | Rahul | 21 | ML

Column Meaning

id Student ID

name Student Name

age Student Age

course Student Course

Easy Rule

Column = One Property

Real AI Example — Chats Table

+----+---------+------------------------------+
| ID | User ID | Prompt |
+----+---------+------------------------------+
| 1 | 5 | Explain FastAPI |
| 2 | 5 | What is Kubernetes? |
+----+---------+------------------------------+

Columns: id , user_id , prompt

Rows: each individual chat message

3.7 Primary Key ★★★★★

This is one of the most important concepts in all of databases.

Imagine your class has three students named Rahul.

Question: How will you identify the correct Rahul? Answer: You need something unique — a Roll Number.

Databases use exactly this idea. It is called a Primary Key.

+----+--------+-----+
| ID | Name | Age |
+----+--------+-----+
| 1 | Rahul | 20 |
| 2 | Rahul | 22 |
| 3 | Rahul | 21 |
+----+--------+-----+

Names can repeat. ID never repeats.

Primary Key Rules

PostgreSQL & FastAPI — Phase 2 Guide Page 16 of 56

A Primary Key must be:

✔ Unique — no two rows can share it

✔ Never NULL — it must always have a value
✔ Identifies exactly one row
✔ Stable — ideally it never changes

Real Examples

Student → Student ID
Product → Product ID
Chat → Chat ID
User → User ID

Why It Matters for Your API

Every REST endpoint like GET /students/1 or DELETE /students/1 uses the primary key to find that one exact row. Without a primary key,
WHERE id = 1 is meaningless.

Bonus: Foreign Key (preview)

If the chats table has a user_id column pointing to the users table's id , that is a Foreign Key. It creates the relationship in "relational
database". You will use it heavily from Lesson 22 onwards.

[Link] ←────── chats.user_id

(Primary Key) (Foreign Key)

3.8 Data Types ★★★★★

Every column has a type — exactly like Python.

age = 20 # Python: integer

age INTEGER -- PostgreSQL: integer

The Types You Will Use Most

Type Stores Example

INTEGER Whole numbers 20 , 45 , 100

BIGINT Very large whole numbers user IDs at scale

TEXT Strings of any length 'Vikas' , 'FastAPI'

VARCHAR(n) Strings with a max length VARCHAR(50) for a name

BOOLEAN True / False true , false

TIMESTAMP Date and time 2026-07-13 18:45:00

DATE Date only 2026-07-13

NUMERIC(10,2) Exact decimals (money!) 1499.00

SERIAL Auto-incrementing integer used for id

UUID Universally unique ID 550e8400-e29b-...

JSONB Structured JSON AI model responses

Money tip: never use FLOAT for currency. Floating point cannot represent 0.10 exactly. Use NUMERIC .

A Realistic Student Table

PostgreSQL & FastAPI — Phase 2 Guide Page 17 of 56

 +------------+-------------+
| Column | Data Type |
+------------+-------------+
| id | INTEGER |
| name | TEXT |
| age | INTEGER |
| course | TEXT |
| is_active | BOOLEAN |
| created_at | TIMESTAMP |
+------------+-------------+

Every production database table looks broadly like this: an identity column, some data columns, a status flag, and timestamps.

Why Data Types Exist

1. Correctness — you cannot accidentally save "twenty" into an age column.
2. Storage efficiency — an INTEGER uses 4 bytes; storing "20" as text wastes space.
3. Speed — comparing numbers is faster than comparing strings.
4. Meaningful operations — age > 20 only makes sense if age is a number.

3.9 Complete Architecture

PostgreSQL Server
│
▼
Database (college_db)
│
▼
Students Table
│
├── Columns: id | name | age | course | is_active | created_at
│
▼
Rows:
Student 1
Student 2
Student 3

3.10 Real AI Backend Example

Database: ai_platform

Tables: users , chats , documents , agents , tasks , research , models

Users Table

id | name | email | password_hash | created_at

Chats Table

id | user_id | prompt | response | created_at

Notice the user_id column — that is the foreign key linking each chat to its owner.

3.11 Practical (No SQL Yet)

sudo -i -u postgres
psql

List databases:

\l

You will see the defaults:

postgres
template0
template1

PostgreSQL & FastAPI — Phase 2 Guide Page 18 of 56

 Database Purpose

postgres Default admin database, used as a landing point

template1 The template every new database is copied from

template0 A pristine, untouched backup template

Exit:

\q

3.12 Why This Matters for FastAPI

Remember your Pydantic model?

class Student(BaseModel):
name: str
age: int
course: str

Soon you will create a matching PostgreSQL table:

students
├── id
├── name
├── age
└── course

Pydantic Model PostgreSQL Table

-------------- ----------------
name: str ←──→ name TEXT
age: int ←──→ age INTEGER
course: str ←──→ course TEXT

Your API model and your database table describe the same student, in two different languages. SQLAlchemy is what keeps them in sync
— that is the entire point of Part 6.

3.13 Assignment

Draw this diagram in your notebook from memory:

PostgreSQL
│
▼
Database
│
▼
Students Table
│
├── id (INTEGER)
├── name (TEXT)
├── age (INTEGER)
├── course (TEXT)
├── is_active (BOOLEAN)
└── created_at (TIMESTAMP)
│
▼
Rows
Student 1
Student 2
Student 3

If you can explain this diagram without looking at notes, you have understood the core architecture of relational databases.

3.14 Interview Questions

Q1. What is a database? An organised collection of related data that is stored permanently and managed by a database management
system (DBMS).

Q2. What is a table? A table stores one type of data in rows and columns. For example, a students table stores information only about

PostgreSQL & FastAPI — Phase 2 Guide Page 19 of 56

students.

Q3. What is the difference between a row and a column? A row represents one complete record (one student). A column represents
one attribute of that record (name, or age).

Q4. What is a Primary Key? A column, or set of columns, that uniquely identifies each row in a table. It cannot contain duplicate or NULL
values.

Q5. What is a Foreign Key? A column that references the primary key of another table, establishing a relationship between the two
tables and enforcing referential integrity.

Q6. Why do databases use data types? Data types guarantee that each column stores the correct kind of data, save storage space,
speed up comparisons, and enable meaningful operations such as arithmetic or date ranges.

Q7. Why is PostgreSQL called a "relational" database? Because data is stored in relations (tables) and tables can be related to each
other through keys, allowing complex queries across connected data.

PostgreSQL & FastAPI — Phase 2 Guide Page 20 of 56

 Part 4 — SQL Basics & Your First Database

(Lesson 19)

Goal: Learn the basic SQL commands and create your first real database and table.

Do not memorise commands. Understand why each command exists.

4.1 First Question

PostgreSQL is installed. How do you tell it:

"Create a database"?
"Add a student"?
"Show all students"?

You need a language to talk to PostgreSQL. That language is SQL.

4.2 What Is SQL?

SQL = Structured Query Language — the standard language used to communicate with relational databases.

Python → talks to → FastAPI

SQL → talks to → PostgreSQL

Real-Life Analogy
Imagine PostgreSQL is a librarian. You say:

"Give me all AI books."

The librarian understands your request and fetches them. You do not walk into the storage room and search the shelves yourself.

Similarly, FastAPI sends SQL commands to PostgreSQL — it never touches the data files directly.

Architecture

React
↓
FastAPI
↓
SQL
↓
PostgreSQL
↓
Disk

4.3 Two Kinds of Commands in psql ★

This confuses everyone at first, so learn it now:

Type Starts with Ends with ; ? Understood by Examples

SQL commands a keyword ✔ Yes PostgreSQL server SELECT , CREATE TABLE , INSERT

psql meta-commands \ ✘ No The psql client only \l , \c , \dt , \d , \q

Meta-commands are shortcuts that exist only inside psql. Your FastAPI application can never run \dt — it can only run SQL.

PostgreSQL & FastAPI — Phase 2 Guide Page 21 of 56

4.4 Step 1 — Open PostgreSQL

sudo -i -u postgres
psql

Prompt becomes:

postgres=#

Think of it like the Python REPL:

Python gives >>>

PostgreSQL gives postgres=#

4.5 Step 2 — Show Existing Databases

\l

Output:

postgres
template0
template1

These are the defaults. Ignore them for now.

4.6 Step 3 — Create Your First Database ★

CREATE DATABASE college_db;

Notice the semicolon. Every SQL command ends with ; .

Just as Python uses indentation to end a block, SQL uses ; to end a statement. If you press Enter and the prompt changes to
college_db-# instead of college_db=# , it means PostgreSQL is still waiting — you forgot the semicolon.

What Happened?

BEFORE AFTER
------ -----
PostgreSQL PostgreSQL
├── postgres ├── postgres
├── template0 ├── template0
└── template1 ├── template1
└── college_db ← new

4.7 Step 4 — Verify

\l

Now college_db appears in the list. You created your first database.

4.8 Step 5 — Connect to the Database

Right now you are inside the postgres database. Move into your own:

\c college_db

Prompt changes:

PostgreSQL & FastAPI — Phase 2 Guide Page 22 of 56

 college_db=#

Analogy

Disk
↓
Folder
↓
college_db
↓
Files

Connecting to a database is like opening a folder. Until you \c , every table you create goes into the wrong place — a very common
beginner mistake.

4.9 Step 6 — Show Tables

\dt

Output:

Did not find any relations.

Meaning: no tables yet. ("Relation" is the formal database word for a table.)

4.10 Step 7 — Create Your First Table ★

CREATE TABLE students (

id INTEGER,
name TEXT,
age INTEGER,
course TEXT
);

Line by line:

Part Meaning

CREATE TABLE Create a new table

students The table name (plural, lowercase — the convention)

id INTEGER Column id , stores whole numbers

name TEXT Column name , stores strings

age INTEGER Column age , stores whole numbers

course TEXT Column course , stores strings

Architecture

college_db
│
▼
students
│
├── id
├── name
├── age
└── course

4.11 Step 8 & 9 — Verify and Describe

\dt

PostgreSQL & FastAPI — Phase 2 Guide Page 23 of 56

Output: students

\d students

Output shows every column with its data type and any constraints. \d is your inspection tool — use it constantly.

4.12 Step 10 — Insert Your First Student ★

INSERT INTO students

VALUES (1, 'Vikas', 20, 'AI');

Rules to remember:

Strings must use single quotes ' ' — not double quotes. In PostgreSQL, double quotes mean identifier (a column or table name), not
a string.
Numbers do not need quotes.
The values must be in the same order as the columns were defined.

Safer Style ✔ (use this in real projects)

INSERT INTO students (id, name, age, course)

VALUES (1, 'Vikas', 20, 'AI');

Naming the columns explicitly means your insert will not break when someone adds a new column to the table later.

Insert More Students

INSERT INTO students (id, name, age, course)

VALUES
(2, 'Rahul', 21, 'Machine Learning'),
(3, 'Aman', 22, 'Deep Learning');

One statement can insert many rows.

4.13 Step 11 — View Data ★

SELECT * FROM students;

Output:

+----+--------+-----+------------------+
| id | name | age | course |
+----+--------+-----+------------------+
| 1 | Vikas | 20 | AI |
| 2 | Rahul | 21 | Machine Learning |
| 3 | Aman | 22 | Deep Learning |
+----+--------+-----+------------------+

You have now stored permanent data. Restart your machine — it will still be there.

Understanding SELECT
Think of SELECT as "show me data."

SELECT * FROM students; -- every column

SELECT name FROM students; -- only names
SELECT name, course FROM students; -- name and course

Query Output

SELECT name FROM students; Vikas, Rahul, Aman

SELECT name, course FROM students; Vikas / AI, Rahul / Machine Learning, Aman / Deep Learning

PostgreSQL & FastAPI — Phase 2 Guide Page 24 of 56

 * means "all columns". In production code, avoid SELECT * — name the columns you actually need so your query does not break or
slow down when the table grows.

4.14 The Production Version of This Table ★

The table you just created works, but a real backend engineer would write it like this:

CREATE TABLE students (

id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL CHECK (age > 0),
course TEXT NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

What each addition buys you:

Keyword What it does Why it matters

SERIAL Auto-generates 1, 2, 3... You never send an id from the API

PRIMARY KEY Unique + not null + indexed WHERE id = 1 becomes instant

NOT NULL Value is compulsory No half-empty records

CHECK (age > 0) Rejects invalid data Database defends itself even if the API has a bug

DEFAULT TRUE Fills the value automatically Cleaner inserts

DEFAULT NOW() Records the creation time Free audit trail, enables ORDER BY created_at

Now inserting no longer needs an id :

INSERT INTO students (name, age, course)

VALUES ('Neha', 20, 'Data Science');

PostgreSQL assigns the id itself.

Key lesson: validation happens in two layers. Pydantic validates at the API boundary (fast, friendly error messages). The database
constraints validate at the storage layer (absolute, cannot be bypassed). Professionals use both.

4.15 Complete Practical

Type each command yourself and watch what changes after every step.

CREATE DATABASE college_db;

\c college_db

CREATE TABLE students (

id INTEGER,
name TEXT,
age INTEGER,
course TEXT
);

INSERT INTO students (id, name, age, course) VALUES (1, 'Vikas', 20, 'AI');
INSERT INTO students (id, name, age, course) VALUES (2, 'Rahul', 21, 'Machine Learning');
INSERT INTO students (id, name, age, course) VALUES (3, 'Aman', 22, 'Deep Learning');

SELECT * FROM students;

4.16 Assignment (with Solution)

Task: Create a teachers table with id , name , subject . Insert two teachers and display them.

Try it yourself first.

PostgreSQL & FastAPI — Phase 2 Guide Page 25 of 56

 CREATE TABLE teachers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
subject TEXT NOT NULL
);

INSERT INTO teachers (name, subject)

VALUES
('Sharma', 'DBMS'),
('Gupta', 'Computer Networks');

SELECT * FROM teachers;

4.17 Useful psql Commands

\l List all databases

\c college_db Connect to a database
\dt Show tables in current database
\d students Describe the students table
\du List all users/roles
\conninfo Show current connection details
\x Toggle expanded (vertical) output — great for wide tables
\? Help for meta-commands
\h SELECT Help for a specific SQL command
\q Quit

4.18 Interview Questions

Q1. What is SQL? Structured Query Language — the standard language used to create, retrieve, update, and delete data in relational
databases such as PostgreSQL.

Q2. What does CREATE DATABASE do? It creates a new database inside the PostgreSQL server, where related tables and data are stored.

Q3. What does CREATE TABLE do? It creates a new table with specified columns, data types, and constraints inside the currently connected
database.

Q4. What does INSERT INTO do? It adds one or more new rows (records) to a table.

Q5. What does SELECT * FROM students; do? It retrieves all columns and all rows from the students table.

Q6. What is SERIAL ? A PostgreSQL pseudo-type that creates an auto-incrementing integer column, typically used for primary keys, so the
database generates IDs automatically.

Q7. Difference between TEXT and VARCHAR(n) ? Both store strings. VARCHAR(n) enforces a maximum length; TEXT has no limit. In
PostgreSQL their performance is essentially identical, so TEXT plus an application-level rule is common.

PostgreSQL & FastAPI — Phase 2 Guide Page 26 of 56

 Part 5 — SQL CRUD Operations

(Lesson 20)

Goal: Retrieve, filter, update, delete, sort, and limit data.

These are the five commands every backend engineer uses every single day. When your FastAPI backend receives:

GET /students
POST /students
PUT /students/1
DELETE /students/1

it eventually executes one of these SQL commands. This lesson connects REST → FastAPI → SQL → PostgreSQL.

5.1 First Question

Your table contains:

id name age course

1 Vikas 20 AI

2 Rahul 21 Machine Learning

3 Aman 22 Deep Learning

4 Neha 20 AI

Question: What if you only want Vikas? Should PostgreSQL return all students?

✘ No. We need filtering.

5.2 The CRUD Mapping ★

C → Create
R → Read
U → Update
D → Delete

CRUD SQL HTTP Method FastAPI Endpoint

Create INSERT POST POST /students

Read (all) SELECT GET GET /students

Read (one) SELECT ... WHERE id=1 GET GET /students/1

Update UPDATE ... WHERE id=1 PUT / PATCH PUT /students/1

Delete DELETE ... WHERE id=1 DELETE DELETE /students/1

This single table is the bridge between everything you learned in Phase 1 and everything in Phase 2. Learn it cold — it is asked
in interviews constantly.

5.3 Part 1 — WHERE ★★★★★

The most important SQL keyword. Think of it as Filter.

Without WHERE

SELECT * FROM students; -- all students

With WHERE

PostgreSQL & FastAPI — Phase 2 Guide Page 27 of 56

 SELECT * FROM students
WHERE id = 1;

Result: only one row.

Real-World Analogy
Your class has 80 students. The teacher says:

"Call Roll No. 25."

She does not call all 80 students and then look for one. She filters. WHERE does exactly this.

More Examples

SELECT * FROM students WHERE course = 'AI';

SELECT * FROM students WHERE age = 20;
SELECT * FROM students WHERE name = 'Rahul';

Comparison Operators

Operator Meaning

= Equal

!= or <> Not equal

> Greater than

< Less than

>= Greater or equal

<= Less or equal

SELECT * FROM students WHERE age > 20;

Multiple Conditions

-- AND: both must be true

SELECT * FROM students
WHERE course = 'AI' AND age = 20;

-- OR: either can be true

SELECT * FROM students
WHERE course = 'AI' OR course = 'Machine Learning';

Useful Extras

-- IN: cleaner than many ORs

SELECT * FROM students WHERE course IN ('AI', 'Machine Learning');

-- BETWEEN: inclusive range

SELECT * FROM students WHERE age BETWEEN 20 AND 22;

-- LIKE: pattern matching (% = any characters)

SELECT * FROM students WHERE name LIKE 'R%'; -- starts with R
SELECT * FROM students WHERE name ILIKE 'r%'; -- case-insensitive

-- NULL checks (never use = NULL, it does not work)

SELECT * FROM students WHERE course IS NULL;
SELECT * FROM students WHERE course IS NOT NULL;

Why = NULL fails: NULL means "unknown". Comparing anything to unknown gives unknown, never true. That is why SQL has a
dedicated IS NULL .

5.4 Part 2 — UPDATE ★★★★★

PostgreSQL & FastAPI — Phase 2 Guide Page 28 of 56

Suppose Vikas changes his course from AI to Generative AI .

UPDATE students
SET course = 'Generative AI'
WHERE id = 1;

Breaking it down:

Clause Question it answers

UPDATE students Which table?

SET course = '...' What should change?

WHERE id = 1 Which row(s)?

Result:

BEFORE AFTER
id | course id | course
1 | AI 1 | Generative AI

⚠ Very Important Warning

UPDATE students
SET age = 25;

Where is the WHERE ? There isn't one.

Result: every student's age becomes 25.

UPDATE
↓
ALWAYS use WHERE

5.5 Part 3 — DELETE ★★★★★

Student ID 4 leaves college:

DELETE FROM students

WHERE id = 4;

⚠ Again — Never Forget WHERE

DELETE FROM students;

Result: every row deleted. This mistake has genuinely happened at real companies and caused production outages.

The Professional Safety Habit ✔

Before any UPDATE or DELETE , run the same WHERE clause as a SELECT first:

-- Step 1: check what will be affected

SELECT * FROM students WHERE id = 4;

-- Step 2: only if the result is what you expect, run the delete
DELETE FROM students WHERE id = 4;

Even Safer — Use a Transaction

PostgreSQL & FastAPI — Phase 2 Guide Page 29 of 56

 BEGIN; -- start a transaction

DELETE FROM students WHERE id = 4;

SELECT * FROM students; -- inspect the result

-- happy?
COMMIT; -- make it permanent

-- made a mistake?
ROLLBACK; -- undo everything since BEGIN

BEGIN ... COMMIT / ROLLBACK is the "undo button" of databases. Remember this — SQLAlchemy sessions use exactly this mechanism
internally, which is why Part 6 talks about commit() and rollback() .

Soft Delete (what production apps often do)

Instead of destroying the row:

UPDATE students SET is_active = FALSE WHERE id = 4;

The data is preserved for audits and can be restored. This is why the is_active column exists in the production table design.

5.6 Part 4 — ORDER BY ★★★★★

Show students from youngest to oldest:

SELECT * FROM students

ORDER BY age; -- ASC is the default

Oldest first:

SELECT * FROM students

ORDER BY age DESC;

Alphabetical by name:

SELECT * FROM students

ORDER BY name;

Multiple columns (course first, then age within each course):

SELECT * FROM students

ORDER BY course ASC, age DESC;

Real AI Example

SELECT * FROM chats

ORDER BY created_at DESC;

Newest chats first — exactly how ChatGPT's sidebar works.

5.7 Part 5 — LIMIT and OFFSET ★★★★★

Suppose your table has 2 million students. Should PostgreSQL send 2 million rows to the browser?

✘ No. Use LIMIT .

SELECT * FROM students

LIMIT 2;

Only the first two rows.

Pagination = LIMIT + OFFSET

PostgreSQL & FastAPI — Phase 2 Guide Page 30 of 56

 -- Page 1 (rows 1-20)
SELECT * FROM students ORDER BY id LIMIT 20 OFFSET 0;

-- Page 2 (rows 21-40)

SELECT * FROM students ORDER BY id LIMIT 20 OFFSET 20;

-- Page 3 (rows 41-60)

SELECT * FROM students ORDER BY id LIMIT 20 OFFSET 40;

Formula:

OFFSET = (page_number - 1) × page_size

Real Backend Example

Instagram loads 20 posts

↓
you scroll
↓
next 20 posts

That is LIMIT 20 OFFSET 20 .

⚠ Always pair LIMIT with ORDER BY . Without an explicit order, PostgreSQL may return rows in any order, so "page 2" could contain
rows you already saw on page 1.

5.8 Complete SQL ↔ REST Flow

FastAPI Endpoint SQL Executed

---------------- ------------
GET /students → SELECT * FROM students;

GET /students/1 → SELECT * FROM students

WHERE id = 1;

POST /students → INSERT INTO students (...)

VALUES (...);

PUT /students/1 → UPDATE students

SET ... WHERE id = 1;

DELETE /students/1 → DELETE FROM students

WHERE id = 1;

GET /students?page=1 → SELECT * FROM students

ORDER BY id LIMIT 20 OFFSET 0;

Everything you learned in REST maps directly to SQL.

5.9 Practical Exercise

Run these one by one and observe what changes each time.

-- 1. Show all
SELECT * FROM students;

-- 2. Only AI students
SELECT * FROM students WHERE course = 'AI';

-- 3. Students older than 20

SELECT * FROM students WHERE age > 20;

-- 4. Update Rahul's age

UPDATE students SET age = 25 WHERE name = 'Rahul';
SELECT * FROM students; -- observe Rahul's age

-- 5. Delete Aman
DELETE FROM students WHERE name = 'Aman';
SELECT * FROM students; -- observe Aman is gone

-- 6. Sort by name
SELECT * FROM students ORDER BY name;

-- 7. Sort by age, oldest first

SELECT * FROM students ORDER BY age DESC;

-- 8. First two rows only

SELECT * FROM students ORDER BY id LIMIT 2;

PostgreSQL & FastAPI — Phase 2 Guide Page 31 of 56

5.10 Real AI Backend Scenario

A user opens their chat history.

Frontend
↓ GET /chats?page=1
FastAPI
↓ SQL
SELECT * FROM chats
ORDER BY created_at DESC
LIMIT 20;
↓
PostgreSQL returns 20 latest chats
↓
FastAPI converts rows → Python objects → JSON
↓
Frontend renders the sidebar

Now you can see how every layer connects.

5.11 SQL Cheat Sheet

SQL Purpose

SELECT Read data

INSERT Add new rows

UPDATE Modify existing rows

DELETE Remove rows

WHERE Filter rows

ORDER BY Sort rows

LIMIT Return only a few rows

OFFSET Skip rows (pagination)

COUNT(*) Count rows

BEGIN / COMMIT / ROLLBACK Transaction control

Bonus aggregate examples:

SELECT COUNT(*) FROM students;

SELECT AVG(age) FROM students;
SELECT course, COUNT(*) FROM students GROUP BY course;

5.12 Common Mistakes

✘ Mistake ✔ Fix

UPDATE students SET age=20; Always add WHERE

DELETE FROM students; Always add WHERE

WHERE name = "Rahul" Use single quotes: 'Rahul'

WHERE course = NULL Use IS NULL

LIMIT 20 without ORDER BY Always order before limiting

Forgetting the ; Statement hangs waiting for input

Forgetting \c college_db Table gets created in the wrong database

5.13 Interview Questions

PostgreSQL & FastAPI — Phase 2 Guide Page 32 of 56

Q1. What does the WHERE clause do? It filters rows so only records matching the specified condition are returned or affected.

Q2. Why is WHERE important with UPDATE and DELETE ? Without it, UPDATE modifies every row and DELETE removes every row, which
causes serious data loss.

Q3. What is the purpose of ORDER BY ? It sorts query results in ascending ( ASC , the default) or descending ( DESC ) order based on one or
more columns.

Q4. Why do we use LIMIT ? It restricts the number of rows returned, improving performance and enabling pagination.

Q5. How do you implement pagination in SQL? Combine ORDER BY with LIMIT and OFFSET , where OFFSET = (page - 1) * page_size .

Q6. What is a transaction? A group of SQL statements executed as a single unit. BEGIN starts it, COMMIT makes all changes permanent,
and ROLLBACK undoes them all — guaranteeing the database is never left half-updated.

Q7. Difference between DELETE , TRUNCATE , and DROP ? DELETE removes selected rows and can be rolled back. TRUNCATE quickly
removes all rows but keeps the table structure. DROP removes the table itself, structure included.

PostgreSQL & FastAPI — Phase 2 Guide Page 33 of 56

 Part 6 — Connecting FastAPI to PostgreSQL using
SQLAlchemy

(Lesson 21)

Goal: Understand how FastAPI talks to PostgreSQL, and why we use SQLAlchemy instead of writing SQL everywhere.

6.1 First Question

Your React frontend sends GET /students .

Who actually talks to PostgreSQL?

React? ✘ No — the browser must never touch the database.

FastAPI? ✘ Not directly.

There is another layer.

6.2 The Complete Production Architecture ★

React
↓
FastAPI ← receives request, runs business logic
↓
Pydantic ← validates the data shape
↓
SQLAlchemy ← translates Python objects into SQL
↓
psycopg2 ← the driver that speaks PostgreSQL's wire protocol
↓
PostgreSQL ← stores and retrieves
↓
Disk ← permanent

Memorise this stack. It is the single most useful diagram in Phase 2.

6.3 Why Not Write SQL Directly?

You can:

[Link]("SELECT * FROM students")

[Link]("UPDATE students SET age=21 WHERE id=1")
[Link]("DELETE FROM students WHERE id=1")
[Link]("INSERT INTO students ...")

It works. But soon your project contains 500 raw SQL strings scattered across files.

Problems:

✘ Typos are only caught at runtime — the editor cannot check a string
✘ Changing a column name means hunting through every file
✘ Easy to accidentally write SQL vulnerable to SQL injection
✘ Rows come back as plain tuples, not objects
✘ Switching databases means rewriting everything

Solution: SQLAlchemy.

6.4 What Is an ORM?

ORM = Object Relational Mapper — a tool that lets you work with database rows as ordinary Python objects instead of writing raw
SQL.

PostgreSQL & FastAPI — Phase 2 Guide Page 34 of 56

Real-Life Analogy
You know only Hindi. The other person knows only Japanese. You need a translator.

You
↓
Translator
↓
Japanese Person

SQLAlchemy is the translator. You write Python; SQLAlchemy writes the SQL.

Side by Side
Without ORM

SELECT * FROM students WHERE id = 1;

With ORM

student = [Link](Student).filter([Link] == 1).first()

You wrote Python. SQLAlchemy generated the SQL and sent it.

Python Code
↓
SQLAlchemy
↓
SQL
↓
PostgreSQL

Why Companies Use an ORM

✔ Benefit Explanation

Less SQL Common queries become one-liners

Easier to read [Link] > 20 is clearer than a string

Safer Parameters are escaped automatically → prevents SQL injection

Database independent The same code works on PostgreSQL, MySQL, SQLite

Easier maintenance Column renamed in one model file, not 50 query strings

Objects, not tuples You get a Student object with real attributes

Honest caveat for interviews: ORMs are not always the answer. For very complex reports, analytics queries, or performance-critical
paths, engineers still drop down to raw SQL. SQLAlchemy fully supports this via [Link](text("...")) . The right answer is "ORM by
default, raw SQL where it genuinely helps."

6.5 Installation

Activate your virtual environment first:

source .venv/bin/activate

Install:

pip install sqlalchemy psycopg2-binary

What Did We Install?

SQLAlchemy — the ORM

Python → SQLAlchemy → SQL

PostgreSQL & FastAPI — Phase 2 Guide Page 35 of 56

psycopg2 — the driver

Analogy:

You → Car → College

Without the car, you cannot reach college. Without psycopg2, Python cannot reach PostgreSQL. SQLAlchemy knows what SQL to write;
psycopg2 knows how to physically send it over TCP port 5432 and read the reply.

FastAPI
↓
SQLAlchemy
↓
psycopg2
↓
PostgreSQL

Note: psycopg2-binary ships pre-compiled and is perfect for development. The newer driver is psycopg (version 3) — installed as pip
install "psycopg[binary]" with the URL prefix postgresql+psycopg:// . Both are fine; this document uses psycopg2 as in the lesson.

6.6 Step 1 — The Database URL

DATABASE_URL = "postgresql://postgres:password@localhost:5432/college_db"

Anatomy

postgresql :// username : password @ host : port / database

│ │ │ │ │ │
│ │ │ │ │ └── database name
│ │ │ │ └────────── port (5432 default)
│ │ │ └───────────────── where the server runs
│ │ └─────────────────────────── the user's password
│ └────────────────────────────────────── who is logging in
└─────────────────────────────────────────────────── which database system

Part Value Meaning

postgresql:// scheme Which database type (and optionally the driver)

postgres username The database user

password password That user's password

localhost host The database runs on your own computer

5432 port PostgreSQL's default port — remember Linux networking!

college_db database The specific database to open

When you later move to Docker, localhost becomes the service name (e.g. db ). When you move to AWS RDS, it becomes a long
hostname. Only this one string changes — that is why it lives in a config file, never hard-coded in your routes.

6.7 Step 2 — Create the Engine

from sqlalchemy import create_engine

engine = create_engine(DATABASE_URL)

The Engine is the connection factory and the home of the connection pool. It is created once for the entire application.

Do not create an engine per request — opening a TCP connection and authenticating takes milliseconds, and doing it thousands of times
per second will destroy your performance. The engine keeps a pool of already-open connections and hands them out.

6.8 Step 3 — The Session

PostgreSQL & FastAPI — Phase 2 Guide Page 36 of 56

Imagine a bank:

Customer arrives
↓
Talks to the cashier
↓
Transaction completed
↓
Customer leaves
↓
Next customer

Databases work the same way. Every request gets its own session.

from [Link] import sessionmaker

SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)

Object Analogy Lifetime

Engine The bank building + its staff One per application

Session One customer's visit to the counter One per request

Connection The counter itself Borrowed from the pool, returned after

6.9 Step 4 — The Base Class

from [Link] import DeclarativeBase

class Base(DeclarativeBase):
pass

Every database model inherits from this. Base collects the metadata of all your tables so SQLAlchemy can create them, inspect them, and
migrate them.

6.10 Step 5 — The Database Model

Remember Pydantic?

class Student(BaseModel): # for API validation

name: str

A database model is different:

from sqlalchemy import Integer, String

from [Link] import Mapped, mapped_column

class Student(Base):
__tablename__ = "students"

id: Mapped[int] = mapped_column(Integer, primary_key=True)

name: Mapped[str] = mapped_column(String)
age: Mapped[int] = mapped_column(Integer)
course: Mapped[str] = mapped_column(String)

This class describes the PostgreSQL table.

6.11 Pydantic Model vs SQLAlchemy Model ★

Beginners mix these up constantly. They look similar and solve completely different problems.

PostgreSQL & FastAPI — Phase 2 Guide Page 37 of 56

 Pydantic Model SQLAlchemy Model

Inherits from BaseModel Base

Purpose Validate API requests / shape responses Represent a database table

Used by FastAPI SQLAlchemy ORM

Lives at The edge of your app (HTTP boundary) The bottom of your app (database)

Stores data? No Maps Python objects to database rows

Also called Schema Model / Entity

Incoming JSON
↓
Pydantic (StudentCreate) ← "is this valid input?"
↓
SQLAlchemy (Student) ← "save this as a row"
↓
PostgreSQL
↓
SQLAlchemy (Student) ← "here is the row as an object"
↓
Pydantic (StudentResponse) ← "what should the client see?"
↓
Outgoing JSON

Why two models and not one? Because what a client is allowed to send is not the same as what you store. A client sends a plain
password; you store a hash. A client never sends id or created_at ; the database generates them. You never send password_hash
back to the client. Two models keep those concerns cleanly separated — this is a very common interview question.

6.12 Step 6 — Create the Tables

[Link].create_all(bind=engine)

If the students table does not exist, SQLAlchemy creates it from your model.

⚠ Important limitation: create_all only creates missing tables. It will never alter an existing table. If you add a column to
your model later, create_all silently does nothing and your app crashes with "column does not exist". The professional solution is
Alembic, the migration tool for SQLAlchemy. For now, create_all is fine for learning — just know why it is temporary.

6.13 Step 7 — Open a Session and Do CRUD

db = SessionLocal()

Now Python can talk to PostgreSQL.

Create

-- SQL
INSERT INTO students (name, age, course) VALUES ('Vikas', 20, 'AI');

# ORM
student = Student(name="Vikas", age=20, course="AI")
[Link](student)
[Link]()
[Link](student) # reload the DB-generated id

Read All

SELECT * FROM students;

students = [Link](Student).all()

PostgreSQL & FastAPI — Phase 2 Guide Page 38 of 56

Read One (Filter)

SELECT * FROM students WHERE id = 1;

student = [Link](Student).filter([Link] == 1).first()

Update

UPDATE students SET age = 21 WHERE id = 1;

[Link] = 21
[Link]()

Delete

DELETE FROM students WHERE id = 1;

[Link](student)
[Link]()

Order + Limit

SELECT * FROM students ORDER BY age DESC LIMIT 20 OFFSET 0;

students = (
[Link](Student)
.order_by([Link]())
.limit(20)
.offset(0)
.all()
)

Notice: you never wrote SQL. But the SQL you learned in Parts 4 and 5 is exactly what is being generated — which is why those parts came
first.

Debugging tip: pass create_engine(DATABASE_URL, echo=True) and SQLAlchemy will print every SQL statement it generates. Do this
once while learning — watching your Python turn into the SQL you already know is the moment the ORM concept truly clicks.

6.14 Complete Request Flow

React
↓ GET /students
FastAPI Router
↓
Dependency: get_db() → opens a Session
↓
Service layer (business logic)
↓
SQLAlchemy query
↓
Generated SQL
↓
psycopg2 → port 5432
↓
PostgreSQL
↓
Rows
↓
Python Student objects
↓
Pydantic StudentResponse
↓
JSON
↓
React
(and finally: get_db() closes the Session)

6.15 Real AI Backend Example

PostgreSQL & FastAPI — Phase 2 Guide Page 39 of 56

A user uploads a PDF:

FastAPI receives the file

↓
Pydantic validates the metadata
↓
SQLAlchemy stores a Document object
↓
PostgreSQL saves:
- pdf_name
- user_id
- upload_time
- file_path

No raw SQL anywhere.

6.16 Interview Questions

Q1. What is an ORM? An Object Relational Mapper is a tool that lets developers interact with relational databases using programming-
language objects instead of writing raw SQL queries.

Q2. Why do we use SQLAlchemy? It simplifies database interaction, improves readability, reduces repetitive SQL, protects against SQL
injection through parameter binding, provides database abstraction, manages connection pooling, and integrates cleanly with FastAPI's
dependency injection.

Q3. Difference between a Pydantic model and a SQLAlchemy model? Pydantic models validate API requests and responses and are
used by FastAPI. SQLAlchemy models represent database tables and are used by the ORM to map Python objects to database rows. They
serve different layers of the application.

Q4. Why do we need psycopg2? psycopg2 is the PostgreSQL database driver. It implements PostgreSQL's wire protocol so Python —
through SQLAlchemy — can physically connect to and communicate with a PostgreSQL server.

Q5. What is the difference between the Engine and the Session? The Engine is created once per application and manages the
connection pool. A Session is short-lived — typically one per request — and represents a single unit of work (a transaction) against the
database.

Q6. What are the disadvantages of an ORM? It adds an abstraction layer that can generate inefficient SQL if used carelessly (for
example the N+1 query problem), it has a learning curve, and very complex analytical queries are often clearer and faster written as raw
SQL.

PostgreSQL & FastAPI — Phase 2 Guide Page 40 of 56

 Part 7 — Proper Connection Management (Production Level)
★★★★★

This is the part that separates a tutorial project from a production backend.

7.1 The Problem With db = SessionLocal()

In Lesson 21 you opened a session manually:

db = SessionLocal()
students = [Link](Student).all()

What is missing?

✘ Nobody closes the session

✘ If an error occurs mid-request, the transaction is left open
✘ Every request would need to repeat this boilerplate
✘ Two requests might accidentally share one session

An unclosed session holds a connection from the pool. Leak enough of them and your application freezes with QueuePool limit of size 5
overflow 10 reached — a classic production incident.

7.2 The Golden Rules of Session Management

1. One Engine per application. Created at startup, never per request.

2. One Session per request. Never shared between requests or threads.
3. Always close the session, even if the request raised an exception.
4. Commit on success, rollback on failure.
5. Never make the session a global variable.

7.3 The Solution — FastAPI Dependency Injection

def get_db():
db = SessionLocal()
try:
yield db # hand the session to the route
finally:
[Link]() # ALWAYS runs, even on exception

Why yield and not return ?

return yield

Function ends Immediately Pauses, resumes after the response

Cleanup code after it Never runs Runs in the finally block

FastAPI treats a generator dependency as "setup → run the endpoint → teardown". The finally block guarantees the session is
returned to the pool no matter what happens — success, validation error, or unhandled exception.

Using It in a Route

from fastapi import Depends

from [Link] import Session

@[Link]("/students")
def list_students(db: Session = Depends(get_db)):
return [Link](Student).all()

PostgreSQL & FastAPI — Phase 2 Guide Page 41 of 56

FastAPI reads Depends(get_db) , runs the dependency, injects the session, then cleans it up. You never call SessionLocal() inside a route
again.

The Modern, Cleaner Style

from typing import Annotated

DbSession = Annotated[Session, Depends(get_db)]

@[Link]("/students")
def list_students(db: DbSession):
return [Link](Student).all()

One type alias, reused by every route.

7.4 The Session Lifecycle, Visualised

Request arrives
↓
get_db() → SessionLocal() → borrows a connection from the pool
↓
yield db
↓
Route function runs → queries, add, commit
↓
Response is built and sent
↓
finally: [Link]() → connection RETURNED to the pool

The connection is borrowed, not created. That is the whole point of pooling.

7.5 Connection Pooling Explained

Opening a PostgreSQL connection requires a TCP handshake plus authentication — a few milliseconds each time. At 1,000 requests per
second, that is unacceptable overhead.

A pool keeps a set of connections permanently open and lends them out.

Connection Pool
┌───────────────────────┐
│ conn1 conn2 conn3 │ ← kept open
└───────────────────────┘
↑ ↓
returned borrowed
↑ ↓
Request A Request B

Configuring the Pool

engine = create_engine(
DATABASE_URL,
pool_size=10, # connections kept permanently open
max_overflow=20, # extra connections allowed during traffic spikes
pool_timeout=30, # seconds to wait for a free connection before erroring
pool_recycle=1800, # recycle a connection after 30 min (avoids stale ones)
pool_pre_ping=True, # test the connection before use — heals dead sockets
echo=False, # True prints every generated SQL statement
)

Setting Why it matters

pool_size Too small → requests queue up. Too large → PostgreSQL runs out of connections (default max is 100).

max_overflow A safety valve for bursts of traffic

pool_pre_ping The single most valuable setting. Firewalls and cloud databases silently drop idle connections; without pre-ping your app
throws random server closed the connection unexpectedly errors.

pool_recycle Prevents connections living longer than the server or firewall allows

Interview-ready line: "Total possible connections = ( pool_size + max_overflow ) × number of application workers. That total must
stay below PostgreSQL's max_connections ."

PostgreSQL & FastAPI — Phase 2 Guide Page 42 of 56

7.6 Commit, Rollback, Refresh, Close

Method What it does When to call it

[Link](obj) Stages an object for insertion Before commit

[Link]() Writes the transaction permanently After a successful write

[Link]() Undoes everything since the last commit On error

[Link](obj) Reloads the object from the database After commit, to get the generated id and created_at

[Link]() Returns the connection to the pool Always — handled by get_db

[Link]() Sends SQL but does not commit Rarely, when you need an ID mid-transaction

Why refresh Matters

student = Student(name="Vikas", age=20, course="AI")

[Link](student)
[Link]()

print([Link]) # without refresh this may be stale or expired

[Link](student)
print([Link]) # 4 — the value PostgreSQL generated

The id and created_at are generated by the database, not by Python. refresh fetches them back.

Safe Write Pattern

try:
[Link](student)
[Link]()
[Link](student)
except SQLAlchemyError:
[Link]()
raise HTTPException(status_code=500, detail="Database error")

rollback() is the ROLLBACK; from Part 5.5 — the same concept, called from Python.

7.7 Never Hard-Code Credentials ★

This is wrong:

DATABASE_URL = "postgresql://postgres:MyPassword123@localhost:5432/college_db"

Because:

✘ The password is committed to Git and visible forever in the history

✘ You cannot use a different database for development, testing, and production
✘ Anyone with repository access has your database

The Right Way — .env + Pydantic Settings

pip install pydantic-settings python-dotenv

.env (never committed)

DATABASE_URL=postgresql://college_user:strong_password@localhost:5432/college_db

.gitignore

.env
.venv/
__pycache__/

.[Link] (committed, so teammates know what is needed)

PostgreSQL & FastAPI — Phase 2 Guide Page 43 of 56

 DATABASE_URL=postgresql://user:password@localhost:5432/dbname

app/core/[Link]

from pydantic_settings import BaseSettings, SettingsConfigDict

class Settings(BaseSettings):
model_config = SettingsConfigDict(env_file=".env")

DATABASE_URL: str
APP_NAME: str = "College API"
DEBUG: bool = False

settings = Settings()

Now the URL comes from the environment. On your laptop it points at localhost; in Docker it points at the db container; on AWS it points at
RDS. Zero code changes.

7.8 The Layered Project Structure

app/
├── [Link] ← creates the FastAPI app, includes routers
│
├── core/
│ └── [Link] ← settings loaded from .env
│
├── database/
│ ├── [Link] ← engine, SessionLocal, Base, get_db
│ └── __init__.py
│
├── models/ ← SQLAlchemy models (database tables)
│ └── [Link]
│
├── schemas/ ← Pydantic models (API contracts)
│ └── [Link]
│
├── services/ ← business logic + database operations
│ └── student_service.py
│
└── routers/ ← HTTP endpoints only
└── [Link]

Why Separate Layers?

Layer Responsibility Should NOT know about

routers/ HTTP: paths, status codes, request/response SQL

services/ Business rules + database operations HTTP

models/ Table structure HTTP or business rules

schemas/ What the API accepts and returns The database

database/ Connection and session management Everything else

The benefit: if you later replace REST with GraphQL, you rewrite only routers/ . If you switch databases, you touch only models/ and
database/ . Each layer changes for exactly one reason.

7.9 Startup and Shutdown (Lifespan)

Instead of calling create_all at import time, use FastAPI's lifespan handler:

from contextlib import asynccontextmanager

from fastapi import FastAPI

@asynccontextmanager
async def lifespan(app: FastAPI):
# startup
[Link].create_all(bind=engine)
yield
# shutdown
[Link]() # closes every pooled connection cleanly

app = FastAPI(lifespan=lifespan)

PostgreSQL & FastAPI — Phase 2 Guide Page 44 of 56

[Link]() on shutdown means PostgreSQL is not left holding dead connections when your container stops.

7.10 Health Check Endpoint

Every production service needs a way to prove the database is reachable — Kubernetes and load balancers use exactly this.

from sqlalchemy import text

@[Link]("/health")
def health_check(db: Session = Depends(get_db)):
try:
[Link](text("SELECT 1"))
return {"status": "ok", "database": "connected"}
except Exception:
raise HTTPException(status_code=503, detail="Database unavailable")

SELECT 1 is the cheapest possible query — it proves the connection works without touching any table.

7.11 Connection Management Checklist

☐ Engine created once, at module level

☐ pool_pre_ping=True enabled
☐ Session created per request through Depends(get_db)
☐ get_db uses yield inside try / finally
☐ [Link]() guaranteed in the finally block
☐ [Link]() on exceptions
☐ [Link]() after inserts that need generated values
☐ DATABASE_URL read from .env , never hard-coded
☐ .env listed in .gitignore
☐ [Link]() on application shutdown
☐ A /health endpoint that verifies the database
☐ Application connects as a limited user, not the postgres superuser

PostgreSQL & FastAPI — Phase 2 Guide Page 45 of 56

 Part 8 — The Complete Working Project

Everything above, assembled into a project you can actually run. This is the code that replaces students = [] for good.

8.0 Setup Commands

# 1. Create the project

mkdir college-api && cd college-api

# 2. Virtual environment
python3 -m venv .venv
source .venv/bin/activate

# 3. Install dependencies
pip install fastapi uvicorn sqlalchemy psycopg2-binary pydantic-settings

# 4. Freeze them
pip freeze > [Link]

# 5. Create the structure

mkdir -p app/{core,database,models,schemas,services,routers}
touch app/__init__.py app/{core,database,models,schemas,services,routers}/__init__.py

Create the database (once, in psql):

CREATE USER college_user WITH PASSWORD 'strong_password_here';

CREATE DATABASE college_db OWNER college_user;
GRANT ALL PRIVILEGES ON DATABASE college_db TO college_user;

8.1 .env

DATABASE_URL=postgresql://college_user:strong_password_here@localhost:5432/college_db
APP_NAME=College API
DEBUG=True

And .gitignore :

.env
.venv/
__pycache__/
*.pyc

8.2 app/core/[Link]

from pydantic_settings import BaseSettings, SettingsConfigDict

class Settings(BaseSettings):
"""Application settings, loaded from environment variables / .env file."""

model_config = SettingsConfigDict(env_file=".env", extra="ignore")

DATABASE_URL: str
APP_NAME: str = "College API"
DEBUG: bool = False

settings = Settings()

8.3 app/database/[Link] ★ (the heart of connection management)

PostgreSQL & FastAPI — Phase 2 Guide Page 46 of 56

 from sqlalchemy import create_engine
from [Link] import DeclarativeBase, sessionmaker, Session

from [Link] import settings

# ---------------------------------------------------------------
# 1. ENGINE — created ONCE for the whole application.
# It owns the connection pool.
# ---------------------------------------------------------------
engine = create_engine(
settings.DATABASE_URL,
pool_size=10, # connections kept permanently open
max_overflow=20, # extra connections allowed during spikes
pool_timeout=30, # seconds to wait for a free connection
pool_recycle=1800, # recycle connections every 30 minutes
pool_pre_ping=True, # verify a connection is alive before using it
echo=[Link], # print generated SQL while developing
)

# ---------------------------------------------------------------
# 2. SESSION FACTORY — produces one Session per request.
# ---------------------------------------------------------------
SessionLocal = sessionmaker(
bind=engine,
autocommit=False, # we control commits explicitly
autoflush=False, # no surprise writes before we ask
expire_on_commit=False # objects stay usable after commit()
)

# ---------------------------------------------------------------
# 3. BASE — every model inherits from this.
# ---------------------------------------------------------------
class Base(DeclarativeBase):
pass

# ---------------------------------------------------------------
# 4. DEPENDENCY — one session per request, always closed.
# ---------------------------------------------------------------
def get_db():
"""
FastAPI dependency.

Opens a database session, hands it to the endpoint, and

guarantees the connection is returned to the pool afterwards
(even if the endpoint raised an exception).
"""
db: Session = SessionLocal()
try:
yield db
except Exception:
[Link]()
raise
finally:
[Link]()

8.4 app/models/[Link] (SQLAlchemy — the table)

from datetime import datetime

from sqlalchemy import Integer, String, Boolean, DateTime, func

from [Link] import Mapped, mapped_column

from [Link] import Base

class Student(Base):
"""Maps to the 'students' table in PostgreSQL."""

__tablename__ = "students"

id: Mapped[int] = mapped_column(

Integer, primary_key=True, index=True
)
name: Mapped[str] = mapped_column(
String(100), nullable=False, index=True
)
age: Mapped[int] = mapped_column(
Integer, nullable=False
)
course: Mapped[str] = mapped_column(
String(100), nullable=False
)
is_active: Mapped[bool] = mapped_column(
Boolean, nullable=False, default=True
)
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=[Link]()
)

def __repr__(self) -> str:

return f"<Student id={[Link]} name={[Link]}>"

PostgreSQL & FastAPI — Phase 2 Guide Page 47 of 56

The equivalent SQL that SQLAlchemy generates:

CREATE TABLE students (

id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INTEGER NOT NULL,
course VARCHAR(100) NOT NULL,
is_active BOOLEAN NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX ix_students_name ON students (name);

Why index=True on name ? Because you will filter by name. Without an index, PostgreSQL scans every row ( O(n) ); with one, it uses a
B-tree ( O(log n) ). Primary keys are indexed automatically.

8.5 app/schemas/[Link] (Pydantic — the API contract)

from datetime import datetime

from pydantic import BaseModel, ConfigDict, Field

class StudentBase(BaseModel):
"""Fields shared by create and update."""
name: str = Field(..., min_length=2, max_length=100)
age: int = Field(..., gt=0, lt=120)
course: str = Field(..., min_length=2, max_length=100)

class StudentCreate(StudentBase):
"""What the client sends on POST. No id, no created_at."""
pass

class StudentUpdate(BaseModel):
"""What the client sends on PATCH. Everything optional."""
name: str | None = Field(None, min_length=2, max_length=100)
age: int | None = Field(None, gt=0, lt=120)
course: str | None = Field(None, min_length=2, max_length=100)
is_active: bool | None = None

class StudentResponse(StudentBase):
"""What the API sends back."""
model_config = ConfigDict(from_attributes=True)

id: int
is_active: bool
created_at: datetime

from_attributes=True (called orm_mode in Pydantic v1) tells Pydantic: "you may read values from object attributes, not just dictionary
keys." Without it, FastAPI cannot convert a SQLAlchemy Student object into JSON.

8.6 app/services/student_service.py (business logic + database)

PostgreSQL & FastAPI — Phase 2 Guide Page 48 of 56

 from [Link] import Session

from [Link] import Student

from [Link] import StudentCreate, StudentUpdate

def get_all_students(
db: Session, skip: int = 0, limit: int = 20
) -> list[Student]:
"""SELECT * FROM students ORDER BY id LIMIT :limit OFFSET :skip;"""
return (
[Link](Student)
.order_by([Link])
.offset(skip)
.limit(limit)
.all()
)

def get_student_by_id(db: Session, student_id: int) -> Student | None:

"""SELECT * FROM students WHERE id = :student_id;"""
return [Link](Student).filter([Link] == student_id).first()

def get_students_by_course(db: Session, course: str) -> list[Student]:

"""SELECT * FROM students WHERE course = :course;"""
return [Link](Student).filter([Link] == course).all()

def create_student(db: Session, payload: StudentCreate) -> Student:

"""INSERT INTO students (...) VALUES (...);"""
student = Student(
name=[Link],
age=[Link],
course=[Link],
)
[Link](student)
[Link]()
[Link](student) # fetch the generated id and created_at
return student

def update_student(
db: Session, student_id: int, payload: StudentUpdate
) -> Student | None:
"""UPDATE students SET ... WHERE id = :student_id;"""
student = get_student_by_id(db, student_id)
if student is None:
return None

# only update the fields the client actually sent

for field, value in payload.model_dump(exclude_unset=True).items():
setattr(student, field, value)

[Link]()
[Link](student)
return student

def delete_student(db: Session, student_id: int) -> bool:

"""DELETE FROM students WHERE id = :student_id;"""
student = get_student_by_id(db, student_id)
if student is None:
return False

[Link](student)
[Link]()
return True

Every function has its SQL equivalent written in the docstring — keep that habit while learning, so you always know what the ORM is really
doing.

8.7 app/routers/[Link] (HTTP layer only)

PostgreSQL & FastAPI — Phase 2 Guide Page 49 of 56

 from typing import Annotated

from fastapi import APIRouter, Depends, HTTPException, status, Query

from [Link] import Session

from [Link] import get_db

from [Link] import StudentCreate, StudentUpdate, StudentResponse
from [Link] import student_service

router = APIRouter(prefix="/students", tags=["Students"])

# reusable session dependency

DbSession = Annotated[Session, Depends(get_db)]

@[Link]("", response_model=list[StudentResponse])
def list_students(
db: DbSession,
page: int = Query(1, ge=1),
size: int = Query(20, ge=1, le=100),
):
skip = (page - 1) * size
return student_service.get_all_students(db, skip=skip, limit=size)

@[Link]("/{student_id}", response_model=StudentResponse)
def get_student(student_id: int, db: DbSession):
student = student_service.get_student_by_id(db, student_id)
if student is None:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=f"Student {student_id} not found",
)
return student

@[Link](
"", response_model=StudentResponse, status_code=status.HTTP_201_CREATED
)
def create_student(payload: StudentCreate, db: DbSession):
return student_service.create_student(db, payload)

@[Link]("/{student_id}", response_model=StudentResponse)
def update_student(student_id: int, payload: StudentUpdate, db: DbSession):
student = student_service.update_student(db, student_id, payload)
if student is None:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=f"Student {student_id} not found",
)
return student

@[Link]("/{student_id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_student(student_id: int, db: DbSession):
deleted = student_service.delete_student(db, student_id)
if not deleted:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=f"Student {student_id} not found",
)

Notice: no SQL and no session creation in this file. The router only knows about HTTP.

8.8 app/[Link]

PostgreSQL & FastAPI — Phase 2 Guide Page 50 of 56

 from contextlib import asynccontextmanager

from fastapi import FastAPI, Depends, HTTPException

from sqlalchemy import text
from [Link] import Session

from [Link] import settings

from [Link] import Base, engine, get_db
from [Link] import student # noqa: F401 (import so the table is registered)
from [Link] import students

@asynccontextmanager
async def lifespan(app: FastAPI):
# --- startup ---
[Link].create_all(bind=engine)
yield
# --- shutdown ---
[Link]()

app = FastAPI(
title=settings.APP_NAME,
version="1.0.0",
lifespan=lifespan,
)

app.include_router([Link])

@[Link]("/")
def root():
return {"message": f"{settings.APP_NAME} is running"}

@[Link]("/health")
def health_check(db: Session = Depends(get_db)):
try:
[Link](text("SELECT 1"))
return {"status": "ok", "database": "connected"}
except Exception:
raise HTTPException(status_code=503, detail="Database unavailable")

8.9 Run It

uvicorn [Link]:app --reload

Open the interactive docs:

[Link]

8.10 Test It

# Health
curl [Link]

# Create
curl -X POST [Link] \
-H "Content-Type: application/json" \
-d '{"name": "Vikas", "age": 20, "course": "AI"}'

# List
curl [Link]

# Get one
curl [Link]

# Update
curl -X PATCH [Link] \
-H "Content-Type: application/json" \
-d '{"age": 21}'

# Delete
curl -X DELETE [Link]

8.11 Verify in PostgreSQL

The real proof — the data exists outside your Python process:

psql -h localhost -U college_user -d college_db

PostgreSQL & FastAPI — Phase 2 Guide Page 51 of 56

 \dt
SELECT * FROM students;

Now stop the server with Ctrl + C , start it again, and call GET /students .

The data is still there.

That is the entire point of Phase 2.

8.12 Common Errors and Fixes

Error Cause Fix

connection refused ... port 5432 PostgreSQL is not running sudo systemctl start postgresql

password authentication failed Wrong password, or no Re-run ALTER USER ... WITH PASSWORD
password set

database "college_db" does not exist Database never created CREATE DATABASE college_db;

role "college_user" does not exist User never created CREATE USER ...

ModuleNotFoundError: psycopg2 Driver not installed pip install psycopg2-binary

relation "students" does not exist Tables never created Ensure the model is imported and create_all runs

column ... does not exist Model changed but table did not Drop the table, or use Alembic migrations

QueuePool limit ... reached Sessions not being closed Use Depends(get_db) with try/finally

Pydantic Input should be a valid Missing from_attributes=True Add model_config = ConfigDict(from_attributes=True)

dictionary

permission denied for table students User lacks rights GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO
college_user;

Prompt stuck at college_db-# Missing semicolon Type ; and press Enter

PostgreSQL & FastAPI — Phase 2 Guide Page 52 of 56

 Part 9 — Consolidated Reference

9.1 Linux / Service Commands

sudo apt update

sudo apt install postgresql postgresql-contrib

psql --version
which psql

sudo systemctl start postgresql

sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl enable postgresql
systemctl status postgresql

sudo -i -u postgres # become the postgres user

psql # open the client
exit # leave the postgres user

ps aux | grep postgres # see the running processes

ss -tlnp | grep 5432 # confirm the port is listening

9.2 psql Meta-Commands (no semicolon)

\l List databases
\c college_db Connect to a database
\dt List tables
\d students Describe a table
\du List roles/users
\conninfo Current connection info
\x Toggle expanded output
\timing Show query execution time
\h SELECT SQL help
\? Meta-command help
\q Quit

9.3 SQL Reference (semicolon required)

-- DATABASE
CREATE DATABASE college_db;
DROP DATABASE college_db;

-- TABLE
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL CHECK (age > 0),
course TEXT NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
ALTER TABLE students ADD COLUMN email TEXT;
DROP TABLE students;

-- CREATE
INSERT INTO students (name, age, course) VALUES ('Vikas', 20, 'AI');

-- READ
SELECT * FROM students;
SELECT name, course FROM students;
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE course IN ('AI', 'ML');
SELECT * FROM students WHERE name LIKE 'R%';
SELECT * FROM students ORDER BY age DESC;
SELECT * FROM students ORDER BY id LIMIT 20 OFFSET 20;
SELECT COUNT(*) FROM students;
SELECT course, COUNT(*) FROM students GROUP BY course;

-- UPDATE
UPDATE students SET course = 'Generative AI' WHERE id = 1;

-- DELETE
DELETE FROM students WHERE id = 4;

-- TRANSACTIONS
BEGIN;
UPDATE students SET age = 25 WHERE id = 1;
ROLLBACK; -- or COMMIT;

PostgreSQL & FastAPI — Phase 2 Guide Page 53 of 56

9.4 SQL ↔ SQLAlchemy Translation Table ★

SQL SQLAlchemy

SELECT * FROM students; [Link](Student).all()

SELECT * FROM students WHERE id=1; [Link](Student).filter([Link] == 1).first()

SELECT * FROM students WHERE age > 20; [Link](Student).filter([Link] > 20).all()

... WHERE course='AI' AND age=20; .filter([Link] == "AI", [Link] == 20)

... WHERE name LIKE 'R%'; .filter([Link]("R%"))

... ORDER BY age DESC; .order_by([Link]())

... LIMIT 20 OFFSET 40; .limit(20).offset(40)

SELECT COUNT(*) FROM students; [Link](Student).count()

INSERT INTO students ... [Link](obj); [Link]()

UPDATE students SET age=21 WHERE id=1; [Link] = 21; [Link]()

DELETE FROM students WHERE id=1; [Link](obj); [Link]()

BEGIN / COMMIT implicit session / [Link]()

ROLLBACK [Link]()

9.5 The Four-Layer Mental Model

LAYER TOOL QUESTION IT ANSWERS

----- ---- -------------------
Transport HTTP / REST How does the request arrive?
Validation Pydantic Is this data acceptable?
Translation SQLAlchemy How do I express this as SQL?
Storage PostgreSQL Where does it live permanently?

If you can place any new concept into one of these four layers, you understand it.

9.6 Master Interview Question Bank

Databases

1. What is a database and why can't we use Python lists?

2. Difference between a database, a table, a row, and a column?
3. What is a Primary Key? What rules must it satisfy?
4. What is a Foreign Key?
5. Why do databases enforce data types?
6. Why PostgreSQL over MySQL or MongoDB?
7. What is ACID?

PostgreSQL Setup

8. What does postgresql-contrib add?

9. What is psql ?
10. Why does PostgreSQL run as a systemd service?
11. What is the postgres Linux user and what is peer authentication?
12. What port does PostgreSQL use?
13. Why should an application not connect as a superuser?

SQL

14. What does the WHERE clause do, and why is it critical with UPDATE and DELETE ?
15. Explain ORDER BY , LIMIT , and OFFSET . How do you paginate?
16. Difference between DELETE , TRUNCATE , and DROP ?

PostgreSQL & FastAPI — Phase 2 Guide Page 54 of 56

17. What is a transaction? Explain BEGIN , COMMIT , ROLLBACK .
18. Why does WHERE column = NULL never work?
19. What is an index and why does it speed up queries?

ORM / FastAPI

20. What is an ORM and why use one?

21. Difference between a Pydantic model and a SQLAlchemy model?
22. What is psycopg2 and why is it needed?
23. Difference between the Engine and the Session?
24. Why one Session per request?
25. Why does get_db use yield instead of return ?
26. What is connection pooling and why does it matter?
27. What does pool_pre_ping solve?
28. What does [Link]() do and when do you need it?
29. Why is create_all not enough for production? (Answer: Alembic migrations.)
30. How do you prevent SQL injection?

9.7 Explain the Whole Phase in 60 Seconds

"Data in a Python list lives in RAM and disappears when the process ends, so real applications store data in a database. PostgreSQL
stores it on disk in tables made of rows and columns, where each row is uniquely identified by a primary key. We talk to PostgreSQL
using SQL — INSERT , SELECT , UPDATE , DELETE , filtered with WHERE , sorted with ORDER BY , and paginated with LIMIT and OFFSET .
Writing those queries as raw strings everywhere is unmaintainable, so we use SQLAlchemy, an ORM that translates Python objects into
SQL, with psycopg2 as the driver that physically sends it over port 5432. In FastAPI, the engine is created once and owns a connection
pool, while each request gets its own session through a Depends(get_db) dependency that uses yield inside try/finally so the
connection is always returned to the pool. Pydantic validates the data at the HTTP boundary, SQLAlchemy models describe the tables,
and the credentials come from a .env file so the same code runs on a laptop, in Docker, and on AWS."

9.8 What Comes Next

Next Topic Why

Relationships ForeignKey , relationship() , one-to-many and many-to-many

Alembic Real schema migrations — the replacement for create_all

Indexes & EXPLAIN Making slow queries fast

The N+1 problem The classic ORM performance trap; fixed with joinedload

Async SQLAlchemy asyncpg + AsyncSession for high-concurrency APIs

Testing A separate test database plus rollback-per-test fixtures

Redis Caching the results of expensive PostgreSQL queries

Docker Running FastAPI and PostgreSQL together with docker-compose

AWS RDS Managed PostgreSQL — only the DATABASE_URL changes

Final Checklist — Can You Do All of This?

☐ Explain why a Python list cannot be used for storage

☐ Install PostgreSQL and manage it with systemctl
☐ Log in via sudo -i -u postgres and explain peer authentication
☐ Create a user, a database, and grant privileges
☐ Draw: Server → Database → Table → Row → Column
☐ Define a table with SERIAL PRIMARY KEY , NOT NULL , and DEFAULT

PostgreSQL & FastAPI — Phase 2 Guide Page 55 of 56

 ☐ Write all five CRUD statements from memory
☐ Paginate with ORDER BY + LIMIT + OFFSET
☐ Explain why UPDATE and DELETE without WHERE are dangerous
☐ Explain what an ORM is, using the translator analogy
☐ Distinguish a Pydantic schema from a SQLAlchemy model
☐ Write get_db() from memory and explain every line
☐ Explain connection pooling and pool_pre_ping
☐ Load DATABASE_URL from .env instead of hard-coding it
☐ Build a full CRUD API and prove the data survives a restart

When every box is ticked, you have completed Phase 2.

PostgreSQL & FastAPI — Phase 2 Guide Page 56 of 56