Stop writing raw SQL in your migrations (most of the time)

Something I keep seeing across teams, especially with people who are getting comfortable with tools like db-migrate or Kysely, is a very specific habit: they reach for raw SQL for everything.

Need to add a column? Raw ALTER TABLE. Need to create a table? Raw CREATE TABLE. Need to add an index? You guessed it, another raw query.

And look, raw SQL is not evil, and I'm not here to tell you to never use it. But when your migration tool gives you a proper schema builder and you skip it to write raw strings for a simple column addition, you're throwing away a lot of the things that make those tools worth using in the first place.

Let me explain why.

First, what does "raw" even look like?

Let's say you want to add a simple email column to a users table. With Kysely, the raw approach tends to look something like this:

import { sql } from 'kysely';

export async function up(db) {
  await sql`ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL`.execute(db);
}

export async function down(db) {
  await sql`ALTER TABLE users DROP COLUMN email`.execute(db);
}

Or with db-migrate, people often do this:

exports.up = function (db) {
  return db.runSql('ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL');
};

exports.down = function (db) {
  return db.runSql('ALTER TABLE users DROP COLUMN email');
};

It works. The migration runs, the column appears, everyone goes home happy.

But the same thing with the builder looks like this in Kysely:

export async function up(db) {
  await db.schema
    .alterTable('users')
    .addColumn('email', 'varchar(255)', (col) => col.notNull())
    .execute();
}

export async function down(db) {
  await db.schema
    .alterTable('users')
    .dropColumn('email')
    .execute();
}

And with db-migrate you don't even need to write SQL at all:

exports.up = function (db) {
  return db.addColumn('users', 'email', {
    type: 'string',
    length: 255,
    notNull: true,
  });
};

exports.down = function (db) {
  return db.removeColumn('users', 'email');
};

At first glance the raw version might even look shorter, so why should you care? Let me give you a few reasons.

Reason 1: you lose portability

This is the big one, and it's the one that bites people the hardest.

When you write VARCHAR(255), SERIAL, AUTO_INCREMENT, BOOLEAN, or NOW() directly in a string, you're hardcoding the dialect of one specific database. The moment you switch, or the moment you run your tests against a different engine than production, things start to break in annoying ways.

If you've read some of my other posts, you'll know I have a soft spot for this problem. In the RETURNING clause post I got bitten by exactly this: RETURNING works great on PostgreSQL, but MySQL/MariaDB only partially support it. Raw SQL assumes you know every quirk of your target engine, forever.

The whole point of the schema builder is that it knows the dialect for you. You say addColumn('active', 'boolean') and Kysely (or db-migrate) figures out whether that becomes a real BOOLEAN, a TINYINT(1), or whatever the engine expects. You describe the intent, the tool handles the syntax.

Reason 2: your down migrations get fragile

Here's a subtle one. When you use the builder, the tool understands the structure of the change you're making, and reversing it is often trivial and consistent.

When you write raw SQL, you are responsible for writing a down that perfectly undoes the up, and it's very easy to get lazy or to make a mistake. I've seen plenty of migrations where the up is a carefully crafted raw query and the down is just... empty, or wrong, because nobody wanted to hand-write the reverse.

A broken down migration is one of those things you don't notice until the worst possible moment: you need to roll back in production and suddenly discover your escape hatch doesn't work.

Reason 3: you skip the safety net

Builder methods are typed and validated. In Kysely especially, you get autocomplete and, if you're using TypeScript, the compiler will shout at you when you try to do something that doesn't make sense.

This is the same argument I made in my post about defining objects with type definitions in JSDoc and the one on abstracting nested types in TypeScript: giving your tools enough information to help you is almost always worth it. A raw string is completely opaque. Your editor can't check it, can't complete it, and can't warn you that you typed VARHCAR instead of VARCHAR. You'll only find out at runtime, when the migration blows up.

// Typo city. Nothing catches this until it runs.
await sql`ALTER TABLE users ADD COLUMN emial VARHCAR(255)`.execute(db);

