SQLite as Embedded Database

Desktop applications require a database for persistent storage.

dataenforced by 1 rule

Context

Desktop applications require a database for persistent storage. The choice of database technology significantly impacts deployment complexity, data portability, user privacy, and application architecture. For local-first desktop applications, users should have full control of their data without requiring external database servers. The application should be distributable as a single package without complex setup, and users should not need to install, configure, or manage database servers. Traditional client-server databases like PostgreSQL, MySQL, or SQL Server require separate installation, network connectivity, user authentication, and ongoing maintenance, adding unnecessary complexity to a desktop application.

Decision

SQLite is the embedded database for desktop applications. It is an embedded, serverless database engine that stores data in a single file on the user's local filesystem.

Embedded: SQLite runs in-process with the application, requiring no separate server.
Serverless: No configuration, no setup, no administration.
Self-contained: The database is a single cross-platform file.
Zero-configuration: Works out-of-the-box with no installation required.
Full SQL support: ACID-compliant with comprehensive SQL features.
Cross-platform: Works identically across macOS, Linux, and Windows.
Use WAL (Write-Ahead Logging) mode for improved concurrent access: PRAGMA journal_mode=WAL.
Set PRAGMA busy_timeout = 30000 as the first PRAGMA after opening the database to handle file locks from cloud sync services.
Enable foreign key constraints: PRAGMA foreign_keys=ON.
Store database files in the user's application data directory, not the installation directory.

Do's and Don'ts

Do

Store SQLite database files in the user's application data directory.
Implement proper file-based backup strategies (the entire database is a single file).
Use WAL mode for improved concurrent access.
Set PRAGMA busy_timeout as the first PRAGMA after opening the database.
Use PRAGMA foreign_keys=ON to enforce referential integrity.
Handle database file permissions appropriately for user privacy.
Close database connections properly to release file locks.

Don't

Attempt to use SQLite for high-concurrency multi-user scenarios.
Store the database file in the application installation directory.
Assume cloud-synced directories work reliably with SQLite WAL mode (use DELETE journal mode for cloud paths).
Run any PRAGMA before busy_timeout (if the first PRAGMA hits a lock, it fails instantly).
Use connection pooling (SQLite uses a single file, connection overhead is minimal).
Bypass SQLite's type affinity system.

Consequences

Positive

Zero setup required; users start immediately without installing database servers.
True data ownership in a single portable file.
Privacy by default; data never leaves the user's device unless explicitly exported.
Simplified distribution as a standalone binary.
Easy backups by copying a single file.
No network latency; all operations are local.

Negative

Limited concurrency; only one writer at a time.
Not suitable for multi-user concurrent access patterns.
Performance and reliability depend on the underlying file system.
No built-in replication; synchronization between devices requires custom implementation.

Enforcement rules

adrs/DATA-001-sqlite-embedded-database.rules.ts

/// <reference path="../rules.d.ts" />

export default {
  rules: {
    "sqlite-dependency-required": {
      description: "Project must use better-sqlite3 or @libsql/client as SQLite driver",
      async check(ctx) {
        const pkg = await ctx.readJSON("package.json");
        const allDeps = {
          ...pkg.dependencies,
          ...pkg.devDependencies,
        };

        const hasSqliteDriver = "better-sqlite3" in allDeps || "@libsql/client" in allDeps;

        if (!hasSqliteDriver) {
          ctx.report.warning({
            message:
              "No SQLite driver found in dependencies. Expected better-sqlite3 or @libsql/client.",
            file: "package.json",
            fix: "Add better-sqlite3 or @libsql/client to your dependencies.",
          });
        }
      },
    },
    "no-server-database-deps": {
      description: "Project must not depend on server-based database clients (pg, mysql2, mongodb)",
      async check(ctx) {
        const pkg = await ctx.readJSON("package.json");
        const allDeps = {
          ...pkg.dependencies,
          ...pkg.devDependencies,
        };

        const forbidden = ["pg", "mysql2", "mongodb", "@prisma/client"];

        for (const dep of forbidden) {
          if (dep in allDeps) {
            ctx.report.violation({
              message: `Server-based database dependency "${dep}" found. Desktop apps must use SQLite.`,
              file: "package.json",
              fix: `Remove "${dep}" and use SQLite via better-sqlite3 or @libsql/client instead.`,
            });
          }
        }
      },
    },
    "drizzle-sqlite-dialect": {
      description: "Drizzle config must use the sqlite dialect",
      async check(ctx) {
        const configFiles = await ctx.glob("**/drizzle.config.ts");

        await Promise.all(
          configFiles.map(async (file) => {
            const content = await ctx.readFile(file);

            if (!content.includes('"sqlite"') && !content.includes("'sqlite'")) {
              ctx.report.violation({
                message: `${file}: Drizzle config must use dialect: "sqlite".`,
                file,
                fix: 'Set dialect: "sqlite" in your drizzle.config.ts.',
              });
            }
          }),
        );
      },
    },
  },
} satisfies RuleSet;