prisma: Raw query failed. Code: `42704`. Message: `db error: ERROR: type \"geography\" does not exist`

Bug description

I’m using PostgresQL with the PostGIS extension and have the following Query:

const query = `
SELECT
	id
FROM
	my_project_prisma2."JobLocation"
WHERE
	ST_DWithin(ST_MakePoint(lng, lat)::geography, ST_MakePoint($1, $2)::geography, $3)
`

export const queryLocationsWithinAreaIds = (areaCenter: AreaCenter, ctx: Context) => {
	return ctx.pg.query(query, [areaCenter.lng, areaCenter.lat, areaCenter.radius])
}

As you can see I’m using ctx.pg which is a pg Pool instance. This is working as expected.

Yesterday I upgraded from Prisma 2.0.0-preview021 to 2.1.0 and wanted to use the now available prisma.queryRaw. But when changing to prisma.queryRaw I get the following error:

Raw query failed. Code: `42704`. Message: `db error: ERROR: type \"geography\" does not exist`

How to reproduce

See https://github.com/RafaelKr/prisma-repro-2847

Expected behavior

It should give me a result. The pg client works.

Prisma information

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

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

model JobLocation {
  id            String   @default(cuid()) @id
  lat           Float?
  lng           Float?
}

POSTGRES_URL=postgresql://my_project:prisma2@127.0.0.1:5432/my_project?schema=my_project_prisma2

Environment & setup

OS: Linux Mint 19.2 Cinnamon
Database: PostgreSQL + PostGIS extension
Node.js version: 13.7.0
Prisma version:

@prisma/cli          : 2.1.0
Current platform     : debian-openssl-1.1.x
Query Engine         : query-engine 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/rafael/[...]/node_modules/@prisma/cli/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/rafael/[...]/node_modules/@prisma/cli/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/rafael/[...]/node_modules/@prisma/cli/introspection-engine-debian-openssl-1.1.x)
Format Binary        : prisma-fmt 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/rafael/[...]/node_modules/@prisma/cli/prisma-fmt-debian-openssl-1.1.x)

About this issue

Original URL
State: open
Created 4 years ago
Reactions: 7
Comments: 20 (13 by maintainers)

Commits related to this issue

chore: work around prisma issue 2847 https://github.com/prisma/prisma/issues/2847#issuecomment-1109714164 — committed to RafaelKr/prisma-repro-2847 by RafaelKr 2 years ago

Most upvoted comments

I’m anyhow changing the whole SQL backend right now to SQLx, so this needs to wait a bit. Might need some support in SQLx too, let’s see…

pimeys on Sep 15, 2022

I’ve created a minimal reproduction: https://github.com/RafaelKr/prisma-repro-2847

Instructions can be found in the README. @janpio @pantharshit00

RafaelKr on Jun 29, 2020

Hey folks,

@RafaelKr I’ve found the root cause for your issue. You’ve installed PostGIS in your public schema but you’re querying data in the repro_2847 schema.

Now, this is not necessarily a mistake but Prisma sets the search_path to the defined schema in your Postgres URL (SET search_path=repro_2847). This prevents PG from finding the PostGIS type definitions stored in the public schema.

Their are two workarounds I can offer you right now:

Move PostGIS to the repro_2847 schema
Update the search_path to include the schema where PostGIS is installed 👇. This command has to be executed once before you run any PostGIS query.
```
 await prisma.$executeRaw`SET search_path=<postgis_schema>,<your_schema>`;
```
In your reproduction, <postgis_schema> = public, and <your_schema> = repro_2847.

Weakky on May 2, 2022

I used @RafaelKr reproduction example, and I’ve updated all the dependencies in the project, to try and see if this issue is solved in the latest versions or not. but unfortunately it didn’t work. and it gave me the same error 😦

If you like you can do a PR with the updated dependencies to my repo.

Update: My reproduction repo at https://github.com/RafaelKr/prisma-repro-2847 is now upgraded to the latest version of prisma and it’s still reproducible there.

RafaelKr on Feb 16, 2022

@Sytten See reproduce

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

export const db = new PrismaClient();

test("Check if postgis works", async () => {
  await db.$queryRaw("select st_point(1.234, 2.234)");
});

error:

FAIL tests/postgis.test.ts
  ✕ Check if postgis works (68 ms)

  ● Check if postgis works


    Invalid `prisma.queryRaw()` invocation:


      Raw query failed. Code: `N/A`. Message: `error deserializing column 0: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `geometry``

      at PrismaClientFetcher.request (node_modules/@prisma/client/runtime/index.js:78121:15)

Newbie012 on Dec 31, 2020

@Sytten Can you shoe me what do u mean cast it to int ? in the query ? ${radius}::numeric ?

Adidi on Dec 23, 2020

Thanks for the detailed reproduction 👍 , I can reproduce this.

@pimeys would be the I guess that would look into this.

pantharshit00 on Jul 1, 2020