database-auth

name	database-auth
description	Set up SQLite database with Prisma ORM and Better Auth for user authentication. Use when the user asks for a full backend, database and auth, or needs needs user accounts, login/signup, or persistent data storage.

Database + Authentication Setup

Sets up Prisma v6 (SQLite) + Better Auth (email/password) for React Vite web apps with Hono backend.

Setup Steps

1. Install Packages

Backend:

cd backend && bun add better-auth @prisma/client@6 @hono/zod-validator && bun add -d prisma@6

Webapp:

cd webapp && bun add better-auth

2. Setup Prisma Schema

Create backend/prisma/schema.prisma:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id            String    @id
  name          String
  email         String    @unique
  emailVerified Boolean   @default(false)
  image         String?
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  sessions      Session[]
  accounts      Account[]
}

model Session {
  id        String   @id
  expiresAt DateTime
  token     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  ipAddress String?
  userAgent String?
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Account {
  id                    String    @id
  accountId             String
  providerId            String
  userId                String
  user                  User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  accessToken           String?
  refreshToken          String?
  idToken               String?
  accessTokenExpiresAt  DateTime?
  refreshTokenExpiresAt DateTime?
  scope                 String?
  password              String?
  createdAt             DateTime  @default(now())
  updatedAt             DateTime  @updatedAt
}

model Verification {
  id         String   @id
  identifier String
  value      String
  expiresAt  DateTime
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
}

3. Create Prisma Client

Create backend/src/prisma.ts:

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
// IMPORTANT: SQLite optimizations for better performance
async function initSqlitePragmas(prisma: PrismaClient) {
  await prisma.$queryRawUnsafe("PRAGMA journal_mode = WAL;");
  await prisma.$queryRawUnsafe("PRAGMA foreign_keys = ON;");
  await prisma.$queryRawUnsafe("PRAGMA busy_timeout = 10000;");
  await prisma.$queryRawUnsafe("PRAGMA synchronous = NORMAL;");
}
initSqlitePragmas(prisma);

export { prisma };

4. Setup Better Auth

Generate and add secret to backend/.env (appends to the file):

echo "BETTER_AUTH_SECRET=\"$(openssl rand -base64 32)\"" >> backend/.env

Update backend/src/env.ts to include:

BETTER_AUTH_SECRET: z.string().min(1, "BETTER_AUTH_SECRET is required"),

Create backend/src/auth.ts:

import { betterAuth } from "better-auth";
import { prismaAdapter } from "better-auth/adapters/prisma";
import { prisma } from "./prisma";
import { env } from "./env";

export const auth = betterAuth({
  database: prismaAdapter(prisma, { provider: "sqlite" }),
  secret: env.BETTER_AUTH_SECRET,
  baseURL: env.BACKEND_URL,
  trustedOrigins: ["http://localhost:8000", process.env.VITE_BACKEND_URL || "http://localhost:3000", process.env.VITE_BASE_URL || "http://localhost:8080"], // IMPORTANT: Include webapp origin
  emailAndPassword: {
    enabled: true,
  },
  advanced: {
    crossSubDomainCookies: {
      enabled: true,
    },
    disableCSRFCheck: true,
    // Cross-origin cookie settings for iframe web preview
    defaultCookieAttributes: {
      sameSite: "none",
      secure: true,
      partitioned: true,
    },
  },
});

Update backend/src/index.ts with typed context and auth middleware:

import { Hono } from "hono";
import { auth } from "./auth";

// Type the Hono app with user/session variables
const app = new Hono<{
  Variables: {
    user: typeof auth.$Infer.Session.user | null;
    session: typeof auth.$Infer.Session.session | null;
  };
}>();

// Auth middleware - populates user/session for all routes
app.use("*", async (c, next) => {
  const session = await auth.api.getSession({ headers: c.req.raw.headers });
  if (!session) {
    c.set("user", null);
    c.set("session", null);
    await next();
    return;
  }
  c.set("user", session.user);
  c.set("session", session.session);
  await next();
});

// Mount auth handler
app.on(["GET", "POST"], "/api/auth/*", (c) => auth.handler(c.req.raw));

Example protected route:

app.get("/api/me", (c) => {
  const user = c.get("user"); // Fully typed User type
  if (!user) return c.body(null, 401);
  return c.json({ user });
});

5. Setup Webapp Auth Client

Create webapp/src/lib/auth-client.ts:

import { createAuthClient } from "better-auth/react";

const backendUrl = import.meta.env.VITE_BACKEND_URL || "http://localhost:3000";

export const authClient = createAuthClient({
  baseURL: backendUrl,
  fetchOptions: {
    credentials: "include", // IMPORTANT: Send cookies with cross-origin requests
  },
});

// Export the useSession hook for React components
export const { useSession, signIn, signUp, signOut } = authClient;

Create webapp/src/lib/use-session.ts:

import { authClient } from "./auth-client";

export const useSession = () => {
  return authClient.useSession();
};

6. Setup API Client with Auth

IMPORTANT: In the browser, cookies are sent automatically when using credentials: "include".

Create or edit webapp/src/lib/api.ts:

const baseUrl = import.meta.env.VITE_BACKEND_URL || "http://localhost:3000";

const request = async <T>(url: string, options: { method?: string; body?: string } = {}): Promise<T> => {
  const response = await fetch(`${baseUrl}${url}`, {
    ...options,
    credentials: "include", // IMPORTANT: Send cookies with requests
    headers: {
      ...(options.body ? { "Content-Type": "application/json" } : {}),
    },
  });
  return response.json();
};

export const api = {
  get: <T>(url: string) => request<T>(url),
  post: <T>(url: string, body: any) => request<T>(url, { method: "POST", body: JSON.stringify(body) }),
  put: <T>(url: string, body: any) => request<T>(url, { method: "PUT", body: JSON.stringify(body) }),
  delete: <T>(url: string) => request<T>(url, { method: "DELETE" }),
  patch: <T>(url: string, body: any) => request<T>(url, { method: "PATCH", body: JSON.stringify(body) }),
};

7. Generate and Push Database

cd backend && bunx prisma generate && bunx prisma db push

8. Setup Protected Routes with React Router

Use React Router to protect routes based on auth state.

Example route structure:

src/
  routes/
    _layout.tsx        (Root layout with auth check)
    login.tsx          (Public - accessible when NOT logged in)
    signup.tsx         (Public - accessible when NOT logged in)
    dashboard.tsx      (Protected - requires login)
    profile.tsx        (Protected - requires login)

Create a protected route wrapper webapp/src/components/ProtectedRoute.tsx:

import { Navigate } from "react-router-dom";
import { useSession } from "@/lib/auth-client";

export function ProtectedRoute({ children }: { children: React.ReactNode }) {
  const { data: session, isPending } = useSession();

  if (isPending) {
    return (
      <div className="flex items-center justify-center min-h-screen">
        <div className="animate-spin rounded-full h-8 w-8 border-b-2 border-gray-900" />
      </div>
    );
  }

  if (!session?.user) {
    return <Navigate to="/login" replace />;
  }

  return <>{children}</>;
}

Create a guest-only route wrapper webapp/src/components/GuestRoute.tsx:

import { Navigate } from "react-router-dom";
import { useSession } from "@/lib/auth-client";

export function GuestRoute({ children }: { children: React.ReactNode }) {
  const { data: session, isPending } = useSession();

  if (isPending) {
    return (
      <div className="flex items-center justify-center min-h-screen">
        <div className="animate-spin rounded-full h-8 w-8 border-b-2 border-gray-900" />
      </div>
    );
  }

  if (session?.user) {
    return <Navigate to="/dashboard" replace />;
  }

  return <>{children}</>;
}

Example App.tsx with routes:

import { BrowserRouter, Routes, Route } from "react-router-dom";
import { ProtectedRoute } from "@/components/ProtectedRoute";
import { GuestRoute } from "@/components/GuestRoute";
import Login from "@/routes/login";
import Signup from "@/routes/signup";
import Dashboard from "@/routes/dashboard";

function App() {
  return (
    <BrowserRouter>
      <Routes>
        <Route path="/login" element={<GuestRoute><Login /></GuestRoute>} />
        <Route path="/signup" element={<GuestRoute><Signup /></GuestRoute>} />
        <Route path="/dashboard" element={<ProtectedRoute><Dashboard /></ProtectedRoute>} />
        <Route path="/" element={<ProtectedRoute><Dashboard /></ProtectedRoute>} />
      </Routes>
    </BrowserRouter>
  );
}

export default App;

9. Create Auth Pages

Example login page webapp/src/routes/login.tsx:

import { useState } from "react";
import { Link, useNavigate } from "react-router-dom";
import { authClient } from "@/lib/auth-client";

export default function Login() {
  const [email, setEmail] = useState("");
  const [password, setPassword] = useState("");
  const [error, setError] = useState("");
  const [loading, setLoading] = useState(false);
  const navigate = useNavigate();

  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault();
    setError("");
    setLoading(true);

    const result = await authClient.signIn.email({
      email: email.trim(),
      password,
    });

    setLoading(false);

    if (result.error) {
      setError(result.error.message || "Invalid email or password");
    } else {
      navigate("/dashboard");
    }
  };

  return (
    <div className="min-h-screen flex items-center justify-center">
      <form onSubmit={handleSubmit} className="w-full max-w-md space-y-4 p-8">
        <h1 className="text-2xl font-bold text-center">Login</h1>
        
        {error && <p className="text-red-500 text-center">{error}</p>}
        
        <input
          type="email"
          placeholder="Email"
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          className="w-full p-3 border rounded"
          required
        />
        
        <input
          type="password"
          placeholder="Password"
          value={password}
          onChange={(e) => setPassword(e.target.value)}
          className="w-full p-3 border rounded"
          required
        />
        
        <button
          type="submit"
          disabled={loading}
          className="w-full p-3 bg-blue-500 text-white rounded hover:bg-blue-600 disabled:opacity-50"
        >
          {loading ? "Signing in..." : "Sign In"}
        </button>
        
        <p className="text-center">
          Don't have an account? <Link to="/signup" className="text-blue-500">Sign up</Link>
        </p>
      </form>
    </div>
  );
}

