[FEATURE]: Running Migrations in the Browser for Local-First Apps

[DallasHoff]

Describe what you want

I have been experimenting with using Drizzle to interact with an SQLite database running in the browser with WebAssembly for local-first apps. For querying in the browser, Drizzle works really well, but it is not currently possible to run migrations generated with Drizzle Kit in the browser since the migrate function requires Node.js packages like node:crypto and node:fs.

I would like to add a method of executing Drizzle Kit migrations in the browser to Drizzle ORM. I have been prototyping such a system in this repo. Let me know what you think. The process currently works like this:

1. Generate the migrations with Drizzle Kit as normal.
   drizzle-kit generate:sqlite --schema ./src/schema.ts --out ./src/migrations

2. Create an index.ts file that combines the journal file and migrations into a single file for the frontend bundle.

import { MigrationJournal } from '../migrator/types';
import _journal from './meta/_journal.json';
import Migration0000 from './0000_broad_cardiac.sql?raw';
import Migration0001 from './0001_silly_sumo.sql?raw';

export const journal: MigrationJournal = _journal;

export const migrations: Record<string, string> = {
  '0000_broad_cardiac': Migration0000,
  '0001_silly_sumo': Migration0001,
};

3. Pass those to a new version of the migrate function that does not use Node.js packages.

import { journal, migrations } from './migrations/';

await migrate(db, { journal, migrations });

The usage of the node:crypto package is replaced by the Web Crypto API, and the migrations are passed directly instead of read from the filesystem in order to eliminate node:fs and because fetching individual migration files over the network would not be ideal.

Ideally, Drizzle Kit could be configured to generate that index.ts file from step 2 or something similar that combines the journal and migrations automatically. Thoughts on this? Edit: with Expo support out, Drizzle can now almost do this, but the generated file should have a .ts extension instead of .js and the imports it contains of .sql files need to end in ?raw to support Vite.

I'd also like feedback on where in the Drizzle repo this new migrator implementation should be placed and how it should be named.

[zihaolam]

+1, unable to run migrate in edge runtime as well

[DallasHoff]

Update: Drizzle recently released Expo support which is a great step forward for this. It supports React Native now, and with just a bit more work, it could support other types of local-first apps.

I'm still willing to help implement this, but I would need a response from a maintainer so we can coordinate.

[Sheraff]

I wrote something for crsqlite-wasm, but I don't really know where to post it so folks find it: #193 (comment). It's extremely close to what would be needed for wa-sqlite, with just a few method names changed (and how to prepare / finalize statements) so if there is interest I could write the wa-sqlite version.

[bruceharrison1984]

I'd love to see this added as a first class feature. Currently, I compile a json file based on the _journal.json file, and then import it to my migrate function. This allows me to ship my migrations with my edge-app, and it is capable of managing the migrations. You just need to remember to recompile the deployment_schema.json file before building the application.

// compileMigrations.mjs
/* eslint-disable no-undef */

/**
 * This script compiles the Drizzle migrations into a format that can be processed by a webworker
 * This allows for migrations to be shipped and ran within the application
 */

import crypto from 'node:crypto';
import fs from 'node:fs';
import journal from './src/migrations/meta/_journal.json' assert { type: 'json' };

const migrate = [];

for (let index = 0; index < journal.entries.length; index++) {
  const { when, idx, tag } = journal.entries[index];

  console.log(`parsing ${tag}`);
  const migrationFile = fs
    .readFileSync(`./src/migrations/${tag}.sql`)
    .toString();

  migrate.push({
    idx,
    when,
    tag,
    hash: crypto.createHash('sha256').update(migrationFile).digest('hex'),
    sql: migrationFile
      .replace(/\n\t?/g, '')
      .split('--> statement-breakpoint')
      .map((x) => x.trim()),
  });
}

fs.writeFileSync(
  './src/migrations/deployment_schema.json',
  JSON.stringify(migrate, null, 2)
);

The code that applies the migrations is a modified version of the official D1 migrator.

import migrations from './migrations/deployment_schema.json';

... method within a class
  /**
   * Run migrations using the modified journal files.
   */
  migrate = async () => {
    const migrationsTable = '__drizzle_migrations';

    await this.drizzle.run(sql`
      CREATE TABLE IF NOT EXISTS ${sql.identifier(migrationsTable)} (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        hash TEXT NOT NULL,
        tag TEXT NOT NULL,
        created_at numeric
      )`);

    const dbMigrations = await this.drizzle.values<[number, string, string]>(
      sql`SELECT id, hash, created_at FROM ${sql.identifier(migrationsTable)} ORDER BY created_at DESC LIMIT 1`
    );

    const lastDbMigration = dbMigrations[0] ?? undefined;
    const statementToBatch = [];

    for (const migration of migrations) {
      if (!lastDbMigration || Number(lastDbMigration[2])! < migration.when) {
        for (const stmt of migration.sql) {
          statementToBatch.push(
            this.drizzle.run(sql.raw(stmt)) as BatchItem<'sqlite'>
          );
        }

        statementToBatch.push(
          this.drizzle.run(
            sql`INSERT INTO ${sql.identifier(migrationsTable)} ("hash", "created_at", "tag") VALUES(
            ${sql.raw(`'${migration.hash}'`)},
            ${sql.raw(`${migration.when}`)},
            ${sql.raw(`'${migration.tag}'`)})`
          ) as BatchItem<'sqlite'>
        );
      }
    }

    if (statementToBatch.length > 0) {
      await this.drizzle.batch(
        statementToBatch as unknown as readonly [
          BatchItem<'sqlite'>,
          ...BatchItem<'sqlite'>[],
        ]
      );
    }
  };

