How to use PostGIS with Prisma

At the time of writing, Prisma doesn’t support PostGIS. However, we can still make spatial queries using raw database access. The setup is fairly simple, but it’s not documented so this posts goes into that.

Requirements

You’ll need to use raw queries, so some SQL knowledge is required.

To actually work with spatial data, you’ll also need to understand how PostGIS works and what functions are available.

There’s surprisingly little information for PostGIS beginners online and the documentation is quite difficult to navigate unless you know exactly what you’re looking for, so I’ll go over the basics in the next section.

If you want a complete course, I used the LearnSQL.com PostGIS course and can easily recommend it.

Feel free to skip to Installing PostGIS if you just want the Prisma setup.

PostGIS basics

There are two types of spatial data in PostGIS: geometry and geography:

• Geometry is for 2D calculations: faster, but inaccurate at global scale.
• Geography is for 3D calculations: exact, but slower.

That’s because geometry calculates on a map projection (a plane) while geography takes the actual shape of the earth (a globe) into account. In most cases, you’ll want to use geometry, and this is what this post assumes.

In PostGIS, X refers to the longitude (vertical lines) and Y refers to the latitude (horizontal lines).

There are 3 basic subtypes of geometries:

• POINT(X Y)
• LINESTRING(X Y, X Y, ...)
• POLYGON((X Y, X Y, ...), (X Y, X Y, ...), ...)

Finally, you can interact with spatial data through PostGIS functions. Here are a few examples:

What SRID should I use?

The SRID is a unique identifier for a specific coordinate system. There are worldwide and local systems as well as systems in degrees or meters.

From what I understand, EPSG:4326 appears to be the standard global coordinate system using degrees (latitude and longitude) and EPSG:3857 is the system used by web mapping applications such as Google Maps.

For my project, I opted to store coordinates as 4326. If I need a distance in meters, I’ll convert to 3857 using ST_Transform.

For more info, see this StackExchange answer.

Installing PostGIS

First, you need to make sure that your database supports PostGIS.

In most cases, managed cloud PostgreSQL databases enable it out of the box. If you’re working locally, I suggest you take a look at the installation instructions for your platform.

I opted for Postgres.app which conveniently includes PostGIS. Highly recommend it if you’re on macOS and want a simple setup.

Adding PostGIS support to Prisma

Since Prisma 4.0, extendedIndexes is Generally Available. If you’re not on version 4 yet, you’ll need to enable some preview features.

Here’s the schema we’ll be using as an example:

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

generator client {
  provider        = "prisma-client-js"
  binaryTargets   = "native"

  // preview features to enable if not on v4:
  previewFeatures = ["extendedIndexes", "improvedQueryRaw"]
}

// we'll assume we had this simple model before enabling postgis
model Location {
  id        Int      @id
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Now, to be able to run PostGIS functions on the Location table, we’ll add the following lines:

model Location {
  id        Int      @id
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // the geometry column itself
  coords    Unsupported("geometry(Point, 4326)")

  // the index for that column
  @@index([coords], name: "location_idx", type: Gist)
}

Let’s break this down:

• The Unsupported field type tells Prisma Migrate to use a specified type for a column. This data will not be available to the Prisma Client. More info in the Prisma docs.
• Unsupported doesn’t work on its own, so we give it a type. It could be as simple as "geometry", but here we also specify one of the subtypes and SRIDs seen above.
• The index is still confusing to me. I’m told you need to use it. I understand the reasoning behind its usage, but I couldn’t describe exactly what it does. Database stuff ¯\_(ツ)_/¯.

Note: Prisma versions 4.5 and above support PostgreSQL extensions through a preview feature. There shouldn’t be any problem during migration if you enable it, so the following section will not be relevant.

If we attempt to migrate at this stage, we’ll most likely encounter this error:

Error: db error: ERROR: type "geometry" does not exist
   0: sql_migration_connector::apply_migration::apply_migration
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:9
   1: migration_core::state::SchemaPush
             at migration-engine/core/src/state.rs:349

That’s because the PostGIS extension needs to be created in the database before we can use its types and functions. To do that, run the following command:

prisma migrate dev --create-only

This will create a SQL migration without applying it, thus allowing you to modify it. The migration file will look like this:

-- AlterTable
ALTER TABLE "Location"
ADD COLUMN "coords" geometry(Point, 4326);
-- CreateIndex
CREATE INDEX "location_idx" ON "Location" USING GIST ("coords");

Add the following statement on top:

-- create postgis extension
CREATE EXTENSION postgis;
-- AlterTable
[...]

You can now apply the migration:

prisma migrate dev

And that’s it! Your can now send PostGIS-enabled raw queries to your database. The development experience is far from what we’re used to with Prisma, but while we wait for native support, it’s better than nothing.