Example signup page webapp/src/routes/signup.tsx:

import { useState } from "react";
import { Link, useNavigate } from "react-router-dom";
import { authClient } from "@/lib/auth-client";

export default function Signup() {
  const [name, setName] = useState("");
  const [email, setEmail] = useState("");
  const [password, setPassword] = useState("");
  const [error, setError] = useState("");
  const [loading, setLoading] = useState(false);
  const navigate = useNavigate();

  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault();
    setError("");
    setLoading(true);

    const result = await authClient.signUp.email({
      email: email.trim(),
      password,
      name: name.trim(),
    });

    setLoading(false);

    if (result.error) {
      setError(result.error.message || "Failed to create account");
    } else {
      navigate("/dashboard");
    }
  };

  return (
    <div className="min-h-screen flex items-center justify-center">
      <form onSubmit={handleSubmit} className="w-full max-w-md space-y-4 p-8">
        <h1 className="text-2xl font-bold text-center">Sign Up</h1>
        
        {error && <p className="text-red-500 text-center">{error}</p>}
        
        <input
          type="text"
          placeholder="Name"
          value={name}
          onChange={(e) => setName(e.target.value)}
          className="w-full p-3 border rounded"
          required
        />
        
        <input
          type="email"
          placeholder="Email"
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          className="w-full p-3 border rounded"
          required
        />
        
        <input
          type="password"
          placeholder="Password"
          value={password}
          onChange={(e) => setPassword(e.target.value)}
          className="w-full p-3 border rounded"
          minLength={8}
          required
        />
        
        <button
          type="submit"
          disabled={loading}
          className="w-full p-3 bg-blue-500 text-white rounded hover:bg-blue-600 disabled:opacity-50"
        >
          {loading ? "Creating account..." : "Sign Up"}
        </button>
        
        <p className="text-center">
          Already have an account? <Link to="/login" className="text-blue-500">Login</Link>
        </p>
      </form>
    </div>
  );
}

