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?
}

mousto090mousto090 1,9491 gold badge13 silver badges12 bronze badges · Accepted Answer · 2020-06-19 12:14:42Z

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

Thank you for your answer ! It's a bug with Prisma 2 github.com/prisma/prisma/issues/2810 — Jun 22, 2020 at 20:38

Simon PuenteSimon Puente 4611 gold badge9 silver badges22 bronze badges · Accepted Answer · 2021-04-28 23:18:08Z

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;`)

medard mandanemedard mandane 5684 silver badges7 bronze badges · Accepted Answer · 2023-11-13 09:22:48Z

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 ${tableName}`)
  }
}

Remove all items in table with Prisma2 and Jest