SQLite and Embedded Databases

Build the mental model

SQLite is an embedded relational database - instead of a separate server process, it's a library linked directly into your application, reading and writing a single file on disk.

Application

Your application code calls into the SQLite library

SQLite Library

Runs in-process and handles the queries

Database File

Reads and writes an ordinary file on disk directly - no server involved

mobile app local storage
desktop applications
small command-line tools
local development and automated tests
edge / offline-first scenarios

A server database like PostgreSQL or MySQL runs as its own long-lived process, and applications connect to it as clients over the network - built for multi-user, production-scale workloads.

Resist Absolute Claims

Avoid claims like "SQLite can't handle real workloads" - modern SQLite architectures have grown genuinely sophisticated, and the right choice depends on your concurrency and deployment needs.

Embedded Database: A database that runs as a library linked directly into your application, reading and writing data in the same process, without a separate server process.

text

EMBEDDED VS SERVER DATABASE MODEL
---------------------------------
EMBEDDED (SQLite)
Application -> SQLite Library -> Database File
              (in-process, single file, no server)

SERVER (PostgreSQL / MySQL)
Application -> Network -> Database Server -> Disk
              (separate process, many clients connect)

Connect it to a real scenario

Reach for SQLite when your application owns its own local storage without needing concurrent access - a mobile local cache, desktop settings, or a test suite.

Reach for a server database like PostgreSQL or MySQL once multiple independent apps, services, or users need concurrent network access to the same data.

Some projects sit in between - it's fine to start with SQLite and migrate later once real concurrent access becomes a requirement.

The runnable example below takes an application's needs and suggests an embedded or server-based starting point.

SQLite is an embedded, single-file relational database running as Application -> SQLite Library -> Database File
A server database (PostgreSQL/MySQL) is built for multi-user, production-scale, networked concurrent access
Avoid absolute claims like "SQLite can't handle real workloads" - the right choice depends on concurrency and deployment needs

Try the working example

javascript

function suggestDatabaseArchitecture(profile) {
  const {
    isMobileOrDesktopApp,
    needsMultipleConcurrentWriters,
    needsNetworkAccess,
    isSimpleLocalTool,
  } = profile;

  if (needsMultipleConcurrentWriters || needsNetworkAccess) {
    return "Server-based database (e.g., PostgreSQL/MySQL) - a reasonable starting point.";
  }
  if (isMobileOrDesktopApp || isSimpleLocalTool) {
    return "Embedded database (e.g., SQLite) - a reasonable starting point.";
  }
  return "Depends on further requirements - consider concurrency and deployment model.";
}

console.log("Mobile note-taking app:", suggestDatabaseArchitecture({
  isMobileOrDesktopApp: true, needsMultipleConcurrentWriters: false,
  needsNetworkAccess: false, isSimpleLocalTool: false,
}));

console.log("Multi-user SaaS backend:", suggestDatabaseArchitecture({
  isMobileOrDesktopApp: false, needsMultipleConcurrentWriters: true,
  needsNetworkAccess: true, isSimpleLocalTool: false,
}));

console.log("Local CLI test fixture:", suggestDatabaseArchitecture({
  isMobileOrDesktopApp: false, needsMultipleConcurrentWriters: false,
  needsNetworkAccess: false, isSimpleLocalTool: true,
}));

You should see

Mobile note-taking app: Embedded database (e.g., SQLite) - a reasonable starting point.
Multi-user SaaS backend: Server-based database (e.g., PostgreSQL/MySQL) - a reasonable starting point.
Local CLI test fixture: Embedded database (e.g., SQLite) - a reasonable starting point.

Both the mobile app and the local test fixture get an embedded suggestion, while the SaaS backend needing concurrent writers and network access gets a server-based one.

5-minute try-it

Extend suggestDatabaseArchitecture with an isReadHeavyAnalytics flag that, when true, also suggests a server-based database.

One important caution

Treating "SQLite can't handle real workloads" as a blanket rule

Choosing SQLite even when multiple processes or machines need concurrent write access to the same data

SQLite: About SQLite — How Databases Work