Data with PostgreSQL and Prisma
The partner ordering API stops keeping orders in memory and starts keeping them in PostgreSQL, through Prisma. This lesson shows what the ORM does for you, what it hides from you, and how to write data code that stays correct when two people order for the same shop at the same second.
By the end you can
- Write a Prisma schema for shops, articles, price lists, orders and order lines, mapped to snake_case tables
- Create, edit and ship migrations with migrate dev and migrate deploy, and add rules Prisma cannot express
- Connect Prisma to NestJS with a PrismaService that the container injects
- Place an order all or nothing in one transaction, with a credit check that holds under concurrency
- Find an N+1 in the query log and fix it with include and select, and use raw SQL without injection
The everyday picture
Think of a shop owner who sends a helper to the wholesale market with a written list. The owner writes in plain words: "rice for the shop, and everything on last week's bill". The helper knows the market's layout and its language, and comes back with the goods. The owner never needs to learn where each stall is.
That helper is very useful, with two catches. If the list says "for each item on last week's bill, go and check its price", a literal helper walks to the market fifty times. And if the list says "pay for the rice and then pay for the oil", a helper who pays for the rice and then gets stuck in traffic leaves you with half a purchase. A good owner writes the list so that the helper makes one trip, and so that the whole purchase either happens or does not.
An ORM is that helper between your TypeScript code and PostgreSQL. This lesson is about writing it a good list.
What an ORM does, and what it costs
An ORM does four jobs. It turns your method calls into SQL. It turns the rows that come back into plain objects. It gives those objects types, so the compiler knows that an order has a totalCents number and no total field. And it keeps the database structure in step with your code through migration files.
Prisma does these jobs from one file, schema.prisma. You describe your models there. Two commands read it: prisma generate writes a TypeScript client whose methods and types match your models, and prisma migrate dev writes the SQL that changes the database to match. Your services then call methods such as prisma.partnerOrder.findMany() and get typed results back.
The costs are real, and you pay them in production. First, the ORM hides how many queries you send. A loop that looks like ordinary code can send fifty queries. Second, it hides the SQL text, so a slow query is harder to connect to the line of code that made it. Third, some database features have no ORM syntax: CHECK constraints, partial indexes, row locks. For those you write SQL yourself, either in a migration file or in a raw query. None of this is a reason to avoid an ORM. It is a reason to keep the SQL lessons close: SQL you'll actually use, Indexes from the inside and Reading query plans and fixing slow queries all still apply, because Prisma ends up sending SQL like anyone else.
Quick check
A new teammate says "we use Prisma, so we don't need to read SQL any more". Which reply is most accurate?
Prisma writes the SQL, but you are still responsible for how many queries run, which indexes exist and which rules the database enforces. The query log and the migration files are both SQL. Raw queries are an escape hatch, not a way of working.