Delete/drop table?

[paul-uz]

How do I create a schema/migration to delete a table?

[Angelelz]

If you have a schema file in sync with your db, you can just update your schema to whatever you need and then create the migration file using drizzle-kit by running in you terminal:

$ drizzle-kit generate:{you db type}

That will create a new .sql file with your migration. Of course all of this is available in the docs

[JuanFe12]

You only have to delete the table created in the schema.ts and run the migration again

[callmetwan]

Just to make it really clear for other readers, remove all references to the table from your schema declarations and then generate a migration. drizzle-kit will generate a new migration that drops the table.

Example:

Let's say you have a table for users declare in schema.ts

// schema.ts
export const users = pgTable("users", {
   name: text("name")
});

Just delete all lines (or the file in its entirety if you have schema declared across multiple files) and then generate a new migration:

npx drizzle-kit generate --name drop_users_table

Will result in:

// XXXX_drop_users_table.sql
DROP TABLE "users";

[paul-uz]

Not great if I already have migrations ran on my DB

[callmetwan]

@paul-uz I think you may be confused. Dropping the table is a migration, just like creating the table is a migration. There’s nothing broken or bad about this operation, it’s just like any other change (except the table doesn’t exist anymore).

[stefanosandes]

I'm using postgres and using this utility (just in development) for when I need to reset all the tables without dealing with permissions and other stuffs.

package.json
"db:reset": "bun drizzle/reset.ts",

reset.ts

import { sql } from "drizzle-orm";
import { db } from "./client";

if (!("POSTGRES_URL" in process.env)) throw new Error("POSTGRES_URL not found on .env.development");

async function reset() {
	console.log("⏳ Resetting database...");
	const start = Date.now();

	const query = sql`
		-- Delete all tables
		DO $$ DECLARE
		    r RECORD;
		BEGIN
		    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
		        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
		    END LOOP;
		END $$;
		
		-- Delete enums
		DO $$ DECLARE
			r RECORD;
		BEGIN
			FOR r IN (select t.typname as enum_name
			from pg_type t 
				join pg_enum e on t.oid = e.enumtypid  
				join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
			where n.nspname = current_schema()) LOOP
				EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.enum_name);
			END LOOP;
		END $$;
		
		`;

	await db.execute(query);

	const end = Date.now();
	console.log(`✅ Reset end & took ${end - start}ms`);
	console.log("");
	process.exit(0);
}

reset().catch((err) => {
	console.error("❌ Reset failed");
	console.error(err);
	process.exit(1);
});

[artivilla]

yeah we understand this is risky but when you're constantly testing things in the dev pre product I don't want 20 migration files. would rather just drop the db and start again.

[sistematico]

Thank you very much!!