SQLite is a self-contained relational database packaged as a single file. The whole database — schema, tables, indices, data — lives in one .sqlite (or .db) file. There’s no server process to start, no network configuration, no separate user-management system. An application that uses SQLite simply opens the file and talks to it through the SQLite library, which is embedded in the application’s address space.
That packaging makes SQLite a natural choice for situations where a Relational database is useful but a heavyweight DBMS would be overkill: desktop applications, mobile apps (every iOS and Android device ships SQLite), browser local storage, scientific data files where the structure is relational but the dataset is small to medium-sized.
Like other relational databases, SQLite is queried with SQL. The same SELECT, INSERT, UPDATE, DELETE, primary keys, foreign keys, and constraints from a server-based DBMS apply. The differences are operational: SQLite has weaker type enforcement than MySQL or PostgreSQL (its types are mostly suggestions), no true user-account system, and only single-writer concurrency — many readers can hit the file at once, but writes are serialized.
Internally, SQLite uses manifest typing: a value’s type is a property of the value itself, not of the column. There are exactly five storage classes — NULL, INTEGER, REAL, TEXT, BLOB — and a column declared as INTEGER will happily accept a TEXT value unless a STRICT table or a CHECK constraint forbids it. Foreign-key enforcement also has to be turned on explicitly with PRAGMA foreign_keys = ON; (off by default for backward compatibility). These quirks are worth remembering when porting code between SQLite and a stricter database.
For data-science work, SQLite is convenient when the dataset is a few gigabytes of structured tabular data — a notebook can open the file, run queries, and close it again without provisioning anything. For larger or shared datasets, MySQL, PostgreSQL, or one of the other server-based databases is the right step up. For very large unstructured scientific arrays, HDF5 is better than any relational database.