[FEATURE]: migrations in TypeScript / `drizzle-kit generate --typescript`

[Nester44]

Describe what you want

I'd love to see support for migrations in TypeScript. It would make life so much easier.

After we do a schema migration, we often need to migrate data too. This usually involves:

1. Selecting the data
2. Preparing it
3. Inserting or updating it

Sure, you can do this with plain SQL, but it’s not as simple or nice as writing a script in TypeScript.

Right now, we have to create a TypeScript script and run it manually, which is a pain because:

• Manual Work: Running scripts manually is prone to mistakes and is just extra work. It’s easy to mess up the order or the environment, especially with a team.

• CI/CD Hassles: Our CI/CD setup doesn’t handle these scripts automatically. We have to apply every migration by hand, which breaks the smooth automation we’re aiming for.

• Tracking Issues: Unlike schema migrations, these scripts don’t get tracked automatically, so we lose the history of what’s been applied. This can lead to inconsistent environments.

drizzle-kit already does a great job tracking and running schema migrations. It would be awesome if it could handle data migrations too, making the whole process smoother and less error-prone.

[1mono2]

I totally agree with this. Currently, when it comes to data migration, once you have to go through typescript, it's a lot of work. It would be nice to be able to write a data migration script in typescript, and then run it through drizzle-kit migrate.

[1mono2]

I have put together a script that can refer to drizzle's code and perform the migration in typescript. This is not perfect, but I hope it helps☺️

Reference drizzle code

drizzle-orm/pg-core/dialect.js drizzle-orm/node-postgres/migrator.js drizzle-orm/migrator.js

migration.ts

Place sql and ts files in migrationsFolder. Do not place extra ts files in the migrations.

import "dotenv/config";
import { resolve } from "node:path";
import { db } from "../db";
import { readMigrationFiles } from "drizzle-orm/migrator";
import fs from "node:fs";
import path from "node:path";
import { sql } from "drizzle-orm";
import crypto from "node:crypto";


async function migrateToDB() {
  
  const migrationsFolder = resolve(__dirname, "./migrations");

  // Read all migration files
  const sqlMigrations = readMigrationFiles({ migrationsFolder: migrationsFolder });
  const tsMigrations = await readTsMigrationFiles(migrationsFolder);

  // Sort all migrations by timestamp
  const allMigrations = [...sqlMigrations, ...tsMigrations].sort((a, b) => a.folderMillis - b.folderMillis);

  await db.execute(sql`
    CREATE TABLE IF NOT EXISTS drizzle.__drizzle_migrations (
      id SERIAL PRIMARY KEY,
      hash text NOT NULL,
      created_at bigint
    )
  `);

  const lastMigration = await db.execute(sql`
    SELECT id, hash, created_at FROM drizzle.__drizzle_migrations
    ORDER BY created_at DESC LIMIT 1
  `);

  for (const migration of allMigrations) {
    if (!lastMigration.rows.length || Number(lastMigration.rows[0].created_at) < migration.folderMillis) {
      await db.transaction(async (tx) => {
        if ('sql' in migration) {
          // In case of sql file
          for (const stmt of migration.sql) {
            await tx.execute(sql.raw(stmt));
          }
        } else {
          // In case of TypeScript file. 
          await migration.up(tx);
        }
        // Since it is not a formal file operation, leaving it in the migration history can be a breeding ground for bugs.
        await tx.execute(sql`
          INSERT INTO drizzle.__drizzle_migrations (hash, created_at)
          VALUES (${migration.hash}, ${migration.folderMillis})
        `);
      });
      console.log(`Migration ${migration.hash} applied successfully.`);
    }
  }

  console.log("All migrations completed.");
}


interface TsMigrationFile {
  up: (db: any) => Promise<void>;
  folderMillis: number;
  hash: string;
}

export async function readTsMigrationFiles(migrationsFolder: string): Promise<TsMigrationFile[]> {
  const tsMigrationFiles = fs.readdirSync(migrationsFolder).filter(file => file.endsWith('.ts'));
  const tsMigrations: TsMigrationFile[] = [];

  for (const file of tsMigrationFiles) {
    const filePath = path.join(migrationsFolder, file);
    const { up, folderMillis } = await import(filePath);
    const fileContent = fs.readFileSync(filePath, 'utf-8');
    const hash = crypto.createHash("sha256").update(fileContent).digest("hex");

    tsMigrations.push({
      up,
      folderMillis,
      hash
    });
  }

  return tsMigrations;
}

migrateToDB().catch(console.error);

smaple.ts

Describe the SQL for the data migration you wish to perform. This file should be placed in the same directory as the other sql. It is recommended to use RAW SQL as much as possible to avoid type errors later. Remember to specify unix time.

import { sql } from 'drizzle-orm';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import { v7 as uuidv7 } from 'uuid';

export async function up(db: NodePgDatabase) {
 // Get all clients
 const { rows: clients } = await db.execute(sql`
  SELECT id, email, password, created_at, updated_at
  FROM clients
  WHERE email IS NOT NULL
`);

for (const client of clients) {
  const uuid = uuidv7();
  await db.execute(sql`
    INSERT INTO accounts (id,  client_id email, password, created_at, updated_at)
    VALUES (
      ${uuid},
      ${client.id},
      ${client.email},
      ${client.password},
      ${client.created_at},
      ${client.updated_at}
    )
  `);
}
}

export const folderMillis = 1729073472000; // Specifies unix time. This is to determine the order of migration.

