Clear the whole database?

For my test environment I'd like to clear the whole db.. is there a way to achieve this using drizzle ?

33 Replies

Mendy•17mo ago

You can’t do it with drizzle. You can delete the tables content with a script, but you can’t delete the indexes and alike, you have to run SQL for that, as far as I know.

LouistitiOP•17mo ago

Thanks for your answer ! do you know if there is a way to retrieve the all the table names programmatically ? so I can automate my script and don't have to add a table name each time I create one when prototyping... I mean something like this :

const clearDatabase = () => {

  const tables = db. ? //.SCRIPT TO GET ALL EXISTING DRIZZLE TABLES

  for (table of tables) {
    // My SQL script to truncate current table
  }
}

const clearDatabase = () => {

  const tables = db. ? //.SCRIPT TO GET ALL EXISTING DRIZZLE TABLES

  for (table of tables) {
    // My SQL script to truncate current table
  }
}

Mendy•17mo ago

I’d presume, internally there has to be, don’t know how. Following this thread to see if any information comes up. Maybe we can loop over some values in the db object, it should all the table in it. db.query[tableName]. But I don’t know if it’s limited to .query I’ll check when I get home

LouistitiOP•17mo ago

Thanks man I have it !

public async clearDb(): Promise<void> {
    const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `;

    const tables = await this.db.execute(query); // retrieve tables

    for (let table of tables) {
      const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
      await this.db.execute(query); // Truncate (clear all the data) the table
    }
  }

public async clearDb(): Promise<void> {
    const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `;

    const tables = await this.db.execute(query); // retrieve tables

    for (let table of tables) {
      const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
      await this.db.execute(query); // Truncate (clear all the data) the table
    }
  }

Mendy•17mo ago

Got home, did it myself, and only now seeing this 😅... Here's the code if you're intrested:

import type { PlanetScaleDatabase } from "drizzle-orm/planetscale-serverless";
import type * as schema from "../drizzle/schema";

async function emptyDBTables(db: PlanetScaleDatabase<typeof schema>) {
  console.log("🗑️ Emptying the entire database");

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

  const queries = Object.values(tablesSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`);
    return sql.raw(`DELETE FROM ${table.dbName};`);
  });

  console.log("🛜 Sending delete queries");

  await db.transaction(async (trx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await trx.execute(query);
      }),
    );
  });

  console.log("✅ Database emptied");
}

import type { PlanetScaleDatabase } from "drizzle-orm/planetscale-serverless";
import type * as schema from "../drizzle/schema";

async function emptyDBTables(db: PlanetScaleDatabase<typeof schema>) {
  console.log("🗑️ Emptying the entire database");

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

  const queries = Object.values(tablesSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`);
    return sql.raw(`DELETE FROM ${table.dbName};`);
  });

  console.log("🛜 Sending delete queries");

  await db.transaction(async (trx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await trx.execute(query);
      }),
    );
  });

  console.log("✅ Database emptied");
}

Since using PlanetScale, cascading doesn't work, but you can change the query to whatever you need.

LouistitiOP•17mo ago

Hahaha thanks buddy ! sorry for the time you took but I appreciate it ! Thanks a lot ! Why are you using DELETE FROM and not TRUNCATE ? This may help planetScale users wondering how to do it 😉

Mendy•17mo ago

It should work for all dbs, just change the db type to the right one You’re right, I tried first to use the orm delete method so I stuck with it.

LouistitiOP•17mo ago

alright ^^ thanks a lot !

Mendy•17mo ago

It’s ok, I needed something like this for myself

Thimo_o•16mo ago

Hello Mendy, I saw your reset script and was trying to make one myself. I came up with a slight variation but I think I made a mistake somewhere and was wondering how you did it I used the db variable from where the connection is created: but I see db._.schema as undefined at my side. Is there a certain order or something I have to do to know the variable is not undefined?

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

import { sql } from "drizzle-orm"
import { db } from "~/db/db"

async function reset() {
  console.log(db)
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️ Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`DELETE FROM ${table.dbName};`)
  })

  console.log("🛜 Sending delete queries")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {
  console.error(e)
})

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

import { sql } from "drizzle-orm"
import { db } from "~/db/db"

async function reset() {
  console.log(db)
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️ Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`DELETE FROM ${table.dbName};`)
  })

  console.log("🛜 Sending delete queries")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {
  console.error(e)
})

francis•16mo ago

I have a stupid question: rather than resetting your database in place, why not actually drop and recreate the underlying database itself? it should be easy using e.g. docker that way you'll never have an issue where you've added a construct (function, trigger, new role, etc) that isn't cleared properly by your clear script

Thimo_o•16mo ago

That would achieve the same goal yes, but since I use planetscale, I can't use docker. Do you know the best way to do this for planetscale?

