scribase-migrate/lib/postgres.mjs
caelum0x d7bbaf483b fix: resend without an account, working --create-project preview, idempotent re-runs, closing CTA line
- resend no longer demands --to or a Scribase token (it only exports JSON)
- --create-project read --region from argv[0] when the flag was absent
- --create-project --kind preview now creates an empty schema_only env; a
  snapshot failed with source_environment_missing on a new project
- clerk --dry-run created users; neon --dry-run wrote into the real
  supabase/migrations folder
- a second neon run re-added the schema step; identical steps are reused and
  pg_dump version banners are stripped so identical dumps stay identical
- <source> --help, --version, clear errors for a missing pg_dump, a reused
  deleted environment name and a missing token (with the signup link)
- output ends with one plain line pointing at scribase.com/migrate
- README: quickstart that works today (npx from Rithub, not the unpublished
  npm name), real output sample, verified/not-verified split; add LICENSE

Co-Authored-By: Claude Fable 5.2 <noreply@anthropic.com>
2026-11-17 20:10:39 +03:00

237 lines
8.8 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', (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 };
}