SQL
Loading "Intro to SQL"
Run locally for transcripts
When working with something you're unfamiliar with, you may struggle with
syntax. As usual, I suggest you send this into some AI assistant like
ChatGPT which can do a surprisingly good job of
helping you identify what's wrong with your syntax. It's not perfect, but it's
a good start.
No matter how great an ORM is, you do sometimes need to just break out some raw
SQL. There are some queries that just can't be represented very well in an ORM.
Search is often a good example of a query that is better done in SQL. Consider
a query that attempts to look up the cities that are closest to a geographical
location. This involves some math that cannot be represented with Prisma.
Here's a snippet from a project I worked on that did this:
acos(
sin(starport.lat * PI()/180)
* sin(city.lat * PI()/180)
+ cos(starport.lat * PI()/180)
* cos(city.lat * PI()/180)
* cos((starport.long - city.long) * PI()/180)
)
* 180/PI() * 60
-- Earth radius: 3958.76 miles
-- And one minute of arc is: 2ฯ * 3958.76 miles / 60 / 360 = 1.1515
* 1.1515
Good luck representing that in Prisma!
Click here for the full SQL if you're interested
SELECT
ship.id,
ship.modelId,
ship.hostId,
ship.starportId,
model.brandId,
ship.imageId,
ship.name,
ship.dailyCharge,
ship.capacity,
(
SELECT
avgRating
FROM
(
SELECT
hostReview.userId,
AVG(hostReview.rating) AS avgRating
FROM
Host host
INNER JOIN HostReview hostReview ON hostReview.subjectId = host.userId
GROUP BY
host.userId
) AS hostRatings
WHERE
hostRatings.userId = ship.hostId
) as hostAvgRating,
(
SELECT
avgRating
FROM
(
SELECT
ship.id,
AVG(shipReview.rating) AS avgRating
FROM
Ship ship
INNER JOIN ShipReview shipReview ON shipReview.subjectId = ship.id
GROUP BY
ship.id
) AS shipRatings
WHERE
shipRatings.id = ship.id
) as shipAvgRating
FROM
Ship ship
INNER JOIN ShipModel model ON model.id = ship.modelId
INNER JOIN (
SELECT
starport.id,
acos(
sin(starport.lat * PI()/180)
* sin(city.lat * PI()/180)
+ cos(starport.lat * PI()/180)
* cos(city.lat * PI()/180)
* cos((starport.long - city.long) * PI()/180)
)
* 180/PI() * 60
-- Earth radius: 3958.76 miles
-- And one minute of arc is: 2ฯ * 3958.76 miles / 60 / 360 = 1.1515
* 1.1515
FROM
starport
JOIN city ON city.id IN ("some_id","some_other_id")
ORDER BY
distance ASC
LIMIT
1
) AS closestStarport ON closestStarport.id = ship.starportId
WHERE
closestStarport.id = ship.starportId
LIMIT
50;
Luckily, in cases like this, we can use
prisma.$queryRaw
to execute raw SQL
queries. This is a great escape hatch for when you need it. It even supports
parameterized queries, so you can avoid SQL injection attacks:const ships = await prisma.$queryRaw`
SELECT name, username from user WHERE user.id = ${params.userId};
`
// the interpolated string is auto-escaped.
The tricky bit here is that the return value of this query is going to be
any
because Prisma has no way of knowing what the shape of the data is going to be.We could definitely just cast this to the shape we expect, but the problem with
that is if we change the query, we may forget to update the type. So instead
we can use
zod
to define a schema for the data we expect to get back and then
validate it.import { z } from 'zod'
const CitySchema = z.object({
id: z.number(),
name: z.string(),
})
const CitiesSchema = z.array(CitySchema)
const rawCities = await prisma.$queryRaw`...`
const result = CitiesSchema.safeParse(rawCities)
if (result.success) {
const cities = result.data
} else {
console.error(result.error)
}
It's unfortunate to parse your own data (if you can't trust yourself, who can
you trust!?), but if this became a bottleneck for you, you could strip out the
runtime validation in production.