using PostGIS with Prisma and Nest.js

PostGIS is the foundation for handling geographic information in the widely used PostgreSQL. I had to implement an API with PostGIS, PostgreSQL, Prisma and Nest.js.

However unfortunately Prisma does not officially support PostGIS. So I had to research how to handle with PostGIS’s geometry column with Prisma.

This is a current open issue regarding this. https://github.com/prisma/prisma/issues/2789

Solution

If you are not familiar with Prisma and Nest.js, please read the official documentation to finalize the set up and come back here.

• Nest.js Prisma

Step 1 (define schema)

First of all, you have to define a table in prisma schema like below. The highlighted line is the PostGIS’s geometry column. I set 3857 because this is widely used for online map services such as Google map

• EPSG:3857

model Shop {
  id               Int                                   @id @default(autoincrement())
  name             String
  isOpen           Boolean                               @default(false) @map("is_open")
  coordinate       Unsupported("geometry(Point, 3857)")?

  @@map("shops")
}

Step 2 (generate migrate file)

Secondly you have to run this command to generate migrate file.

npx prisma migrate dev --create-only
npx prisma generate

Due to the command, you can find migration files under prisma/migrations.yml. Find the generated migration file and add this line at the top of the file.

CREATE EXTENSION postgis;

Unless we don’t add this or you don’t create an extension in advance, you will encounter an error below when you deploy the migration file.

Error: Database error
Error querying the database: db error: ERROR: type "geometry" does not exist
   0: migration_core::api::ApplyMigrations
             at migration-engine/core/src/api.rs:73

Step 3 (deploy migrate file)

Run the command and deploy the migration file.

npx prisma migrate deploy

Step 4 (Get data from DB)

If the migration success, we can get data from the database by postgres command or prisma. Here is the sample code.

Following the Nest.js’s documentation, I created prisma.service.ts. The detail of this file can be found in the documentation, so I will skip the explanation.

import { Injectable, OnModuleInit } from "@nestjs/common";
import { PrismaClient } from "@prisma/client";

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  constructor() {
    super({
      log: ["query"], // logging type. Please set if it is required.
    });
  }

  async onModuleInit() {
    await this.$connect();
  }
}

I define the output type for Prisma SQL results and import this type into Prisma $queryRaw results.

import { Shop } from "@prisma/client";

export type ShopWithCoordinate = Pick<Shop, "id" | "name"> & {
  longitude: number;
  latitude: number;
};

Importing defined type and PrismaService in shop.repository.ts, we can get result like below. In the example, if the argument of the getShops function contains name, prisma raw query can return shops filtering with the name, unless, it returns all shops’s id, name and coordinate.

$queryRaw is safe from SQL injections and currently this is the only approach to handle PostGIS.

• Prisma: Raw queries

@Injectable()
export class PlaceRepository {
  constructor(private readonly prisma: PrismaService) {}

  async getShops(name?: string) {
    const searchCondition = name
      ? Prisma.sql`WHERE name ILIKE ${name}`
      : Prisma.empty;

    return this.prisma.$queryRaw<ShopWithCoordinate[]>`
      SELECT
        id,
        name,
        ST_X(coordinate) AS longitude,
        ST_Y(coordinate) AS latitude,
      FROM shops
      ${searchCondition}
    `;
  }
}

https://www.prisma.io/docs/orm/prisma-client/queries/raw-database-access/raw-queries