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.tsTypeScriptRawExternal
// 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>;

Back to the guide