If drizzle migration folder get affected or lost?

[siamahnaf]

I am facing one issues from when I start using drizzle from my first project. Actually I am not getting any idea, same issues, same topic same blog on internet world.

Issue Short Summary

When I create schema and table and migrate it, in a position, if I delete the migration folder accidentally re run the following command is not working
npx drizzle-kit generate && npx drizzle-kit migrate
Sometimes, if I do wrong table design, the migration folder get affected that folder not worked even I fix the wrong I do, it need to regenerate and migrate to new database instance to be fix totally.

Issue details explanation

So, I am postgre database.

Case 1:

Very first time, I create a table schema like following-

import { pgTable, uuid, text, timestamp, pgEnum } from "drizzle-orm/pg-core";

export const paymentStatus = pgEnum("status", ["PAID", "UNPAID"]);

export const payment = pgTable("payments", {
    id: uuid("id").notNull().primaryKey().defaultRandom().unique(),
    status: paymentStatus("status").default("UNPAID"),
})

and I migrate it successfully by running following two command-

npx drizzle-kit generate
npx  drizzle-kit migrate

And then I create another table schemas, on this new schema, I put pgEnum with same name-

export const trailStatus = pgEnum("status", ["TRAILED", "SUBSCRIBED"]);

and then when I run same command for generating and migrating, it generated perfectly, in migrating stage, it just throw an error like enum already exist.

That's perfect, because I do wrong in my schema. When I realize that did wrong, I just fix the code by renaming the pg enum.

What I face after it-

1. After renaming the enum, I just run same command
2. I am getting some unknown error.
3. After sometime later, I just delete the migration folder and create it again, this time migration not worked because the table already exist on that database instance.
4. Then I drop the database instance and create new one, and run same command and this time it successfully migrate.

Case 2:

I create a table like following code-

export const users = pgTable("user", {
    id: uuid("id").primaryKey().defaultRandom().unique(),
    subscribed: text("subscribed").notNull()
})

And then I successfully migrate it running following two command-

npx drizzle-kit generate
npx  drizzle-kit migrate

It migrated successfully. But After migrating I realize, I need to use timestamp instead text

export const users = pgTable("user", {
    id: uuid("id").primaryKey().defaultRandom().unique(),
    subscribed: timestamp("subscribed", { withTimezone: true }).notNull()
})

After editing my code, when I run same command, the drizzle folder get affected and just throwing error. By seeing that, I just make my code as previous (I mean change timestamp to text again), but every time I am getting some unknown error(Error is not formatted).

Most of time I see, If I get one error by chance, it just keeping throwing error, even I fix the schema or table design code. Only solve is, deleting drizzle folder and create a new database instance solve the problem.

So, I can understand, something was wrong on that migration folder history. That's why I need to create refresh migration folder and migrate it to new database instance (Previous database instance not working because, that instance already have some previously migrated table as new migration folder has no history now)

But I don't want to reset database instance or create new database instance because its not proper way even on production or dev database ( Why I recreate the database instance, dev database also contain some data for testing).

Additionally:

After reading drizzle docs, I get a command for pulling the migration states from database as drizzle keep it save in database-

npx drizzle-kit pull

It create a migration folder successfully from database state. But then when I want to migrate this folder to see everything is under sync, it just throwing error like-

applying migrations...error: relation "payments_id_unique" already exists

So pulling and migrating also not working. After researching google and with AI, I get nothing about this use case, even I ask the same question on drizzle discussions, issues, no one pick it.

So, following is my questions if anyone take it please for me-

1. What if I accidentally delete (or lost or it get affected with some error) of migration folder?

Details: I found a solutions that I need to totally reset database instance or create new database instance and run generate and migrate command again to be working. But it would not be perfect for me, because, my database is containing some data, exporting and importing to new database is hardworking.

3. How can I get a totally syncing migration folder if I lost it or it broke or it affected by any chance or I delete it manually without reseting database instance or creating new database instance? I mean If I delete the drizzle migration folder, how can I get a new migration folder which one should be syncing perfectly.

Note: I didn't lost my schema folder. I lost only drizzle migration folder.

Following is drizzle.config.ts-

import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env" });

export default defineConfig({
    schema: "./src/schema.ts",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL!,
    },
});

[siamahnaf]

@AlexBlokh, Please resolve this issues, I am looking a solve for the past 6 months.