SQLite
SQLite is a relational database engine that runs inside an application instead of as a separate database server. It stores tables, indexes, and other database content in a portable file, which suits local application data and devices that need transactional storage without a database service.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
SQLite
SQLite is an embedded relational database engine. An application links the SQLite library and calls it in the same process. There is no separate server, network protocol, account system, or service daemon between the application and the database. A complete database normally lives in one cross-platform file.
That arrangement changes the operational boundary. The application owns the database connection, while the operating system owns file access and locking. SQLite parses SQL, plans and executes queries, and maintains transactional storage. It does not accept remote clients or manage a fleet of database servers.
How a statement reaches storage
SQLite divides SQL processing from file management:
SQL text
-> tokenizer and parser
-> code generator
-> virtual machine bytecode
-> B-tree and page cache
-> pager and journal
-> virtual file system
-> database file
The parser turns SQL into an internal representation. The code generator produces instructions for SQLite's virtual database engine. The virtual machine executes those instructions against B-trees that hold tables and indexes. The pager reads and writes fixed-size pages, coordinates locks, and implements rollback or write-ahead logging. A virtual file system adapts those operations to the host operating system.
This layered design keeps SQL semantics above platform-specific file operations. It also explains why copying an active database file without a supported backup method can produce an inconsistent copy: a transaction may involve the database file and a journal or write-ahead log at the same time.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.sqlite.org/about.html
Supports
- Embedded, in-process, serverless, transactional architecture
- Single cross-platform database file and core-engine landscape entry
- https://www.sqlite.org/arch.html
Supports
- Parser, code generator, VDBE, B-tree, pager, page cache, and VFS execution path
- Pager responsibility for locking, rollback, and atomic commit
- https://www.sqlite.org/whentouse.html
Supports
- Application-local, device, file-format, testing, and modest website uses
- Client-server guidance for networked data and many concurrent writers
- https://www.sqlite.org/lang_transaction.html
Supports
- Implicit and explicit transactions, one writer, and multiple readers
- DEFERRED, IMMEDIATE, and EXCLUSIVE transaction behavior
- https://www.sqlite.org/transactional.html
Supports
- Atomic, consistent, isolated, and durable transaction claims
- https://www.sqlite.org/wal.html
Supports
- WAL append, reader snapshots, checkpoints, and reader-writer overlap
- Same-host requirement, checkpoint delay, and WAL growth behavior
- https://www.sqlite.org/lockingv3.html
Supports
- Rollback-journal locking states, commit exclusion, and hot-journal recovery
- https://www.sqlite.org/fileformat.html
Supports
- SQLite 3 file-format generation, pages, B-trees, WAL, and schema storage
- https://www.sqlite.org/datatype3.html
Supports
- NULL, INTEGER, REAL, TEXT, and BLOB storage classes
- Dynamic typing, type affinity, and value conversion
- https://www.sqlite.org/stricttables.html
Supports
- STRICT table types, lossless conversion, ANY, and file-format compatibility
- SQLite 3.37.0 date and schema-contract timeline milestone
- https://www.sqlite.org/lang_createtable.html#rowid
Supports
- Ordinary rowid tables and exact INTEGER PRIMARY KEY alias behavior
- https://www.sqlite.org/withoutrowid.html
Supports
- WITHOUT ROWID storage organized by the declared primary key
- https://www.sqlite.org/foreignkeys.html
Supports
- Foreign-key support, per-connection enablement, and 2009 introduction
- https://www.sqlite.org/queryplanner.html
Supports
- Scans, rowid lookup, multi-column indexes, leftmost order, and covering indexes
- Read benefits and write/storage costs of indexes
- https://www.sqlite.org/eqp.html
Supports
- EXPLAIN QUERY PLAN output and SCAN versus SEARCH interpretation
- https://www.sqlite.org/lang_analyze.html
Supports
- ANALYZE statistics and PRAGMA optimize guidance
- https://www.sqlite.org/backup.html
Supports
- Online backup API and consistent live database copies
- https://www.sqlite.org/lang_vacuum.html
Supports
- VACUUM rebuild behavior, space requirements, locking, and VACUUM INTO
- https://www.sqlite.org/howtocorrupt.html
Supports
- Risks from separating journals, unsafe copies, bad locking, and file mishandling
- https://www.sqlite.org/pragma.html
Supports
- Busy timeout, integrity checks, quick checks, page counts, and checkpoint pragmas
- https://www.sqlite.org/limits.html
Supports
- Documented size and structural limits and distinction from practical workload fit
- https://www.sqlite.org/lang.html
Supports
- SQL language surface including statements, expressions, clauses, and pragmas
- https://www.sqlite.org/json1.html
Supports
- JSON functions and JSON1 history
- https://www.sqlite.org/loadext.html
Supports
- Runtime extension capabilities and native-code loading boundary
- https://www.sqlite.org/cli.html
Supports
- Official command-line shell and logical dump reference
- https://www.sqlite.org/chronology.html
Supports
- Release dates across the complete SQLite timeline
- https://www.sqlite.org/versionnumbers.html
Supports
- Major format changes and gdbm storage in SQLite 1.x
- https://www.sqlite.org/releaselog/3_0_0.html
Supports
- SQLite 3.0 file-format, text, and typing changes
- https://www.sqlite.org/releaselog/3_7_0.html
Supports
- WAL introduction in SQLite 3.7.0 on 2010-07-21
- https://www.sqlite.org/releaselog/3_8_0.html
Supports
- Partial indexes in SQLite 3.8.0
- https://www.sqlite.org/partialindex.html
Supports
- Partial-index subset and write/storage tradeoffs
- https://www.sqlite.org/releaselog/3_8_3.html
Supports
- Common table expressions in SQLite 3.8.3
- https://www.sqlite.org/lang_with.html
Supports
- Ordinary and recursive common table expressions
- https://www.sqlite.org/releaselog/3_9_0.html
Supports
- JSON1 and indexes on expressions in SQLite 3.9.0
- https://www.sqlite.org/releaselog/3_25_0.html
Supports
- Window functions in SQLite 3.25.0
- https://www.sqlite.org/windowfunctions.html
Supports
- Window-function semantics and 2018 introduction date
- https://www.sqlite.org/releaselog/3_37_0.html
Supports
- STRICT tables in SQLite 3.37.0
- https://github.com/sindresorhus/awesome
Supports
- Discovery path to the curated Database Tools awesome list
- https://github.com/mgramin/awesome-db-tools
Supports
- Curation of LiteCLI, sqlite-utils, DBeaver, DataGrip, Beekeeper Studio, DbGate, and Litestream
- https://litecli.com/
Supports
- SQLite terminal client with completion and syntax highlighting
- https://sqlite-utils.datasette.io/en/stable/
Supports
- CLI and Python library for creating, loading, transforming, indexing, and querying SQLite files
- https://dbeaver.com/docs/dbeaver/Database-driver-SQLite/
Supports
- SQLite file connections, objects, SQL work, and extension configuration in DBeaver
- DBeaver landscape placement
- https://www.jetbrains.com/help/datagrip/sqlite.html
Supports
- SQLite data sources, files, schema inspection, data editing, and consoles in DataGrip
- https://docs.beekeeperstudio.io/user_guide/connecting/sqlite/
Supports
- Opening and creating SQLite files and opt-in runtime extension loading
- Beekeeper Studio landscape placement
- https://www.dbgate.io/
Supports
- SQLite support, relational browsing, foreign-key navigation, and query completion
- https://sqlitebrowser.org/
Supports
- Visual creation, browsing, editing, querying, import, and export of SQLite files
- https://litestream.io/
Supports
- Continuous SQLite change replication to local or cloud storage
- Litestream landscape placement
- https://fly.io/docs/litefs/
Supports
- File-system transaction replication, primary, read replicas, and write forwarding
- https://docs.turso.tech/libsql
Supports
- SQLite-compatible engine relationship, remote SDK boundary, and hosted-service placement
- https://developers.cloudflare.com/d1/
Supports
- Managed SQLite SQL semantics, Worker bindings, HTTP API, and disaster recovery