/** * Generic Postgres export (Neon, or any plain Postgres) as plain SQL text, * fed through Scribase's own `POST .../migrations` endpoint rather than a * direct `pg_restore` connection. * * Real constraint found while building this tool (verified live, see * README "Why not pg_restore straight into Scribase"): `environments.connection()` * returns an internal hostname (documented SERVER-SIDE ONLY in the SDK) that * does not resolve outside Scribase's own network — there is currently no * public Postgres TCP endpoint to `pg_restore`/`psql` into directly, unlike * Supabase or Neon. The one real, working path for a repeatable, lint-safe * migration is the same HTTPS path `scribase migrate` itself uses: schema * DDL and data as plain SQL text, sent through `client.migrations.run()`. */ import { spawn } from 'node:child_process'; import { existsSync } from 'node:fs'; import pg from 'pg'; const { Client } = pg; /** * `pg_dump`'s major version must match the source server's, not just be * ≥ it. Verified live: dumping a Postgres 16 source with `pg_dump` 17 * produces syntax Postgres 16 itself rejects * (`ALTER TABLE ... ALTER COLUMN ... SET (pg_attver=…)`, a PG17-only * column-storage attribute pg_dump 17 emits even for an older source) — * `pg_dump`'s usual "can dump older servers" guarantee does not cover every * statement it chooses to emit. This probes the source's major version and * prefers an exact-version Homebrew keg (`postgresql@`) over * whatever `pg_dump` happens to be on PATH. */ export async function pgDumpBinaryFor(sourceUrl) { const client = new Client({ connectionString: sourceUrl }); await client.connect(); let major; try { const result = await client.query('show server_version_num'); major = Math.floor(Number(result.rows[0].server_version_num) / 10000); } finally { await client.end(); } const candidates = [ `/opt/homebrew/opt/postgresql@${major}/bin/pg_dump`, `/usr/lib/postgresql/${major}/bin/pg_dump`, `/usr/local/opt/postgresql@${major}/bin/pg_dump`, ]; const exact = candidates.find((path) => existsSync(path)); return { binary: exact ?? 'pg_dump', major, exactMatch: Boolean(exact) }; } function spawnCapture(cmd, args) { return new Promise((resolve, reject) => { const child = spawn(cmd, args, { stdio: ['ignore', 'pipe', 'pipe'] }); const chunks = []; let stderr = ''; child.stdout.on('data', (c) => chunks.push(c)); child.stderr.on('data', (c) => { stderr += c.toString(); }); child.on('error', (error) => { if (error.code === 'ENOENT') { reject( new Error( `"${cmd}" was not found. This source needs the Postgres client tools: ` + 'install them (macOS: brew install libpq or postgresql; Debian/Ubuntu: apt install postgresql-client) and re-run.', ), ); return; } reject(error); }); child.on('close', (code) => resolve({ stdout: Buffer.concat(chunks).toString('utf8'), stderr, code })); }); } /** Strips psql-only meta-commands (`\restrict`/`\unrestrict`, pg16+ dumps) and session `SET`s. */ function cleanDumpSql(text, { dropCreateSchema } = {}) { const lines = text.split('\n').filter((line) => { if (line.startsWith('\\restrict') || line.startsWith('\\unrestrict')) return false; if (line.startsWith('SET ') || line.startsWith('SELECT pg_catalog.set_config')) return false; // Version banners change with every minor release of the server or of // pg_dump; left in, an otherwise identical re-run produces different // bytes and can no longer be recognised as the step already applied. if (line.startsWith('-- Dumped from database version') || line.startsWith('-- Dumped by pg_dump version')) return false; if (dropCreateSchema) { const schema = dropCreateSchema; if (line.trim() === `CREATE SCHEMA ${schema};`) return false; if (line.startsWith(`COMMENT ON SCHEMA ${schema}`)) return false; } return true; }); return lines.join('\n').replace(/\n{3,}/g, '\n\n').trim(); } /** * `pg_dump --schema-only` for one schema, cleaned to plain SQL. Picks a * `pg_dump` matching the source's own major version when one is * installed (see {@link pgDumpBinaryFor}). */ export async function dumpSchemaSql(sourceUrl, schema, { schemaExists = false, onLog } = {}) { const { binary, major, exactMatch } = await pgDumpBinaryFor(sourceUrl); if (!exactMatch) { onLog?.( `warning: no pg_dump for Postgres ${major} found (install \`postgresql@${major}\` via Homebrew/apt); ` + `falling back to "${binary}" on PATH — this can emit syntax the source/target reject`, ); } const result = await spawnCapture(binary, [ sourceUrl, '--schema-only', '--schema', schema, '--no-owner', '--no-privileges', '--no-acl', ]); if (result.code !== 0) { throw new Error(`pg_dump --schema-only failed (exit ${result.code}): ${result.stderr.slice(0, 2000)}`); } return cleanDumpSql(result.stdout, schemaExists ? { dropCreateSchema: schema } : {}); } /** `pg_dump --data-only --inserts --column-inserts` for one schema, cleaned to plain SQL. */ export async function dumpDataSql(sourceUrl, schema, { onLog } = {}) { const { binary, major, exactMatch } = await pgDumpBinaryFor(sourceUrl); if (!exactMatch) { onLog?.(`warning: no pg_dump for Postgres ${major} found; falling back to "${binary}" on PATH`); } const result = await spawnCapture(binary, [ sourceUrl, '--data-only', '--inserts', '--column-inserts', '--schema', schema, '--no-owner', '--no-privileges', '--no-acl', ]); if (result.code !== 0) { throw new Error(`pg_dump --data-only failed (exit ${result.code}): ${result.stderr.slice(0, 2000)}`); } return cleanDumpSql(result.stdout); } /** Row count of one table on a directly reachable Postgres (the source). */ export async function countRowsDirect(connectionString, qualifiedTable) { const client = new Client({ connectionString }); await client.connect(); try { const result = await client.query(`select count(*)::bigint as n from ${qualifiedTable}`); return Number(result.rows[0].n); } finally { await client.end(); } } const AUTH_USER_COLUMNS = [ 'id', 'instance_id', 'aud', 'role', 'email', 'encrypted_password', 'email_confirmed_at', 'raw_app_meta_data', 'raw_user_meta_data', 'created_at', 'updated_at', ]; /** * GoTrue's Go driver scans these `varchar` columns as a plain `string`, not * a nullable type — a `NULL` (Postgres's own column default when a value is * never set) makes every later login 500 with * `converting NULL to string is unsupported` (verified live; this is the * same well-known pitfall anyone hand-writing `insert into auth.users` * hits). They are forced to `''` here rather than copied from the source, * since a fresh migrated account has no pending confirmation/recovery flow * anyway. */ const AUTH_USER_EMPTY_STRING_COLUMNS = [ 'confirmation_token', 'recovery_token', 'email_change_token_new', 'email_change', 'email_change_token_current', 'phone_change', 'phone_change_token', 'reauthentication_token', ]; function sqlLiteral(value) { if (value === null || value === undefined) return 'null'; if (typeof value === 'number') return String(value); if (value instanceof Date) return `'${value.toISOString()}'`; if (typeof value === 'object') return `'${JSON.stringify(value).replace(/'/g, "''")}'::jsonb`; return `'${String(value).replace(/'/g, "''")}'`; } /** * Reads `auth.users` from a directly-reachable source (one that has its own * GoTrue-shaped auth table — common for a self-hosted Supabase stack moved * onto Neon) and returns an `INSERT ... ON CONFLICT` SQL text for the * stable column set both sides share. `encrypted_password` is copied * byte-for-byte, so an existing password keeps working. */ export async function dumpAuthUsersSql(sourceUrl) { const client = new Client({ connectionString: sourceUrl }); await client.connect(); let rows; try { rows = (await client.query(`select ${AUTH_USER_COLUMNS.join(', ')} from auth.users order by created_at`)).rows; } finally { await client.end(); } if (rows.length === 0) return { sql: '', count: 0 }; const allColumns = [...AUTH_USER_COLUMNS, ...AUTH_USER_EMPTY_STRING_COLUMNS]; const assignments = allColumns .filter((c) => c !== 'id') .map((c) => `${c} = excluded.${c}`) .join(',\n '); const statements = rows.map((row) => { const values = [ ...AUTH_USER_COLUMNS.map((c) => sqlLiteral(row[c])), ...AUTH_USER_EMPTY_STRING_COLUMNS.map(() => "''"), ].join(', '); return ( `insert into auth.users (${allColumns.join(', ')}) values (${values})\n` + ` on conflict (id) do update set\n ${assignments};` ); }); return { sql: statements.join('\n'), count: rows.length }; }