Database

SvelteForge Admin uses SQLite as its database, accessed through Drizzle ORM with the better-sqlite3 driver. This gives your Svelte 5 and SvelteKit application a zero-configuration, high-performance database that lives as a single file in your project root.

Why SQLite

SQLite is the ideal database for SvelteKit admin dashboards and internal tools:

• Zero configuration — No database server to install, configure, or maintain. Just a single file (svelteforge.db) in your project root.
• Simple local setup — Embedded SQLite needs no separate database service. With WAL mode enabled, concurrent reads never block each other.
• Perfect for deployment — Deploy your SvelteKit app with its database as a single unit. No connection strings to manage, no cold start latency.
• Type-safe with Drizzle — Drizzle ORM provides full TypeScript inference from your schema, catching query errors at compile time in your Svelte 5 components and SvelteKit server routes.

Connection Setup

The database connection is established in src/lib/server/db/index.ts. Because this file lives inside SvelteKit's #lib/server/ directory, it is guaranteed to never leak to the client bundle — a key security feature of SvelteKit's module system.

// src/lib/server/db/index.ts
import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import * as schema from "./schema.js";

const dbPath = process.env.DATABASE_URL || "svelteforge.db";
const sqlite = new Database(dbPath);
sqlite.pragma("journal_mode = WAL");

export const db = drizzle(sqlite, { schema });

Key details:

• WAL journal mode — Write-Ahead Logging allows concurrent reads while a write is in progress. This is critical for SvelteKit apps where multiple server-side load functions may query simultaneously.
• DATABASE_URL env var — Defaults to svelteforge.db in the project root. Override it in production to point to a persistent volume.
• Full schema import — Passing the entire schema to drizzle() enables Drizzle's relational query API (db.query.users.findFirst(), etc.).

Schema Deep Dive

The complete database schema is defined in src/lib/server/db/schema.ts using Drizzle ORM's SQLite table builder. Every table, column, type, constraint, and default is defined in TypeScript — giving your Svelte 5 components and SvelteKit server routes full type inference.

Users Table

The users table stores all registered accounts with role-based access control. The first user to register gets the admin role automatically.

export const users = sqliteTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  username: text("username").notNull().unique(),
  passwordHash: text("password_hash").notNull(),
  name: text("name").notNull(),
  avatarUrl: text("avatar_url"),
  role: text("role", { enum: ["admin", "editor", "viewer"] })
    .notNull()
    .default("viewer"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

Sessions Table

Session tokens are SHA-256 hashed before storage. The expiresAt column uses a raw integer (milliseconds since epoch) instead of { mode: "timestamp" } for precise expiry comparisons in the auth system.

export const sessions = sqliteTable("sessions", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => users.id),
  expiresAt: integer("expires_at").notNull(),
  userAgent: text("user_agent"),
  ipAddress: text("ip_address"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .$defaultFn(() => new Date()),
});

Pages Table

The content management system stores pages with three templates and a status workflow. The authorId foreign key links each page to its creator in the users table.

export const pages = sqliteTable("pages", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").notNull().unique(),
  content: text("content").notNull().default(""),
  template: text("template", { enum: ["default", "landing", "blog"] })
    .notNull()
    .default("default"),
  status: text("status", { enum: ["draft", "published", "archived"] })
    .notNull()
    .default("draft"),
  authorId: text("author_id")
    .notNull()
    .references(() => users.id),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
  publishedAt: integer("published_at", { mode: "timestamp" }),
});

Notifications Table

Notifications can be user-specific or global (when userId is null). The SvelteKit app layout queries both types for the authenticated user's notification bell.

export const notifications = sqliteTable("notifications", {
  id: text("id").primaryKey(),
  userId: text("user_id").references(() => users.id),
  title: text("title").notNull(),
  message: text("message").notNull(),
  type: text("type", { enum: ["info", "warning", "error", "success"] })
    .notNull()
    .default("info"),
  read: integer("read", { mode: "boolean" }).notNull().default(false),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

Password Reset Tokens Table

Tokens for the forgot-password flow. The token is SHA-256 hashed before storage (same pattern as sessions) and has a time-limited expiry.

export const passwordResetTokens = sqliteTable("password_reset_tokens", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => users.id),
  tokenHash: text("token_hash").notNull(),
  expiresAt: integer("expires_at", { mode: "timestamp" }).notNull(),
});

OAuth Accounts Table

Links external OAuth providers (Google, GitHub) to user accounts. A unique composite index on (provider, providerUserId) ensures no duplicate OAuth links.

export const oauthAccounts = sqliteTable(
  "oauth_accounts",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id),
    provider: text("provider", { enum: ["google", "github"] }).notNull(),
    providerUserId: text("provider_user_id").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (table) => [
    uniqueIndex("oauth_provider_user_idx")
      .on(table.provider, table.providerUserId)
  ]
);

Unique composite index: oauth_provider_user_idx on (provider, providerUserId) prevents the same OAuth account from being linked to multiple users.

App Settings Table

A simple key-value store for application-wide configuration. The key column is the primary key — no separate ID needed.

