SafeQL & Prisma Client

This page explains how to improve the experience of writing raw SQL in Prisma ORM. It uses Prisma Client extensions and SafeQL to create custom, type-safe Prisma Client queries which abstract custom SQL that your app might need (using $queryRaw).

The example will be using PostGIS and PostgreSQL, but is applicable to any raw SQL queries that you might need in your application.

SafeQL allows for advanced linting and type safety within raw SQL queries. After setup, SafeQL works with Prisma Client $queryRaw and $executeRaw to provide type safety when raw queries are required.

SafeQL runs as an ESLint plugin and is configured using ESLint rules. This guide doesn't cover setting up ESLint and we will assume that you already having it running in your project.

To follow along, you will be expected to have:

• A PostgreSQL database with PostGIS installed
• Prisma ORM set up in your project
• ESLint set up in your project

At the time of writing, Prisma ORM does not support working with geographic data, specifically using PostGIS.

A model that has geographic data columns will be stored using the Unsupported data type. Fields with Unsupported types are present in the generated Prisma Client and will be typed as any. A model with a required Unsupported type does not expose write operations such as create, and update.

Prisma Client supports write operations on models with a required Unsupported field using $queryRaw and $executeRaw. You can use Prisma Client extensions and SafeQL to improve the type-safety when working with geographical data in raw queries.

If you haven't already, enable the postgresqlExtensions Preview feature and add the postgis PostgreSQL extension in your Prisma schema:

generator client {

  provider        = "prisma-client"

  output          = "./generated"

  previewFeatures = ["postgresqlExtensions"] 

}

datasource db {

  provider   = "postgresql"

  extensions = [postgis] 

}

Next, create a migration and execute a migration to enable the extension:

bunx prisma migrate dev --name add-postgis

pnpm prisma migrate dev --name add-postgis

yarn prisma migrate dev --name add-postgis

npx prisma migrate dev --name add-postgis

For reference, the output of the migration file should look like the following:

-- CreateExtension

CREATE EXTENSION IF NOT EXISTS "postgis";

You can double-check that the migration has been applied by running prisma migrate status.

Add a new model with a column with a geography data type once the migration is applied. For this guide, we'll use a model called PointOfInterest.

model PointOfInterest {

  id       Int                                   @id @default(autoincrement())

  name     String

  location Unsupported("geography(Point, 4326)")

}

You'll notice that the location field uses an Unsupported type. This means that we lose a lot of the benefits of Prisma ORM when working with PointOfInterest. We'll be using SafeQL to fix this.

Like before, create and execute a migration using the prisma migrate dev command to create the PointOfInterest table in your database:

bunx prisma migrate dev --name add-poi

pnpm prisma migrate dev --name add-poi

yarn prisma migrate dev --name add-poi

npx prisma migrate dev --name add-poi

For reference, here is the output of the SQL migration file generated by Prisma Migrate:

-- CreateTable

CREATE TABLE "PointOfInterest" (

    "id" SERIAL NOT NULL,

    "name" TEXT NOT NULL,

    "location" geography(Point, 4326) NOT NULL,

    CONSTRAINT "PointOfInterest_pkey" PRIMARY KEY ("id")

);

SafeQL integrates with Prisma ORM to lint $queryRaw and $executeRaw Prisma operations. You can reference SafeQL's integration guide or follow the steps below.

bun add --dev @ts-safeql/eslint-plugin libpg-query

pnpm add -D @ts-safeql/eslint-plugin libpg-query

yarn add --dev @ts-safeql/eslint-plugin libpg-query

npm install -D @ts-safeql/eslint-plugin libpg-query

This ESLint plugin is what will allow for queries to be linted.

Next, add @ts-safeql/eslint-plugin to your list of ESLint plugins. In our example we are using an .eslintrc.js file, but this can be applied to any way that you configure ESLint.

/** @type {import('eslint').Linter.Config} */

module.exports = {

  "plugins": [..., "@ts-safeql/eslint-plugin"],

  ...

}

Now, setup the rules that will enable SafeQL to mark invalid SQL queries as ESLint errors.

/** @type {import('eslint').Linter.Config} */

module.exports = {

  plugins: [..., '@ts-safeql/eslint-plugin'],

  rules: { 

    '@ts-safeql/check-sql': [ 

      'error', 

      { 

        connections: [ 

          { 

            // The migrations path:

            migrationsDir: './prisma/migrations', 

            targets: [ 

              // This makes `prisma.$queryRaw` and `prisma.$executeRaw` commands linted

              { tag: 'prisma.+($queryRaw|$executeRaw)', transform: '{type}[]' }, 

            ], 

          }, 

        ], 

      }, 

    ], 

  }, 

} 

Note: If your PrismaClient instance is called something different than prisma, you need to adjust the value for tag accordingly. For example, if it is called db, the value for tag should be 'db.+($queryRaw|$executeRaw)'.

Finally, set up a connectionUrl for SafeQL so that it can introspect your database and retrieve the table and column names you use in your schema. SafeQL then uses this information for linting and highlighting problems in your raw SQL statements.

