Document how to delete all records from all tables about docs HOT 22 CLOSED

Maybe add a button in the data studio?

from docs.

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()
}

from docs.

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

from docs.

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()));
};

from docs.

npx prisma db push --force-reset

from docs.

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.

from docs.

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.

from docs.

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 !

from docs.

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()),
    );
  }
}

from docs.

@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);
})();

from docs.

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

from docs.

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

from docs.

how is this not a button in the prisma studio

from docs.

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 🙏

from docs.

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!

from docs.

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

from docs.

@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.

from docs.

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

from docs.

@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.

from docs.

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

from docs.

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.

from docs.

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

from docs.