Jest tests for api do not work when prisma schema has non-nullable relations

[hemildesai]

Currently, prisma does not support cascading deletes - See prisma/prisma#2810
You can add ON DELETE CASCADE manually, but that runs into prisma/prisma#2057

So, if your model has non nullable relations without cascade, yarn rw test api fails because the setup https://github.com/redwoodjs/redwood/blob/main/packages/core/src/configs/node/jest.setup.js#L13-L17 tries to invoke deleteMany on all models in the order that they're defined in schema.prisma. If this order tries to delete a parent model first, we get an error like

Invalid `prisma.user.deleteMany()` invocation:

The change you are trying to make would violate the required relation 'ProfileToUser' between the `Profile` and `User` models.

One hacky workaround is to order the models appropriately in schema.prisma, but would love to have a proper solution for this issue. Another option would be to run a raw SQL query to empty the db. I am not sure if prisma has a command to empty the database. If it does, we can directly use that ideally.

This issue may still persist after prisma supports CASCADE because not all users would want to cascade on delete.

[peterp]

Aaaah, good point, I think we should drop all the tables and recreate from scratch, @RobertBroersma ?

[RobertBroersma]

Good catch!

@peterp The reason for the weird hacky delete everything loop is because removing and re-creating the entire DB by running prisma migrate up takes forever if you want to do it after every test.

Prisma doesn't have a cleaning command yet, but I think they're working on it? [Insert reference to ticket]

A raw SQL query that truncates all tables sounds like a good solution, but I guess that wouldn't work on some NoSQL DB in the future... at which point we could change it?

[hemildesai]

@RobertBroersma Looks like you already have a relevant issue at #782. Sorry I missed this. These two issues can probably be merged.

[RobertBroersma]

@hemildesai They are related, but my ticket is still under the impression that the hack works 😅

I guess we should fix the hack until prisms releases the method described in #782

[hemildesai]

@RobertBroersma Makes sense!

[jvanbaarsen]

I was just about to create an issue for this when I decided to search first :-) I'm running in the exact same problem. I created a test repo that shows when / how the problem manifest itself: https://github.com/jvanbaarsen/constraint-example.

The workaround I used for now was to make the relation optional, so you get something like this:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  Task  Task[]
}

model Task {
  id      Int    @id @default(autoincrement())
  title   String
  owner   User?   @relation(fields: [ownerId], references: [id])
  ownerId Int?
}

The downside of this approach is that "combined" unique constraints don't work, so you cant add a @@unique([id, ownerId]) and then query on id_ownerId. This is because Prisma doesn't support nullables in unique constraints.

@hemildesai I'm curious, how did you currently solve this problem?

[hemildesai]

@jvanbaarsen The hack I used was just to order the Prisma schema. I haven't checked if Prisma has added an option to clear the entire db. If it has, you could use that.

[thedavidprice]

There have been some updates to Prisma's CLI. Looks like migrate reset is getting close to a solution:
https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-reset

The migrate reset command:

• Drops the database
• Creates a new database with the same name
• Applies all migrations

[jtoar]

@cannikin Do you think this was closed by the series of scenario-teardown PRs you did?

• Have Redwood figure out in what order tables need to be cleared during scenario teardown #3716
• Scenario teardown needs to catch different error codes from Postgres and MySQL #3724
• On scenario teardown, change table quote style based on provider #3738

[cannikin]

@jtoar yep this should be fixed, we now delete in a way that works for foreign key constraints!