export const appSettings = sqliteTable("app_settings", {
  key: text("key").primaryKey(),
  value: text("value").notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

Timestamp Modes

Drizzle ORM's { mode: "timestamp" } option on integer columns controls how values are serialized:

• With mode: "timestamp" — Drizzle automatically converts between JavaScript Date objects and Unix timestamps. Used for createdAt, updatedAt, and publishedAt across most tables.
• Without mode: "timestamp" — The column stores and returns raw integers. Used for sessions.expiresAt which stores milliseconds since epoch for precise expiry comparisons without Date conversion overhead.

Type Exports

The schema file also exports inferred TypeScript types for use throughout the SvelteKit application:

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Session = typeof sessions.$inferSelect;
export type Page = typeof pages.$inferSelect;
export type NewPage = typeof pages.$inferInsert;
export type Notification = typeof notifications.$inferSelect;
export type OAuthAccount = typeof oauthAccounts.$inferSelect;
export type AppSetting = typeof appSettings.$inferSelect;

ID Generation

All entity IDs in SvelteForge Admin are generated using cryptographic randomness, defined in src/lib/server/id.ts:

// src/lib/server/id.ts
import { encodeBase32LowerCaseNoPadding } from "@oslojs/encoding";

export function generateId(length: number = 15): string {
  const bytes = new Uint8Array(length);
  crypto.getRandomValues(bytes);
  return encodeBase32LowerCaseNoPadding(bytes);
}

• Cryptographically secure — Uses crypto.getRandomValues() for truly random bytes.
• Base32 encoded — Lowercase, no padding. URL-safe and case-insensitive.
• Default length: 15 bytes — Produces a 24-character string with 120 bits of entropy. The seed script uses generateId(10) for shorter IDs.

Migrations & Schema Changes

SvelteForge Admin uses Drizzle Kit for database migrations. The configuration lives in drizzle.config.ts:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/lib/server/db/schema.ts",
  out: "./drizzle",
  dialect: "sqlite",
  dbCredentials: {
    url: "svelteforge.db",
  },
});

Available Commands

Step-by-Step: Changing the Schema

1. Edit the schema — Modify src/lib/server/db/schema.ts (add columns, tables, constraints).
2. Push changes — Run pnpm db:push to apply changes directly to your local SQLite database.
3. Update test utilities — If you have tests, update the SCHEMA_SQL constant in test-utils.ts to match the new schema. Tests use an in-memory SQLite database that is created from this SQL string.
4. Generate migrations (optional) — For production deployments, run pnpm db:generate to create versioned SQL migration files.

Database Seeding

The seed script at src/lib/server/db/seed.ts populates the database with realistic sample data. Run it with:

pnpm db:seed

What Gets Created

Key Details

• All passwords: password123 — Hashed with Argon2id (memoryCost: 19456, timeCost: 2, outputLen: 32, parallelism: 1).
• Login with: [email protected] / password123 (or any seeded user with password123).
• Realistic timestamps: Uses a daysAgo() helper to create dates spread across the past 12 months.
• Runs outside SvelteKit: The seed script is executed via the locally installed tsx, not through SvelteKit's Vite server. This means it uses relative imports (./index.js, ../id.js) instead of #lib/ aliases.

Drizzle Studio

Drizzle Studio provides a browser-based GUI for inspecting and editing your SQLite database. Launch it with:

pnpm db:studio

This opens a visual interface where you can browse tables, run queries, edit rows, and inspect relationships — useful during development of your Svelte 5 components and SvelteKit server routes.

Querying Patterns

Here are common Drizzle ORM query patterns used throughout the SvelteKit server routes in SvelteForge Admin.

Select All Records

import { db } from "#lib/server/db/index.js";
import { users } from "#lib/server/db/schema.js";

// Select specific columns
const allUsers = await db
  .select({
    id: users.id,
    name: users.name,
    email: users.email,
    role: users.role,
    createdAt: users.createdAt,
  })
  .from(users)
  .orderBy(users.createdAt);

Find One Record

import { eq } from "drizzle-orm";

// Using the relational query API
const user = await db.query.users.findFirst({
  where: eq(users.email, "[email protected]"),
});

Insert a Record

import { generateId } from "#lib/server/id.js";

await db.insert(users).values({
  id: generateId(10),
  email: "[email protected]",
  username: "newuser",
  passwordHash: hashedPassword,
  name: "New User",
  role: "viewer",
});

Update a Record

import { eq } from "drizzle-orm";

await db
  .update(users)
  .set({ role: "editor", updatedAt: new Date() })
  .where(eq(users.id, userId));

Delete Records

import { eq } from "drizzle-orm";

await db.delete(users).where(eq(users.id, userId));

Aggregation with SQL

import { sql, eq, and, or, isNull } from "drizzle-orm";

// Count unread notifications for a user (including global ones)
const [result] = await db
  .select({ count: sql<number>`count(*)` })
  .from(notifications)
  .where(
    and(
      eq(notifications.read, false),
      or(
        eq(notifications.userId, userId),
        isNull(notifications.userId)
      )
    )
  );

Pattern Matching (LIKE)

import { sql, or } from "drizzle-orm";

const pattern = `%${searchQuery}%`;
const results = await db
  .select({ id: users.id, name: users.name })
  .from(users)
  .where(or(
    sql`${users.name} LIKE ${pattern}`,
    sql`${users.email} LIKE ${pattern}`
  ))
  .limit(10);

Need More?