Lesson 16.0

Orientation: the chapter that puts a real database behind your API

Artifact: A clear map of the three layers (SQL, Postgres, Prisma) and what each one does

1. The promise

You'll know what this chapter covers, why it goes SQL first, and what the Nest API from Ch 15 looks like at the end. The assignment plugs straight into the to-do API you already have. No new app to invent.

2. The mental model

Think of the chapter as a Russian doll. The outside is SQL, the language databases speak. The middle is Postgres, the database we install. The inside is Prisma, the tool that writes the SQL for you. You learn the outside first, so the inside makes sense when you get there.

(DIAGRAM: the chapter's three layers — annotated SVG — outer ring "SQL", middle ring "Postgres", inner ring "Prisma + Nest", arrows showing each layer wraps the previous)

A database is a program that stores structured data and answers questions about it. Postgres is the one we use. SQL (structured query language) is how you ask it questions. A schema is the shape of the data: which tables exist, which columns, which types. A migration is a small file that changes the schema, applied in order, checked into Git. An ORM (object-relational mapper) turns rows into objects in code. Prisma is the one we use.

3. You do it, with me

Nothing to install in this unit. Read along.

Open your Ch 15 Nest project on disk. You'll need it for every unit from here.

(SCREENSHOT: the course chapter index — Ch 16 expanded — full window — Orientation unit highlighted)

Start the Ch 15 to-do API. The endpoint returns mocked JSON from an in-memory array. By the end of this chapter, the same endpoint returns rows from Postgres.

(SCREENSHOT: the to-do Nest API from Ch 15 running in VSCode — terminal showing GET /todos returning mocked JSON — full window)

The chapter has six parts, in order:

  • Concepts. Why databases. SQL vs NoSQL. (Units 16.1, 16.2)
  • Setup. Install Postgres. Connect with psql and a GUI. (Units 16.3, 16.4)
  • SQL by hand. Tables, queries, joins, normalisation. (Units 16.5 to 16.11)
  • Performance. Transactions, indexes, the N+1 trap. (Units 16.12 to 16.14)
  • Tooling. Prisma, pooling, seeding. (Units 16.15 to 16.17)
  • Assignment. Wire it all into the Nest app. (Unit 16.assign)

Pace yourself. Most units run 14 to 18 minutes. The Prisma unit (16.15) runs 20. The assignment runs about three hours.

A note on the running example. The to-do API from Ch 15 is the example. You don't need to invent a new app. By the end you'll have a User, a TodoList, and a Todo table, all wired up through Prisma.

A note on what comes next. Ch 17 (Auth) puts a User table to work for login. Ch 24 (Deploying) ships the migration files to production. The discipline you build here pays off in both.

4. What you should be seeing

Three things in your head:

  1. One chapter, three layers. SQL on the outside, Postgres in the middle, Prisma on the inside.
  2. One running example. The Ch 15 to-do API, swapped from mocks to a real database.
  3. One sequence. Concepts, setup, SQL by hand, performance, tooling, assignment.

If you can list those three, you're done with this unit.

5. Common stumbles

  • You skip ahead to Prisma. The Prisma errors are unreadable without SQL. The chapter trains you in that order on purpose.
  • You start from a fresh project. Don't. Open the Ch 15 Nest repo. The assignment grades the same app, now backed by Postgres.
  • You read the whole chapter in one weekend. Two units a day for a week beats seventeen in one sitting. The B-tree unit alone needs space to land.

6. You should know

1/19
Next