How drizzle migration actually work?

[siamahnaf]

Hello team, hope everyone doing well. If anyone can help me with some questions-

So, I am trying to start new projects with drizzle and neon-postgre. 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 already exist.

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

Steps:

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.

So, then I can understand, something was wrong on that previous migration folder history. That's why I need to create refresh migration folder and migrate it to new database instance is working (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. 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 use case.

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

1. What if I accidentally delete (or lost) 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 production database, exporting and importing to new database is hardworking.

2. How can I get a totally syncing migration folder if I lost it or it broke by any chance without reseting database instance or creating new database instance?

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!,
    },
});

I need some idea!