SQLite Basics

Introduction

SQLite is a lightweight relational database engine that runs inside an application process and commonly stores an entire database in one ordinary disk file. It supports tables, rows, columns, indexes, views, triggers, constraints, joins, transactions, and a substantial SQL language without requiring a separately installed database server.

That architecture makes SQLite different from MySQL, PostgreSQL, SQL Server, and Oracle. Those products normally run a service that listens for client connections. With SQLite, the application links to or loads the SQLite library, opens a file, and reads or writes the database directly. There is no hostname, database port, server account, or daemon to manage for an ordinary local database.

SQLite is not merely a text file and is not a simplified substitute for every server database. It is a transactional relational database designed for local application storage, devices, embedded systems, desktop files, mobile applications, prototypes, tests, and many low-to-medium traffic services. Understanding the problem it solves is more useful than ranking it as universally better or worse than a client-server engine.

What Is SQLite?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Self-contained means the core database functionality has few external dependencies. Serverless means no intermediary database-server process stands between the application and database file. Zero-configuration means an application can usually open a file and begin using the database without provisioning an instance.

The source code is in the public domain, and the database-file format is stable and cross-platform. SQLite has a small footprint but still provides ACID transactions, indexes, query planning, views, triggers, common table expressions, window functions, JSON capabilities in current builds, and many other features expected from a relational engine.

SQLite Architecture

A traditional client-server database separates the application from a database service:

Application
    -> network protocol or local IPC
    -> database server process
    -> database storage

SQLite places the engine in the same process as the application:

Application
    -> SQLite library
    -> database file

The absence of a server removes network round trips, server deployment, service startup, and account provisioning. It also means file permissions and application behavior become part of the database security and reliability boundary.

What Serverless Means in SQLite

Serverless in SQLite does not refer to cloud functions or managed serverless computing. It means there is no separate database-server process. The application process calls SQLite APIs, and those APIs operate on the database file.

This design is efficient for local storage because a query does not need to cross a network or process boundary. However, a database server can centralize authentication, isolate clients from storage, coordinate large shared workloads, and protect server memory from application bugs. SQLite makes a different tradeoff for applications that own or closely control their local data.

A Database Is Usually One File

An SQLite database is commonly a single file such as school.db, notes.sqlite, or inventory.sqlite3. The extension is a convention rather than a requirement. The file can contain schema definitions, table rows, indexes, views, and triggers.

sql_mastery.db
    -> students table
    -> courses table
    -> enrollments table
    -> indexes
    -> views
    -> triggers

Temporary journal, shared-memory, or write-ahead-log files can appear beside the main file while transactions or WAL mode are active. Copying only the main file at an arbitrary moment is therefore not always a valid live backup.

SQLite Is Embedded

An embedded database engine is packaged within or loaded by the application that uses it. Mobile frameworks, desktop programs, programming-language runtimes, browsers, devices, and command-line tools can all embed SQLite. The user may never see a database installation screen.

Embedding does not mean the data lacks structure. SQLite still stores relational tables and evaluates SQL. It simply changes the deployment and ownership model: the application usually owns both the database connection and the file lifecycle.

Common SQLite Use Cases

SQLite is widely used for mobile app data, offline-first workflows, desktop application documents, local caches, embedded and IoT devices, browser or application state, configuration, prototypes, test fixtures, command-line utilities, and small websites. It also works well as a structured application-file format because one portable file can hold related tables, metadata, indexes, and binary content.

It is particularly strong when data belongs to one application or device, administration must be minimal, and writes are brief. A server database is often better when many independent clients need direct network access, write concurrency is high, centralized user management is required, or data must be distributed across several servers.

SQLite for Learning SQL

SQLite is a convenient learning environment because creating a database requires only opening a file. Students can practice CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, constraints, joins, grouping, subqueries, views, indexes, transactions, and query plans without installing a service.

It is not an exact replacement for another DBMS in tests or education. Dynamic typing, date handling, identity generation, ALTER TABLE support, foreign-key defaults, concurrency, and dialect features differ. Use SQLite to learn relational reasoning while labeling SQLite-specific behavior explicitly.

SQLite Compared with Server Databases

CharacteristicSQLiteTypical client-server DBMS
Engine locationInside the application processSeparate server process
StorageUsually one main database fileServer-managed files and directories
ConnectionOpen a file pathHost, port, database, and credentials
AdministrationMinimal for ordinary useService, accounts, network, patching, backup
Write concurrencyOne writer at a time per database fileDesigned for broader concurrent writes
Best fitLocal application or device dataShared centralized data services

Zero Configuration, No Port, and No Hostname

