Something went wrong. Try again.
[READ-ONLY] Mirror of https://github.com/nperez0111/bookhive. Track your books, share your shelves, see what others are reading bookhive.buzz
atproto bluesky books bookshelf goodreads management-system
Something went wrong. Try again.
TypeScript
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196/** * One-time data migration script: SQLite → PostgreSQL. * * Usage: * bun run scripts/migrate-sqlite-to-pg.ts --sqlite-db ./db.sqlite --pg-url postgres://localhost:5432/bookhive [--sqlite-kv ./kv.sqlite] * * Migrates all 8 main tables + selected KV tables (identity, follows_sync). * Auth and cache KV tables are skipped (they rebuild organically). */import { Database as DatabaseSync } from "bun:sqlite";import pg from "pg";
const args = process.argv.slice(2);
function getArg(name: string): string | undefined { const idx = args.indexOf(name); return idx >= 0 ? args[idx + 1] : undefined;}
const sqliteDbPath = getArg("--sqlite-db");const pgUrl = getArg("--pg-url");const sqliteKvPath = getArg("--sqlite-kv");
if (!sqliteDbPath || !pgUrl) { console.error("Usage: bun run scripts/migrate-sqlite-to-pg.ts --sqlite-db <path> --pg-url <url> [--sqlite-kv <path>]"); process.exit(1);}
// PostgreSQL has a 65535 parameter limit per query.// Dynamically compute batch size based on column count.function batchSizeForColumns(numCols: number): number { return Math.floor(65000 / numCols);}
async function migrateTable( sqlite: DatabaseSync, pool: pg.Pool, tableName: string, columns: string[], destTableName?: string,) { const dest = destTableName || tableName; const quotedCols = columns.map((c) => `"${c}"`).join(", "); const total = (sqlite.prepare(`SELECT COUNT(*) as cnt FROM "${tableName}"`).get() as any).cnt; console.log(` ${tableName}${dest !== tableName ? ` → ${dest}` : ""}: ${total} rows`);
if (total === 0) return;
const batchSize = batchSizeForColumns(columns.length); let offset = 0; let migrated = 0;
while (offset < total) { const rows = sqlite .prepare(`SELECT ${quotedCols} FROM "${tableName}" LIMIT ${batchSize} OFFSET ${offset}`) .all() as Record<string, unknown>[];
if (rows.length === 0) break;
// Build a multi-row INSERT const placeholders: string[] = []; const values: unknown[] = []; let paramIdx = 1;
for (const row of rows) { const rowPlaceholders: string[] = []; for (const col of columns) { rowPlaceholders.push(`$${paramIdx++}`); values.push(row[col] ?? null); } placeholders.push(`(${rowPlaceholders.join(", ")})`); }
const insertSql = `INSERT INTO "${dest}" (${quotedCols}) VALUES ${placeholders.join(", ")} ON CONFLICT DO NOTHING`; await pool.query(insertSql, values);
migrated += rows.length; offset += rows.length;
if (migrated % 50000 === 0) { console.log(` ... ${migrated}/${total}`); } }
console.log(` done: ${migrated} rows migrated`);}
async function getTableColumns(sqlite: DatabaseSync, tableName: string): Promise<string[]> { const cols = sqlite.prepare(`PRAGMA table_info("${tableName}")`).all() as Array<{ name: string }>; return cols.map((c) => c.name);}
async function main() { console.log("Opening SQLite database:", sqliteDbPath); const sqlite = new DatabaseSync(sqliteDbPath!); sqlite.exec("PRAGMA busy_timeout = 10000");
console.log("Connecting to PostgreSQL:", pgUrl); const pool = new pg.Pool({ connectionString: pgUrl, max: 5 });
// Test PG connection const client = await pool.connect(); client.release(); console.log("PostgreSQL connected.\n");
// Main tables in dependency order const mainTables = [ "hive_book", "user_book", "buzz", "user_follows", "book_id_map", "hive_book_genre", "book_list", "book_list_item", ];
console.log("=== Migrating main database tables ==="); for (const table of mainTables) { try { let columns = await getTableColumns(sqlite, table); // Skip 'id' column for hive_book_genre (it's SERIAL in PG) if (table === "hive_book_genre") { columns = columns.filter((c) => c !== "rowid"); // The SQLite table doesn't have an 'id' column, PG one does (SERIAL). // Just insert hiveId + genre; PG auto-generates id. } await migrateTable(sqlite, pool, table, columns); } catch (err) { console.error(` ERROR migrating ${table}:`, err); } }
sqlite.close();
// KV tables (optional) if (sqliteKvPath) { console.log("\n=== Migrating KV tables ==="); const kvSqlite = new DatabaseSync(sqliteKvPath); kvSqlite.exec("PRAGMA busy_timeout = 10000");
// Only migrate identity and follows_sync — skip profile/kv caches and auth tables const kvTables: Record<string, string> = { identity: "kv_identity", follows_sync: "kv_follows_sync", };
// Create KV tables in PG if they don't exist for (const destTable of Object.values(kvTables)) { await pool.query(` CREATE TABLE IF NOT EXISTS "${destTable}" ( id TEXT PRIMARY KEY, value TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ) `); }
for (const [srcTable, destTable] of Object.entries(kvTables)) { try { // Check if source table exists const exists = kvSqlite .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`) .get(srcTable) as any; if (!exists) { console.log(` ${srcTable}: table does not exist, skipping`); continue; }
const columns = await getTableColumns(kvSqlite, srcTable); await migrateTable(kvSqlite, pool, srcTable, columns, destTable); } catch (err) { console.error(` ERROR migrating ${srcTable}:`, err); } }
kvSqlite.close(); }
// Verify console.log("\n=== Verification ==="); for (const table of mainTables) { const result = await pool.query(`SELECT COUNT(*) as cnt FROM "${table}"`); console.log(` ${table}: ${result.rows[0].cnt} rows`); }
await pool.end(); console.log("\nMigration complete!");}
main().catch((err) => { console.error("Migration failed:", err); process.exit(1);});