Geo queries with Prisma
I haven't used relational databases in years since I switched to MongoDB. The freeform structure of document-based databases gave me so much freedom, and I rode that high until last year when I came across Prisma.
Back then, Prisma was still in beta and had so many limitations that prevented me from using it in any production system, even my side projects. Fortunately, that's changed with version 2.
Prisma is now my defacto standard for all side projects. It's still missing a few things I'd like (bulk insert, extending migrations), but almost everything you'd need to build a project is now there.
What is Prisma?
What about geo queries?
I was recently building Homemade and needed to show users any chefs near them. With MongoDB, this would've been incredibly easy. Just pop in a 2D index and fetch based on coordinates. But PostgreSQL has PostGIS, which isn't as fast or easy to work with as MongoDB's GeoJSON, but it's no spring chicken either.
However, the kicker is that Prisma doesn't yet support native types. This means that you can't define
Geometry types in your schema and then run queries on those fields.
Luckily, I have an easy workaround. It doesn't involve any
ALTER statements to your database or fake
String fields in your Prisma schema. However, I haven't benchmarked it and don't know how well it'll perform under production loads.
What are we building?
Our app has a list of chefs that each deliver near them in a preset radius. When a user opens the app, we'll show them all chefs nearby within delivery distance.
To start, we'll create a sample Prisma schema we can use for our example.
Chef model has
longitude defined as
Float to run any operations on them if needed. This is not ideal as PostGIS uses its own data type where the coordinates are stored in a single field, but it's a decent workaround. And
radius is an
Int for kilometers.
Next, we install PostGIS on our database. Assuming you develop on macOS like me, here are the steps.
- Install PostGIS with
brew install postgisin your terminal.
- Install the extension on your database with
CREATE EXTENSION postgis. You might want or need other extensions like
postgis_topology, so get those, too. More here.
- And you're ready to roll!
Now run your Prisma migrations and get your database populated and add some dummy data to start testing.
Since Prisma won't let you run geo queries, you'll have to add one extra query to your fetch function.
Let's say this is how you fetch a list of chefs right now.
We're gonna switch it around a bit and get the best of both worlds; geo queries with
$queryRaw and the actual data with Prisma. It'll look something like this.
And we're done! Let's break down what just happened.
- Fetch your coordinates and radius from the user. Ensure you sanitize all input properly since we're passing the data into a raw query and bypassing Prisma's sanitizer. Radius is in meters, so don't forget to multiply it by 1,000 if your input is in kilometers.
- In our raw query, we're creating two geo points with
ST_MakePoint; one for the chef location and another for input. We're then checking if our input location falls within a circle with the center as the chef's location and
radiusas the chef's delivery radius. Finally, we'll return only the
idof the chefs that our query selects.
- Call Prisma to fetch the chefs by passing the ids we got back from our raw query. This allows us to use all Prisma features like relationships and serialization vs. if we had just used the raw query to fetch all data. This one is up to you. You can just return all columns from the raw query and send them back to the client, or you can do what I did.
That was it. Most workarounds I've found for running geo queries with Prisma require you to add
geometry fields manually and then set them as
String in your Prisma schema and finally run raw queries anyway. This method allows you to avoid all that extra work. However, it does come with a slight hit to performance because we're creating two new points to run our operations on every query instead of one.
Until Prisma expands their support for native types, this is an excellent workaround as it doesn't involve any extra work to get PostGIS up and running. Some database providers (like Render) already have many extensions preinstalled on their PostgreSQL databases, including PostGIS. Without custom migration scripts, you can deploy this with a lot more ease than you would if you had to alter your database.