migrations for multiple environment

[arashi-dev]

I want to use the same schema for different environments (production, development, preview, etc.). I did it like this:

// _table.ts
export const pgTable = pgTableCreator((name) => `myapp_${process.env.NODE_ENV}_${name}`)

// drizzle.config.ts
export default {
  schema: "./schema",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL,
  },
  tablesFilter: [`myapp_${process.env.NODE_ENV}_*`],
  out: "./migrations",
} satisfies Config;

it works perfectly. but the question is what should I do with the migrations?
my first approach was to make the migrations only for the production and use push for other environments. Is that gonna make any problem? or I should export migrations for each environment?

PS: I know this is not a drizzle-specific question but I am not a backend expert so I don't know the best practices.
PS 2: if you have any other suggestions, I will be really thankful

[Angelelz]

You definitely need to configure and apply migrations in all environments. Otherwise you'll have differences in your environments and won't be able to track bugs and have issues everywhere. All your environments should be in sync.

I believe you can just run the generate command once per migration, and then apply it to each environment. To be honest I think this is a great idea.

[arashi-dev]

thanks for the response. for now, I decided to generate the migrations for only production. because the project is small and don't want to make it difficult to manage. I will do what you have suggested in the future if I encounter a problem 😅

[mullwaden]

You can create your own migration approach I guess. Have not tested the following extensively but it basically does the same thing.

• migrations becomes a "proper" table so I can see what is going on & they are tied to the table_filter namespace
• it would be possible to extend migrations to also execute scripts before or after running the sql statements

// in schema.tsx
export const migrations = pgTable("migrations", {
  id: serial("id").primaryKey(),
  hash: text("hash").notNull(),
  created_at: bigint("created_at", { mode: "bigint" }),
});

import path from "node:path";

import { sql } from "drizzle-orm";
import { readMigrationFiles } from "drizzle-orm/migrator";

import { db } from "~/backend/db";
import { migrations } from "~/backend/db/schema";

export async function execMigrations(): Promise<void> {
  // get absolute path to my migrations folder
  const migrationsFolder = path.join(__dirname, "../migrations");

  // see https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/migrator.ts
  const migrationData = readMigrationFiles({ migrationsFolder });

  const migrationTableCreate = sql/* sql */ `
    CREATE TABLE IF NOT EXISTS ${migrations} (
      id SERIAL PRIMARY KEY,
      hash text NOT NULL,
      created_at bigint
    )
  `;

  await db.execute(migrationTableCreate);

  console.log("Created migrations table");

  const lastDbMigration = await db.query.migrations.findFirst({
    orderBy: (migrations, { desc }) => [desc(migrations.created_at)],
  });

  await db.transaction(async (tx) => {
    for await (const migration of migrationData) {
      if (
        !lastDbMigration ||
        Number(lastDbMigration.created_at) < migration.folderMillis
      ) {
        for (const stmt of migration.sql) {
          await tx.execute(sql.raw(stmt));
        }

        await tx
          .insert(migrations)
          .values({
            hash: migration.hash,
            created_at: BigInt(migration.folderMillis),
          });
      }
    }
  });
}