Drizzle Team•10mo ago

how can I truncate all tables in the new drizzle studio? Previously there was a drop down button

tsia

5 Replies

Mykhailo•10mo ago

Hi, @shikishikichangchang! As for now, there is no such option, but it will be added in the future

shikishikichangchangOP•10mo ago

Thanks that would be really helpful. Why was that feature removed?

Mykhailo•10mo ago

As I know it's due to studio redesign

wkd9241•9mo ago

I have created 2 files + 1 script to do it from the terminal. Just copy and paste them and you should be fine. Edit them to suit your needs. This code will be executed using the tsx package. It asks a confirmation twice before executing. You can edit the prompts as you wish.

// drizzle/drop-prompt.ts

// these 2 are globally available, no need npm
import readline from 'readline';
import {exec} from 'child_process';

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout,
});

function confirmDropDatabase() {
  rl.question(
    '\n🛑 YOU ARE ABOUT TO DROP THE ENTIRE DATABASE, ARE YOU SURE? (y/N) ',
    answer => {
      if (answer.toLowerCase().trim() === 'y') {
        rl.question(
          '\n🛑 ARE YOU ABSOLUTELY SURE? THIS ACTION CANNOT BE UNDONE. (y/N) ',
          answer2 => {
            if (answer2.toLowerCase().trim() === 'y') {
              // Your command
              exec('tsx drizzle/drop-db.ts', (error, stderr) => {
                if (error) {
                  console.error(`Error: ${error.message}`);
                  return;
                }
                if (stderr) {
                  console.error(`stderr: ${stderr}`);
                  return;
                }
                console.log(`\n✅ Database dropped successfully!\n`);
              });
              rl.close();
            } else if (answer2.toLowerCase().trim() === 'n') {
              console.log('\n✅ Operation cancelled.\n');
              rl.close();
            } else {
              console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
              confirmDropDatabase(); // Ask again
            }
          }
        );
      } else if (answer.toLowerCase().trim() === 'n') {
        console.log('\n✅ Operation cancelled.\n');
        rl.close();
      } else {
        console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
        confirmDropDatabase(); // Ask again
      }
    }
  );
}

confirmDropDatabase();

// drizzle/drop-prompt.ts

// these 2 are globally available, no need npm
import readline from 'readline';
import {exec} from 'child_process';

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout,
});

function confirmDropDatabase() {
  rl.question(
    '\n🛑 YOU ARE ABOUT TO DROP THE ENTIRE DATABASE, ARE YOU SURE? (y/N) ',
    answer => {
      if (answer.toLowerCase().trim() === 'y') {
        rl.question(
          '\n🛑 ARE YOU ABSOLUTELY SURE? THIS ACTION CANNOT BE UNDONE. (y/N) ',
          answer2 => {
            if (answer2.toLowerCase().trim() === 'y') {
              // Your command
              exec('tsx drizzle/drop-db.ts', (error, stderr) => {
                if (error) {
                  console.error(`Error: ${error.message}`);
                  return;
                }
                if (stderr) {
                  console.error(`stderr: ${stderr}`);
                  return;
                }
                console.log(`\n✅ Database dropped successfully!\n`);
              });
              rl.close();
            } else if (answer2.toLowerCase().trim() === 'n') {
              console.log('\n✅ Operation cancelled.\n');
              rl.close();
            } else {
              console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
              confirmDropDatabase(); // Ask again
            }
          }
        );
      } else if (answer.toLowerCase().trim() === 'n') {
        console.log('\n✅ Operation cancelled.\n');
        rl.close();
      } else {
        console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
        confirmDropDatabase(); // Ask again
      }
    }
  );
}

confirmDropDatabase();

Here's the file that will be executed by the exec function after the 2 confirmations (don't forget to edit the command in the function to match your file path). In my implementation, I totally drop the tables. If you just want to truncate them (deleting all of the rows but keeping the table), change DROP TABLE to TRUNCATE TABLE

// drizzle/drop-db.ts

import {drizzle} from 'drizzle-orm/node-postgres'
import * as schema from './schema'
import {sql} from 'drizzle-orm'
import {Client} from 'pg'
import 'dotenv/config'

const dropDatabase = async () => {
  const client = new Client({connectionString: process.env.SUPABASE_URL})
  await client.connect()
  const db = drizzle(client, {schema})

  const tablesSchema = db._.schema
  if (!tablesSchema) throw new Error('Schema not loaded')

  const tables = Object.values(tablesSchema).map(table => table.dbName)
  for (const table of tables)
    await db.execute(sql.raw(`DROP TABLE ${table} CASCADE;`))

  await client.end()
}

dropDatabase()

// drizzle/drop-db.ts

import {drizzle} from 'drizzle-orm/node-postgres'
import * as schema from './schema'
import {sql} from 'drizzle-orm'
import {Client} from 'pg'
import 'dotenv/config'

const dropDatabase = async () => {
  const client = new Client({connectionString: process.env.SUPABASE_URL})
  await client.connect()
  const db = drizzle(client, {schema})

  const tablesSchema = db._.schema
  if (!tablesSchema) throw new Error('Schema not loaded')

  const tables = Object.values(tablesSchema).map(table => table.dbName)
  for (const table of tables)
    await db.execute(sql.raw(`DROP TABLE ${table} CASCADE;`))

  await client.end()
}

dropDatabase()

Now add this script into your package.json file: Edit it to match the path of the file containing the prompts. You can change the script command as well.

"db:drop": "tsx drizzle/drop-prompt.ts"

"db:drop": "tsx drizzle/drop-prompt.ts"

Now you can use pnpm db:drop in your terminal. It will trigger the prompts and after 2 confirmations, it will execute the drop/truncation function.

shikishikichangchangOP•9mo ago

thanks!

Gaming

Programming

how can I truncate all tables in the new drizzle studio? Previously there was a drop down button