Week 2 — PostgreSQL & TypeORM

Learn SQL first, then connections (pool/SSL), then TypeORM entities, repos, migrations, seeds, relations, and transactions — then ship Notes P2 as this week’s mini-project.

1. Tables & columns
Everything else in this week — keys, joins, TypeORM entities — is just more structure on top of tables and columns. Read raw SQL before you read decorators.
Open
2. Primary & foreign keys
Keys are what turn separate tables into a connected schema. tickets.assignee_id pointing at users.id is the whole relationship this example schema is built around.
Open
3. SQL CRUD
Before TypeORM writes any of this for you, know what the actual SQL looks like — it is what a Repository call or QueryBuilder produces under the hood.
Open
4. Joins
The example’s core relationship — a ticket and its assignee — lives across two tables. A join is how you see both together in one query.
Open
5. DB connections: URL, pooling, SSL, timeouts
Before Nest or TypeORM enter the picture, understand what DATABASE_URL actually configures, why pooling exists, and what happens when an app misuses connections.
Open
6. Nest ↔ PostgreSQL: TypeORM root/env
This is where the SQL-level concepts (URL, entities-as-tables, no synchronize) become one concrete Nest config object.
Open
7. Entities
User and Ticket are the example entities behind the users/tickets rows you have already seen in raw SQL.
Open
8. Repositories / QueryBuilder
This is the layer a service like TicketsService uses — @InjectRepository(Ticket) gives it a Repository<Ticket>, wired up via TypeOrmModule.forFeature in the feature module.
Open
9. Migrations (deep dive)
This is the single most important backend habit in the course: schema changes are files you review, not something TypeORM infers silently.
Open
10. Seeders
A seed script is exactly how demo accounts like admin@course.local and member@course.local (password123) get into a fresh database.
Open
11. Relationships: 1:1, 1:N, N:N
The example schema has a real 1:N relationship (User ↔ Ticket) — 1:1 and N:N are covered here as patterns you will need for other features, shown as illustrative extensions of this same schema.
Open
12. Transactions & multi-write consistency
Repository/QueryBuilder calls outside a transaction each commit independently; TypeORM gives you an explicit way to group writes when that independence would be a bug.
Open
13. Notes API — Postgres + TypeORM milestone
Maps to Week 2 SQL + TypeORM. Use W3Schools SQL/PostgreSQL under /resources if you need query refreshers.
Open

End-of-week mini-project · practice only

Library Catalog API (Postgres + TypeORM)

Model a small library — books, authors, publishers, and loans — with real tables, migrations, seeds, and every relationship kind.

Definition

A PostgreSQL-backed Nest service that stores a book catalog and lending records using TypeORM entities, reviewed migrations, and 1:1 / 1:N / N:N relations.

In simpler words

Build the backend a small library would use: add books and authors, connect them properly, and lend a book out inside one safe transaction.

Concepts covered this week

Tables & columns: books, authors, publishers, book_details, and loans tables with typed columns.
Primary & foreign keys: PKs on every table and FKs linking loans → books and books → publishers.
SQL CRUD: Explore insert/select/update/delete in psql before wrapping them in TypeORM.
Joins: A "books with author + publisher" list built from joined tables.
DB connections: URL, pooling, SSL, timeouts: DATABASE_URL plus pool size, SSL, and connect timeout in the data source.
Nest ↔ PostgreSQL: TypeORM root/env: TypeOrmModule.forRootAsync reads config from ConfigService/env.
Entities: Book, Author, Publisher, BookDetail, and Loan entity classes.
Repositories / QueryBuilder: A /books/search endpoint filters by title/author using the QueryBuilder.
Migrations (deep dive): Generate and run migrations with synchronize: false; review the SQL first.
Seeders: A seed script inserts a few publishers, authors, and books.
Relationships: 1:1, 1:N, N:N: Book↔BookDetail (1:1), Publisher↔Book (1:N), Book↔Author (N:N), Book↔Loan (1:N).
Transactions & multi-write consistency: Borrowing a book creates a Loan and decrements availableCopies atomically.

Deliverables

Five entities wired with correct 1:1, 1:N, and N:N relations
Reviewed migrations (synchronize: false) plus a seeder
GET /books with author + publisher joined; GET /books/search via QueryBuilder
POST /loans that borrows a book inside a transaction and updates availableCopies

Build guide

Sketch the ERDOn paper, draw books, authors, publishers, book_details, loans and mark each relationship cardinality before coding.
Explore in psqlCreate the tables by hand and run raw CRUD + a join to feel the SQL your entities will generate.
Wire the connectionConfigure TypeOrmModule.forRootAsync with DATABASE_URL, pool size, SSL, and a connect timeout; keep synchronize: false.
Define entities + relationsAdd @OneToOne (BookDetail), @ManyToOne/@OneToMany (Publisher↔Book, Book↔Loan), and @ManyToMany (Book↔Author).
Generate + run a migrationRead the generated SQL, then run it. Add a seeder that inserts sample publishers, authors, and books.
Query without N+1Build GET /books that joins author + publisher, and /books/search using the QueryBuilder for title/author filters.
Borrow in a transactionPOST /loans opens a transaction: insert the loan and decrement availableCopies; roll back if no copies remain.

Acceptance checklist

- [ ] TypeOrmModule.forRootAsync reads env; synchronize: false
- [ ] 1:1, 1:N, and N:N relations all present and migrated
- [ ] Migration SQL reviewed before running; seeder inserts sample data
- [ ] GET /books joins author + publisher (no N+1)
- [ ] POST /loans is transactional and cannot oversell copies

Stretch goals

Add a return-book endpoint that restores availableCopies in a transaction.
Add pagination to GET /books using take/skip.
Add a unique constraint on ISBN and handle the conflict cleanly.

Tips

Never flip synchronize to true — practice the migration workflow instead.
Read the generated SQL before every migration run.