Drizzle migrations on Cloudflare D1

Drizzle is now my favourite ORM for Sql databases when writing typescript applications. I was using Kysely for a while and think it worked well, but there are a few things that in my opinion is slightly better with Drizzle:

• The typescript types are generated based on the schema file that also are used for migrations, so there's one source of truth. With Kysely you need to make sure the types match with the migration files which can lead to some nasty bugs.

• The migrations generate proper sql statements making it easy to understand what's happening. It also makes it very easy to apply the migrations manually to any SQL database that supports... sql.

• It has builtin support for cloudflare D1. Or.. it's actually the other way around. But as wrangler support a folder with sql statements it works well together.

• It makes a difference between MySQL, postgres and SQLite. They are pretty similar but by handling separately you can use the all the features. Kysely handles them all in the same way which to me feels like a leaky abstraction.

• The drizzle-kit studio is pretty cool and a nice way to work with any SQL database in a unified fashion.

Installation and config

There are a few steps needed to add Drizzle to the project. First install the orm and the drizzle kit and the database package. In this case we use better-sqlite3 for a local SQLite database.

yarn add drizzle-kit @types/better-sqlite3 -D
yarn add drizzle-orm better-sqlite3 -S

Add the drizzle.config.ts config file. You can have multiple config files but this is the default config file name.

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  driver: 'better-sqlite',
  dialect: 'sqlite',
  dbCredentials: {
    url: 'db.sqlite',
  },
};

The config file points to the schema files which in this case should be available in the src folder:

import { relations } from 'drizzle-orm';

import {
  primaryKey,
  sqliteTable,
  text,
} from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('title').notNull(),
});

Migrations

So, Drizzle handles migrations by comparing the schema file to the actual database and by running npx drizzle-kit generate it will create a new migration sql file and some metadata files to keep track of the state.

Creating the migration doesn't actually change the database. I have written this small helper to apply the migration to the database, but guess there should be a built in solution for this?

// scripts/migrate.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import * as schema from '../src/schema';

const sqlite = new Database('db.sqlite');
const db = drizzle(sqlite, { schema });

async function init() {
  console.log('Migrating database...');
  try {
    await migrate(db, { migrationsFolder: './drizzle' });
    console.log('Database migrated');
  } catch (error) {
    console.error('Migration failed:', error);
  } finally {
    sqlite.close();
  }
}

init();

Run the script to apply the migrations:

npx ts-node scripts/migrate.ts