Document how to delete all records from all tables

[nikolasburk]

Sometimes it's helpful to wipe all data records in the DB but keep the tables. This is currently not possible via the Prisma Client API so we should document a workaround.

One potential approach which currently doesn't work because it relies on a private property of PrismaClient is this:

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

function lowerCase(name: string): string {
  return name.substring(0, 1).toLowerCase() + name.substring(1)
}

const promises = prisma.dmmf.datamodel.models.map(model => {
  return new Promise(async resolve => {
    const modelName = lowerCase(model.name)
    const modelMethods = prisma[modelName]

    if (modelMethods) {
      try {
        resolve(await modelMethods.findMany({}))
      } catch (e) {
        resolve(e)
      }
    }
  }

async function main() {                    
  await Promise.all(promises)
}

main()

Alternatively, the following approach (by @nnennajohn) which is based on knex.js can be used:

import camelCase from 'lodash/camelCase';
import { prisma } from '@cpath/universal/shared/db';
import knexClient from './knex';
// Contents of the knexClient import above
// import Knex from 'knex';
// const knexClient: Knex = Knex({
//   client: 'pg',
//   connection: {
//     user: process.env.POSTGRES_USER,
//     password: process.env.POSTGRES_PASSWORD,
//     host: process.env.POSTGRES_HOST,
//     port: Number(process.env.POSTGRES_PORT),
//     database: process.env.POSTGRES_DB,
//   },
// });
// Contents of the prisma import above
// export const prisma = new PrismaClient();
function deleteRecords(tablesList) {
  const validTables = tablesList.filter((tableName) => tableName[0] !== '_');
  console.log('These are the valid tables', validTables);
  const recordsDeletions = validTables.map((table) => {
    return prisma[camelCase(table)].deleteMany({});
  });
  return Promise.all(recordsDeletions);
}
function listTables() {
  const query =
    'SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() AND table_catalog = ?';
  const bindings = [knexClient.client.database()];
  return knexClient.raw(query, bindings).then((results) => {
    return results.rows.map((row) => row.table_name);
  });
}
async function main() {
  const tablesList = await listTables();
  console.log('This is the list of knex tables', tablesList);
  if (tablesList && tablesList.length) {
    deleteRecords(tablesList)
      .then((res) => {
        console.log('Successfully deleted table records', res);
      })
      .catch((err) => {
        console.log('Error deleting table records', err);
      });
  }
}
main()
  .catch((e) => console.error(e))
  .finally();

We should also document a workaround that allows to do this without an external dependency like knex.js.

[sorenbs]

I guess you could also do something like this:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

const allProperties = Reflect.ownKeys(Object.getPrototypeOf(prisma))
const modelNames = allProperties.filter(x => x != "constructor" && x != "on" && x != "connect" && x != "runDisconnect" && x != "disconnect")

for (modelName of modelNames) {

    // handle async stuff
    prisma[modelName].deleteMany()
}

[kitze]

Maybe add a button in the data studio?

[schickling]

Maybe add a button in the data studio?

I've already created an issue for this back in October last year https://github.com/prisma/studio/issues/237. I'll raise it to the Studio team to fast track this 🙏

[harryhorton]

Another approach:

import { Injectable, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { PrismaClient, dmmf } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient
  implements OnModuleInit, OnModuleDestroy {
  constructor() {
    super();
  }

  onModuleInit() {
    return this.$connect();
  }

  onModuleDestroy() {
    return this.$disconnect();
  }

  clearDatabase() {
    const modelKeys = dmmf.datamodel.models.map(model=>model.name);

    return Promise.all(
      modelKeys.map((table) => this.executeRaw(`DELETE FROM ${table};`)),
    );
  }
}

or

import { Injectable, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient
  implements OnModuleInit, OnModuleDestroy {
  constructor() {
    super();
  }

  async onModuleInit() {
    return await this.$connect();
  }

  async onModuleDestroy() {
    return await this.$disconnect();
  }

  clearDatabase() {
    const models = Reflect.ownKeys(this).filter(key=> key[0] !== '_');

    return Promise.all(
      models.map(modelKey => this[modelKey].deleteMany()),
    );
  }
}

[5t111111]

With the latest versions of prisma and prisma cilent , unfortunately any solutions proposed in this issue fails.

prisma               : 2.20.0
@prisma/client       : 2.20.0

Although I don't know if I am doing correct ways, I have got them work with the following fixes:

import { PrismaClient, Prisma } from '@prisma/client';

export const cleanupDatabase = () => {
  const prisma = new PrismaClient();
  const modelNames = Prisma.dmmf.datamodel.models.map((model) => model.name);

  return Promise.all(
    modelNames.map((modelName) => prisma[modelName.toLowerCase()].deleteMany())
  );
};

or

import { PrismaClient } from '@prisma/client';

export const cleanupDatabase = () => {
  const prisma = new PrismaClient();
  const propertyNames = Object.getOwnPropertyNames(prisma);
  const modelNames = propertyNames.filter(
    (propertyName) => !propertyName.startsWith('_')
  );

  return Promise.all(modelNames.map((model) => prisma[model].deleteMany()));
};

[kieronjmckenna]

I couldn't seem to get any of the above solutions to work as Prisma was unhappy removing the data due to relations.

An easy solution for me was to run a yarn script that dropped the schema with Postgres, then ran Prisma migrate immediately after.

Package.json

 "scripts": {
    "nuke": "psql -Atx postgresql://<username>:<password>@<host:<port>/<db> -f nuke.sql",
    "reset": "yarn nuke && prisma migrate dev -n reset"
  }

-n to stop being promoted

nuke.sql

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Run

yarn reset

and you've got an empty DB

I thought I'd share; even though it's not strictly done with Prisma, I hope it helps someone out.

[seanwash]

I found this approach in the docs, works well enough for me: https://www.prisma.io/docs/concepts/components/prisma-client/crud#deleting-all-data-with-raw-sql--truncate

[adam-arold]

I guess you could also do something like this:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

const allProperties = Reflect.ownKeys(Object.getPrototypeOf(prisma))
const modelNames = allProperties.filter(x => x != "constructor" && x != "on" && x != "connect" && x != "runDisconnect" && x != "disconnect")

for (modelName of modelNames) {

    // handle async stuff
    prisma[modelName].deleteMany()
}

Note that this is not the same as TRUNCATE TABLE as this will not reset the ids!

[nemanjam]

these two don't work, data is still in db

[nemanjam]

TypeError: prisma[modelName].deleteMany is not a function

[vuki656]

This works nicely for me:

export const wipeDatabase = async () => {
    // @ts-expect-error -- Extract all model names
    const models = Object.keys(orm._dmmf.mappingsMap)

    const promises = models.map((model) => {
        // Names formatted like `AccessRight`, we need `accessRight` for the orm call
        const name = model.charAt(0).toLowerCase() + model.slice(1)

        // @ts-expect-error
        return orm[name].deleteMany()
    })

    await Promise.all(promises)
}

[yoshitamagoshi]

The recommendation on this thread on using prisma migrate reset --force && prisma migrate deploy is likely the most Prisma native approach for dropping and resetting the database tables as you'd have happen on production, without the production data.

[ydennisy]

@yoshitamagoshi I do not suggest using that as it wipes settings that your DB may have which you want to keep, such as various users, permissions etc.

If used with a hosted platform like supabase it will destroy your project.

[yoshitamagoshi]

@ydennisy yes agreed, but the thread here is about "how to delete all records from all tables"?

[tsongas]

@yoshitamagoshi it's also painfully slow (like ten seconds) compared to a raw query with a script to truncate tables, not ideal for CI/CD.

[lord007tn]

@vuki656 I updated your version. I found an error where the key "_middleware" was not deleted.

import prisma from "../client";

(async function flush() {
    const models = Object.keys(prisma).filter((key) => key[0] !== "_");

    const promises = models.map((name) => {
        // @ts-expect-error
        return prisma[name].deleteMany();
    });

    await Promise.all(promises);
})();

[Chetan11-dev]

async function deleteAll() { const models = [db.user] await Promise.all(models.map(model=>model.deleteMany())) }

[Lagyu]

const prismaClient = new PrismaClient();
const modelNames = Object.keys(prismaClient).filter(
  (key) => !["_", "$"].includes(key[0])
);

for (let i = 0; i < modelNames.length; i += 1) {
  const name = modelNames[i];
  try {
    // @ts-expect-error https://github.com/prisma/docs/issues/451
    await prismaClient[name].deleteMany();
  } catch (e) {
    console.error(`Error while deleting ${name}`);
    throw e;
  }
}

In my case, $extends key prevented from working. Also, running in parallel sometimes causes deadlock, so you might better await each delete.

[sneko]

I found this approach in the docs, works well enough for me: https://www.prisma.io/docs/concepts/components/prisma-client/crud#deleting-all-data-with-raw-sql--truncate

I really recommend this suggestion as a reference. You will avoid wasting time on adjusting things due to foreign key constraints... (much harder when you manage soft delete too).

Thank you @seanwash !

[joshbedo]

how is this not a button in the prisma studio

[fredanup]

I think the most easiest solution is create another table at the beginning of the schema and then cut your old schema for a while, then run "npx prisma migrate dev --name init", these will erase all your data and create and schema with your only new table, and then do the same but now erase your single table and paste your old database schema

[munirmuratovic]

npx prisma db push --force-reset