Database Migrations and ORMs: Prisma, Drizzle, Knex, and Beyond

Database Migrations and ORMs: Prisma, Drizzle, Knex, and Beyond

The question of how to talk to your database from application code has more answers than ever. Full ORMs, lightweight query builders, type-safe raw SQL wrappers, standalone migration runners -- each makes different trade-offs between convenience, control, and correctness. Picking the wrong one costs you months of workarounds.

The Landscape

Database tools fall into three categories:

• Full ORMs: Map tables to objects, handle queries and migrations. Prisma, TypeORM, Sequelize.
• Query builders: Programmatic API for constructing SQL, no object mapping. Knex, Drizzle, Kysely.
• Migration-only tools: Manage schema changes with plain SQL files. dbmate, golang-migrate.

The lines blur -- Drizzle handles migrations and relationships, Prisma's query API feels like a query builder. Focus on what the tool does, not what category it claims.

Prisma

Prisma is the most popular TypeScript database tool. You define your schema in Prisma's DSL, and it generates a type-safe client and migration files.

// prisma/schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

bunx prisma migrate dev --name add-posts   # generate and apply
bunx prisma migrate deploy                  # production

Prisma generates inspectable SQL migration files in prisma/migrations/. Querying is type-safe and concise:

const users = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
  include: { posts: { where: { published: true } } },
  orderBy: { createdAt: "desc" },
  take: 10,
});

// Raw SQL escape hatch for complex queries
const result = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) as post_count
  FROM "User" u LEFT JOIN "Post" p ON p."authorId" = u.id
  GROUP BY u.name HAVING COUNT(p.id) > ${minPosts}
`;

bunx prisma studio opens a web GUI at localhost:5555 for browsing data during development.

Strengths: Excellent type safety, declarative schema, auto-generated migrations, large community.

Weaknesses: The Rust engine adds ~15MB to deployments. CTEs, window functions, and complex aggregations require $queryRaw. Connection pooling needs PgBouncer or Prisma Accelerate in serverless environments.

Drizzle

Drizzle takes the opposite approach: define your schema in TypeScript, write queries that look like SQL, skip the runtime engine.

import { pgTable, serial, text, boolean, integer } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name"),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  published: boolean("published").default(false).notNull(),
  authorId: integer("author_id").notNull().references(() => users.id),
});

bunx drizzle-kit generate   # generate SQL migration files
bunx drizzle-kit migrate    # apply migrations
bunx drizzle-kit push       # push schema directly (dev only)

Queries map directly to the SQL you'd write by hand:

import { eq, gt, count } from "drizzle-orm";

const recentUsers = await db.select().from(users)
  .where(gt(users.createdAt, thirtyDaysAgo))
  .orderBy(users.createdAt).limit(10);

const postCounts = await db
  .select({ name: users.name, postCount: count(posts.id) })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.name);

Strengths: No runtime engine, SQL-like queries, schema is plain TypeScript, supports PostgreSQL, MySQL, SQLite.

Weaknesses: Relation queries less intuitive than Prisma's include. Documentation has gaps. Smaller plugin ecosystem.

Knex

Knex is a mature query builder with a solid migration system. It predates the TypeScript ORM wave.

// migrations/20260209_add_users.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("users", (table) => {
    table.increments("id").primary();
    table.string("email").notNull().unique();
    table.string("name");
    table.timestamps(true, true);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTable("users");
}

const users = await knex("users")
  .where("created_at", ">", thirtyDaysAgo)
  .orderBy("created_at", "desc").limit(10);

Strengths: Battle-tested, excellent migration system, works with any database.

Weaknesses: Query results typed as any unless manually annotated. Development has slowed. For new TypeScript projects, Drizzle or Kysely are better choices.

TypeORM

TypeORM uses decorators to define entities. It's popular in the NestJS ecosystem.

@Entity()
class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @OneToMany(() => Post, (post) => post.author)
  posts: Post[];
}

The honest assessment: TypeORM has persistent issues. Migration generation is unreliable -- it sometimes produces incorrect SQL. The synchronize: true option has caused data loss in production for many teams. The query builder and repository APIs have subtle inconsistencies. Over 2,000 open issues on GitHub.

If you're already using TypeORM in NestJS, it works. For new projects, choose Prisma or Drizzle.

Type-Safe Raw SQL

For full SQL control with TypeScript safety, two libraries stand out.

Kysely

Define your database as TypeScript interfaces, get auto-completed, type-checked queries:

interface Database {
  users: { id: Generated<number>; email: string; name: string | null };
  posts: { id: Generated<number>; title: string; author_id: number };
}

const db = new Kysely<Database>({ dialect: new PostgresDialect({ pool }) });

const users = await db
  .selectFrom("users")
  .innerJoin("posts", "posts.author_id", "users.id")
  .select(["users.name", "posts.title"])
  .where("posts.published", "=", true)
  .execute();

No runtime overhead beyond SQL string generation. Pairs well with dbmate for migrations.

Slonik

PostgreSQL-specific client with runtime type validation via Zod:

const UserSchema = z.object({
  id: z.number(), email: z.string(), name: z.string().nullable(),
});

const users = await pool.any(sql.type(UserSchema)`
  SELECT id, email, name FROM users WHERE created_at > ${thirtyDaysAgo}
