Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Document how to delete all records from all tables #451

Closed
nikolasburk opened this issue Mar 16, 2020 · 22 comments · Fixed by #1510
Closed

Document how to delete all records from all tables #451

nikolasburk opened this issue Mar 16, 2020 · 22 comments · Fixed by #1510
Assignees
Labels
docs Documentation creation, updates or corrections

Comments

@nikolasburk
Copy link
Member

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.

@nikolasburk nikolasburk self-assigned this Mar 16, 2020
@sorenbs
Copy link
Member

sorenbs commented Mar 16, 2020

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
Copy link

kitze commented Mar 17, 2020

Maybe add a button in the data studio?

@schickling
Copy link
Member

Maybe add a button in the data studio?

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

@janpio janpio transferred this issue from prisma/prisma Jun 12, 2020
@mhwelander mhwelander added the docs Documentation creation, updates or corrections label Jun 16, 2020
@harryhorton
Copy link

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
Copy link

5t111111 commented Apr 2, 2021

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
Copy link

kieronjmckenna commented Apr 28, 2021

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
Copy link

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
Copy link

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
Copy link

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

@nemanjam
Copy link

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

@vuki656
Copy link
Contributor

vuki656 commented Feb 16, 2022

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
Copy link

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
Copy link

@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
Copy link

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

@tsongas
Copy link
Contributor

tsongas commented Jul 18, 2022

@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
Copy link

lord007tn commented Aug 12, 2022

@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
Copy link

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

@Lagyu
Copy link

Lagyu commented Dec 7, 2022

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
Copy link

sneko commented Dec 16, 2022

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
Copy link

how is this not a button in the prisma studio

@fredanup
Copy link

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
Copy link

npx prisma db push --force-reset

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
docs Documentation creation, updates or corrections
Projects
None yet
Development

Successfully merging a pull request may close this issue.