📝 | 08. SQL | Data Modeling Deep Dive 💾

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.

📜 Prisma $queryRaw