SQLite does not normally need an installer, service account, listener, port, or hostname. A program opens a path such as C:\Data\sql_mastery.db. If the file does not exist and permissions allow creation, SQLite can create it.

This simplicity removes setup failures associated with stopped services and incorrect network settings. It introduces file-oriented responsibilities instead: the application must choose a safe path, coordinate concurrent access, set filesystem permissions, handle backup, and avoid deleting or replacing an open database.

Get the SQLite Command-Line Shell

Many languages and platforms already include SQLite through a library or module. For direct learning, download the command-line tools from the official SQLite download page. Extract the tools to a stable directory and run sqlite3 or sqlite3.exe depending on the platform.

Verify the shell version, then open or create a database:

sqlite3 --version
sqlite3 sql_mastery.db

The shell opens the named file in the current directory. Use an absolute or carefully controlled path in scripts so the application does not silently create a database in an unexpected working directory.

Configure the Learning Session

The SQLite shell supports dot-commands that configure the client rather than the database engine. A useful interactive setup is:

.headers on
.mode column
.nullvalue NULL

PRAGMA foreign_keys = ON;

Dot-commands do not end with semicolons. SQL and PRAGMA statements do. Enabling foreign keys explicitly is important because applications should not depend on a build-time or future default.

Create Your First Table

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name TEXT NOT NULL,
    email TEXT UNIQUE,
    score REAL CHECK (score BETWEEN 0 AND 100),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

This definition demonstrates an integer key, required text, a uniqueness rule, a numeric check, and a generated default. SQLite stores the schema inside the database file.

Insert and Read Data

INSERT INTO students (student_name, email, score)
VALUES ('John', 'john@test.com', 88.5);

INSERT INTO students (student_name, email, score)
VALUES
    ('Asha', 'asha@test.com', 94),
    ('David', 'david@test.com', 79.5);

SELECT student_id, student_name, email, score
FROM students
ORDER BY score DESC;

Omitting student_id allows the special integer primary key to generate an identifier. Selecting named columns is usually clearer than SELECT * in application code.

Filter, Update, and Delete Rows

SELECT student_name, score
FROM students
WHERE score >= 85
ORDER BY student_name;

UPDATE students
SET score = 90
WHERE email = 'john@test.com';

DELETE FROM students
WHERE email = 'david@test.com';

Always verify the WHERE predicate before an UPDATE or DELETE. SQLite transactions can protect a multi-step change, but they do not infer which rows the user intended to modify.

SQLite Storage Classes

SQLite values use five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. NULL represents missing information. INTEGER stores signed whole numbers. REAL stores floating-point values. TEXT stores strings in supported encodings. BLOB stores bytes exactly as supplied.

Storage classTypical contentExample
NULLMissing or unknown valueNULL
INTEGERWhole number42
REALFloating-point number99.95
TEXTCharacter string'SQLite'
BLOBBinary bytesImage or encoded payload

Dynamic Typing

In ordinary SQLite tables, the type belongs primarily to the stored value rather than rigidly to its column container. A column declaration influences conversion through type affinity, but it does not always prohibit other storage classes.

For example, a column declared INTEGER attempts to store compatible text such as '123' as an integer. If a value cannot be converted according to the affinity rules, a non-STRICT table may preserve another storage class. This flexibility is intentional, but applications must not confuse it with validation.

Type Affinity

Column declarations map to one of SQLite's affinities: INTEGER, REAL, TEXT, BLOB, or NUMERIC. Affinity is a preferred conversion behavior, not the same rigid rule used by many server databases. The mapping depends on text within the declared type name, which can surprise developers who invent arbitrary type names.

Use conventional declarations, explicit CHECK constraints where needed, parameterized values, and application validation. Test edge cases such as numeric-looking text, decimal values, dates, and booleans against SQLite's actual rules.

STRICT Tables

SQLite supports per-table strict typing when STRICT follows the closing parenthesis. A STRICT table requires every column to declare one of the supported type names and rejects values that cannot be converted losslessly.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0),
    sku TEXT UNIQUE
) STRICT;

STRICT mode narrows typing behavior but does not replace keys, CHECK constraints, foreign keys, or application rules. It is a useful choice when predictable types matter more than flexible ingestion.

Primary Keys, rowid, and INTEGER PRIMARY KEY

Most ordinary SQLite tables have an internal 64-bit signed integer key called rowid. A column declared exactly INTEGER PRIMARY KEY becomes an alias for that rowid. Inserting NULL or omitting the column usually causes SQLite to choose an available identifier.

Other PRIMARY KEY declarations normally create uniqueness semantics without becoming the rowid alias. WITHOUT ROWID tables use a different organization and can be appropriate for certain natural or composite keys, but they should be selected from schema and access-pattern requirements.

