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.
222 lines
8.1 KiB
JavaScript
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 };
|
|
}
|