drizzle+pglite+indexeddb+migrations.md

Drizzle + PGlite + indexeddb + migrations

Haven't found a built-in solution for db migrations for the setup:

• PGlite (indexeddb)
• Drizzle

Here's a super simple one that creates a migration table that includes already finished migrations.

It's implemented in a couple of minutes so by far not rock-solid. But a good starter if you need a starter.

Database Controller

import { PGlite } from "@electric-sql/pglite"
import { drizzle } from "drizzle-orm/pglite"
import { DB_MIGRATIONS, INIT_ID } from "../constants/migrations.const"
import { migrations } from "../db/schema"

const INDEXEDDB_NAME = "magic-tools"

enum DbState {
	disconnected = "disconnected",
	setup = "setup",
	migrate = "migrate",
	connected = "connected",
	error = "error",
}

class DbController {
	private client: PGlite
	private db
	private state: DbState = DbState.disconnected

	constructor() {
		this.state = DbState.setup
		this.client = new PGlite(`idb://${INDEXEDDB_NAME}`)
		this.db = drizzle(this.client, { logger: true })
	}

	public getDb() {
		if (this.state !== DbState.connected) {
			throw new Error("Database not connected")
		}
		return this.db
	}

	public async migrate() {
		if ([DbState.migrate, DbState.connected].includes(this.state)) {
			console.log(`Migrations running`)
			return
		}
		this.state = DbState.migrate

		let migrationIdsResponse = await this.getFinishedMigrationIds()
		if (!migrationIdsResponse.success) {
			// db not initialized: run first migration
			const migration = DB_MIGRATIONS.find(
				(migration) => migration.id === INIT_ID,
			)
			if (!migration) {
				console.error(`Migration ${INIT_ID} not found`)
				this.state = DbState.error
				return
			}
			for (const statement of migration.statements) {
				await this.db.execute(statement)
			}
			await this.db.insert(migrations).values({ id: INIT_ID })
		}

		migrationIdsResponse = await this.getFinishedMigrationIds()
		if (!migrationIdsResponse.success) {
			console.error(`Failed to get migration ids`)
			this.state = DbState.error
			return
		}

		const unfinishedMigrationIds = DB_MIGRATIONS.filter(
			(migration) => !migrationIdsResponse.ids.includes(migration.id),
		)

		// executing new migrations
		for (const migration of unfinishedMigrationIds) {
			for (const statement of migration.statements) {
				await this.db.execute(statement)
			}
			await this.db.insert(migrations).values({ id: migration.id })
		}
		this.state = DbState.connected
	}

	private async getFinishedMigrationIds(): Promise<{
		success: boolean
		ids: string[]
	}> {
		const response: { success: boolean; ids: string[] } = {
			success: false,
			ids: [],
		}

		try {
			const data = await this.db
				.select({ id: migrations.id })
				.from(migrations as any)
			response.success = true
			response.ids = data.map((entry) => entry.id)
		} catch (error) {
			console.error(error)
		}

		console.log(response)
		return response
	}
}

export const db = new DbController()

Migrations

After generating the db statements for db updates it's needed to manually update the migrations file. This step can be automated if you needed.

import { sql } from "drizzle-orm"

interface DbMigration {
	id: string
	statements: any[]
}

export const INIT_ID = "0000_black_mentallo"

export const DB_MIGRATIONS: DbMigration[] = [
	{
		id: INIT_ID,
		statements: [
			sql`CREATE SCHEMA IF NOT EXISTS "magic_tools";`,
			sql`CREATE TABLE IF NOT EXISTS "magic_tools"."migrations" (
      "id" text PRIMARY KEY NOT NULL
    );`,
		],
	},
	{
		id: "0001_slimy_radioactive_man",
		statements: [
			sql`CREATE TABLE IF NOT EXISTS "magic_tools"."..." (
        ...
      );`,
		],
	},
]