The briefing
The schema you already have
A refresher, not a lab. The models that ship with the repository, why the database spells things differently from your code, and which lines are real columns.
In this chapter
- Why the database says effective_from and your code says effectiveFrom
- Which lines become columns, and which are Prisma's convenience
- The rules PostgreSQL refuses to break, whatever your code does
You do not write this file. It ships with the repository, and npm run setup
has already pushed it into your database. Read it once, skim whatever you
already know, and come back when a lab mentions a field you have not met.
Open prisma/schema.prisma. Three models matter today — User sits there
unused until Lab C.
model Stop {
id Int @id @default(autoincrement())
name String
region String
originOf Route[] @relation("origin")
destinationOf Route[] @relation("destination")
@@map("stops")
}
model Route {
id Int @id @default(autoincrement())
originId Int @map("origin_id")
destinationId Int @map("destination_id")
vehicleType String @map("vehicle_type")
origin Stop @relation("origin", fields: [originId], references: [id])
destination Stop @relation("destination", fields: [destinationId], references: [id])
fares Fare[]
@@unique([originId, destinationId, vehicleType])
@@map("routes")
}
model Fare {
id Int @id @default(autoincrement())
routeId Int @map("route_id")
amountDalasi Int @map("amount_dalasi")
effectiveFrom DateTime @map("effective_from") @db.Date
source String?
status String @default("sample")
route Route @relation(fields: [routeId], references: [id], onDelete: Cascade)
@@index([routeId, effectiveFrom])
@@map("fares")
}
Everything here has two names
This is the one to read before anything else, because it will confuse somebody today.
@@map("fares") and @map("effective_from") mean: the database uses one
spelling and your JavaScript uses another. The table is fares, the model is
Fare. The column is effective_from, the field is effectiveFrom.
So all three of these are the same thing, and you will meet all three:
| Where you see it | How it is spelled |
|---|---|
psql, and the SQL in the diagram |
effective_from |
schema.prisma |
effectiveFrom @map("effective_from") |
| Every line you write in a lab | effectiveFrom |
Nothing is wrong when those disagree. SQL convention is snake_case, JavaScript
convention is camelCase, and @map is where the two are reconciled — once,
here, rather than in every query.
One of these lines is a column. Two are not.
Look at the three lines that join Route and Fare:
| Line | A real column? | What it is |
|---|---|---|
routeId Int @map("route_id") |
Yes | The foreign key. One column in fares, holding a route’s id |
route Route @relation(...) |
No | Wiring. It tells Prisma which field is the key and what it points at |
fares Fare[] on Route |
No | A convenience, so you can travel from a route to its fares |
Open fares in psql and look: there is no fares column on routes, and
there never will be. fares.route_id is the database’s truth; the rest is
Prisma making that column pleasant to use.
That convenience is not decoration, though. fares Fare[] is the only
reason Lab A can write this, half an hour from now:
include: {
fares: { where: { effectiveFrom: { lte: new Date() } } },
}
Delete that one line from the schema and the lab stops working — while the database, which never had the column, would not notice.
A route points at a stop twice
Route has two foreign keys into the same table: originId and
destinationId. Prisma cannot guess which Stop[] on the other side belongs
to which, so each relation is named — @relation("origin") — and Stop
carries both originOf and destinationOf to match.
That is why the-problem says a route runs one way. Westfield → Brikama and
Brikama → Westfield are two rows, and the pair is ordered.
The rules PostgreSQL will not let you break
Two lines do work that no amount of careful JavaScript can replace:
@@unique([originId, destinationId, vehicleType]) — the same trip, by the
same vehicle, cannot be entered twice. Your validator can check for duplicates
and still lose a race between two requests; this constraint cannot be raced,
because it is enforced at the only level nothing bypasses.
onDelete: Cascade on Fare.route — delete a route and its fares go with
it, rather than being left pointing at nothing.
And @@index([routeId, effectiveFrom]) is there because Lab A’s question is
always latest effective_from for this route. The index is shaped like the
question.
Prisma did not replace the SQL
The trace in the problem has a step that says Prisma builds the SQL, and
that is literal. prisma db push generates CREATE TABLE statements; a
findFirst generates a SELECT. The SQL did not go away — you stopped typing
it.
This matters on the day something breaks, because the error comes back in the language underneath. A failed constraint, a slow query, a migration that will not apply: all of those arrive as PostgreSQL errors, not as Prisma ones. Which is the same bargain as the one on the front of this module — a generator is fast and usually right, and you can only check it in the language it generates.
Two decisions you might have made differently
No migrations. npm run setup runs prisma db push, which makes the
database match the schema and keeps no history. That is right for a workshop
and wrong for anything holding real data — see Where to take it next.
Integer ids, not UUIDs. Sequential ids are guessable: anyone who can read
fare 41 can try 40. A public system often wants @default(uuid()) for exactly
that reason. Here, ?from=1&to=8 being readable in a URL and in curl is worth
more than unguessable keys, because you are going to spend four hours reading
those URLs. That is a trade, and it is worth saying out loud to your own
students rather than letting them assume integers are simply what one does.