francis•16mo ago

no idea - I have never had tests run against an actual hosted database, I've always set them up to run locally having tests against an actual hosted database seems like a synchronization problem too imagine you have the test run in a CI action, and you push two branches at once, whoops! now one test is clearing data while the other is still running

Thimo_o•16mo ago

I would rather prefer locally too, but the way planetscale uses mysql through vitess, it is a lot easier to use a branch on there than to try and set that up locally for now. I haven't seen anyone do a complete copy of a planetscale db locally yet

francis•16mo ago

if planetscale is "mysql compatible" could you not just run mysql in a container? actually nvm, @thimo_o if you use the planetscale api to open a new "branch" for each test execution, it'll probably work?

Thimo_o•16mo ago

Opening a new branch would be quite overkill I think, since I then need to also push the schema again and wait for planetscale to configure it. I wonder how Prisma does it (although this was mostly for a local sqlite thing too ) https://github.com/prisma/prisma/issues/11261 I think I saw a tweet from drizzle before, where they said it was quite easy to use multiple providers at once, like an sqlite and a planetscale one, that might be a sweet spot. Do you know how that might be done perhaps?

francis•16mo ago

I def would not do that - you'll miss cases where underlying database behavior causes a bug in your application

Thimo_o•16mo ago

yeah, thats also true, I hope I can get my clear script working then

francis•16mo ago

can you not just create a planetscale branch from an empty database>? (I don't know how it works) or actually @thimo_o probably the best solution is to have a separate planetscale database instance just for testing where the mainline branch is never modified on a test run, create a new branch, do your stuff, drop the branch that way you never run the risk of your automated database branching stuff breaking your main database

Thimo_o•16mo ago

I now have a separate account just for testing, so that works quite good

LouistitiOP•16mo ago

Hi ! I had the same issue... db._.schema was undefined... So I am using this script to clear my db :

import { sql } from 'drizzle-orm';

const clearDb = async (): Promise<void> => {
    const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `;

    const tables = await db.execute(query);

    for (let table of tables) {
      const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
      await db.execute(query);
    }
  }

import { sql } from 'drizzle-orm';

const clearDb = async (): Promise<void> => {
    const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `;

    const tables = await db.execute(query);

    for (let table of tables) {
      const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
      await db.execute(query);
    }
  }

rphlmr ⚡•16mo ago

That's very smart! I've stolen your code to put it into my gist :p (with credits of course) https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15

LouistitiOP•16mo ago

Haha no problem

Mendy•16mo ago

You didn’t tag me so I’m only seeing it now. @louistiti_ solution works great. Lmk if you still want help with my specific implementation

Thimo_o•16mo ago

@mendy.l @louistiti I tried the raw example, but I'm doing/forgetting something I think In the for loop of the raw example I get tables: ExecutedQuery, which I can not iterate over. Do I have to change something here so the typing is correct? Mendy, I had db..schema undefined but I still don't know why. Do you know why that might happen? For my db variable I have this: That should be correct right?

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

LouistitiOP•16mo ago

This would work I think :

tables.forEach(async (table) => {
  const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
  await db.execute(query);
})

tables.forEach(async (table) => {
  const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
  await db.execute(query);
})

@thimo_o (If you are using my implementation)

Thimo_o•16mo ago

@louistiti_ I do get Property 'forEach' does not exist on type 'ExecutedQuery'.. Does your tables variable have another type?

LouistitiOP•16mo ago

please send your whole function here did you await the db.execute ? this is mandatory

Thimo_o•15mo ago

@louistiti_ As far as I know I do the same

My reset db file: 

const clearDb = async (): Promise<void> => {
  const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `

  const tables = await db.execute(query)

  tables.forEach(async (table) => {
    const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`)
    await db.execute(query)
  })
}

clearDb().catch((e) => {
  console.error(e)
})

my db.ts

import { connect } from "@planetscale/database"
import { env } from "~/env.mjs"
import { drizzle } from "drizzle-orm/planetscale-serverless"

// create the connection
export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

My schema


export const user = mysqlTable("auth_user", {
  id: varchar("id", {
    length: 15, // change this when using custom user ids
  }).primaryKey(),
  username: varchar("username", {
    length: 255,
  }),
  githubUsername: varchar("github_username", {
    length: 255,
  }),
  // other user attributes
})

export const key = mysqlTable("user_key", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  hashedPassword: varchar("hashed_password", {
    length: 255,
  }),
})

