How to define test scenarios using raw sql queries (because PostGIS)

Get Help and Help Others
1

Hey all,

Came back to a project I started in Redwood some time ago.

Some prisma models are using postgis coordinates. Since this isn’t supported in Prisma, I have to use the Unsupported field and seed using raw sql, which works adequately.

I’m now wondering how to go about testing a service that uses PostGIS functionnality. Is it possible at all to use the defineScenario function with raw sql? Or is there an alternative I’m missing?

Here’s an example model using PostGIS:

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

  coords    Unsupported("geometry(Point, 4326)")?

  @@index([coords], name: "location_idx", type: Gist)
}

and an example service with hard coded values:

export const locationsByDistance: QueryResolvers['locations'] =
  () => {
    return db.$queryRaw`
  SELECT id,
  -- other fields
  ST_DistanceSphere (coords, ST_MakePoint (- 73.567253, 45.501690)) AS distance
  FROM "Location"
  WHERE ST_DistanceSphere (coords, ST_MakePoint (- 73.567253, 45.501690)) <= 600000
  ORDER BY distance ASC
  `
  }

Any help would be greatly appreciated.

1 Like
2

Dug into this a bit more.

Judging from the the seedScenario function implementation, it appears to be impossible.

It’s doing a db.create call, which cannot create unsupported types.

This wouldn’t be a problem if I could add some logic between lines 129 and 130 of buildScenario to run db.$executeRaw which would allow me to add the postgis column.

At the moment, tests run immediatly after seeding:

const scenarioData = await seedScenario(scenario)
// no way to do anything here between seeding and calling the test
result = await testFunc(scenarioData)