AUTOINCREMENT Is Usually Unnecessary

INTEGER PRIMARY KEY AUTOINCREMENT changes identifier selection so automatically generated rowids are not reused from previously committed rows. It adds CPU, memory, disk, and write overhead and maintains an internal sequence table.

Most applications need a unique generated key, not a guarantee that an old maximum value is never reused. Use plain INTEGER PRIMARY KEY unless the stronger no-reuse rule is an explicit requirement.

Constraints

SQLite supports PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT, and FOREIGN KEY constraints. Constraints protect invariants close to the data and reject invalid writes regardless of which code path issues them.

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_code TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    credits INTEGER NOT NULL DEFAULT 3
        CHECK (credits BETWEEN 1 AND 6)
);

Remember that SQL NULL semantics affect CHECK and UNIQUE behavior. Test the exact constraints rather than assuming another DBMS behaves identically.

Foreign-Key Enforcement

SQLite supports foreign-key definitions, cascades, and deferred constraints, but applications should explicitly enable enforcement on every database connection:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

The setting cannot be changed effectively in the middle of an active transaction. Build configurations can alter the default, and a future release could change it, so reliable applications set the desired behavior rather than depending on the environment.

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Joins, Grouping, Ordering, and Limits

SQLite supports common relational querying patterns:

SELECT
    s.student_name,
    c.course_code,
    e.enrolled_at
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.student_name, c.course_code
LIMIT 20 OFFSET 0;

INNER JOIN returns matching rows. LEFT JOIN retains rows from the left side when no match exists. GROUP BY and aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize rows. LIMIT and OFFSET support basic result windows, although keyset pagination can be more stable for changing large datasets.

Date and Time Values

SQLite does not have a dedicated date-time storage class. Applications commonly store date and time values as ISO-8601 TEXT, Julian day REAL, or Unix timestamp INTEGER. Built-in date and time functions operate on supported formats.

SELECT date('now') AS utc_date;
SELECT datetime('now') AS utc_datetime;
SELECT datetime('now', '+7 days') AS one_week_later;

Choose one representation, timezone policy, and precision for an application. ISO-8601 UTC text is easy to inspect and sorts correctly when consistently formatted. Do not mix local times and UTC without explicit metadata.

Transactions and ACID Behavior

SQLite provides ACID transactions. A transaction can group several changes so all commit or none remain. The default rollback journal and optional WAL mode provide recovery mechanisms around failures.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1
  AND balance >= 100;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If a required condition fails, issue ROLLBACK. Applications must still define correct transaction boundaries, verify affected rows, handle busy errors, and avoid external side effects that a database rollback cannot undo.

Concurrency and Locking

SQLite supports many simultaneous readers but only one writer at an instant for a database file. Writers usually complete quickly and queue when necessary. This model works well for local applications with short transactions and moderate write frequency.

Long transactions, slow filesystem operations, and multiple processes attempting frequent writes can produce SQLITE_BUSY. Applications should keep transactions short, configure a suitable busy timeout or retry strategy, and avoid waiting for user interaction while a write transaction is open.

Write-Ahead Logging Mode

WAL mode writes committed changes to a separate write-ahead log before checkpointing pages back into the main file. Readers and a writer can proceed concurrently because readers use a stable snapshot, but there is still only one writer at a time.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

WAL mode persists for the database and creates -wal and -shm companion files while in use. All participating processes must be on the same host; WAL is not designed for a database shared across network filesystems. Long readers can delay checkpoints and allow the WAL to grow.

Why Heavy Shared Writes Need Another Design

If many independent clients need to send frequent writes to one shared database over a network, a client-server DBMS is normally a better fit. A server can arbitrate connections, execute concurrent transactions, centralize authentication, and place storage behind a controlled process.

Do not place an SQLite database on a network share and let many computers write directly. Network latency and unreliable filesystem locking can cause poor performance or corruption. An application server can still use SQLite locally and expose an API, but then the server serializes access and owns the file.

Indexes and Query Plans

Indexes can accelerate filters, joins, ordering, and uniqueness checks, but every index consumes storage and adds write work. Create indexes for measured access patterns rather than indexing every column.

CREATE INDEX idx_students_name
ON students(student_name);

CREATE UNIQUE INDEX ux_courses_code
ON courses(course_code);

EXPLAIN QUERY PLAN
SELECT student_id, student_name
FROM students
WHERE email = 'john@test.com';

Use EXPLAIN QUERY PLAN, realistic data, and timing to evaluate indexing. A tiny tutorial table can appear fast even when the design would scan millions of production rows.

