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)
3

I have a similar issue, which I have somewhat solved by building test and describe tags in Jest that allow execution of either SQL statements or files before and after tests: GitHub - klobetime/samplecode. A bit rough – any embedded semi-colons in the SQL will break the logic – and the code is somewhat ham-fisted in places, but it seems to work for my purposes right now.

4

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

I think you’re correct about this! Scenarios can be great, but there’s always going to be that 10-20% of cases where you’ll need to roll your own solution - this is especially true if you’re using features not supported by Prisma directly!

Remember scenarios are just “sugar” around the concept of:

  1. Defining your mocks (scenarios)
  2. Seeding your test database
  3. Running your test
  4. Resetting your test database

In the case of needing a bit more control, such as PostGIS, if you’re not constantly doing this, it would probably be easiest to seed your data in a beforeAll or beforeEach block. Just remember to clear the data in afterAll or afterEach :slight_smile:

Hope this helps :v: