Free, Open Source, Self-Hosted WhatsApp API Gateway
TypeScript apibotgatewayself-hostedwhatsappwhatsapp-apiwhatsapp-automationwhatsapp-botwhatsapp-gatewaywhatsapp-gateway-apiwhatsapp-webwhatsapp-web-api
// Colocated in migrations/__tests__/ — the TypeORM migrations glob (`migrations/*{.ts,.js}`) matches
// only direct children, so this subdir is NOT loaded as a migration by the CLI/start:dev.
//
// This migration is Postgres-only (SQLite early-returns), so a real in-memory SQLite DataSource
// cannot exercise the conversion path. We mock the QueryRunner and script its catalog-query responses
// (information_schema.columns, pg_constraint, server_version_num, pg_extension) the way a
// synchronize-built PG schema would answer, then assert the exact DDL sequence.
import { QueryRunner } from 'typeorm';
import { NormalizeSynchronizeUuidColumns1770200000000 } from '../1770200000000-NormalizeSynchronizeUuidColumns';
const PK_TABLES = [
'sessions',
'webhooks',
'messages',
'message_batches',
'templates',
'baileys_stored_messages',
'webhook_delivery_failures',
'conversation_mappings',
'integration_delivery_failures',
];
const SESSION_FKS = [
{ table: 'webhooks', column: 'sessionId' },
{ table: 'templates', column: 'sessionId' },
{ table: 'baileys_stored_messages', column: 'sessionId' },
];
interface SchemaOpts {
type: string;
existingTables?: Set<string>;
/** "table.column" keys that are native uuid on this schema. */
uuidColumns?: Set<string>;
/** "table.column" -> FK constraint names currently referencing sessions(id). */
fkConstraints?: Record<string, string[]>;
versionNum?: number; // server_version_num (150000 = PG15, 120000 = PG12)
extInstalled?: boolean;
}
function makeQueryRunner(opts: SchemaOpts) {
const {
type,
existingTables = new Set([...PK_TABLES, ...SESSION_FKS.map(f => f.table)]),
uuidColumns = new Set<string>(),
fkConstraints = {},
versionNum = 150000,
extInstalled = true,
} = opts;
const query = jest.fn((sql: string, params?: unknown[]) => {
const s = String(sql);
if (/information_schema\.columns/i.test(s)) {
const [table, column] = (params ?? []) as string[];
const isUuid = uuidColumns.has(`${table}.${column}`);
return Promise.resolve([{ udt_name: isUuid ? 'uuid' : 'varchar' }]);
}
if (/pg_constraint/i.test(s)) {
const [table, column] = (params ?? []) as string[];
return Promise.resolve((fkConstraints[`${table}.${column}`] ?? []).map(conname => ({ conname })));
}
if (/server_version_num/i.test(s)) return Promise.resolve([{ num: versionNum }]);
if (/pg_extension/i.test(s)) return Promise.resolve(extInstalled ? [{ ok: 1 }] : []);
return Promise.resolve(undefined);
});
return {
dataSource: { options: { type } },
hasTable: jest.fn((t: string) => Promise.resolve(existingTables.has(t))),
query,
};
}
const sqlOf = (qr: ReturnType<typeof makeQueryRunner>): string[] =>
qr.query.mock.calls.map(call => String((call as unknown[])[0]));
const idxOf = (calls: string[], re: RegExp): number => calls.findIndex(q => re.test(q));
describe('NormalizeSynchronizeUuidColumns migration', () => {
const migration = new NormalizeSynchronizeUuidColumns1770200000000();
// ---- name convention (load-bearing for TypeORM sort + the PG-migrations CI gate) ----
it('class name and .name both end in the 13-digit timestamp 1770200000000', () => {
expect(NormalizeSynchronizeUuidColumns1770200000000.name.endsWith('1770200000000')).toBe(true);
expect(migration.name.endsWith('1770200000000')).toBe(true);
});
// ---- SQLite: absolute no-op ----
it('is a no-op on SQLite — issues no query and never probes tables', async () => {
const qr = makeQueryRunner({ type: 'better-sqlite3', uuidColumns: new Set(['sessions.id']) });
await migration.up(qr as unknown as QueryRunner);
expect(qr.query).not.toHaveBeenCalled();
expect(qr.hasTable).not.toHaveBeenCalled();
});
// ---- healthy Postgres (already varchar): single probe, zero DDL ----
it('on a healthy (varchar) Postgres schema probes only sessions.id once and issues no DDL', async () => {
const qr = makeQueryRunner({ type: 'postgres', uuidColumns: new Set() });
await migration.up(qr as unknown as QueryRunner);
const calls = sqlOf(qr);
expect(calls).toHaveLength(1);
expect(calls[0]).toMatch(/information_schema\.columns/);
expect(calls.some(q => /ALTER TABLE|DROP CONSTRAINT|ADD CONSTRAINT|SET LOCAL/i.test(q))).toBe(false);
});
// ---- full conversion of a synchronize-built (uuid) schema ----
it('converts every uuid PK + session-FK to varchar and recreates the 3 CASCADE FKs', async () => {
const discoveredHashes: Record<string, string[]> = {
'webhooks.sessionId': ['FK_53208331c90e332d3f13780729c'],
'templates.sessionId': ['FK_55c1dfd945d2c552a5313b25460'],
'baileys_stored_messages.sessionId': ['FK_9a8b7c6d5e4f3a2b1c0d9e8f7a6b'],
};
const uuidCols = new Set<string>([
...PK_TABLES.map(t => `${t}.id`),
...SESSION_FKS.map(f => `${f.table}.${f.column}`),
]);
const qr = makeQueryRunner({ type: 'postgres', uuidColumns: uuidCols, fkConstraints: discoveredHashes });
await migration.up(qr as unknown as QueryRunner);
const calls = sqlOf(qr);
// PG13+: no extension touch.
expect(calls.some(q => /CREATE EXTENSION/i.test(q))).toBe(false);
// statement_timeout lifted for the rewrite.
expect(calls.some(q => /SET LOCAL statement_timeout\s*=\s*0/i.test(q))).toBe(true);
// Every discovered sync-hash FK dropped; every canonical FK re-added.
for (const hash of Object.values(discoveredHashes).flat()) {
expect(calls.some(q => new RegExp(`DROP CONSTRAINT IF EXISTS "${hash}"`).test(q))).toBe(true);
}
for (const canon of [
'FK_d209715bb62b12255e825580af6',
'FK_templates_sessionId',
'FK_baileys_stored_messages_sessionId',
]) {
expect(calls.some(q => new RegExp(`ADD CONSTRAINT "${canon}"`).test(q))).toBe(true);
}
// Every PK converted (DROP DEFAULT immediately before its TYPE change), every session-FK converted.
for (const t of PK_TABLES) {
const dropIdx = idxOf(calls, new RegExp(`ALTER TABLE "${t}" ALTER COLUMN "id" DROP DEFAULT`));
const typeIdx = idxOf(calls, new RegExp(`ALTER TABLE "${t}" ALTER COLUMN "id" TYPE varchar USING "id"::text`));
expect(dropIdx).toBeGreaterThanOrEqual(0);
expect(typeIdx).toBeGreaterThan(dropIdx); // DROP DEFAULT precedes ALTER TYPE (uuid default not varchar-coercible)
expect(
calls.some(q =>
new RegExp(`ALTER TABLE "${t}" ALTER COLUMN "id" SET DEFAULT gen_random_uuid\\(\\)::varchar`).test(q),
),
).toBe(true);
}
for (const f of SESSION_FKS) {
expect(
calls.some(q =>
new RegExp(`ALTER TABLE "${f.table}" ALTER COLUMN "${f.column}" TYPE varchar USING "${f.column}"::text`).test(
q,
),
),
).toBe(true);
}
});
// ---- the load-bearing ordering invariant (Test E) ----
it('drops ALL session-FKs BEFORE converting the sessions.id PK they reference', async () => {
const uuidCols = new Set<string>([
...PK_TABLES.map(t => `${t}.id`),
...SESSION_FKS.map(f => `${f.table}.${f.column}`),
]);
const qr = makeQueryRunner({
type: 'postgres',
uuidColumns: uuidCols,
fkConstraints: {
'webhooks.sessionId': ['FK_w'],
'templates.sessionId': ['FK_t'],
'baileys_stored_messages.sessionId': ['FK_b'],
},
});
await migration.up(qr as unknown as QueryRunner);
const calls = sqlOf(qr);
const lastDrop = Math.max(
idxOf(calls, /DROP CONSTRAINT IF EXISTS "FK_w"/),
idxOf(calls, /DROP CONSTRAINT IF EXISTS "FK_t"/),
idxOf(calls, /DROP CONSTRAINT IF EXISTS "FK_b"/),
);
const firstPkType = idxOf(calls, /ALTER TABLE "sessions" ALTER COLUMN "id" TYPE varchar/);
expect(lastDrop).toBeGreaterThanOrEqual(0);
expect(firstPkType).toBeGreaterThan(lastDrop); // cannot ALTER the referenced PK while a uuid FK still points at it
});
// ---- per-column idempotency: a partially-drifted table is skipped for TYPE but still gets its DEFAULT ----
it('skips ALTER TYPE on an already-varchar column but still sets its DEFAULT (partial drift)', async () => {
const uuidCols = new Set<string>([
...PK_TABLES.filter(t => t !== 'messages').map(t => `${t}.id`), // messages.id already varchar
...SESSION_FKS.map(f => `${f.table}.${f.column}`),
]);
const qr = makeQueryRunner({ type: 'postgres', uuidColumns: uuidCols, fkConstraints: {} });
await migration.up(qr as unknown as QueryRunner);
const calls = sqlOf(qr);
expect(calls.some(q => /ALTER TABLE "messages" ALTER COLUMN "id" TYPE varchar/.test(q))).toBe(false);
expect(calls.some(q => /ALTER TABLE "messages" ALTER COLUMN "id" DROP DEFAULT/.test(q))).toBe(false);
// DEFAULT set unconditionally (idempotent) for every existing PK table.
expect(
calls.some(q => /ALTER TABLE "messages" ALTER COLUMN "id" SET DEFAULT gen_random_uuid\(\)::varchar/.test(q)),
).toBe(true);
// other PKs still converted
expect(calls.some(q => /ALTER TABLE "sessions" ALTER COLUMN "id" TYPE varchar/.test(q))).toBe(true);
});
// ---- absent tables (older sync snapshot) are skipped ----
it('skips tables that do not exist (e.g. an older sync snapshot without templates)', async () => {
const uuidCols = new Set<string>(['sessions.id', 'webhooks.id', 'messages.id', 'message_batches.id']);
const qr = makeQueryRunner({
type: 'postgres',
existingTables: new Set(['sessions', 'webhooks', 'messages', 'message_batches']),
uuidColumns: uuidCols,
fkConstraints: { 'webhooks.sessionId': ['FK_w'] },
});
await migration.up(qr as unknown as QueryRunner);
const calls = sqlOf(qr);
expect(calls.some(q => /"templates"/.test(q))).toBe(false);
expect(calls.some(q => /ALTER TABLE "sessions" ALTER COLUMN "id" TYPE varchar/.test(q))).toBe(true);
});
// ---- down(): inverts to native uuid (best-effort; validates via ::uuid cast) ----
it('down() converts varchar back to native uuid and recreates the CASCADE FKs', async () => {
const qr = makeQueryRunner({
type: 'postgres',
uuidColumns: new Set(), // post-up state: all varchar
fkConstraints: {
'webhooks.sessionId': ['FK_d209715bb62b12255e825580af6'],
'templates.sessionId': ['FK_templates_sessionId'],
'baileys_stored_messages.sessionId': ['FK_baileys_stored_messages_sessionId'],
},
});
await migration.down(qr as unknown as QueryRunner);
const calls = sqlOf(qr);
expect(calls.some(q => /ALTER TABLE "sessions" ALTER COLUMN "id" TYPE uuid USING "id"::uuid/.test(q))).toBe(true);
for (const f of SESSION_FKS) {
expect(
calls.some(q =>
new RegExp(`ALTER TABLE "${f.table}" ALTER COLUMN "${f.column}" TYPE uuid USING "${f.column}"::uuid`).test(q),
),
).toBe(true);
}
for (const canon of [
'FK_d209715bb62b12255e825580af6',
'FK_templates_sessionId',
'FK_baileys_stored_messages_sessionId',
]) {
expect(calls.some(q => new RegExp(`ADD CONSTRAINT "${canon}"`).test(q))).toBe(true);
}
});
it('down() is a no-op on SQLite', async () => {
const qr = makeQueryRunner({ type: 'better-sqlite3' });
await migration.down(qr as unknown as QueryRunner);
expect(qr.query).not.toHaveBeenCalled();
});
});