Prisma, how to clear the database

To clear a table with Prisma you call deleteMany() with an empty filter. It deletes every record in that table. If you have relations between tables, you also need to delete records in the right order.

While testing a site that used Prisma I had the need to clear the database from time to time, to clear the test data I entered.

Deleting all records in a table

You can clear all items in a table using:

await prisma.user.deleteMany({})

This deletes every row in the User table and returns the number of deleted records:

const deleted = await prisma.user.deleteMany({})
console.log(deleted) //{ count: 18 }

If for some reason you want to iterate on the items to do some processing before deleting each one, you can fetch them first with findMany() and delete them one by one:

const users = await prisma.user.findMany({})

const deleteUser = async (user) => {
  return await prisma.user.delete({
    where: { id: user.id }
  })
}

for (const user of users) {
  await deleteUser(user)
}

In this case I am not doing anything more than the previous example, which makes all this code redundant, but you could do anything you want inside deleteUser(), like logging the record or archiving it somewhere before it’s gone.

Watch out for relations

I had a problem though because I had a relation between 2 tables, tweets and users. A tweet was associated to a user.

If you delete the users first, the database refuses. You get a Foreign key constraint failed error, because tweets still point to those users.

The order matters: first remove all tweets, then remove all users. So I wrote this function:

export const clearData = async (prisma) => {
  const users = await prisma.user.findMany({})
  const tweets = await prisma.tweet.findMany({})

  const deleteUser = async (user) => {
    return await prisma.user.delete({
      where: { id: user.id }
    })
  }
  const deleteTweet = async (tweet) => {
    return await prisma.tweet.delete({
      where: { id: tweet.id }
    })
  }

  const deleteTweets = async () => {
    return Promise.all(tweets.map((tweet) => deleteTweet(tweet)))
  }

  const deleteUsers = async () => {
    return Promise.all(users.map((user) => deleteUser(user)))
  }

  await deleteTweets()
  await deleteUsers()
}

Notice the use of Promise.all() to wrap tweets.map() so I could use await on it. All tweets are removed before I start deleting users.

Without Promise.all(), map() would fire all the deletes and return immediately, and deleteUsers() would start while tweets are still being deleted. Back to the foreign key error.

A shorter version

If you don’t need to process each record, deleteMany() in the right order does the same job with much less code:

await prisma.tweet.deleteMany({})
await prisma.user.deleteMany({})

That’s what I’d use today for a test cleanup script. Children first, then parents.