[scamden]

This script is working a treat for me, thanks @1mono2!

One question / thought I have:

Is it actually safe to pass and use the Drizzle client in these migrations?

Here's the concern: imagine we write a migration that uses a Drizzle ORM call like tx.query.someTable.findFirst(). At the time the migration is written, it works fine because someTable exists in the schema.

However, if the project later evolves — say we rename or drop that table — then TypeScript will start erroring when it tries to typecheck the old migration, because someTable no longer exists in the Drizzle schema.

In a real example:

// drizzle schema before migration1.sql
import { pgTable, serial, text } from 'drizzle-orm/pg-core';

export const someTable = pgTable('someTable', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

-- migration1.sql
CREATE TABLE someTable (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

// drizzle migration2.ts
import { db } from './db';

async function doSomething(tx: typeof db) {
  const result = await tx.query.someTable.findFirst();
  console.log(result);
}

// drizzle schema before migration3.sql
import { pgTable, serial, text } from 'drizzle-orm/pg-core';

export const someTableRenamed = pgTable('someTableRenamed', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

-- migration3.sql
ALTER TABLE someTable RENAME TO someTableRenamed;

After migration3, TypeScript now complains that tx.query.someTable no longer exists in migration2.ts.

If we update it to use someTableRenamed, the migration would break on a fresh DB (because someTableRenamed doesn't exist yet after migration1).
If we don't update it, the project won't even typecheck — and the migration would legitimately fail at runtime, because tx.query.someTable wouldn't exist on the Drizzle client anymore.

So it seems like there's a tension between:

• Typechecking the current schema
• Versus the historical schema at the time the migration was authored

Possible solutions we thought of:

• Only ever use sql strings (db.execute(sql)) inside migrations to avoid coupling to Drizzle ORM types.
• If a migration type error shows up later, manually convert it to use sql instead.
• (Crazy idea) Freeze a copy of the Drizzle schema at the time the migration is written and make the migration use that frozen schema for typechecking.

Curious what you think about the "official" or most practical approach! Thanks again 🙏

[AndriiSherman]

Hey everyone!

I've created this message to send in a batch to all opened issues we have, just because there are a lot of them and I want to update all of you with our current work, why issues are not responded to, and the amount of work that has been done by our team over ~8 months.

I saw a lot of issues with suggestions on how to fix something while we were not responding – so thanks everyone. Also, thanks to everyone patiently waiting for a response from us and continuing to use Drizzle!

We currently have 4 major branches with a lot of work done. Each branch was handled by different devs and teams to make sure we could make all the changes in parallel.

---

First branch is drizzle-kit rewrite

All of the work can be found on the alternation-engine branch. Here is a PR with the work done: https://github.com/drizzle-team/drizzle-orm/pull/4439

As you can see, it has 167k added lines of code and 67k removed, which means we've completely rewritten the drizzle-kit alternation engine, the way we handle diffs for each dialect, together with expanding our test suite from 600 tests to ~9k test units for all different types of actions you can do with kit. More importantly, we changed the migration folder structure and made commutative migrations, so you won't face complex conflicts on migrations when working in a team.

What's left here:

• We are finishing handling defaults for Postgres, the last being geometry (yes, we fixed the srid issue here as well).
• We are finishing commutative migrations for all dialects.
• We are finishing up the command, so the migration flow will be as simple as drizzle-kit up for you.

Where it brings us:

• We are getting drizzle-kit into a new good shape where we can call it [email protected]!

Timeline:

• We need ~2 weeks to finish all of the above and send this branch to beta for testing.

Second big branch is a complex one with several HUGE updates

• Bringing Relational Queries v2 finally live. We've done a lot of work here to actually make it faster than RQBv1 and much better from a DX point of view. But in implementing it, we had to make another big rewrite, so we completely rewrote the drizzle-orm type system, which made it much simpler and improved type performance by ~21.4x:

(types instantiations for 3300 lines production drizzle schema + 990 lines relations)

TS v5.8.3: 728.8k -> 34.1k
TS v5.9.2: 553.7k -> 25.4k

You can read more about it here.

What's left here:

• We have 1 issue with TS that is already in progress of being fixed. The issue and Post about fixing.

Where it brings us:

• We are getting drizzle-orm into a new good shape where we can call it [email protected]!

Breaking changes:

• We will have them, but we will have open channels for everyone building on top of drizzle types, so we can guide you through all the changes.

Third branch is adding support for CockroachDB and MSSQL dialects

Support for them is already in the alternation-engine branch and will be available together with the drizzle-kit rewrite.

Summary

All of the work we are doing is crucial and should be done sooner rather than later. We've received a lot of feedback and worked really hard to find the best strategies and decisions for API, DX, architecture, etc., so we can confidently mark it as v1 and be sure we can improve it and remain flexible for all the features you are asking for, while becoming even better for everyone building on top of the drizzle API as well.

We didn't want to stay with some legacy decisions and solutions we had, and instead wanted to shape Drizzle in a way that will be best looking ahead to 2025–2026 trends (v1 will get proper effect support, etc.).

We believe that all of the effort we've put in will boost Drizzle and benefit everyone using it.

Thanks everyone, as we said, we are here to stay for a long time to build a great tool together!

Timelines

We are hoping to get v1 for drizzle in beta this fall and same timeline for latest. Right after that we can go through all of the issues and PRs and resond everyone. v1 for drizzle should close ~70% of all the bug tickets we have, so on beta release we will start marking them as closed!