export const session = mysqlTable("user_session", {
  id: varchar("id", {
    length: 128,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  activeExpires: bigint("active_expires", {
    mode: "number",
  }).notNull(),
  idleExpires: bigint("idle_expires", {
    mode: "number",
  }).notNull(),
})

export const seedTable = mysqlTable("seed", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  value: varchar("value", {
    length: 255,
  })
})

My reset db file: 

const clearDb = async (): Promise<void> => {
  const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `

  const tables = await db.execute(query)

  tables.forEach(async (table) => {
    const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`)
    await db.execute(query)
  })
}

clearDb().catch((e) => {
  console.error(e)
})

my db.ts

import { connect } from "@planetscale/database"
import { env } from "~/env.mjs"
import { drizzle } from "drizzle-orm/planetscale-serverless"

// create the connection
export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

My schema


export const user = mysqlTable("auth_user", {
  id: varchar("id", {
    length: 15, // change this when using custom user ids
  }).primaryKey(),
  username: varchar("username", {
    length: 255,
  }),
  githubUsername: varchar("github_username", {
    length: 255,
  }),
  // other user attributes
})

export const key = mysqlTable("user_key", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  hashedPassword: varchar("hashed_password", {
    length: 255,
  }),
})

export const session = mysqlTable("user_session", {
  id: varchar("id", {
    length: 128,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  activeExpires: bigint("active_expires", {
    mode: "number",
  }).notNull(),
  idleExpires: bigint("idle_expires", {
    mode: "number",
  }).notNull(),
})

export const seedTable = mysqlTable("seed", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  value: varchar("value", {
    length: 255,
  })
})

This is what made the planetscale version work for me, I had to add the schema in my drizzle function and use truncate table

// db.ts
import * as schema from "./schema"
export const db = drizzle(connection, { schema })


//reset.ts
async function reset() {
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️  Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`TRUNCATE TABLE ${table.dbName};`)
  })

  console.log("📨 Sending delete queries...")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {

  console.error(e)
})

// db.ts
import * as schema from "./schema"
export const db = drizzle(connection, { schema })


//reset.ts
async function reset() {
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️  Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`TRUNCATE TABLE ${table.dbName};`)
  })

  console.log("📨 Sending delete queries...")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {

  console.error(e)
})

IstoraMandiri•15mo ago

package.json script

    "db:nuke": "export $(cat .env.local | grep -v '^#' | xargs) && psql \"${POSTGRES_URL}\" -c 'DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;'",

    "db:nuke": "export $(cat .env.local | grep -v '^#' | xargs) && psql \"${POSTGRES_URL}\" -c 'DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;'",

LouistitiOP•15mo ago

ohhhhhhh yeah ! I remember now struggling with that ! I had the same issue as you and this fixed everything... sorry I forgot 🫠

OSCAR•10mo ago

I was not able to use the execute function, so I did the following

async function clearDataBase(db:LibSQLDatabase<typeof schema>) {

  const tableSchema = db._.schema
  if (!tableSchema) throw new Error("No table schema found")

  console.log("🗑️  Emptying the entire database")

  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return table.tsName
  })
  console.log(queries);
  queries.forEach(async (query) => {
    const schemaToDelete = schema[query];
    if (!schemaToDelete) throw new Error(`No schema found for ${query}`);
    await db.delete(schemaToDelete);
  });
}

async function clearDataBase(db:LibSQLDatabase<typeof schema>) {

  const tableSchema = db._.schema
  if (!tableSchema) throw new Error("No table schema found")

  console.log("🗑️  Emptying the entire database")

  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return table.tsName
  })
  console.log(queries);
  queries.forEach(async (query) => {
    const schemaToDelete = schema[query];
    if (!schemaToDelete) throw new Error(`No schema found for ${query}`);
    await db.delete(schemaToDelete);
  });
}

The variable schema is defined as

import * as schema from "./turso/schemas/index";

import * as schema from "./turso/schemas/index";

Where I export all the schemas used

Hebilicious•10mo ago

FYI I'm using a more nuclear option which works great :

const main = async () => {
    if (process.env.NODE_ENV === 'production') return;
    if (pgSchema === 'staging' || pgSchema === 'production') return;
    await client.connect();
    // Drop the database if it exists
    await client.query(/*SQL*/ `DROP DATABASE IF EXISTS ${dbName}`);
    // Create the database
    await client.query(/*SQL*/ `CREATE DATABASE ${dbName}`);
    console.log('Succesfully cleared the database.');
    await client.end();
};

const main = async () => {
    if (process.env.NODE_ENV === 'production') return;
    if (pgSchema === 'staging' || pgSchema === 'production') return;
    await client.connect();
    // Drop the database if it exists
    await client.query(/*SQL*/ `DROP DATABASE IF EXISTS ${dbName}`);
    // Create the database
    await client.query(/*SQL*/ `CREATE DATABASE ${dbName}`);
    console.log('Succesfully cleared the database.');
    await client.end();
};

Gaming

Programming

Clear the whole database?