`);

Slonik catches mismatches between SQL and TypeScript types at runtime -- stricter than Kysely's compile-time-only checks.

Migration-Only Tools

When you want plain SQL migrations without a query abstraction.

dbmate

dbmate new add_users    # create migration
dbmate up               # apply pending
dbmate rollback         # roll back last

-- migrate:up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrate:down
DROP TABLE users;

Fast, no runtime dependencies, supports PostgreSQL, MySQL, SQLite, ClickHouse. The best standalone migration tool available.

golang-migrate

migrate create -ext sql -dir migrations -seq add_users
migrate -path migrations -database "postgres://localhost/mydb" up

Uses separate .up.sql and .down.sql files. Slightly more cumbersome than dbmate but widely used in Go projects.

Comparison Table

When to Use What

Use Prisma for CRUD applications with standard relational patterns, teams less comfortable with SQL, or when you want the fastest path from schema to working queries. Accept the trade-off of limited SQL control.

Use Drizzle or Kysely when you know SQL and want your queries to look like SQL, need complex queries (CTEs, window functions) without escaping to raw strings, or want type safety without a runtime engine.

Use dbmate + Kysely/Slonik when your team thinks in SQL, your queries are complex enough that abstraction gets in the way, or you need database-specific features like PostgreSQL extensions.

Avoid: TypeORM for new projects. Sequelize -- it's the jQuery of ORMs. synchronize: true in any ORM in production. Mixing migration tools within one project.

Migration Best Practices

1. Every schema change gets a migration. No manual DDL in production.
2. Make migrations idempotent where possible. Use IF NOT EXISTS and IF EXISTS.
3. Write down migrations. If a change can't be reversed, document why.
4. Test against production-like data. A migration that works on an empty database may fail on real data.
5. Never edit an applied migration. Create a new one instead.
6. Keep migrations small. One concern per migration file.
7. Separate schema from data migrations. Schema changes and data backfills go in different files.

Recommendations

For most TypeScript projects in 2026, Drizzle is the best default choice. It gives you type safety, SQL-like queries, built-in migrations, and no runtime overhead. You write TypeScript that maps directly to the SQL you'd write by hand.

If your team prioritizes developer experience over SQL control, Prisma is still excellent. The ecosystem, documentation, and tooling make it the easiest path from zero to a working data layer.

For experienced teams that want maximum control, Kysely + dbmate is the power combination -- full SQL expressiveness with compile-time type checking and plain SQL migration files any DBA can review.

Whatever you choose, avoid starting with raw SQL strings and planning to "add an ORM later." It's much easier to drop from a query builder to raw SQL for specific queries than to retrofit structure onto scattered SQL strings across your codebase.