Views and Triggers

A view saves a query as a virtual table interface. A trigger runs database logic in response to specified writes.

CREATE VIEW student_course_summary AS
SELECT s.student_name, COUNT(e.course_id) AS course_count
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
GROUP BY s.student_id, s.student_name;

Triggers can maintain audit data or enforce specialized rules, but hidden side effects make systems harder to understand. Keep trigger logic small, documented, and tested for recursion and transaction behavior.

ALTER TABLE and Schema Evolution

SQLite supports common ALTER TABLE operations including renaming a table, renaming a column, adding a column, and dropping a column in supported current releases. More complex transformations can require creating a new table, copying data, recreating indexes and triggers, and replacing the old table inside a transaction.

Use numbered migration scripts and test upgrades from every supported application version. Back up important data before schema migration and verify PRAGMA integrity_check, foreign keys, row counts, and application queries afterward.

SQLite Has No Server-Side Stored Procedures

SQLite does not provide server-side stored procedures like Oracle PL/SQL, SQL Server T-SQL procedures, or PostgreSQL functions. Business workflows normally live in application code, while SQL, constraints, views, and triggers handle data operations and local invariants.

Applications can register custom SQL functions through language bindings. Those functions execute inside the application's SQLite connection, not in an independent database server.

Users, Authentication, and File Security

SQLite has no built-in server users, login accounts, roles, or network authentication because there is no server. Access is primarily controlled by operating-system permissions and by the application that opens the file.

A process that can read the database file can generally inspect its contents with SQLite tools. A process that can modify it may change or replace the data. Store files in protected application directories, use least-privilege filesystem permissions, protect backups, and avoid placing sensitive databases in public or broadly synchronized locations.

SQLite Is Not Automatically Encrypted

The standard SQLite database format is not encrypted merely because it is binary. Anyone with file access can often inspect values using the SQLite shell. Transport encryption is also not relevant to ordinary local access because there is no database network connection.

When encryption at rest is required, use a vetted encryption extension, platform data-protection mechanism, encrypted volume, or a supported product design. Encryption still requires key management, backup planning, and protection of plaintext while the application is running.

ATTACH and DETACH Databases

A connection can attach additional database files and reference them by schema name:

ATTACH DATABASE 'archive.db' AS archive;

SELECT *
FROM archive.old_orders;

DETACH DATABASE archive;

ATTACH is useful for local data movement and cross-file queries. It does not turn separate files into one permanently distributed database, and transaction guarantees across attached databases have conditions that should be reviewed for the chosen journal mode.

SQLite CLI Commands

CommandPurpose
.databasesList the main and attached databases
.tablesList tables and views
.schema studentsShow schema SQL for an object
.indexes studentsList indexes for a table
.headers onDisplay result column names
.mode columnFormat results in aligned columns
.quitExit the shell

Dot-commands belong to the shell and are not portable SQL. An application driver will not normally accept .tables; query sqlite_schema or use driver metadata APIs instead.

Inspect the Schema and Database Health

SELECT type, name, tbl_name, sql
FROM sqlite_schema
WHERE name NOT LIKE 'sqlite_%'
ORDER BY type, name;

PRAGMA foreign_key_check;
PRAGMA integrity_check;

foreign_key_check reports referential violations, while integrity_check performs broader consistency checks. They do not replace application tests or backups, but they are valuable after migrations, recovery, or suspicious shutdowns.

Backup Strategies

The safest backup approach depends on whether the database is active and which API is available. SQLite provides an online backup API, and the shell provides commands such as .backup. VACUUM INTO can create a compact database copy on supported versions.

.backup 'sql_mastery.backup.db'

VACUUM INTO 'sql_mastery.compact.db';

Do not casually copy only the main file while a write transaction or WAL activity is occurring. Coordinate application shutdown or use a supported online method. Test opening the backup and running integrity checks.

Database File Portability

The SQLite file format is designed to be portable across supported platforms and byte orders. A database can often move between Windows, Linux, macOS, mobile devices, and embedded systems without a logical export.

Portability does not remove application compatibility concerns. The receiving application must support the schema, SQLite features, collations, extensions, and data conventions in the file. Copy the database through a consistent backup process rather than while it is being modified.

When SQLite Is a Good Choice

  • Data belongs primarily to one application or device.
  • The application needs offline or local-first storage.
  • Deployment should not require database administration.
  • Read traffic is high but writes are short and moderate.
  • A portable structured application file is useful.
  • The project is a prototype, test fixture, local cache, or small utility.
  • An embedded device needs dependable relational storage.

