scribase-migrate/lib/postgres.mjs
caelum0x 261ea5dea7 feat: scribase-migrate — one-command migration from Supabase/Neon/Clerk/Resend onto Scribase
Composes Scribase's own shipped APIs (the `scribase` SDK's imports/migrations/
database/environments resources, plus the GoTrue admin API every self-hosted
Supabase migration tool already uses) rather than reinventing a dump format:

- supabase: wraps the real POST /v1/imports one-call migration (Postgres,
  auth.users with hashes, storage).
- neon: pg_dump schema+data as plain SQL, submitted through POST .../migrations
  (environments.connection() is internal-network-only — verified live — so
  this is the only path that actually reaches a Scribase env from outside);
  auto-generates owner-based RLS so the real migration lint's BLOCK on
  RLS-less public tables is satisfied without ever opening a table to every
  user; branches land in branch_<name> schemas.
- clerk: exports users via the real Backend API, creates them via the
  destination's GoTrue admin API with a one-time password + reset flag
  (Clerk never exposes a portable hash on any plan).
- resend: exports sending domains + broadcasts (as templates) to a portable
  JSON bundle; honest about Scribase having no write endpoint for either yet.

Verified live end-to-end against a real local Supabase-shaped Postgres
(Docker, GoTrue-shaped auth.users with a real bcrypt hash) and a real
Scribase project (ereboslab/scribase-migrate-verify-internal): schema+RLS
applied with 0 lint blocks, data row counts matched via a real HTTP query
(2/2 profiles, 3/3 notes), and a real password-grant login returned a real
access_token for the migrated user. Full artifacts in
test-fixtures/verification-run/.

docs/migrate-page.md is a ready-to-paste scribase.com/migrate page.
2026-11-17 14:54:36 +03:00

222 lines
8.1 KiB
JavaScript

/**
* 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@<major>`) 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', reject);
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;
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 };
}