Running a multi-tenant SaaS on D1 and Drizzle

When Emberstack moved off Postgres, the database was the part that needed the most thought. D1 is SQLite, and SQLite still gets filed under "great for tests, not for production."

For this kind of app, that filing is out of date.

A SaaS starter needs users, teams, memberships, sessions and a few feature tables. That's a handful of joins and a lot of small reads. SQLite handles that happily, and D1 gives you backups, a REST API and a binding with nothing to host.

The schema looks almost the same

Tables live in src/db/schema.ts with sqliteTable instead of pgTable. The differences are small and mostly about types:

The schema has createdAt() and updatedAt() helpers at the top, and updatedAt() bumps itself on every Drizzle update, so you stop writing updatedAt: new Date() by hand.

Migrations work the way you'd hope. bun run db:generate writes SQL to migrations/, you read it (SQLite rebuilds a table for a lot of column changes, so actually read it), and bun run db:migrate:local applies it.

Every row belongs to a team

Emberstack is multi-tenant. Every user gets their own team on signup, can be invited to others, and switches between them with the team switcher. So the rule is simple: every tenant table has an organizationId, and every query filters on it.

const rows = await db.select().from(projects)
  .where(eq(projects.organizationId, Astro.locals.organizationId))
  .orderBy(desc(projects.updatedAt));

The interesting bit is where organizationId comes from. The session stores an "active organization," but I don't trust it directly. It's null right after signup (the session gets created a moment before the team does), and it can be stale if someone was removed from a team.

So middleware resolves it on every request: use the session's active team if the user is still a member, otherwise fall back to their first membership. Pages and endpoints just read Astro.locals.organizationId and Astro.locals.memberRole, already checked.

And never trust an ID from a URL on its own. Pair it with the team:

await db.update(projects).set({ name })
  .where(and(eq(projects.id, id), eq(projects.organizationId, orgId)));

That one line is the difference between "edit your project" and "edit anyone's project by guessing a number."

The 3 D1 limits you'll hit

No interactive transactions. db.transaction() doesn't work on D1. Use db.batch([...]) instead, which runs its statements atomically. The signup hook does this to create the team and the owner membership together:

await db.batch([
  db.insert(organization).values({ id: orgId, name, slug }),
  db.insert(member).values({ id: nanoid(), userId, organizationId: orgId, role: 'owner' }),
]);

100 bound parameters per statement. A bulk insert of 30 rows with 4 columns is 120 parameters and fails. Chunk big inserts and big inArray() lists.

No `ilike`. You won't miss it: SQLite's like is already case-insensitive for ASCII.

That's honestly the whole list for most apps.

Poking at the data

Rather than writing a one-off script every time you have a question about the data, use scripts/db-query.ts. It runs 1 read-only statement through wrangler and prints JSON:

echo "SELECT id, email FROM users LIMIT 5" | node scripts/db-query.ts
echo "SELECT count(*) FROM notes" | node scripts/db-query.ts --local --format table

It refuses writes unless you pass --write, and a remote write also needs --confirm "write remote", which makes it hard to change production by accident.

Backups you don't have to think about

D1 keeps point-in-time history with Time Travel. Before anything risky, grab a bookmark:

bunx wrangler d1 time-travel info my-app-db

That prints a bookmark and the exact restore command. It's the cheapest insurance in the whole stack.

The Database doc has the full workflow if you want the reference version of this post.