Usage

Webapp: Auth Operations

import { authClient } from "@/lib/auth-client";

// Sign up
await authClient.signUp.email({
  email: "[e-mail]",
  password: "password123",
  name: "User Name",
});

// Sign in
await authClient.signIn.email({
  email: "[e-mail]",
  password: "password123",
});

// Sign out
await authClient.signOut();

Webapp: Making Authenticated API Calls

import { api } from "@/lib/api";

// GET request
const data = await api.get<{ users: User[] }>("/api/users");

// POST request
const newUser = await api.post<{ user: User }>("/api/users", { name: "John" });

// DELETE request
await api.delete("/api/users/123");

Webapp: Session Hook

import { useSession } from "@/lib/auth-client";

export default function Dashboard() {
  const { data: session, isPending } = useSession();

  if (isPending) return <div>Loading...</div>;

  if (!session?.user) return <div>Not logged in</div>;

  return <div>Hello, {session.user.name}</div>;
}

Auth Endpoints

• POST /api/auth/sign-up/email — Create account
• POST /api/auth/sign-in/email — Login
• POST /api/auth/sign-out — Logout
• GET /api/auth/session — Get current session

The auth middleware automatically populates c.get("user") and c.get("session") for all routes.

Environment Variables

Webapp .env:

VITE_BACKEND_URL=http://localhost:3000

Backend .env:

DATABASE_URL="file:./dev.db"
BACKEND_URL=http://localhost:3000
BETTER_AUTH_SECRET="your-generated-secret"