Kinda hacky, but it seems to work just fine.

[fenicento]

+1, this would be extremely helpful for offline-first applications

[calebpitan]

A modified version of @bruceharrison1984 solution has been working for me so far, using sql.js and drizzle-orm/sql-js.

As with Bruce's, I set up a prebuild script which deploys the migrations to a JSON config file written to the source code and then implemented a migrator that runs the migrations on the browser and keeps a record of the latest migration in the __drizzle_migrations table of the SQLite database file.

/scripts/deploy.mjs

#!/usr/bin/env node
import crypto from 'node:crypto'
import fs from 'node:fs'
import path from 'node:path'
import url from 'node:url'

const { default: journal } = await import('../drizzle/meta/_journal.json', {
  with: { type: 'json' }
})

const migrate = []

const root = path.resolve(url.fileURLToPath(path.dirname(import.meta.url)), '..')
const outdir = path.resolve(root, './src/migrations/')
const outfile = path.resolve(outdir, 'deployment.json')

console.log()

for (let index = 0; index < journal.entries.length; index++) {
  const { when, idx, tag } = journal.entries[index]

  console.log('(%d) Parsing migration tagged "%s"', index + 1, tag)

  const filepath = path.resolve(root, 'drizzle', `${tag}.sql`)
  const migration_file = fs.readFileSync(filepath).toString()

  migrate.push({
    idx,
    when,
    tag,
    hash: crypto.createHash('sha256').update(migration_file).digest('hex'),
    sql: migration_file
      .replace(/\n\t?/g, '')
      .split('--> statement-breakpoint')
      .map((x) => x.trim())
  })
}

if (fs.existsSync(outdir) === false) fs.mkdirSync(outdir)

fs.writeFileSync(outfile, JSON.stringify(migrate, null, 2))

console.log()
console.log('Migration deployment config file written out to "%s"\n', outfile)

package.json scripts

{
  "postinstall": "yarn build",
  "prebuild": "run-s migrate:generate migrate:deploy",
  "build:tsc": "tsc",
  "build": "run-s prebuild build:tsc",
  "migrate:deploy": "node ./scripts/deploy.mjs",
  "migrate:generate": "drizzle-kit generate"
}

migrator.ts (This runs in the browser)

import { sql } from 'drizzle-orm'
import { SQLJsDatabase } from 'drizzle-orm/sql-js'

import config from './migrations/deployment.json'

export function migrate<TSchema extends Record<string, unknown>>(db: SQLJsDatabase<TSchema>) {
  const TABLE_NAME = sql.identifier('__drizzle_migrations')

  db.run(
    sql`
      CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        hash TEXT NOT NULL,
        tag TEXT NOT NULL,
        created_at INTEGER NOT NULL
      );
    `
  )

  const deployments = db.values<[number, string, string]>(
    sql`
      SELECT id,
        hash,
        created_at 
      FROM ${TABLE_NAME} 
      ORDER BY created_at DESC 
      LIMIT 1;
    `
  )

  const deployment = deployments.at(0)

  const migrations = config.filter((migration) => {
    const timestamp = deployment?.at(2)
    return !deployment || Number(timestamp) < migration.when
  })

  if (migrations.length === 0) {
    return console.log('There, currently, are no migrations to deploy')
  }

  db.transaction((tx) => {
    migrations.forEach((migration, i) => {
      console.info('%d. Deploying migration:', i + 1)
      console.info('     TAG => %s', migration.tag)
      console.info('     HASH => %s', migration.hash)
      migration.sql.forEach((stmt) => tx.run(stmt))

      tx.run(
        sql`
          INSERT INTO ${TABLE_NAME} ("hash", "created_at", "tag") VALUES (
            ${sql.raw(`'${migration.hash}'`)},
            ${sql.raw(`${migration.when}`)},
            ${sql.raw(`'${migration.tag}'`)}
          );
        `
      )
    })
  })

  console.info('Database up to date!')
}

[lautaropaske]

+1! the solutions presented thus far all look great; it would be great to incorporate first-class support

i've used @bruceharrison1984 's suggestion successfully in Bun + typescript + SQLocal with these trivial adaptations:

• compileMigrations.ts with path adaptations to the json import, readFileSync and writeFileSync lines
• applyMigrations.ts exporting async function migrate(db: ...), where db is the return of drizzle(...) later on
• added compileMigrations.ts call to my build command in package.json