Database

Hoikka uses SQLite with Drizzle ORM. The schema is defined in TypeScript (dialect sqlite) and runs on better-sqlite3 on the node target and on Cloudflare D1 on the cloudflare target.

The db seam


src/lib/server/db/
├── index.ts       # `db` — lazy Proxy resolving to the right backend
├── node.ts        # better-sqlite3 singleton (node); migrates on first use
├── node-stub.ts   # aliased in on the cloudflare build
├── config.ts      # DATABASE_URL resolution (default ./data/hoikka.db)
└── schema.ts      # table definitions (sqliteTable)

index.ts exports a db Proxy. On node it’s a better-sqlite3 singleton created by node.ts — WAL mode, foreign keys on — which applies migrations from drizzle/ on first use. On Cloudflare it’s a Drizzle client over the D1 binding (platform.env.DB), cached per request. The default node database file is ./data/hoikka.db, overridable with DATABASE_URL.

Core tables

Products


products
├── id, name, slug, description       // Default language fields
├── type (physical | digital)
├── visibility (public | private | draft)
├── taxCode, featuredAssetId
├── deletedAt, createdAt, updatedAt
 
product_translations                  // Non-default language translations
├── id, productId, languageCode, name, slug, description
 
product_variants
├── id, productId, name, sku, price, stock, ...
 
product_variant_translations
├── id, variantId, languageCode, name

Orders


orders
├── id, code (unique order number)
├── customerId (nullable), checkoutToken, active
├── state: created | payment_pending | paid | shipped | delivered | cancelled
├── subtotal, shipping, discount, total
├── taxTotal, totalNet, isTaxExempt
├── currencyCode, exchangeRate
├── shippingFullName, shippingStreetLine1, shippingCity, ...
├── customerEmail, orderPlacedAt
 
order_lines
├── id, orderId, variantId, quantity
├── unitPrice, lineTotal, taxCode, taxRate, taxAmount
├── productName, variantName, sku
 
payments
├── id, orderId, paymentMethodId, method
├── amount, state (pending | authorized | settled | declined | refunded), transactionId
 
order_shipping
├── id, orderId, shippingMethodId
├── price, status, trackingNumber

An order row is created only at checkout. Before that the cart lives in a cookie — see Architecture.

Facets


facets
├── id, name, code (e.g., "color", "size")
├── isHidden (hidden from storefront filters)
 
facet_values
├── id, facetId, name, code (e.g., "red", "xl")
 
product_facet_values (productId, facetValueId)
variant_facet_values (variantId, facetValueId)

Full-text search

Product search uses SQLite FTS5. The virtual table product_search_fts is created by a raw-SQL migration (drizzle/0001_product_search_fts.sql) with unicode61 tokenization and diacritic removal (so Finnish “äöå” matches their ASCII forms). SQL triggers (drizzle/0003_fts_stock_price_triggers.sql) keep the index’s stock and price columns in sync as variants change.

Migrations

Migrations live in drizzle/ and are generated from the schema. On the node target they apply automatically at server start; on Cloudflare apply them explicitly with Wrangler.


# Generate from schema changes
pnpm db:generate
 
# Apply to remote D1 (cloudflare target)
pnpm db:migrate:cf
 
# Open Drizzle Studio
pnpm db:studio

Never use drizzle-kit push. Always generate a migration file.

Querying


import { db } from "$lib/server/db";
import { products } from "$lib/server/db/schema";
import { eq } from "drizzle-orm";
 
// Simple query
const product = await db.query.products.findFirst({
	where: eq(products.id, 123)
});
 
// With relations
const productWithVariants = await db.query.products.findFirst({
	where: eq(products.id, 123),
	with: {
		translations: true,
		variants: { with: { translations: true } }
	}
});