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 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 good fit 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). Watch for these quirks 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.