SQLite Backend

SQLite is the shortest path from the demo catalog to a real store. No container, no connection string to get right, no account: the database is a file next to your code, and every write the app can do — admin product CRUD, quote requests, orders, discounts, carts — actually persists.

echo 'DATABASE_URL="file:./dev.db"'          >> .env.local
echo 'COMMERCE_BACKEND=sqlite'               >> .env.local
echo 'NEXT_PUBLIC_COMMERCE_BACKEND=sqlite'   >> .env.local

COMMERCE_BACKEND=sqlite npm run prisma:generate
COMMERCE_BACKEND=sqlite npm run prisma:push
npm run dev

Add npm run seed and you have a populated store — 18 products, 6 featured, the same fixture the demo backend serves. Delete dev.db to start over.

What it is good for

SQLite replaces where the data lives, not who the visitor is — that is a separate choice. Firebase Auth is the default, and AUTH_PROVIDER=authjs puts sessions in this same file instead, which drops the Firebase project entirely. See authentication.

Two variables, plus the file

DATABASE_URL="file:./dev.db"       # relative to the project root
COMMERCE_BACKEND=sqlite            # server: run the Prisma adapter on SQLite
NEXT_PUBLIC_COMMERCE_BACKEND=sqlite # browser: route through /api/commerce

The browser never opens the file. It calls POST /api/commerce/<service>/<method>, which identifies the caller through the configured auth provider and enforces public/user/admin access per method before touching the database — the same RPC path every SQL backend uses.

COMMERCE_BACKEND=sqlite npm run prisma:generate
COMMERCE_BACKEND=sqlite npm run prisma:push

Grant yourself admin

admin.isAdmin(uid) is row existence in admins, keyed by your auth uid. Sign in once so the uid exists, then:

sqlite3 dev.db "insert or ignore into admins (uid) values ('<your-uid>');"

The uid is shown on the /admin access-denied screen.

Why there are two schema files

prisma/schema.prisma (Postgres) and prisma/schema.sqlite.prisma (SQLite) are identical below their datasource block, and tests/prisma-schemas.test.ts fails the build if they ever drift.

They are separate files only because Prisma will not read the provider from the environment: provider = env("DATABASE_PROVIDER") is a validation error, and prisma.config.ts accepts only url and shadowDatabaseUrl. So the config file picks the schema by COMMERCE_BACKEND instead:

const isSqlite = process.env.COMMERCE_BACKEND === "sqlite";

export default defineConfig({
  schema: isSqlite ? "prisma/schema.sqlite.prisma" : "prisma/schema.prisma",
  datasource: { url: env("DATABASE_URL") },
});

Add a column to one schema and the drift test tells you to add it to the other.

Category pages

data.categories is promoted into a product_categories join table so a category page is an indexed WHERE clause rather than a scan, and countByCategory is a real count. The adapter rewrites a product's rows in the same transaction as the product, so they cannot drift.

If you created a database before this table existed, backfill it once after prisma:push:

sqlite3 dev.db "insert or ignore into product_categories (product_id, category) \
  select p.id, j.value from products p, json_each(p.data, '\$.categories') j;"

Where the two engines differ

Three statements, all behind isSqlite() in the adapter, and all of them the SQLite side being simpler:

Everything else — every query, every write, the transaction in recordPaidSession, the lifecycle events — is one shared implementation.

Tested against the real thing

SQLite is the only backend the test suite can exercise for real, because it needs no server. tests/sqlite-integration.test.ts creates a throwaway database, pushes the schema, and drives the actual adapter:

npm run test:sqlite

It covers the JSON body round-trip, the byte-ordered prefix search (including the pro-tools / protools case), paging cursors, discounts, carts, and recordPaidSession — recording, stock and variant-stock decrements, and a retried delivery hitting the unique constraint and rolling back. Since the two engines share every line outside the three branches above, what passes here is evidence for the Postgres path too.

Where this suite sits relative to npm test and npm run test:e2e is on the testing page.

Moving to Postgres later

Nothing in your code changes:

# .env.local
DATABASE_URL="postgresql://user:password@localhost:5432/store"
COMMERCE_BACKEND=postgres
NEXT_PUBLIC_COMMERCE_BACKEND=postgres

npm run prisma:generate
npm run prisma:push

The data does not migrate itself — export what you need first, or re-seed. See Postgres / Prisma.