DT
Drizzle TeamLouistiti

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 ?
M
Mendy•286d 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.
L
Louistiti•286d 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
  }
}
M
Mendy•286d 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
L
Louistiti•286d 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
    }
  }
M
Mendy•286d 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.
L
Louistiti•286d 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 😉
M
Mendy•286d 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.
L
Louistiti•286d ago
alright ^^ thanks a lot !
M
Mendy•286d ago
It’s ok, I needed something like this for myself
T
Thimo_o•253d 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)
})
F
francis•253d 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
T
Thimo_o•253d 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?
F
francis•253d 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
T
Thimo_o•253d 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
F
francis•253d 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?
T
Thimo_o•253d 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?
F
francis•253d ago
I def would not do that - you'll miss cases where underlying database behavior causes a bug in your application
T
Thimo_o•253d ago
yeah, thats also true, I hope I can get my clear script working then
F
francis•253d 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
T
Thimo_o•253d ago
I now have a separate account just for testing, so that works quite good
L
Louistiti•252d 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);
    }
  }
R
rphlmr ⚡•252d 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
L
Louistiti•252d ago
Haha no problem
M
Mendy•251d 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
T
Thimo_o•250d 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)
L
Louistiti•250d 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)
T
Thimo_o•250d ago
@louistiti_ I do get Property 'forEach' does not exist on type 'ExecutedQuery'.. Does your tables variable have another type?
L
Louistiti•250d ago
please send your whole function here did you await the db.execute ? this is mandatory
T
Thimo_o•247d 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)
})
I
IstoraMandiri•247d 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;'",
L
Louistiti•246d ago
ohhhhhhh yeah ! I remember now struggling with that ! I had the same issue as you and this fixed everything... sorry I forgot 🫠
O
OSCAR•68d 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
H
Hebilicious•68d 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();
};
Want results from more Discord servers?
Add your server
More Posts
generate:pg "snapshot.json data is malformed" error after merge conflictsHi there, my team and I use the following method to avoid merge issues: If master has migrations thImporting drizzle-zod Schemas on the ClientHi, Is it ok to import schemas generated with `drizzle-zod` via `createInsertSchema` on the client?Updating jsonb objects with Drizzle?Hey there, i'm currently working on migrating my database and code from MongoDB with Mongoose to Posauto updated_atis there a way to automatically update the updatedAt column of a postgres table ?Just bringing attention to this potential issue: use of wrong schema name in drizzle kit outputhttps://github.com/drizzle-team/drizzle-orm/issues/890 Just wondering if this is intended behavior oFulltext index MySQLHow can I define a fulltext index with MySQL? there is no fulltextIndex available in mysql-core.Anyone who can give a example of thissupport fsp for .onUpdateNow(),title explains itYou're about to add not-null version without default valueI added this column ``` version: int("version").notNull().default(0), ``` and it's trying to truncCannot read properties of undefined (reading 'columns')``` /home/thdxr/dev/projects/bumi/bumi/node_modules/.pnpm/drizzle-kit@0.19.6/node_modules/drizzle-kineon coldstarts in vercel edgehey!! if my neondb is in `idle` state, and i try to query something -- it will error out. `- error How to create GIN/GIST index on text[] column?According to some issues this is not implemented or buggy - https://github.com/drizzle-team/drizzle-Module '"drizzle-orm/mysql-core"' has no exported member 'unique'.ts(2305)Getting this error when trying to import `unique` ```typescript import { text, unique, // erroriIs there a way to set the index operator type for postgres?I'm migrating our project to drizzle, and I want to add the indexes to the table. Previously we wereHow to update multiple rows with one query?Take this array as an example: ```ts const data = [ {id: 1, col1: "col1 data where id = 1"} {id:Use Drizzle in NestJS appHi everyone ! I am new to both drizzle and NestJS and was wondering if there was any recipe out ther[Solved] Transforming const to the configured target environment ("es5") is not supported yetSo I had a old project, and I copied the `DATABASE_URL` and on created a basic drizzle project and `Placeholders in inserting: db.insert().values(placeholder('example'))Hey, how do I add placeholders inside the values of an insert prepare statement? I get an typeerror Can I add a Unique constraint to a column?I couldn't find an option for that in the docs. I'm assuming Drizzle doesn't support that. So, coul"Cannot parse date or time" using AWS Data API, aurora postgresI'm trying drizzle-orm (with sst) and aurora pg thru aws data api, when trying to insert a Date valu