SQLite gets dismissed as a toy database. It ships with Python, it is the default for mobile apps, and it turns up in tutorials about learning SQL. None of that is wrong, but it obscures what SQLite actually is: a highly capable, in-process relational engine that runs without a server, fits in a single file, and handles workloads most teams would never think to throw at it.

The useful reframe is to think of SQLite not as a limited database but as a file format that understands SQL. A single .db file carries your schema, your indexes, your data, and your relationships. You can copy it, version it, open it with any SQLite client, and query it with the same SQL you already know. That is a different thing from a JSON file or a CSV, and it is a different thing from a Postgres instance.

SQLite as a File Format

The practical properties follow from the architecture:

  • In-process operations: No network round-trip, no connection pool, no server to manage. Queries run in microseconds.
  • Relational structure: Foreign keys, indexes, and constraints, in a file you can pass around or embed in a library.
  • SQL: The same query language your team already knows, including aggregations, window functions, and full-text search via FTS5.
  • Zero configuration: Import the library, open a file, query. Nothing else required.

Where It Works Well

The file-format architecture — portable, self-contained, inspectable — is what makes SQLite genuinely useful in production contexts, not just convenient for throwaway work:

  • Embedded lookup tables: Instead of an in-memory hash map or a separate caching service, a SQLite file gives you an indexed, queryable store with a fraction of the memory cost. Queries run in hundreds of microseconds. The file ships with the application, requires no external dependency, and can be rebuilt and swapped atomically.
  • Data pipeline staging: Intermediate results in a processing pipeline can live in a SQLite file rather than in memory or in a shared database. The file is self-contained, easy to inspect mid-pipeline, and trivially versioned alongside the code that produced it.
  • Testing data fixtures: Complex test datasets with relational structure are easier to build and query as SQLite than as nested JSON or in-memory dicts. The fixture file travels with the test suite.

Example: Low-Latency Metadata Store

A real-world example from my work at ComplyAdvantage illustrates this perfectly. We needed to enhance data with extra features to improve our machine learning models. Over time, this system grew to hold approximately 6-7GB of lookup data in memory per process. The requirement for frequent, low-latency lookups precluded using a separate service, necessitating that all data be embedded in a library.

When spread across many processes across many instances, this proved problematic both in production and during test runs of our CI/CD pipelines, leading to recurring and jarring out of memory issues, and a reluctance to add new lookup data to the system for fear of having to add substantive memory resource across the board each time.

This system is now powered by a SQLite based lookup database with a variety of indexes, offering performance measured in hundreds of microseconds and an enormous reduction of memory pressure, down to 200-300Mb, most of which is an internal LRU cache. We feel confident that we can add much more data to the system without issue, and no longer worry about out of memory errors.

All of this was built within a couple of weeks and is easy to understand and maintain by the whole team.

Summary

SQLite is the right tool for a specific set of problems: in-process, embedded, low-operational-overhead storage where SQL and indexing add real value over plain data structures. The constraint to know is write concurrency: there are known bottlenecks for effectively single-threaded write concurrency with a database of this type. For workloads that need multiple concurrent writers, a client-server database is the right choice. For everything else, SQLite is considerably more capable than its reputation suggests.

Wrapping it behind a clean data access layer keeps your options open if you outgrow it.