When a Client-Server Database Is Better

  • Many computers need direct concurrent access to one database.
  • The workload has frequent or long-running concurrent writes.
  • Centralized users, roles, auditing, and network policy are required.
  • The data must be distributed across hosts or availability zones.
  • Server-side stored procedures or administration features are essential.
  • The database must scale beyond one host's file and write boundary.
  • Operations require enterprise replication, failover, and monitoring.

SQLite in Automated Tests

SQLite can make tests fast and isolated because each test can create a temporary file or in-memory database. It is appropriate when the production application also uses SQLite or when tests intentionally target generic repository behavior.

It is risky as a transparent substitute for PostgreSQL, MySQL, SQL Server, or Oracle. SQL syntax, typing, NULL handling, functions, isolation, locking, generated keys, constraints, and query plans differ. Keep production-DBMS integration tests for behaviors that depend on the real engine.

Build a Practical Example Database

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    ordered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price REAL NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

This model supports one-to-many and many-to-many relationships, composite keys, joins, totals, indexes, transaction practice, and foreign-key testing.

Query the Complete Order

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders AS o
JOIN customers AS c
    ON c.customer_id = o.customer_id
JOIN order_items AS oi
    ON oi.order_id = o.order_id
JOIN products AS p
    ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;

The query demonstrates that SQLite remains a relational engine despite its file-based deployment. Relationships are expressed through keys and joins, not through manually parsing a flat file.

Common SQLite Mistakes

Calling SQLite a flat-file database understates its relational engine, transactions, indexes, and query planner. Calling it a server database is also incorrect because applications normally access files in-process.

Other mistakes include forgetting PRAGMA foreign_keys = ON, using AUTOINCREMENT without a no-reuse requirement, assuming declared types are rigid in ordinary tables, storing inconsistent date formats, keeping write transactions open, sharing a database over a network filesystem, treating a copied live main file as a guaranteed backup, and assuming SQLite tests prove compatibility with another DBMS.

A Systematic Troubleshooting Flow

Can the application locate and open the intended file?
    No  -> path or filesystem permission issue
    Yes -> Is the schema the expected version?
              No  -> migration or wrong-file issue
              Yes -> Are foreign keys explicitly enabled?
                        No  -> initialize the connection correctly
                        Yes -> Is SQLITE_BUSY occurring?
                                  Yes -> shorten transactions and review concurrency
                                  No  -> inspect SQL, types, constraints, and query plan

Always log the absolute database path, SQLite library version, journal mode, foreign-key setting, migration version, and exact error code when diagnosing a local database problem.

Practical Review Checklist

  • Store the database in a controlled application directory.
  • Log or verify the absolute path to avoid accidental duplicate files.
  • Enable foreign-key enforcement on every connection.
  • Use constraints and STRICT tables where they fit the data contract.
  • Use parameterized queries rather than concatenating user input.
  • Keep write transactions short and handle busy responses.
  • Choose WAL only after understanding companion files and checkpoints.
  • Avoid direct multi-host access through a network filesystem.
  • Protect database files and backups with filesystem permissions.
  • Use supported online backup methods for active databases.
  • Test schema migrations and backup restoration.
  • Use the production DBMS for integration tests when dialect behavior matters.

Interview Questions and Answers

Why is SQLite called serverless? The database engine runs in the application process and accesses the file directly instead of using a separate database-server process.

Is SQLite a flat file? No. It uses a single-file format, but that file contains a transactional relational database with schema, tables, indexes, views, and triggers.

What is type affinity? It is a column's preferred conversion and storage behavior in SQLite's dynamic type system.

What makes a STRICT table different? It restricts declared type names and rejects values that cannot be losslessly converted to the specified type.

What is special about INTEGER PRIMARY KEY? In an ordinary rowid table, it aliases the internal rowid and can generate integer identifiers.

Does WAL allow multiple writers? No. WAL lets readers and a writer proceed concurrently, but only one writer can write at a time.

How does SQLite authenticate users? It has no built-in server users; access is controlled mainly by the application and filesystem permissions.

Key Takeaway

SQLite is a full relational database engine packaged as an in-process library rather than a separate server. Its single-file portability, zero-configuration setup, ACID transactions, SQL support, and small operational footprint make it highly effective for local application and device data.

Application
    -> SQLite library
    -> database file
        -> schema and constraints
        -> tables and rows
        -> indexes, views, and triggers
        -> transactions and journal or WAL

Use it deliberately: enable foreign keys, understand dynamic typing, choose STRICT tables when useful, keep writes short, protect the file, and back it up through supported methods. Choose a client-server DBMS when direct multi-user network access, high concurrent writes, centralized identity, or distributed operations define the workload.