// The builder won't let 'emial' silently become a problem,
// and the column type is a known value, not a free-form string.
await db.schema
  .alterTable('users')
  .addColumn('email', 'varchar(255)')
  .execute();

Reason 4: readability and consistency

If half your migrations are raw SQL and the other half use the builder, reading through your migration history becomes a chore. Every file speaks a slightly different language and the reader has to context-switch constantly.

I made basically the same point in why you shouldn't mix ES modules and CommonJS: pick one style and stick with it. Consistency isn't just aesthetic, it genuinely lowers the mental cost for the next person who has to touch the code (and that next person is very often you, six months from now, with no memory of what you were thinking).

So when should you use raw SQL?

Now, here's the important part, because I don't want you to walk away thinking "raw SQL bad, builder good, the end". That's not the point at all.

Raw SQL is the right tool when the ORM or migration library simply doesn't give you a way to express what you need. And that happens more often than you'd think, especially once you go past basic table shapes. Some examples:

• Database-specific features that the builder doesn't wrap, like PostgreSQL extensions (CREATE EXTENSION "uuid-ossp"), custom types with CREATE TYPE ... AS ENUM, or generated columns.
• Complex data migrations, where you're not just changing the schema but also transforming existing rows, for example backfilling a new column from old data with a CASE statement.
• Concurrent index creation in Postgres (CREATE INDEX CONCURRENTLY), which the builder might not support because it has special transaction requirements.
• Triggers, functions, materialized views, and other things that are inherently SQL and have no clean builder equivalent.

In those cases, reaching for raw SQL isn't a shortcut, it's the correct answer. The builder abstraction has a ceiling, and when you hit it, you drop down a level. That's a totally healthy thing to do.

The rule I try to follow is simple:

Use the builder for anything the builder can express. Drop to raw SQL only when the builder can't do the job.

A quick reality check

To be fair, there are a couple of arguments on the other side, and I want to acknowledge them rather than pretend they don't exist:

• "I know SQL better than the builder's API." Fair. If you're comfortable with SQL, the builder can feel like an extra layer between you and the thing you already know how to write. But the builder isn't there to teach you SQL, it's there to keep your migrations portable, reversible and checkable.
• "My app is tied to one database and always will be." Also fair, and if that's genuinely true, the portability argument loses some weight. But the readability, the safer down migrations, and the type safety still stand. And "we'll never change database" has a funny way of not aging well.

So it's not black and white. It's about defaults. My default is the builder, and raw SQL is the deliberate exception, not the reflex.

Conclusions

Writing raw SQL for a simple ALTER TABLE is a bit like using parseInt when Number would do the job better: it works, but you're reaching for the wrong tool out of habit, and you're quietly giving up things you'd actually like to keep.

Let the builder handle the boring, portable, reversible stuff. Save raw SQL for the moments where the ORM genuinely can't help you, and it'll feel like a precision tool instead of a crutch.

And as always, this is just how I like to do it, not the one and only truth. If you've got a different take, I'd love to hear it in the comments.

DISCLAIMER
I used AI to help me write this article and fix grammar issues.

Comments

[Kane Lim]

I agree with the default here: use the builder for the changes it can actually model well, and drop to SQL when you need database-specific behavior.

One thing I’d add is that portability can sometimes be overstated. If a team is deliberately PostgreSQL-only, raw SQL isn’t necessarily a problem. In that case, I’d care more about making the migration explicit, reviewable, and safely reversible than avoiding SQL itself.

The strongest argument for the builder is probably consistency and maintainability. For common schema changes, having a predictable API makes migrations much easier to review. Then when someone sees raw SQL, it signals that there’s probably a reason for it CONCURRENTLY, data backfills, triggers, extensions, etc.

That “builder by default, SQL by exception” rule is a pretty sensible team convention.

[Kane Lim]

I would like to get to know you better. Would you please contact me? telegram@CRDT_CTO