db.ts
From the guide Supabase authentication in TypeScript: two safe ways, with code. Placeholder values only: put your own keys in environment variables, never in the code.
// db.ts: every database call of the app, from the server only, with Supabase's secret key.
// Plain fetch to Supabase's REST API (PostgREST), so short-lived functions have no client or pool to keep alive.
// The secret key's role (service_role) bypasses Row Level Security, so this file must never reach a browser.
// Error messages hold the method, path, status and error code, never row values or the server's free text.
export class DbError extends Error {
readonly status: number;
readonly code: string | undefined;
constructor(message: string, status: number, code?: string) {
super(message);
this.status = status;
this.code = code;
}
}
export interface DbOptions {
/** https://<project-ref>.supabase.co */
url?: string;
/** sb_secret_... (Project Settings > API Keys). Never in a NEXT_PUBLIC_, VITE_ or PUBLIC_ variable. */
secretKey?: string;
fetch?: typeof fetch;
}
type Method = 'GET' | 'POST' | 'PATCH' | 'DELETE';
export function createDb(opts: DbOptions = {}) {
const base = (opts.url ?? process.env.SUPABASE_URL ?? '').replace(/\/$/, '') + '/rest/v1';
const key = opts.secretKey ?? process.env.SUPABASE_SECRET_KEY ?? '';
if (!key) throw new Error('SUPABASE_SECRET_KEY is not set');
// The new secret keys go in the apikey header alone, which is all this file sends: a legacy service_role key is not accepted.
if (!key.startsWith('sb_secret_')) throw new Error('SUPABASE_SECRET_KEY must be a secret key (sb_secret_...)');
const f = opts.fetch ?? fetch;
async function call<T>(method: Method, path: string, o: { query?: Record<string, string>; body?: unknown } = {}): Promise<T> {
const qs = o.query ? '?' + new URLSearchParams(o.query).toString() : '';
const headers: Record<string, string> = { apikey: key };
if (o.body !== undefined) {
headers['Content-Type'] = 'application/json';
headers.Prefer = 'return=representation';
}
let res: Response;
try {
res = await f(base + path + qs, { method, headers, body: o.body === undefined ? undefined : JSON.stringify(o.body) });
} catch {
throw new DbError(`Database request failed: ${method} ${path} -> network`, 0, 'network');
}
if (!res.ok) {
let code: string | undefined;
try {
code = ((await res.json()) as { code?: string }).code;
} catch {
// no JSON body: the status alone is reported
}
throw new DbError(`Database request failed: ${method} ${path} -> ${res.status} ${code ?? ''}`.trim(), res.status, code);
}
const text = await res.text();
return (text ? JSON.parse(text) : undefined) as T;
}
const eq = (v: string): string => `eq.${v}`;
return {
/** The app's user for this sign-in, created the first time. Returns its id (the users table in tables.sql). */
async upsertUser(input: { provider: string; providerUserId: string; email: string | null }): Promise<string> {
const now = new Date().toISOString();
const match = { provider: eq(input.provider), provider_user_id: eq(input.providerUserId) };
const found = await call<{ id: string }[]>('GET', '/users', { query: { ...match, select: 'id', limit: '1' } });
if (found[0]) {
await call('PATCH', '/users', { query: match, body: { last_sign_in_at: now, ...(input.email ? { email: input.email } : {}) } });
return found[0].id;
}
const created = await call<{ id: string }[]>('POST', '/users', {
body: { provider: input.provider, provider_user_id: input.providerUserId, email: input.email, last_sign_in_at: now },
});
return created[0]!.id;
},
};
}
export type Db = ReturnType<typeof createDb>;