mike-backend / backend /src /lib /supabase.ts
bardd's picture
Deploy Mike backend-only Space
06671aa verified
Raw History Blame Contribute Delete
15.1 kB
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<string, unknown>[] = [];
private updateData: Record<string, unknown> = {};
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<string, unknown> | Record<string, unknown>[]) {
this.action = "insert";
this.insertRows = Array.isArray(rows) ? rows : [rows];
return this;
}
update(data: Record<string, unknown>) {
this.action = "update";
this.updateData = data;
return this;
}
delete() {
this.action = "delete";
return this;
}
upsert(
rows: Record<string, unknown> | Record<string, unknown>[],
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<string> {
return "dev-user-001";
}
export async function closeServerSupabasePool() {
await closeDatabasePool();
}