Using SQLite with Next.js: SSR Issues and Solutions

Introduction

SQLite is an excellent choice for small to medium Next.js applications. It requires no separate database server, supports ACID transactions, and is extremely fast for read-heavy workloads. However, using SQLite with Next.js Server-Side Rendering introduces several challenges related to file system access, connection management, and Edge Runtime compatibility.

After using SQLite as the primary database for a personal project, I encountered every possible issue and documented the solutions.

Environment

• OS: Windows 11
• Node.js: v20.10.0
• Next.js: 14.0.4
• SQLite: better-sqlite3 9.4.3
• ORM: Prisma 5.8.0

Problem

SQLite integration produced several errors:

Error: SQLite database file is not accessible in Edge Runtime

TypeError: The "path" argument must be of type string. Received undefined

Error: SQLite: Cannot open database file - EACCES: permission denied

Warning: SQLite database is locked. Cannot acquire lock.

During build, the database file was created in the wrong location:

# Expected: ./data/app.db
# Actual: /tmp/nextjs/app.db (or undefined)

Analysis

The root causes were:

1. Edge Runtime incompatibility. SQLite requires native Node.js APIs that are not available in Edge Runtime. Middleware and some API routes run in Edge by default.

2. Database path resolution. better-sqlite3 requires an absolute file path. Relative paths resolve differently during build versus runtime.

3. File locking. SQLite uses file locks for concurrent access. Multiple server processes or concurrent requests can cause lock contention.

4. Serverless deployment. On Vercel or similar platforms, the file system is ephemeral. SQLite databases are lost on each deployment.

Solution

Fix 1: Use better-sqlite3 correctly in Server Components

// lib/db.ts
import Database from 'better-sqlite3'
import path from 'path'

const DB_PATH = path.join(process.cwd(), 'data', 'app.db')

let db: Database.Database | null = null

export function getDb(): Database.Database {
  if (!db) {
    // Ensure the data directory exists
    const fs = require('fs')
    const dir = path.dirname(DB_PATH)
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true })
    }

    db = new Database(DB_PATH, {
      verbose: process.env.NODE_ENV === 'development' ? console.log : undefined,
    })

    // Enable WAL mode for better concurrent performance
    db.pragma('journal_mode = WAL')
    db.pragma('busy_timeout = 5000')
  }

  return db
}

Fix 2: Prisma with SQLite

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

# .env
DATABASE_URL="file:./dev.db"

Fix 3: Avoid Edge Runtime with SQLite

// app/api/posts/route.ts
// Force Node.js runtime for SQLite access
export const runtime = 'nodejs'

import { NextResponse } from 'next/server'
import { getDb } from '@/lib/db'

export async function GET() {
  const db = getDb()
  const posts = db.prepare('SELECT * FROM Post').all()
  return NextResponse.json(posts)
}

Fix 4: Connection pooling for production

// lib/db-pool.ts
import Database from 'better-sqlite3'
import path from 'path'

class DatabasePool {
  private db: Database.Database | null = null
  private isInitializing = false

  async connect(): Promise {
    if (this.db) return this.db

    if (this.isInitializing) {
      // Wait for initialization
      await new Promise((resolve) => setTimeout(resolve, 100))
      return this.db!
    }

    this.isInitializing = true

    const dbPath = path.join(process.cwd(), 'data', 'app.db')
    this.db = new Database(dbPath)
    this.db.pragma('journal_mode = WAL')
    this.db.pragma('busy_timeout = 5000')
    this.db.pragma('synchronous = NORMAL')
    this.db.pragma('cache_size = -64000') // 64MB cache

    this.isInitializing = false
    return this.db
  }

  close() {
    if (this.db) {
      this.db.close()
      this.db = null
    }
  }
}

export const pool = new DatabasePool()

Fix 5: Migration script

// scripts/migrate.ts
import { getDb } from '@/lib/db'
import fs from 'fs'
import path from 'path'

function migrate() {
  const db = getDb()
  const migrationsDir = path.join(process.cwd(), 'prisma', 'migrations')

  // Read migration files
  const migrations = fs.readdirSync(migrationsDir).sort()

  for (const migration of migrations) {
    const sqlFile = path.join(migrationsDir, migration, 'migration.sql')
    if (fs.existsSync(sqlFile)) {
      const sql = fs.readFileSync(sqlFile, 'utf-8')
      db.exec(sql)
      console.log(`Applied migration: ${migration}`)
    }
  }

  console.log('All migrations applied successfully')
}

migrate()

Lessons Learned

1. SQLite is not suitable for Vercel/serverless. The ephemeral file system means your database is wiped on every deployment. Use PostgreSQL or MySQL for serverless deployments.

2. Always use WAL mode. Write-Ahead Logging provides significantly better concurrent read performance than the default journal mode.

3. Set busy_timeout. Without it, concurrent writes fail immediately instead of waiting for locks to release.

4. Use Prisma or Drizzle for type safety. Raw SQL is error-prone. An ORM provides type checking and migration management.

5. Back up your SQLite database regularly. Unlike cloud databases, there is no automatic backup. Implement a backup strategy:

# Simple backup script
sqlite3 data/app.db ".backup data/backup-$(date +%Y%m%d).db"

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.