Building a Lightweight Backend with Drizzle ORM + SQLite

When people think databases, PostgreSQL or MySQL come to mind first. But for small services and side projects, SQLite is far more practical — no server installation needed, a single file is the database. Combine it with Drizzle ORM and you get type-safe queries and clean schema management with minimal overhead.

Drizzle ORM vs Prisma

Prisma is the most popular Node.js ORM, but its bundle size and query engine create overhead in serverless and lightweight environments. Drizzle is a lightweight ORM with SQL-like syntax.

If you already know SQL, Drizzle feels natural. Its query builder maps almost 1:1 to SQL:select().from(users).where(eq(users.id, 1)) reads immediately if you know SQL.

Why Use SQLite in Production?

"Isn't SQLite just for development?" — a common misconception. SQLite is the most widely deployed database in the world. With proper configuration, it works well in production.

Important: SQLite is file-based, so server failure can mean data loss. Always set up periodic backup scripts with cron, or use tools like Litestream for real-time replication.

Schema Definition

Drizzle ORM defines schemas in TypeScript code — not in a separate schema file like Prisma. IDE autocomplete and type checking work immediately.

# Install packages
npm install drizzle-orm better-sqlite3
npm install -D drizzle-kit @types/better-sqlite3

// lib/db/schema.ts
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  role: text("role").notNull().default("user"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: text("author_id")
    .notNull()
    .references(() => users.id),
  publishedAt: integer("published_at", { mode: "timestamp" }),
});

Migration Workflow

Drizzle Kit detects schema changes and auto-generates SQL migration files — similar to Prisma's prisma migrate.

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

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

# Generate migration files (detects schema changes)
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly to DB (fast iteration during development)
npx drizzle-kit push

# Browse DB contents with Drizzle Studio (GUI)
npx drizzle-kit studio

Use push during development for quick iteration. In production, always usegenerate + migrate to prevent data loss from schema changes.

Seed Data

A seed script for initial data (admin accounts, default settings) makes new environment setup and test database initialization easy:

// scripts/seed.ts
import { db } from "../lib/db";
import { users } from "../lib/db/schema";

async function seed() {
  console.log("Inserting seed data...");

  await db.insert(users).values({
    id: "admin-001",
    email: "admin@example.com",
    name: "Admin",
    role: "admin",
  }).onConflictDoNothing();

  console.log("Seed data complete");
}

seed().catch(console.error);

# Run seed script with tsx
npx tsx scripts/seed.ts

# Or add to package.json scripts:
# "scripts": { "seed": "tsx scripts/seed.ts" }
npm run seed

WAL Mode for Concurrent Read Performance

SQLite's default journal mode (delete) blocks reads during writes. Enabling WAL (Write-Ahead Logging) mode allows reads to continue during writes, dramatically improving concurrent access performance for web services.

// lib/db/index.ts — Enable WAL mode
import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import * as schema from "./schema";

const sqlite = new Database("./data/app.db");

// Enable WAL mode (persists after first run)
sqlite.pragma("journal_mode = WAL");

// Additional performance optimizations
sqlite.pragma("busy_timeout = 5000");     // Wait 5s on lock
sqlite.pragma("synchronous = NORMAL");     // Balance speed/safety
sqlite.pragma("foreign_keys = ON");        // Enable foreign keys

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

Note: WAL mode creates .db-wal and .db-shm files alongside your database file. When backing up, copy these files too — otherwise recent changes may be lost.

Summary Checklist

• Drizzle ORM has a small bundle (~50KB) with intuitive SQL-like syntax
• SQLite replaces PostgreSQL for single-server, read-heavy workloads
• Define schemas in TypeScript and run migrations with drizzle-kit
• Use seed scripts for initial data (admin accounts, defaults)
• Enable WAL mode for concurrent read performance
• Back up the SQLite file and WAL files regularly

This guide is based on Drizzle ORM v0.35+ with better-sqlite3. SQLite's concurrent write limitation can become a bottleneck as traffic grows — consider migrating to PostgreSQL when your service scales beyond single-server capacity.