/** * Auto Drizzle Migrator * * Seamlessly integrates Drizzle Kit migrations with postgres.do's AutoMigrator. * This provides automatic migration execution on DO initialization while * maintaining compatibility with both Drizzle's journal and postgres.do's * version tracking. * * @example * ```typescript * import { createAutoDrizzleMigrator } from 'postgres.do/drizzle' * import bundledMigrations from './drizzle-bundle' * * const migrator = createAutoDrizzleMigrator({ * drizzle: bundledMigrations, * autoRun: true, * journalTable: '__drizzle_migrations', * checksumValidation: true, * }) * * // In your DO * class PostgresDO { * async fetch(request: Request) { * await migrator.ensureMigrated(this.pglite) * // Handle request... * } * } * ``` */ import type { BundledDrizzleMigrations, DrizzleJournal, DrizzleJournalEntry as _DrizzleJournalEntry, DrizzleMigrationProgressEvent, DrizzleMigrationResult, } from './drizzle-migrator.js' import type { PostgresDoMigration } from './migration-bridge.js' /** * Query executor interface matching postgres.do and PGLite */ interface QueryExecutor { query( sql: string, params?: unknown[] ): Promise<{ rows: T[] }> } /** * Configuration for Auto Drizzle Migrator * * This matches the proposed drizzle.config.ts format from the issue */ export interface AutoDrizzleMigratorConfig { /** * Bundled Drizzle migrations from drizzle-kit generate */ drizzle: BundledDrizzleMigrations /** * Optional down migrations keyed by migration tag * Drizzle doesn't generate down migrations by default, * but you can provide them manually for rollback support. */ downMigrations?: Record /** * Run migrations automatically on first connection * Default: true */ autoRun?: boolean /** * Name of the Drizzle migrations tracking table * Default: '__drizzle_migrations' */ journalTable?: string /** * Name of the postgres.do migrations tracking table * Default: '_migrations' */ postgresDoTable?: string /** * Validate migration checksums to detect modifications * Default: true */ checksumValidation?: boolean /** * Sync Drizzle journal with postgres.do version tracking * Default: true */ syncJournals?: boolean /** * Progress callback for migration events */ onProgress?: (event: DrizzleMigrationProgressEvent) => void /** * Callback when all migrations complete */ onComplete?: (result: AutoDrizzleMigrationResult) => void /** * Callback on migration error */ onError?: (error: Error) => void /** * Enable debug logging * Default: false */ debug?: boolean } /** * Result of auto Drizzle migration */ export interface AutoDrizzleMigrationResult extends DrizzleMigrationResult { /** postgres.do migrations result */ postgresDoResult?: { fromVersion: number toVersion: number migrationsRun: number migrationsSkipped: number } /** Whether journals were synced */ journalsSynced: boolean } /** * Migration status record */ interface MigrationStatusRecord { id: string version: number hash?: string drizzle_hash?: string drizzle_tag?: string status: string applied_at: string } /** * Migrator state */ export type AutoDrizzleMigratorState = 'idle' | 'running' | 'completed' | 'failed' /** * Generate hash from content */ function generateHash(content: string): string { let hash = 0 for (let i = 0; i < content.length; i++) { const char = content.charCodeAt(i) hash = ((hash << 5) - hash) + char hash = hash & hash } return Math.abs(hash).toString(16).padStart(8, '0') } /** * Clean Drizzle SQL */ function cleanDrizzleSql(sql: string): string { return sql .replace(/-->\s*statement-breakpoint\s*/g, '\n') .trim() } /** * Convert tag to human-readable name */ function tagToName(tag: string): string { return tag .replace(/_/g, ' ') .replace(/(^|\s)\w/g, (c) => c.toUpperCase()) } /** * SQL statements for creating the unified migrations table */ function getCreateUnifiedTableStatements(tableName: string): string[] { const cleanName = tableName.replace(/"/g, '') return [ `CREATE TABLE IF NOT EXISTS "${tableName}" ( id TEXT PRIMARY KEY, name TEXT NOT NULL, version INTEGER NOT NULL UNIQUE, checksum TEXT NOT NULL, applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, execution_time_ms INTEGER NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'applied' CHECK (status IN ('pending', 'applied', 'failed', 'rolled_back')), drizzle_hash TEXT, drizzle_tag TEXT, drizzle_idx INTEGER )`, `CREATE INDEX IF NOT EXISTS idx_${cleanName}_version ON "${tableName}" (version)`, `CREATE INDEX IF NOT EXISTS idx_${cleanName}_drizzle_hash ON "${tableName}" (drizzle_hash)`, `CREATE INDEX IF NOT EXISTS idx_${cleanName}_status ON "${tableName}" (status)`, ] } /** * SQL statement for creating the Drizzle-compatible migrations table */ function getCreateDrizzleTableSQL(tableName: string): string { return ` CREATE TABLE IF NOT EXISTS "${tableName}" ( id SERIAL PRIMARY KEY, hash TEXT NOT NULL UNIQUE, created_at BIGINT NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT ) ` } /** * Auto Drizzle Migrator * * Combines Drizzle Kit migrations with postgres.do's AutoMigrator * for seamless schema management in Durable Objects. */ export class AutoDrizzleMigrator { private config: Required> private drizzle: BundledDrizzleMigrations private downMigrations: Record private onProgress?: ((event: DrizzleMigrationProgressEvent) => void) | undefined private onComplete?: ((result: AutoDrizzleMigrationResult) => void) | undefined private onError?: ((error: Error) => void) | undefined private state: AutoDrizzleMigratorState = 'idle' private migrationPromise: Promise | null = null private lastResult: AutoDrizzleMigrationResult | null = null private lastError: Error | null = null private cachedMigrations: PostgresDoMigration[] | null = null constructor(config: AutoDrizzleMigratorConfig) { this.drizzle = config.drizzle this.downMigrations = config.downMigrations ?? {} this.config = { autoRun: config.autoRun ?? true, journalTable: config.journalTable ?? '__drizzle_migrations', postgresDoTable: config.postgresDoTable ?? '_migrations', checksumValidation: config.checksumValidation ?? true, syncJournals: config.syncJournals ?? true, debug: config.debug ?? false, } this.onProgress = config.onProgress this.onComplete = config.onComplete this.onError = config.onError } /** * Log debug message */ private log(message: string, ...args: unknown[]): void { if (this.config.debug) { console.log(`[AutoDrizzleMigrator] ${message}`, ...args) } } /** * Get current state */ getState(): AutoDrizzleMigratorState { return this.state } /** * Get last result */ getLastResult(): AutoDrizzleMigrationResult | null { return this.lastResult } /** * Get last error */ getLastError(): Error | null { return this.lastError } /** * Get migrations in postgres.do format */ getMigrations(): PostgresDoMigration[] { if (this.cachedMigrations) { return this.cachedMigrations } const { journal, migrations } = this.drizzle this.cachedMigrations = journal.entries .sort((a, b) => a.idx - b.idx) .map((entry) => { const folderName = `${entry.idx.toString().padStart(4, '0')}_${entry.tag}` const sql = migrations[folderName] || migrations[entry.tag] if (!sql) { throw new Error(`Missing migration SQL for: ${folderName}`) } const cleanSql = cleanDrizzleSql(sql) const hash = generateHash(`${folderName}:${sql}`) const down = this.downMigrations[entry.tag] || this.downMigrations[folderName] const migration: PostgresDoMigration = { id: folderName, name: tagToName(entry.tag), version: entry.idx + 1, up: cleanSql, tags: ['drizzle'], transactional: entry.breakpoints !== false, drizzle: { idx: entry.idx, hash, tag: entry.tag, when: entry.when, }, } if (down) { migration.down = cleanDrizzleSql(down) migration.isReversible = true } else { migration.isReversible = false } return migration }) return this.cachedMigrations } /** * Get the Drizzle journal */ getJournal(): DrizzleJournal { return this.drizzle.journal } /** * Get latest version */ getLatestVersion(): number { const migrations = this.getMigrations() return migrations.length > 0 ? Math.max(...migrations.map((m) => m.version)) : 0 } /** * Initialize tables */ private async initializeTables(executor: QueryExecutor): Promise { this.log('Initializing migration tables') // Create postgres.do unified table (execute each statement separately for PGLite compatibility) const statements = getCreateUnifiedTableStatements(this.config.postgresDoTable) for (const statement of statements) { await executor.query(statement) } // Create Drizzle-compatible table for drizzle-kit compatibility await executor.query(getCreateDrizzleTableSQL(this.config.journalTable)) } /** * Get applied migrations from both systems */ private async getAppliedMigrations(executor: QueryExecutor): Promise<{ postgresDoVersions: Map drizzleHashes: Set }> { // Get postgres.do applied versions const pdoResult = await executor.query( `SELECT id, version, checksum as hash, drizzle_hash, drizzle_tag, status, applied_at::text FROM "${this.config.postgresDoTable}" WHERE status = 'applied' ORDER BY version` ) const postgresDoVersions = new Map() for (const row of pdoResult.rows) { postgresDoVersions.set(row.version, row) } // Get Drizzle applied hashes const drizzleResult = await executor.query<{ hash: string }>( `SELECT hash FROM "${this.config.journalTable}"` ) const drizzleHashes = new Set(drizzleResult.rows.map((r) => r.hash)) return { postgresDoVersions, drizzleHashes } } /** * Ensure migrations are applied */ async ensureMigrated(executor: QueryExecutor): Promise { // If already running, wait for completion if (this.migrationPromise) { return this.migrationPromise } // If already completed, return cached result if (this.state === 'completed' && this.lastResult) { return this.lastResult } // If failed, throw the last error if (this.state === 'failed' && this.lastError) { throw this.lastError } // If autoRun is disabled, return empty result if (!this.config.autoRun) { return { success: true, appliedCount: 0, skippedCount: 0, durationMs: 0, applied: [], journalsSynced: false, } } // Start migration this.migrationPromise = this.runMigrations(executor) try { const result = await this.migrationPromise return result } finally { this.migrationPromise = null } } /** * Run all pending migrations */ private async runMigrations(executor: QueryExecutor): Promise { this.state = 'running' this.log('Starting Drizzle migrations') const startTime = performance.now() const applied: string[] = [] let skippedCount = 0 let journalsSynced = false try { // Initialize tables await this.initializeTables(executor) // Get applied migrations const { postgresDoVersions, drizzleHashes } = await this.getAppliedMigrations(executor) // Get all migrations const migrations = this.getMigrations() this.log(`Found ${migrations.length} migrations, ${postgresDoVersions.size} in postgres.do, ${drizzleHashes.size} in Drizzle journal`) const startVersion = postgresDoVersions.size > 0 ? Math.max(...Array.from(postgresDoVersions.keys())) : 0 for (let i = 0; i < migrations.length; i++) { const migration = migrations[i]! const drizzleHash = migration.drizzle?.hash ?? generateHash(migration.id + migration.up) // Check if already applied in either system const appliedInPostgresDo = postgresDoVersions.has(migration.version) const appliedInDrizzle = drizzleHashes.has(drizzleHash) if (appliedInPostgresDo && appliedInDrizzle) { this.log(`Skipping already applied: ${migration.id}`) skippedCount++ this.onProgress?.({ migration: { hash: drizzleHash, tag: migration.drizzle?.tag ?? migration.id, sql: migration.up, }, index: i, total: migrations.length, phase: 'skipped', }) continue } // Checksum validation if (this.config.checksumValidation && appliedInPostgresDo) { const existingRecord = postgresDoVersions.get(migration.version) if (existingRecord && existingRecord.hash !== generateHash(migration.up)) { throw new Error( `Migration checksum mismatch for ${migration.id}. ` + `The migration content has changed since it was applied.` ) } } // Report starting this.onProgress?.({ migration: { hash: drizzleHash, tag: migration.drizzle?.tag ?? migration.id, sql: migration.up, }, index: i, total: migrations.length, phase: 'starting', }) const migrationStart = performance.now() try { this.onProgress?.({ migration: { hash: drizzleHash, tag: migration.drizzle?.tag ?? migration.id, sql: migration.up, }, index: i, total: migrations.length, phase: 'executing', }) // Execute migration SQL const statements = migration.up .split(/;\s*(?=(?:[^']*'[^']*')*[^']*$)/) .map((s) => s.trim()) .filter((s) => s.length > 0) for (const statement of statements) { await executor.query(statement + ';') } const execTime = Math.round(performance.now() - migrationStart) const checksum = generateHash(migration.up) // Record in postgres.do table await executor.query( `INSERT INTO "${this.config.postgresDoTable}" (id, name, version, checksum, execution_time_ms, status, drizzle_hash, drizzle_tag, drizzle_idx) VALUES ($1, $2, $3, $4, $5, 'applied', $6, $7, $8) ON CONFLICT (id) DO UPDATE SET status = 'applied', execution_time_ms = $5, applied_at = CURRENT_TIMESTAMP`, [ migration.id, migration.name, migration.version, checksum, execTime, drizzleHash, migration.drizzle?.tag ?? null, migration.drizzle?.idx ?? null, ] ) // Record in Drizzle table for compatibility await executor.query( `INSERT INTO "${this.config.journalTable}" (hash) VALUES ($1) ON CONFLICT (hash) DO NOTHING`, [drizzleHash] ) applied.push(migration.id) this.log(`Applied ${migration.id} in ${execTime}ms`) this.onProgress?.({ migration: { hash: drizzleHash, tag: migration.drizzle?.tag ?? migration.id, sql: migration.up, }, index: i, total: migrations.length, phase: 'completed', durationMs: execTime, }) } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error) const durationMs = Math.round(performance.now() - migrationStart) this.log(`Failed ${migration.id}: ${errorMessage}`) this.onProgress?.({ migration: { hash: drizzleHash, tag: migration.drizzle?.tag ?? migration.id, sql: migration.up, }, index: i, total: migrations.length, phase: 'failed', durationMs, error: errorMessage, }) throw error } } // Sync journals if configured if (this.config.syncJournals) { journalsSynced = await this.syncJournals(executor) } const totalDuration = Math.round(performance.now() - startTime) const endVersion = this.getLatestVersion() const result: AutoDrizzleMigrationResult = { success: true, appliedCount: applied.length, skippedCount, durationMs: totalDuration, applied, journalsSynced, postgresDoResult: { fromVersion: startVersion, toVersion: endVersion, migrationsRun: applied.length, migrationsSkipped: skippedCount, }, } this.state = 'completed' this.lastResult = result this.log(`Migration complete: ${applied.length} applied, ${skippedCount} skipped in ${totalDuration}ms`) this.onComplete?.(result) return result } catch (error) { const totalDuration = Math.round(performance.now() - startTime) const errorMessage = error instanceof Error ? error.message : String(error) const result: AutoDrizzleMigrationResult = { success: false, appliedCount: applied.length, skippedCount, durationMs: totalDuration, applied, error: errorMessage, journalsSynced: false, } this.state = 'failed' this.lastResult = result this.lastError = error instanceof Error ? error : new Error(errorMessage) this.onError?.(this.lastError) return result } } /** * Sync Drizzle journal with postgres.do tracking * * Ensures both tracking systems have consistent records */ private async syncJournals(executor: QueryExecutor): Promise { try { this.log('Syncing journals') // Get all records from postgres.do table const pdoResult = await executor.query( `SELECT drizzle_hash FROM "${this.config.postgresDoTable}" WHERE drizzle_hash IS NOT NULL AND status = 'applied'` ) // Get all records from Drizzle table const drizzleResult = await executor.query<{ hash: string }>( `SELECT hash FROM "${this.config.journalTable}"` ) const drizzleHashes = new Set(drizzleResult.rows.map((r) => r.hash)) // Add any missing hashes to Drizzle table let synced = 0 for (const row of pdoResult.rows) { if (row.drizzle_hash && !drizzleHashes.has(row.drizzle_hash)) { await executor.query( `INSERT INTO "${this.config.journalTable}" (hash) VALUES ($1) ON CONFLICT (hash) DO NOTHING`, [row.drizzle_hash] ) synced++ } } this.log(`Synced ${synced} migration records`) return true } catch (error) { this.log('Journal sync failed:', error) return false } } /** * Force re-run migrations */ async forceMigrate(executor: QueryExecutor): Promise { this.state = 'idle' this.migrationPromise = null this.lastResult = null this.lastError = null return this.ensureMigrated(executor) } /** * Get migration status */ async getStatus(executor: QueryExecutor): Promise<{ initialized: boolean appliedCount: number pendingCount: number appliedMigrations: Array<{ id: string; version: number; tag?: string | undefined }> pendingMigrations: Array<{ id: string; version: number; tag?: string | undefined }> journalsSynced: boolean }> { try { await this.initializeTables(executor) const { postgresDoVersions, drizzleHashes } = await this.getAppliedMigrations(executor) const allMigrations = this.getMigrations() const appliedMigrations = allMigrations .filter((m) => postgresDoVersions.has(m.version)) .map((m) => ({ id: m.id, version: m.version, tag: m.drizzle?.tag })) const pendingMigrations = allMigrations .filter((m) => !postgresDoVersions.has(m.version)) .map((m) => ({ id: m.id, version: m.version, tag: m.drizzle?.tag })) // Check if journals are in sync const allDrizzleHashesInSync = allMigrations .filter((m) => postgresDoVersions.has(m.version)) .every((m) => m.drizzle?.hash && drizzleHashes.has(m.drizzle.hash)) return { initialized: true, appliedCount: appliedMigrations.length, pendingCount: pendingMigrations.length, appliedMigrations, pendingMigrations, journalsSynced: allDrizzleHashesInSync, } } catch { const allMigrations = this.getMigrations() return { initialized: false, appliedCount: 0, pendingCount: allMigrations.length, appliedMigrations: [], pendingMigrations: allMigrations.map((m) => ({ id: m.id, version: m.version, tag: m.drizzle?.tag })), journalsSynced: false, } } } /** * Check if there are pending migrations */ async needsMigration(executor: QueryExecutor): Promise { const status = await this.getStatus(executor) return status.pendingCount > 0 } /** * Rollback the last migration */ async rollbackLast(executor: QueryExecutor): Promise<{ success: boolean migration?: PostgresDoMigration error?: string }> { const migrations = this.getMigrations() // Get current status const status = await this.getStatus(executor) if (status.appliedMigrations.length === 0) { return { success: false, error: 'No migrations to rollback' } } // Find the last applied migration const lastApplied = status.appliedMigrations[status.appliedMigrations.length - 1] const migration = migrations.find((m) => m.id === lastApplied?.id) if (!migration) { return { success: false, error: `Migration ${lastApplied?.id} not found in registry` } } if (!migration.down) { return { success: false, error: `Migration ${migration.id} has no down SQL - cannot rollback` } } try { this.log(`Rolling back migration ${migration.id}`) // Execute down SQL const statements = migration.down .split(/;\s*(?=(?:[^']*'[^']*')*[^']*$)/) .map((s) => s.trim()) .filter((s) => s.length > 0) for (const statement of statements) { await executor.query(statement + ';') } // Update status in postgres.do table await executor.query( `UPDATE "${this.config.postgresDoTable}" SET status = 'rolled_back', applied_at = CURRENT_TIMESTAMP WHERE id = $1`, [migration.id] ) // Remove from Drizzle table if (migration.drizzle?.hash) { await executor.query( `DELETE FROM "${this.config.journalTable}" WHERE hash = $1`, [migration.drizzle.hash] ) } this.log(`Rolled back ${migration.id}`) return { success: true, migration } } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error) return { success: false, migration, error: errorMessage } } } /** * Rollback to a specific version */ async rollbackToVersion(executor: QueryExecutor, targetVersion: number): Promise<{ success: boolean rolledBack: string[] error?: string | undefined }> { const rolledBack: string[] = [] const status = await this.getStatus(executor) // Get migrations to rollback (in reverse order) const toRollback = status.appliedMigrations .filter((m) => m.version > targetVersion) .sort((a, b) => b.version - a.version) for (const migration of toRollback) { const result = await this.rollbackLast(executor) if (!result.success) { return { success: false, rolledBack, error: result.error, } } rolledBack.push(migration.id) } return { success: true, rolledBack } } } /** * Create an auto Drizzle migrator instance * * @example * ```typescript * import { createAutoDrizzleMigrator } from 'postgres.do/drizzle' * import bundledMigrations from './drizzle-bundle' * * const migrator = createAutoDrizzleMigrator({ * drizzle: bundledMigrations, * autoRun: true, * onProgress: (event) => console.log(`${event.phase}: ${event.migration.tag}`), * }) * * // In your DO * await migrator.ensureMigrated(pglite) * ``` */ export function createAutoDrizzleMigrator(config: AutoDrizzleMigratorConfig): AutoDrizzleMigrator { return new AutoDrizzleMigrator(config) }