A database per signed-in user

Your app already knows who its users are — it has an auth layer. This recipe gives each of those users their own isolated SQLite database, provisioned the first time they sign in, so your application code writes plain single-tenant SQL: SELECT * FROM entries — no WHERE user_id, no row-level rules, no shared table to leak across. The isolation lives in the topology, not the query.

One server call maps a user to a database (created on first touch) and mints a token scoped to only that database. The example uses Better Auth, but the only thing PerSQL needs is a stable user id — see Any auth library below.

1. The server bridge

Keep your workspace token on the server. When a request arrives with a valid session, exchange that session’s user id for a fresh, database-scoped PerSQL token:

import { PerSQL } from "@persql/sdk";
import { auth } from "./auth"; // your Better Auth instance

// Server-only. Never ships to the client.
const persql = new PerSQL({ token: process.env.PERSQL_TOKEN });

// GET /api/db-token — the `(request) => Response` shape works as a Next.js
// route handler, a Hono handler, or a bare Workers fetch handler.
export async function GET(request: Request) {
  const session = await auth.api.getSession({ headers: request.headers });
  if (!session) return Response.json({ error: "unauthorized" }, { status: 401 });

  const { token, namespaceSlug, database, expiresAt } =
    await persql.users.provision(session.user.id, { template: "app-template" });

  return Response.json({
    token,
    database: `${namespaceSlug}/${database.slug}`,
    expiresAt,
  });
}

provision is idempotent on the subject (session.user.id): the first call creates that user’s database, every later call reuses it and mints a fresh short-lived token. So this endpoint is safe to hit on every page load or sign-in — there’s nothing to remember server-side.

template is the slug of a database whose schema (tables, indexes, triggers — never its rows) seeds each new user’s database, so it’s query-ready on the very first request. Omit it for an empty database.

2. The client queries its own database

The browser asks the bridge for a token, then talks to PerSQL directly — no data proxy in the middle:

import { PerSQL } from "@persql/sdk";

const { token, database } = await fetch("/api/db-token").then((r) => r.json());

const db = new PerSQL({ token }).database(database);

// Plain single-tenant SQL — only this user's rows exist.
await db.query("INSERT INTO entries (body) VALUES (?)", ["hello"]);
const { data } = await db.query("SELECT * FROM entries ORDER BY id DESC");

The token reaches exactly one database — this user’s — so a stolen token’s blast radius is one user, the same as a session cookie. Cache it client-side until expiresAt and re-fetch when it lapses.

What stays on the server

• The workspace token (PERSQL_TOKEN) never leaves the server. It can provision and reach every user’s database; only the narrow per-user token ever reaches a browser.
• The per-user token can’t run DDL. Your app owns the schema: seed it with template and roll out changes with migrations. The token reads and writes rows; it never alters structure.
• Usage bills to your workspace. Your users never see PerSQL. Keep the workspace’s prepaid balance funded; a fresh schema-only database is nearly free until it’s written to.

Any auth library

PerSQL needs one thing from your auth: a stable identifier for the user. Swap the session lookup for whatever you run — the rest is unchanged.

// Auth.js (NextAuth)
const session = await getServerSession(authOptions);
const subject = session?.user?.id;

// Clerk
const { userId: subject } = auth();

// Your own session table
const subject = lookupUserId(request);

Pass that subject to persql.users.provision(subject). Any string up to 256 characters works — a UUID, an email, an external id; PerSQL hashes it into a stable database slug.

When the user should own the data instead

Use this recipe when you run a multi-tenant app and the per-user database is an implementation detail your users never see — you provision it in your own workspace and you pay for it.

If instead the end user should own their data — inspect it, export it, disconnect your app — use user-owned app databases: the database lives in the user’s own PerSQL account via the database OAuth scope, and you only hold a token scoped to it.

Next step: db.describe() to hand the schema to an agent, or wire the same per-user token into db.asTools() so an assistant can query that user’s data and nothing else.