Spaces:
Runtime error
Runtime error
File size: 10,352 Bytes
46252cd | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | import { MigrationInterface, QueryRunner } from 'typeorm';
/**
* Normalizes a PostgreSQL schema previously bootstrapped with DATABASE_SYNCHRONIZE=true.
*
* Under synchronize, `@PrimaryGeneratedColumn('uuid')` creates NATIVE uuid id/FK columns, and every
* `@ManyToOne(() => Session)` FK column is derived as uuid too. The canonical migration chain, however,
* builds the schema with varchar ids. The two strategies are not interchangeable on Postgres:
* `AddUuidDefaultsForPostgres` (1779235200000) is the first migration to touch a uuid column — its
* `gen_random_uuid()::varchar` DEFAULT on a uuid column is rejected — and later CREATE TABLE migrations
* (AddTemplates, AddBaileysStoredMessages) cannot add a varchar FK referencing the uuid sessions(id).
* Because `migrationsRun: true` is hardcoded for the Postgres data connection, this crash-loops boot.
*
* Ordered immediately after the baseline (1770108659848) and before that first collision, this migration
* converts the 9 generated-uuid PKs + 3 session-FK columns to varchar in lockstep (dropping/recreating
* the CASCADE FKs), then sets the `gen_random_uuid()::varchar` DEFAULTs. It is a no-op on SQLite and on
* an already-varchar (healthy) Postgres schema via a single information_schema probe.
*
* See issue #690. Operational note: `ALTER COLUMN ... TYPE varchar` is a full-table rewrite under an
* ACCESS EXCLUSIVE lock; for very large `messages` tables, run via the CLI (`npm run migration:run`)
* against the stopped app during a maintenance window.
*/
export class NormalizeSynchronizeUuidColumns1770200000000 implements MigrationInterface {
name = 'NormalizeSynchronizeUuidColumns1770200000000';
// The 9 generated-uuid PKs on the DATA connection. api_keys/audit_logs live on the sqlite 'main'
// connection (immune). lid_mappings/ingress_events/plugin_instances use a varchar @PrimaryColumn by
// design — do NOT add them here.
private readonly uuidPkTables = [
'sessions',
'webhooks',
'messages',
'message_batches',
'templates',
'baileys_stored_messages',
'webhook_delivery_failures',
'conversation_mappings',
'integration_delivery_failures',
];
// The 3 @ManyToOne(() => Session) FK-bearing tables. `name` is the CANONICAL re-add name matching the
// hasTable-guarded CREATE TABLE migrations (which no-op on a sync-built DB, so they do NOT re-add the
// FK — this migration owns it). The DROP name is DISCOVERED at runtime: synchronize names constraints
// with a TypeORM hash, not these canonical names.
private readonly sessionFks = [
{ table: 'webhooks', column: 'sessionId', name: 'FK_d209715bb62b12255e825580af6' },
{ table: 'templates', column: 'sessionId', name: 'FK_templates_sessionId' },
{ table: 'baileys_stored_messages', column: 'sessionId', name: 'FK_baileys_stored_messages_sessionId' },
];
public async up(queryRunner: QueryRunner): Promise<void> {
if (queryRunner.dataSource.options.type !== 'postgres') return;
// Gate: synchronize builds the WHOLE schema atomically (all-uuid, or synchronize itself errors), so a
// single representative probe on sessions.id is sufficient to detect a drifted schema. Per-column
// guards inside the loops below add defense-in-depth for the (non-reachable) partial-drift case.
if (!(await this.columnIsUuid(queryRunner, 'sessions', 'id'))) return;
await queryRunner.query(`SET LOCAL statement_timeout = 0`);
await this.ensureGenRandomUuid(queryRunner);
// 1. Drop FKs referencing sessions(id) FIRST — a uuid FK blocks ALTER of the referenced PK.
for (const fk of this.sessionFks) {
if (!(await queryRunner.hasTable(fk.table))) continue;
for (const c of await this.fkConstraintNames(queryRunner, fk.table, fk.column, 'sessions')) {
await queryRunner.query(`ALTER TABLE "${fk.table}" DROP CONSTRAINT IF EXISTS "${c}"`);
}
}
// 2. PK uuid -> varchar. DROP DEFAULT first: gen_random_uuid() (native uuid) is not assignment-coercible
// to varchar, so Postgres rejects ALTER TYPE while the default is still attached.
for (const t of this.uuidPkTables) {
if (!(await queryRunner.hasTable(t))) continue;
if (!(await this.columnIsUuid(queryRunner, t, 'id'))) continue; // per-column idempotency
await queryRunner.query(`ALTER TABLE "${t}" ALTER COLUMN "id" DROP DEFAULT`);
await queryRunner.query(`ALTER TABLE "${t}" ALTER COLUMN "id" TYPE varchar USING "id"::text`);
}
// 3. Session-FK columns uuid -> varchar.
for (const fk of this.sessionFks) {
if (!(await queryRunner.hasTable(fk.table))) continue;
if (!(await this.columnIsUuid(queryRunner, fk.table, fk.column))) continue;
await queryRunner.query(
`ALTER TABLE "${fk.table}" ALTER COLUMN "${fk.column}" TYPE varchar USING "${fk.column}"::text`,
);
}
// 4. Recreate the 3 CASCADE FKs with canonical names.
for (const fk of this.sessionFks) {
if (!(await queryRunner.hasTable(fk.table))) continue;
await queryRunner.query(
`ALTER TABLE "${fk.table}" ADD CONSTRAINT "${fk.name}" ` +
`FOREIGN KEY ("${fk.column}") REFERENCES "sessions" ("id") ON DELETE CASCADE ON UPDATE NO ACTION`,
);
}
// 5. gen_random_uuid()::varchar DEFAULT on every existing PK. Mandatory for tables whose CREATE TABLE
// migration no-op'd on the sync-built DB; idempotent SET DEFAULT for the rest.
for (const t of this.uuidPkTables) {
if (!(await queryRunner.hasTable(t))) continue;
await queryRunner.query(`ALTER TABLE "${t}" ALTER COLUMN "id" SET DEFAULT gen_random_uuid()::varchar`);
}
}
public async down(queryRunner: QueryRunner): Promise<void> {
// Best-effort inverse (varchar -> native uuid); only meaningful to re-enable synchronize. USING id::uuid
// validates every value — Postgres aborts on non-uuid strings (no silent corruption).
if (queryRunner.dataSource.options.type !== 'postgres') return;
if (await this.columnIsUuid(queryRunner, 'sessions', 'id')) return; // already native uuid: nothing to revert
await queryRunner.query(`SET LOCAL statement_timeout = 0`);
await this.ensureGenRandomUuid(queryRunner);
// Drop FKs (discovered, same as up — robust to either hash or canonical names).
for (const fk of this.sessionFks) {
if (!(await queryRunner.hasTable(fk.table))) continue;
for (const c of await this.fkConstraintNames(queryRunner, fk.table, fk.column, 'sessions')) {
await queryRunner.query(`ALTER TABLE "${fk.table}" DROP CONSTRAINT IF EXISTS "${c}"`);
}
}
for (const t of this.uuidPkTables) {
if (!(await queryRunner.hasTable(t))) continue;
await queryRunner.query(`ALTER TABLE "${t}" ALTER COLUMN "id" DROP DEFAULT`);
await queryRunner.query(`ALTER TABLE "${t}" ALTER COLUMN "id" TYPE uuid USING "id"::uuid`);
}
for (const fk of this.sessionFks) {
if (!(await queryRunner.hasTable(fk.table))) continue;
await queryRunner.query(
`ALTER TABLE "${fk.table}" ALTER COLUMN "${fk.column}" TYPE uuid USING "${fk.column}"::uuid`,
);
}
for (const fk of this.sessionFks) {
if (!(await queryRunner.hasTable(fk.table))) continue;
await queryRunner.query(
`ALTER TABLE "${fk.table}" ADD CONSTRAINT "${fk.name}" ` +
`FOREIGN KEY ("${fk.column}") REFERENCES "sessions" ("id") ON DELETE CASCADE ON UPDATE NO ACTION`,
);
}
for (const t of this.uuidPkTables) {
if (!(await queryRunner.hasTable(t))) continue;
await queryRunner.query(`ALTER TABLE "${t}" ALTER COLUMN "id" SET DEFAULT gen_random_uuid()`);
}
}
// --- helpers ---
private async columnIsUuid(queryRunner: QueryRunner, table: string, column: string): Promise<boolean> {
const rows = (await queryRunner.query(
`SELECT udt_name FROM information_schema.columns ` +
`WHERE table_schema = current_schema() AND table_name = $1 AND column_name = $2`,
[table, column],
)) as { udt_name?: string }[] | undefined;
return rows?.[0]?.udt_name === 'uuid';
}
// Schema-scoped on BOTH sides so a custom POSTGRES_SCHEMA (search_path `<schema>,public`) does not match
// a namesake table in public. current_schema() returns the first existing schema in the path.
private async fkConstraintNames(
queryRunner: QueryRunner,
table: string,
column: string,
referenced: string,
): Promise<string[]> {
const rows = (await queryRunner.query(
`SELECT c.conname FROM pg_constraint c ` +
`JOIN pg_class cl ON c.conrelid = cl.oid JOIN pg_namespace n ON cl.relnamespace = n.oid ` +
`JOIN pg_attribute a ON a.attrelid = cl.oid AND a.attnum = ANY(c.conkey) ` +
`JOIN pg_class ref ON c.confrelid = ref.oid JOIN pg_namespace rn ON ref.relnamespace = rn.oid ` +
`WHERE c.contype = 'f' AND n.nspname = current_schema() AND cl.relname = $1 ` +
`AND a.attname = $2 AND rn.nspname = current_schema() AND ref.relname = $3`,
[table, column, referenced],
)) as { conname: string }[] | undefined;
return (rows ?? []).map(r => r.conname);
}
// gen_random_uuid() is core on PG13+; pgcrypto on <=12. This migration sets varchar DEFAULTs before
// AddUuidDefaultsForPostgres/AddIntegrationUuidDefaults, so it owns the gate (mirrors their logic).
private async ensureGenRandomUuid(queryRunner: QueryRunner): Promise<void> {
const v = (await queryRunner.query(`SELECT current_setting('server_version_num')::int AS num`)) as
{ num?: number | string }[] | undefined;
const num = Number(v?.[0]?.num ?? 0);
if (num > 0 && num < 130000) {
const installed = (await queryRunner.query(`SELECT 1 FROM pg_extension WHERE extname = 'pgcrypto'`)) as
unknown[] | undefined;
if (!installed?.length) {
try {
await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS pgcrypto`);
} catch (err) {
throw new Error(
`PostgreSQL ${num} (< 13) needs the pgcrypto extension for gen_random_uuid(), but it is ` +
`not installed and this database role cannot create it. Have a superuser run ` +
`"CREATE EXTENSION pgcrypto;" once, then restart.`,
{ cause: err },
);
}
}
}
}
}
|