Our example relies on the dotenv package to get the same connection string that is used by Prisma ORM. We recommend this in order to keep your database URL out of version control.

If you haven't installed dotenv yet, you can install it as follows:

bun add dotenv

pnpm add dotenv

yarn add dotenv

npm install dotenv

Then update your ESLint config as follows:

require("dotenv").config(); 

/** @type {import('eslint').Linter.Config} */

module.exports = {

  plugins: ["@ts-safeql/eslint-plugin"],

  // exclude `parserOptions` if you are not using TypeScript

  parserOptions: {

    project: "./tsconfig.json", 

  }, 

  rules: {

    "@ts-safeql/check-sql": [

      "error",

      {

        connections: [

          {

            connectionUrl: process.env.DATABASE_URL, 

            // The migrations path:

            migrationsDir: "./prisma/migrations",

            targets: [

              // what you would like SafeQL to lint. This makes `prisma.$queryRaw` and `prisma.$executeRaw`

              // commands linted

              { tag: "prisma.+($queryRaw|$executeRaw)", transform: "{type}[]" },

            ],

          },

        ],

      },

    ],

  },

};

SafeQL is now configured to lint the raw SQL you write with Prisma Client.

Create two model extensions with custom queries for the PointOfInterest model:

1. A create query that allows us to create new PointOfInterest records in the database
2. A findClosestPoints query that returns the PointOfInterest records that are closest to a given coordinate

The PointOfInterest model in the Prisma schema uses an Unsupported type. As a consequence, the generated PointOfInterest type in Prisma Client can't be used to carry values for latitude and longitude.

We will resolve this by defining two custom types that better represent our model in TypeScript:

type MyPoint = {

  latitude: number;

  longitude: number;

};

type MyPointOfInterest = {

  name: string;

  location: MyPoint;

};

Next, you can add a create query to the pointOfInterest property of your Prisma Client:

const prisma = new PrismaClient().$extends({

  model: {

    pointOfInterest: {

      async create(data: { name: string; latitude: number; longitude: number }) {

        // Create an object using the custom types from above

        const poi: MyPointOfInterest = {

          name: data.name,

          location: {

            latitude: data.latitude,

            longitude: data.longitude,

          },

        };

        // Insert the object into the database

        const point = `POINT(${poi.location.longitude} ${poi.location.latitude})`;

        await prisma.$queryRaw`

          INSERT INTO "PointOfInterest" (name, location) VALUES (${poi.name}, ST_GeomFromText(${point}, 4326));

        `;

        // Return the object

        return poi;

      },

    },

  },

});

Notice that the SQL in the line that's highlighted in the code snippet gets checked by SafeQL! For example, if you change the name of the table from "PointOfInterest" to "PointOfInterest2", the following error appears:

error  Invalid Query: relation "PointOfInterest2" does not exist  @ts-safeql/check-sql

This also works with the column names name and location.

You can now create new PointOfInterest records in your code as follows:

const poi = await prisma.pointOfInterest.create({

  name: "Berlin",

  latitude: 52.52,

  longitude: 13.405,

});

Next, add a Prisma Client extension that finds the closest points of interest to a given longitude and latitude.

const prisma = new PrismaClient().$extends({

  model: {

    pointOfInterest: {

      async create(data: { name: string; latitude: number; longitude: number }) {

        // ... same code as before

      },

      async findClosestPoints(latitude: number, longitude: number) {

        // Query for closest points of interests

        const result = await prisma.$queryRaw<

          {

            id: number | null;

            name: string | null;

            st_x: number | null;

            st_y: number | null;

          }[]

        >`SELECT id, name, ST_X(location::geometry), ST_Y(location::geometry)

            FROM "PointOfInterest"

            ORDER BY ST_DistanceSphere(location::geometry, ST_MakePoint(${longitude}, ${latitude})) DESC`;

        // Transform to our custom type

        const pois: MyPointOfInterest[] = result.map((data) => {

          return {

            name: data.name,

            location: {

              latitude: data.st_x || 0,

              longitude: data.st_y || 0,

            },

          };

        });

        // Return data

        return pois;

      },

    },

  },

});

Now, you can use our Prisma Client as normal to find close points of interest to a given longitude and latitude using the custom method created on the PointOfInterest model.

const closestPointOfInterest = await prisma.pointOfInterest.findClosestPoints(53.5488, 9.9872);

Similar to before, we again have the benefit of SafeQL to add extra type safety to our raw queries. For example, if we removed the cast to geometry for location by changing location::geometry to just location, we would get linting errors in the ST_X, ST_Y or ST_DistanceSphere functions respectively.

error  Invalid Query: function st_distancesphere(geography, geometry) does not exist  @ts-safeql/check-sql

While you may sometimes need to drop down to raw SQL when using Prisma ORM, you can use various techniques to make the experience of writing raw SQL queries with Prisma ORM better.

In this article, you have used SafeQL and Prisma Client extensions to create custom, type-safe Prisma Client queries to abstract PostGIS operations which are currently not natively supported in Prisma ORM.