Remove all items in table with Prisma2 and Jest

Question

I would like to know how can I remove all items in table with Prisma2 and Jest ?

I read the CRUD documentation and I try with this :

user.test.js

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

beforeEach(async () => {
    const prisma = new PrismaClient()
    await prisma.user.deleteMany({})
})
...

But I have an error :

Invalid `prisma.user.deleteMany()` invocation:
The change you are trying to make would violate the required relation 'PostToUser' between the `Post` and `User` models.

My Database

CREATE TABLE User (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE Post (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  title VARCHAR(255) NOT NULL,
  createdAt TIMESTAMP NOT NULL DEFAULT now(),
  content TEXT,
  published BOOLEAN NOT NULL DEFAULT false,
  fk_user_id INTEGER NOT NULL,
  CONSTRAINT `fk_user_id` FOREIGN KEY (fk_user_id) REFERENCES User(id) ON DELETE CASCADE
);

schema.prisma

model Post {
  content    String?
  createdAt  DateTime @default(now())
  fk_user_id Int
  id         Int      @default(autoincrement()) @id
  published  Boolean  @default(false)
  title      String
  author     User     @relation(fields: [fk_user_id], references: [id])

  @@index([fk_user_id], name: "fk_user_id")
}

model User {
  email    String   @unique
  id       Int      @default(autoincrement()) @id
  name     String?
  password String   @default("")
  Post     Post[]
  Profile  Profile?
}

Supper

Answer 1

You are violating the foreign key constraint between Post and User. You can not remove a User before deleting its Posts

beforeEach(async () => {
    const prisma = new PrismaClient()
    await prisma.post.deleteMany({where: {...}}) //delete posts first
    await prisma.user.deleteMany({})
})

Or set CASCADE deletion on the foreign key, this way when you delete a User its posts will be automatically deleted

Pangermanism

Thank you for your answer ! It's a bug with Prisma 2 github.com/prisma/prisma/issues/2810 – Funnelform

Answer 2

Alternative solution:

I found a guide from a medium article. But here's the code I'm using based from that article the only difference is the table names are dynamic.

It works well if you set cascade deletes on the tables properly. Either way, you can use the one from the article or maybe turn off foreign key checks instead

You can also create a separate function truncateTable and pass a table name or prisma model. Or maybe pass an array of tablenames to refreshDatabase instead.

import prisma from .....
import {Prisma} from ".prisma/client";

const tableNames = Object.values(Prisma.ModelName);

export default async function refreshDatabase() {
  for (const tableName of tableNames) {
    await prisma.$queryRawUnsafe(`TRUNCATE TABLE "${tableName}" RESTART IDENTITY CASCADE`)
  }
}

Conciliar

Answer 3

This is another way to do it, this would remove all rows and its dependant rows, also would reset the ids. This way you can iterate over all the tables and order doesn't matter.

prisma.$executeRaw(`TRUNCATE TABLE "${table}" RESTART IDENTITY CASCADE;`)

Gazetteer