Monitor websites uptime using Cloudflare Workers
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191import pg from 'pg';
export interface QueryResult<T extends object> { readonly rows: T[]; readonly rowCount: number | null;}
export interface Database { readonly schema: string; query<T extends object = Record<string, unknown>>( sql: string, values?: readonly unknown[], ): Promise<QueryResult<T>>; transaction<T>(callback: (db: Database) => Promise<T>): Promise<T>; table(name: string): string;}
export interface DatabaseScope { readonly schema: string; readonly tables: readonly string[];}
export interface StatementTelemetry { readonly durationMs: number; readonly rowCount: number | null; readonly success: boolean;}
export interface PostgresOptions extends DatabaseScope { readonly connectionString: string; readonly onStatement?: (event: StatementTelemetry) => void;}
export interface PostgresDatabase extends Database { close(): Promise<void>;}
export function quotePostgresIdentifier(identifier: string): string { if (!/^[a-z_][a-z0-9_]{0,62}$/.test(identifier)) { throw new Error('Invalid trusted PostgreSQL identifier'); } return `"${identifier}"`;}
/** Preserve the existing TEXT/0-or-1 row contract without changing SQL dialect. */export function normalizePostgresValue(value: unknown): unknown { if (value === undefined || value === null) return null; if (value instanceof Date) return value.toISOString(); if (typeof value === 'boolean') return value ? 1 : 0; if (Array.isArray(value)) return value.map(normalizePostgresValue); if (value instanceof ArrayBuffer) return Buffer.from(value); if (ArrayBuffer.isView(value)) { return Buffer.from(value.buffer, value.byteOffset, value.byteLength); } if (typeof value === 'object') return JSON.stringify(value); return value;}
export function isPostgresUniqueViolation(error: unknown): boolean { return typeof error === 'object' && error !== null && 'code' in error && error.code === '23505';}
/** Scope identifiers only; the role's grants remain the database security boundary. */export function scopePostgresDatabase(db: Database, scope: DatabaseScope): Database { const schema = quotePostgresIdentifier(scope.schema); const tables = new Set(scope.tables); for (const table of tables) quotePostgresIdentifier(table); return { schema: scope.schema, query: (sql, values) => db.query(sql, values), transaction: (callback) => db.transaction((tx) => callback(scopePostgresDatabase(tx, scope))), table(name) { if (!tables.has(name)) throw new Error('Table is outside the trusted PostgreSQL scope'); return `${schema}.${quotePostgresIdentifier(name)}`; }, };}
/** One client per request. Parallel ordinary statements queue; transactions own the queue. */export async function openPostgresDatabase(options: PostgresOptions): Promise<PostgresDatabase> { quotePostgresIdentifier(options.schema); for (const table of options.tables) quotePostgresIdentifier(table); const client = new pg.Client({ connectionString: options.connectionString, connectionTimeoutMillis: 10_000, query_timeout: 30_000, }); try { await client.connect(); } catch (error) { await client.end().catch(() => undefined); throw error; } let closed = false; let tail: Promise<unknown> = Promise.resolve(); function enqueue<T>(operation: () => Promise<T>): Promise<T> { if (closed) return Promise.reject(new Error('PostgreSQL database is closed')); const pending = tail.then(operation); tail = pending.catch(() => undefined); return pending; } async function execute<T extends object>(sql: string, values: readonly unknown[] = []) { const started = performance.now(); let rowCount: number | null = null; let success = false; try { const response = await client.query<T>(sql, values.map(normalizePostgresValue)); // Native simple-protocol migration scripts return one result per statement. const result = Array.isArray(response) ? (response.at(-1) as pg.QueryResult<T>) : response; if (!result) return { rows: [], rowCount: null }; rowCount = result.rowCount; success = true; return { rows: result.rows, rowCount }; } finally { // Observers cannot break a successful transaction or reveal SQL/parameters. try { options.onStatement?.({ durationMs: performance.now() - started, rowCount, success }); } catch { /* Telemetry is best effort. */ } } } const base: Database = { schema: options.schema, table: () => { throw new Error('Unscoped PostgreSQL table'); }, query: (sql, values) => enqueue(() => execute(sql, values)), transaction: (callback) => enqueue(async () => { await execute('BEGIN'); let active = true; const tx = scopePostgresDatabase( { schema: options.schema, table: base.table, query: (sql, values) => { if (!active) return Promise.reject(new Error('PostgreSQL transaction has ended')); return execute(sql, values); }, transaction: async () => { throw new Error('Nested PostgreSQL transactions are unsupported'); }, }, options, ); try { const result = await callback(tx); await execute('COMMIT'); return result; } catch (error) { try { await execute('ROLLBACK'); } catch { closed = true; await client.end(); } throw error; } finally { active = false; } }), }; return { ...scopePostgresDatabase(base, options), async close() { if (closed) return; closed = true; await tail; await client.end(); }, };}
export interface HyperdriveBinding { readonly connectionString: string;}
export async function withPostgresDatabase<T>( binding: HyperdriveBinding, scope: DatabaseScope, callback: (db: Database) => Promise<T>,): Promise<T> { const db = await openPostgresDatabase({ ...scope, connectionString: binding.connectionString }); try { return await callback(db); } finally { await db.close(); }}