Migrate after push in local dev database

[HalfdanJ]

I'm a little bit in doubt what the intended flow of development is.

I have a local dev database, as i'm working on a new feature i'll modify my schema. I can then push the schema to my database using push, and test my queries.
Once i'm certain about my changes, i then want to generate the migration file, but now I can't apply that migration to my local instance because the changes have already been applied, so the migration might fail.

So how do i get out of this situation? What I want to do is mark that the migration has already happened so its ignored, but so future migration files do get applied.

My only solution is to either hack the migration file so its empty when I apply, or nuke my db.

[Angelelz]

I don't think your question have a right answer. There might be recommended ways, but I don't think it's correct or anything.
I would suggest not to push. I would suggest to run migrations locally, while in development, maybe more than one, maybe a lot, and when you feel your schema is correct, now you can run those migrations in your real db.
Another option when you feel like your schema is correct, you could drop all the migrations you ran while in development, and then generate a new one for the real db. After all, your schema should be correct.

I don't really like push, I feel like I'm not in control, it just runs. With migrations, I can see the migration file and make sure is correct.
Push is good when you have db branches like in planetscale or supabase.

[HalfdanJ]

Thank you, helpful perspective. That was also the workflow i had started to naturally gravitate towards, but got in doubt when reading the docs.
I do still in that case find my self on the situation sometimes that I want to merge (so drop and recreate migrations) before creating a PR for the sake of readability, and there I can again be in the situation that I can't apply the migrations because it's already applied and all I want is to mark it as manually migrated. Would it make sense to add a cli feature for that?

[dnishiyama]

It is surprising that there isn't a way to mark migrations as already complete. This feature exists in mikro-orm. Looking at the migrations table, this is the structure:

+----+------------------------------------------------------------------+---------------+
| id | hash                                                             | created_at    |
|----+------------------------------------------------------------------+---------------|
| 1  | 6d4bbd440b5db8256d3dea52d461bb6ceb444b36e0da27d4e83bbeec3cc23fc5 | 1709291139875 |
| 2  | b5277cde07fe08b3a7bd4f0f267baf8e72fba6795d5cb8866a25186757f970ac | 1709291831651 |
| 3  | 8f097e12de277ffcc92c35c893b164fe82919936ea46ad19a3eaf3d151612952 | 1709803150615 |
| 4  | dcdd07e15248c120447ab3139e9a28f4333212a7c53dc72c1e231489b4dd7de9 | 1710997870623 |
| 5  | e7eae31df4138f3d54fc42cdd7cec5b3fc9de8a8ee846a0eddab0fae05ecd2de | 1711099298274 |
| 6  | 9e39f27b634166b7f1ffa8b89b9b13fdb633106594dd353f7b470d0b452dd1d4 | 1711099332299 |
| 7  | 845765f8c175042d8ceecfd7467b159fe9cfe0fa65e76686f7b2cf39d7b40dd9 | 1712038575543 |
| 8  | 6e1f61bd93b92cba26ac817066a79dda512a6371d4e57acb0c3abdc5bca77afd | 1712209567058 |
| 9  | 768ef1086a0bf6b7b6bd7780fc4a411d17a7052768a385e525acfdc95e864de9 | 1712224808495 |
| 10 | de42fb58fe4f98b7a53889629b02d0bdef56b91a70069093bc0e2a145038b0c4 | 1712310088444 |
| 11 | da6509b8f0682ed5fe8d3e4bc2a799c1f66bf1351d8fe83b72e96adcc017070b | 1714276518497 |
| 12 | db22072d074bb2870bd8ef2f502fb89efcac306eddec944bdfdfac8da629402f | 1714117905520 |
| 13 | 5459137f95a97ccd86af959dbdd2e2f37b13f03136562d8b62dae9f55af2e13e | 1714454518503 |
| 14 | 392ead996da4f573b90679a699ebee14ee2a4689ce9bcd9fb1087bd484bc012c | 1714659270549 |
| 15 | 00e5588519eeb14cc31aa77211c24b706c9c9fc455e7f91bdd8af7710b406ba8 | 1713977113151 |
| 16 | 7e63c83051ba104e6ab191fb374243b6ac1fd0968d588ea9ed004b08f7fa5923 | 1714789231282 |
| 17 | 7f3418d152c4afe27663d96197e15fb8cb60c93baa8a9cb61996da5a0e9a897f | 1714940256328 |
| 18 | 4d6038a68629ddaf9684ec0cf113687dbe5019083b67817ecc2868ae03788a20 | 1714940634386 |
| 19 | ab7155ad7684e1b3b113e4f5d4e6a991c174b400ace59173cf24fa58768d87cb | 1715049017448 |
| 20 | ae57d8c584cf9217e74c8a164ca09d51dc953fab7d0ad9174c7e98266ea496ce | 1715609652027 |
+----+------------------------------------------------------------------+---------------+

I might try to manually create a new row and see how it handles

[ntaraujo]

For anyone needing a workaround to mark your migrations as completed, since they were by drizzle-kit push:
(in this case I'm using postgres)

// fix_drizzle_push.ts
import original_config from "./drizzle.config";
import { MigrationConfig, readMigrationFiles } from "drizzle-orm/migrator";
import postgres from "postgres";

const config = {
  ...original_config,
  migrationsFolder: original_config.out,
  migrationsTable: original_config.migrations?.table ?? "__drizzle_migrations",
  migrationsSchema: original_config.migrations?.schema ?? "drizzle",
} as MigrationConfig;

const migrations = readMigrationFiles(config);

const sql = postgres(process.env.POSTGRES_URL!);

const table_name = `${config.migrationsSchema}.${config.migrationsTable}`;

const get_db_migrations = sql`SELECT id, hash, created_at FROM ${sql(
  table_name
)}`;

async function main() {
  const db_migrations_hashs = (await get_db_migrations.execute()).map((m) => {
    return m.hash as string;
  });

  for (const migration of migrations) {
    if (!db_migrations_hashs.includes(migration.hash)) {
      console.log(
        `######## Adding migration to ${table_name}:\n\n${migration.sql}\n\n`
      );
      const new_db_migration = {
        hash: migration.hash,
        created_at: migration.folderMillis,
      };
      await sql`INSERT INTO ${sql(table_name)} ${sql(
        new_db_migration,
        "hash",
        "created_at"
      )}`.execute();
    }
  }
}

main().finally(() => process.exit(0));

[vvvvvvvector]

@ntaraujo

Thank you.