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 could be called an oversimplification, Prisma is an ORM framework. However, with all its extra bells and whistles, it goes beyond that and does more. It's my favorite backend tool right now.

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 Point or 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.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Post {
  id Int @id @default(autoincrement())

  body      String
  latitude  Float
  longitude Float
  radius    Int

  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Post {
  id Int @id @default(autoincrement())

  body      String
  latitude  Float
  longitude Float
  radius    Int

  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
}

Our Chef model has latitude and 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.

1. Install PostGIS with brew install postgis in your terminal.
2. 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.
3. 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.

const chefs = await prisma.chef.findMany()

const chefs = await prisma.chef.findMany()

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.

const query = await prisma.$queryRaw<{ id: number }[]>(
  `SELECT id, items FROM "Chef" WHERE ST_DWithin(ST_MakePoint(longitude, latitude), ST_MakePoint(${longitude}, ${latitude})::geography, radius * 1000)`
)

const chefs = await db.chef.findMany({
  where: {
    id: {
      in: query.map(({ id }) => id)
    }
  }
})

const query = await prisma.$queryRaw<{ id: number }[]>(
  `SELECT id, items FROM "Chef" WHERE ST_DWithin(ST_MakePoint(longitude, latitude), ST_MakePoint(${longitude}, ${latitude})::geography, radius * 1000)`
)

const chefs = await db.chef.findMany({
  where: {
    id: {
      in: query.map(({ id }) => id)
    }
  }
})

And we're done! Let's break down what just happened.

1. 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.
2. 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 radius as the chef's delivery radius. Finally, we'll return only the id of the chefs that our query selects.
3. 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.