How to use PGLite with "drizzle-kit migrate"?

[Vishtar]

$ npm run migrations:run

> [email protected] migrations:run
> npm run migrations migrate

> [email protected] migrations
> npx drizzle-kit migrate

drizzle-kit: v0.22.7
drizzle-orm: v0.31.2

No config path provided, using default path
Reading config file '/home/vishtar/Documents/code/work/ton_2x/drizzle.config.ts'
To connect to Postgres database - please install either of 'pg', 'postgres', '@neondatabase/serverless' or '@vercel/postgres' drivers

drizzle.config.ts:

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: 'file://main.db', // './main.db' also doesn't work
  },
});

"@electric-sql/pglite": "^0.1.5",

Isn't there a proper dialect or something?..

[Vishtar]

I made a workaround solution to apply migrations:

import fs from 'fs'
import { createHash } from 'node:crypto'
import { bigint, pgTable, serial, text } from 'drizzle-orm/pg-core'
import { PGlite } from '@electric-sql/pglite'
import { drizzle } from 'drizzle-orm/pglite'
import { eq } from 'drizzle-orm'

const migrationsDirectory = './src/db/migrations'
const databaseUrl = 'file://data/postgresql'

const createDrizzleMigrationsTable = `
CREATE TABLE IF NOT EXISTS "__drizzle_migrations" (
	"id" serial PRIMARY KEY NOT NULL,
	"hash" text NOT NULL,
	"timestamp" bigint NOT NULL,
	CONSTRAINT "__drizzle_migrations_hash_unique" UNIQUE("hash")
);`

const drizzleMigrations = pgTable('__drizzle_migrations', {
  id: serial('id').primaryKey(),
  hash: text('hash').unique().notNull(),
  timestamp: bigint('timestamp', { mode: 'number' }).notNull(),
})

export const applyMigrations = async () => {
  const database = new PGlite(databaseUrl)
  const db = drizzle(database)
  await database.query(createDrizzleMigrationsTable)
  const migrations = fs.readdirSync(migrationsDirectory).sort()
  for (const migration of migrations) {
    if (!/\.sql$/.test(migration)) continue
    const migrationPath = `${migrationsDirectory}/${migration}`
    const migrationData = fs.readFileSync(migrationPath)
    const hash = createHash('sha256').update(migrationData).digest('hex')
    const [migrationHashInDb] = await db
      .select()
      .from(drizzleMigrations)
      .where(eq(drizzleMigrations.hash, hash))
    if (migrationHashInDb) {
      console.log(`«${migration}» already applied`)
      continue
    }
    const migrationQueries = migrationData.toString().split('--> statement-breakpoint')
    for (const query of migrationQueries) {
      await database.query(query)
    }
    await db.insert(drizzleMigrations).values({
      hash,
      timestamp: Date.now(),
    })
  }
  await database.close()
}

applyMigrations()

[daltonkyemiller]

Server Implementation

There is a migrate function exported from drizzle-orm/pglite/migrator that suffices if you're not in a browser environment.

// db.ts

import { PGlite } from "@electric-sql/pglite";
import { drizzle } from "drizzle-orm/pglite";

const client = new PGlite();

const db = drizzle(client);

export { db };

// migrate.ts

import { migrate } from "drizzle-orm/pglite/migrator"
import { db } from "./db"

await migrate(db, {
  migrationsFolder: "./drizzle/",
});

console.log("Migration complete")

---

Browser Implementation

However, this migrate function uses node APIs. So, if you're in the browser, you can compile the migrations to a json file and read them. Which is what I ended up doing since I am working on an offline only app.

// db.ts

import { PGlite } from "@electric-sql/pglite";
import { drizzle } from "drizzle-orm/pglite";

const client = new PGlite("idb://my-db");

const db = drizzle(client);

export { db };

// compile-migrations.ts

import { readMigrationFiles } from "drizzle-orm/migrator";
import { join } from "node:path";

const migrations = readMigrationFiles({ migrationsFolder: "./drizzle/" });

await Bun.write(
  join(import.meta.dir, "./migrations.json"),
  JSON.stringify(migrations),
);

console.log("Migrations compiled!");

// package.json

{
...
  "scripts": {
    "db:generate": "drizzle-kit generate && bun ./src/compile-migrations.ts"
  }
...
}

// migrate.ts

import type { MigrationConfig } from "drizzle-orm/migrator";
import { db } from "./db";
import migrations from "./migrations.json";

export async function migrate() {
  // dialect and session will appear to not exist...but they do
  db.dialect.migrate(migrations, db.session, {
    migrationsTable: "drizzle_migrations",
  } satisfies Omit<MigrationConfig, "migrationsFolder">);
}

// root.ts

import { migrate } from "./migrate"

migrate().then(renderApp);

[zhanglun]

i really appreciate your sharing; it has been incredibly helpful.