import { getDatabasePool, closeDatabasePool } from "./db"; export async function runSql(sql: string, params: unknown[] = []) { return getDatabasePool().query(sql, params); } // --------------------------------------------------------------------------- // Query builder that mimics the Supabase JS client API surface. // Supported: .from().select().insert().update().delete().upsert() // .eq() .in() .neq() .lt() .gte() .or() .contains() .is() .not() // .order() .limit() .single() .maybeSingle() // { count: "exact", head: true } // --------------------------------------------------------------------------- interface Filter { kind: "eq" | "in" | "neq" | "lt" | "gte" | "contains" | "is" | "not"; column: string; value: unknown; } interface OrFilter { kind: "or"; clause: string; } type Where = Filter | OrFilter; class QueryBuilder { private table: string; private action: "select" | "insert" | "update" | "delete" | "upsert" = "select"; private selectCols: string = "*"; private insertRows: Record[] = []; private updateData: Record = {}; private wheres: Where[] = []; private orders: { column: string; asc: boolean; nullsFirst?: boolean }[] = []; private limitCount?: number; private wantSingle = false; private wantMaybeSingle = false; private wantCount = false; private wantHead = false; private upsertConflict = ""; private upsertIgnoreDuplicates = false; private wantReturning = false; constructor(table: string) { this.table = table; } select(cols?: string, opts?: { count?: string; head?: boolean }) { this.selectCols = cols ?? "*"; if (opts?.count === "exact") this.wantCount = true; if (opts?.head) this.wantHead = true; if (this.action === "insert" || this.action === "update" || this.action === "delete" || this.action === "upsert") { this.wantReturning = true; return this; } this.action = "select"; return this; } insert(rows: Record | Record[]) { this.action = "insert"; this.insertRows = Array.isArray(rows) ? rows : [rows]; return this; } update(data: Record) { this.action = "update"; this.updateData = data; return this; } delete() { this.action = "delete"; return this; } upsert( rows: Record | Record[], opts?: { onConflict?: string; ignoreDuplicates?: boolean }, ) { this.action = "upsert"; this.insertRows = Array.isArray(rows) ? rows : [rows]; if (opts?.onConflict) this.upsertConflict = opts.onConflict; if (opts?.ignoreDuplicates) this.upsertIgnoreDuplicates = true; return this; } eq(column: string, value: unknown) { this.wheres.push({ kind: "eq", column, value }); return this; } in(column: string, values: unknown[]) { this.wheres.push({ kind: "in", column, value: values }); return this; } neq(column: string, value: unknown) { this.wheres.push({ kind: "neq", column, value }); return this; } lt(column: string, value: unknown) { this.wheres.push({ kind: "lt", column, value }); return this; } gte(column: string, value: unknown) { this.wheres.push({ kind: "gte", column, value }); return this; } or(clause: string) { this.wheres.push({ kind: "or", clause }); return this; } contains(column: string, value: unknown) { this.wheres.push({ kind: "contains", column, value }); return this; } is(column: string, value: unknown) { this.wheres.push({ kind: "is", column, value }); return this; } not(column: string, op: string, value: unknown) { this.wheres.push({ kind: "not", column, value: { op, value } }); return this; } order(column: string, opts?: { ascending?: boolean; nullsFirst?: boolean }) { this.orders.push({ column, asc: opts?.ascending ?? true, nullsFirst: opts?.nullsFirst }); return this; } limit(n: number) { this.limitCount = n; return this; } single() { this.wantSingle = true; return this.execute(); } maybeSingle() { this.wantMaybeSingle = true; return this.execute(); } then(onResolve: (r: any) => void, onReject?: (e: any) => void) { return this.execute().then(onResolve, onReject); } // --------------------------------------------------------------------------- // SQL generation // --------------------------------------------------------------------------- private buildWhere(): { sql: string; params: unknown[] } { const parts: string[] = []; const params: unknown[] = []; let idx = 0; for (const w of this.wheres) { if (w.kind === "eq") { parts.push(`${ident(w.column)} = $${++idx}`); params.push(w.value); } else if (w.kind === "in") { const vals = w.value as unknown[]; const placeholders = vals.map(() => `$${++idx}`).join(", "); parts.push(`${ident(w.column)} IN (${placeholders})`); params.push(...vals); } else if (w.kind === "neq") { parts.push(`${ident(w.column)} != $${++idx}`); params.push(w.value); } else if (w.kind === "lt") { parts.push(`${ident(w.column)} < $${++idx}`); params.push(w.value); } else if (w.kind === "gte") { parts.push(`${ident(w.column)} >= $${++idx}`); params.push(w.value); } else if (w.kind === "contains") { parts.push(`${ident(w.column)} @> $${++idx}::jsonb`); params.push(JSON.stringify(w.value)); } else if (w.kind === "is") { if (w.value === null) { parts.push(`${ident(w.column)} IS NULL`); } else { parts.push(`${ident(w.column)} IS $${++idx}`); params.push(w.value); } } else if (w.kind === "not") { const { op, value } = w.value as { op: string; value: unknown }; if (op === "is" && value === null) { parts.push(`${ident(w.column)} IS NOT NULL`); } else { parts.push(`NOT (${ident(w.column)} = $${++idx})`); params.push(value); } } else if (w.kind === "or") { const orParts: string[] = []; // Robust split: only split on commas that are not inside parentheses let depth = 0; let currentSeg = ""; const segments: string[] = []; for (const char of w.clause) { if (char === "(") depth++; else if (char === ")") depth--; if (char === "," && depth === 0) { segments.push(currentSeg); currentSeg = ""; } else { currentSeg += char; } } if (currentSeg) segments.push(currentSeg); for (const s of segments) { const trimmed = s.trim(); if (!trimmed) continue; // Check for .in.(...) const inMatch = trimmed.match(/^(.+?)\.in\.\((.*)\)$/); if (inMatch) { const col = inMatch[1]; const inVals = inMatch[2] .split(",") .map((v) => v.trim()) .filter(Boolean); const inPlaceholders = inVals.map(() => `$${++idx}`).join(", "); orParts.push(`${ident(col)} IN (${inPlaceholders})`); params.push(...inVals); continue; } // Check for simple .eq. .neq. etc const dotParts = trimmed.split("."); if (dotParts.length >= 3) { const col = dotParts[0]; const op = dotParts[1]; const valStr = dotParts.slice(2).join("."); const val = valStr === "null" ? null : valStr; if (op === "eq") orParts.push(`${ident(col)} = $${++idx}`); else if (op === "neq") orParts.push(`${ident(col)} != $${++idx}`); else orParts.push(`${ident(col)} ${op} $${++idx}`); params.push(val); } } if (orParts.length > 0) { parts.push(`(${orParts.join(" OR ")})`); } } } return { sql: parts.length ? " WHERE " + parts.join(" AND ") : "", params }; } private buildSql(): { sql: string; params: unknown[] } { const where = this.buildWhere(); const params = [...where.params]; if (this.action === "select") { if (this.wantCount) { return { sql: `SELECT count(*)::int as count FROM ${ident(this.table)}${where.sql}`, params, }; } const cols = this.wantHead ? "1" : this.selectCols; return { sql: `SELECT ${cols} FROM ${ident(this.table)}${where.sql}${this.buildOrderBy()}${this.limitCount ? ` LIMIT ${this.limitCount}` : ""}`, params, }; } if (this.action === "insert") { let paramIdx = params.length; const rows = this.insertRows; if (rows.length === 0) { throw new Error("insert requires at least one row"); } const cols = Array.from( new Set(rows.flatMap((row) => Object.keys(row))), ); if (cols.length === 0) { throw new Error("insert requires at least one column"); } const valuesSql = rows .map((row) => { const placeholders = cols.map((column) => { const placeholder = `$${++paramIdx}`; params.push(toSqlValue(row[column])); return placeholder; }); return `(${placeholders.join(", ")})`; }) .join(", "); const returning = this.selectCols || "*"; return { sql: `INSERT INTO ${ident(this.table)} (${cols.map(ident).join(", ")}) VALUES ${valuesSql} RETURNING ${returning}`, params, }; } if (this.action === "update") { let paramIdx = params.length; const setCols: string[] = []; for (const [k, v] of Object.entries(this.updateData)) { setCols.push(`${ident(k)} = $${++paramIdx}`); params.push(toSqlValue(v)); } const returning = this.selectCols || "*"; return { sql: `UPDATE ${ident(this.table)} SET ${setCols.join(", ")}${where.sql} RETURNING ${returning}`, params, }; } if (this.action === "delete") { const returning = this.selectCols || "*"; return { sql: `DELETE FROM ${ident(this.table)}${where.sql} RETURNING ${returning}`, params, }; } if (this.action === "upsert") { let paramIdx = params.length; const rows = this.insertRows; if (rows.length === 0) { throw new Error("upsert requires at least one row"); } const cols = Array.from( new Set(rows.flatMap((row) => Object.keys(row))), ); if (cols.length === 0) { throw new Error("upsert requires at least one column"); } const valuesSql = rows .map((row) => { const placeholders = cols.map((column) => { const placeholder = `$${++paramIdx}`; params.push(toSqlValue(row[column])); return placeholder; }); return `(${placeholders.join(", ")})`; }) .join(", "); const returning = this.selectCols || "*"; let sql = `INSERT INTO ${ident(this.table)} (${cols.map(ident).join(", ")}) VALUES ${valuesSql}`; if (this.upsertConflict) { const conflictColumnNames = this.upsertConflict .split(",") .map((c) => c.trim()) .filter(Boolean); const conflictCols = conflictColumnNames.map(ident); if (this.upsertIgnoreDuplicates) { sql += ` ON CONFLICT (${conflictCols.join(", ")}) DO NOTHING`; } else { const updateCols = cols .filter((c) => !conflictColumnNames.includes(c)) .map((c) => `${ident(c)} = EXCLUDED.${ident(c)}`); const fallbackUpdateColumn = conflictColumnNames.find((c) => cols.includes(c)) ?? cols[0]; const fallbackUpdate = `${ident(fallbackUpdateColumn)} = EXCLUDED.${ident(fallbackUpdateColumn)}`; sql += ` ON CONFLICT (${conflictCols.join(", ")}) DO UPDATE SET ${updateCols.join(", ") || fallbackUpdate}`; } } sql += ` RETURNING ${returning}`; return { sql, params }; } throw new Error(`Unsupported action: ${this.action}`); } private buildOrderBy(): string { if (!this.orders.length) return ""; return " ORDER BY " + this.orders.map((o) => { let s = `${ident(o.column)} ${o.asc ? "ASC" : "DESC"}`; if (o.nullsFirst === true) s += " NULLS FIRST"; if (o.nullsFirst === false) s += " NULLS LAST"; return s; }).join(", "); } private async execute(): Promise<{ data: any | null; error: any | null; count?: number }> { try { const { sql, params } = this.buildSql(); const result = await getDatabasePool().query(sql, params); if (this.wantCount && this.wantHead) { const count = result.rows[0]?.count ?? 0; return { data: null, count, error: null }; } if (this.wantSingle) { if (result.rows.length === 0) { return { data: null, error: { message: "No rows found", code: "PGRST116" } }; } if (result.rows.length > 1) { return { data: null, error: { message: "Multiple rows found", code: "PGRST116" } }; } return { data: result.rows[0], error: null }; } if (this.wantMaybeSingle) { return { data: result.rows[0] || null, error: null }; } return { data: result.rows, error: null }; } catch (err: any) { console.error("DB shim error:", err.message); return { data: null, error: { message: err.message, code: err.code } }; } } } function ident(name: string): string { return `"${name.replace(/"/g, '""')}"`; } function toSqlValue(val: unknown): unknown { if (val === undefined) return null; if (typeof val === "object" && val !== null) return JSON.stringify(val); return val; } // --------------------------------------------------------------------------- // Fake Supabase client + auth // --------------------------------------------------------------------------- function from(table: string) { return new QueryBuilder(table); } function fakeAuthAdmin() { return { listUsers: async () => { const result = await getDatabasePool().query( "select id, email from auth.users order by id", ); return { data: { users: result.rows.map((row) => ({ id: row.id, email: row.email, user_metadata: {}, })), }, error: null, }; }, deleteUser: async (userId: string) => { await getDatabasePool().query("delete from auth.users where id = $1", [ userId, ]); return { data: null, error: null }; }, }; } export function createServerSupabase() { return { from, auth: { admin: fakeAuthAdmin() } } as any; } export async function getUserIdFromRequest(_req: any): Promise { return "dev-user-001"; } export async function closeServerSupabasePool() { await closeDatabasePool(); }