SQLite is a relational database management system contained in a C programming library. In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program.
SQLite is an open source SQL database that stores data to a text file on a device. It is a popular choice as an embedded database software for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones).
What Is SQLite?
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.
Features of SQLite?
- Self-contained: no external dependencies.
- Serverless
- A Zero-Configuration Database
- Transactional
- Full-Featured SQL
- Single-file Cross-platform Database
- Great for use as an application file format
- Supports terabyte-sized databases and gigabyte-sized strings and blobs
- Small code footprint: less than 600KiB fully configured or much less with optional features omitted.
- Simple, easy to use API.
- Fast: In some cases, SQLite is faster than direct filesystem I/O
- Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately.
- Well-commented source code with 100% branch test coverage.
- Available as a single ANSI-C source-code file that is easy to compile and hence is easy to add into a larger project.
- Cross-platform: Android, *BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems.
- Sources are in the public domain. Use for any purpose.
- Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.
Some feature explained below in details,
- Self-contained: no external dependencies.
SQLite is “stand-alone” or “self-contained” in the sense that it has very few dependencies. It runs on any operating system, even stripped-down bare-bones embedded operating systems. SQLite uses no external libraries or interfaces (other than a few standard C-library calls described below). The entire SQLite library is encapsulated in a single source code file that requires no special facilities or tools to build.
Normally, an RDBMS such as MySQL, PostgreSQL, etc., requires a separate server process to operate. The applications that want to access the database server use TCP/IP protocol to send and receive requests. This is called client/server architecture.
The following diagram illustrates the RDBMS client/server architecture:
SQLite database is integrated with the application that accesses the database. The applications interact with the SQLite database read and write directly from the database files stored on disk.
The following diagram illustrates the SQLite server-less architecture:
- SQLite Is A Zero-Configuration Database (no setup or administration needed)
SQLite does not need to be “installed” before it is used. There is no “setup” procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.
Other database engines may be comparable to SQLite, but implementing the initial installation and configuration for those engines can often be intimidating.
- SQLite is Transactional
A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.
We would like to reiterate for emphasis: All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by
- a program crash,
- an operating system crash, or
- a power failure
- Full-Featured SQL
SQLite may be small in size and have “Lite” in its name, but it is not lacking in capability. SQLite has a full-featured SQL implementation, including:
- Tables, indexes, triggers, and views in unlimited quantity
- Up to 32K columns in a table and unlimited rows
- Multi-column indexes
- Indexes can use DESC and COLLATE
- Partial indexes
- Indexes On Expressions
- Clustered indexes
- Covering indexes
- CHECK, UNIQUE, NOT NULL, and FOREIGN KEY constraints.
- ACID transactions using BEGIN, COMMIT, and ROLLBACK
- Nested transactions using SAVEPOINT, RELEASE, and ROLLBACK TO
- Subqueries, including correlated subqueries
- Up to 64-way joins
- LEFT JOIN
- DISTINCT, ORDER BY, GROUP BY, HAVING, LIMIT, and OFFSET
- UNION, UNION ALL, INTERSECT, and EXCEPT
- A rich library of standard SQL functions
- Aggregate functions including DISTINCT aggregates
- Window functions
- UPDATE, DELETE, and INSERT (of course)
- Common table expressions including recursive common table expressions
- Row values
- UPSERT
- An advanced query planner
- Full-text search
- R-tree indexes
- JSON support
- The IS operator
- Table-valued functions
- REPLACE INTO
- VACUUM
- REINDEX
- The GLOB operator
- Hexadecimal integer literals
- The ON CONFLICT clause
- The INDEXED BY clause
- Virtual tables
- Multiple databases on the same database connection using ATTACH DATABASE
- The ability to add application-defined SQL functions, including aggregate and table-valued functions.
- Application-defined collating functions
- Single-file Cross-platform Database
A database in SQLite is a single disk file. Furthermore, the file format is cross-platform. A database that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across 32-bit and 64-bit machines and between big-endian and little-endian architectures.
- SQLite As An Application File Format
An “application file format” is the file format used to persist application state to disk or to exchange information between programs.
Most application formats fit into one of these three categories:
- Fully Custom Formats
Custom formats are specifically designed for a single application. DOC, DWG, PDF, XLS, and PPT are examples of custom formats. Custom formats are usually contained within a single file, for ease of transport.
- Pile-of-Files Formats
Sometimes the application state is stored as a hierarchy of files. Git is a prime example of this, though the phenomenon occurs frequently in one-off and bespoke applications. A pile-of-files format essentially uses the filesystem as a key/value database, storing small chunks of information into separate files.
- Wrapped Pile-of-Files Formats
Some applications use a Pile-of-Files that is then encapsulated into some kind of single-file container, usually a ZIP archive. EPUB, ODT and ODP are examples of this approach. An EPUB book is really just a ZIP archive that contains various XHTML files for the text of book chapters, GIF and JPEG images for the artwork, and a specialized catalog file that tells the eBook reader how all the XML and image files fit together. Some applications use a Pile-of-Files that is then encapsulated into some kind of single-file container, usually a ZIP archive. EPUB, ODT and ODP are examples of this approach.
An SQLite database file with a defined schema often makes an excellent application file format. Here are a few of these advantages,
- Simplified Application Development
- Single-File Documents
- High-Level Query Language
- Accessible Content
- Cross-Platform
- Atomic Transactions
- Incremental And Continuous Updates
- Easily Extensible
- Performance
- Concurrent Use By Multiple Processes
- Multiple Programming Languages
- Better Applications
Appropriate Uses for SQLite?
- Database for the Internet of Things. SQLite is popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. SQLite has a small code footprint, makes efficient use of memory, disk space, and disk bandwidth, is highly reliable, and requires no maintenance from a Database Administrator.
- Application File Format. Rather than using fopen() to write XML, JSON, CSV, or some proprietary format into disk files used by your application, use an SQLite database. You’ll avoid having to write and troubleshoot a parser, your data will be more easily accessible and cross-platform, and your updates will be transactional.
- Website Database. Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites.
- Stand-in for an Enterprise RDBMS. SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing and which makes demos perky and easy to launch.
Development and distribution?
SQLite’s code is hosted with Fossil, a distributed version control system that is itself built upon an SQLite database.
A standalone command-line program is provided in SQLite’s distribution. It can be used to create a database, define tables, insert and change rows, run queries and manage an SQLite database file. It also serves as an example for writing applications that use the SQLite library.
SQLite uses automated regression testing prior to each release.
SQLite is not the perfect application file format for every situation. But in many cases, SQLite is a far better choice than either a custom file format, a pile-of-files, or a wrapped pile-of-files. SQLite is a high-level, stable, reliable, cross-platform, widely-deployed, extensible, performant, accessible, concurrent file format. It deserves your consideration as the standard file format on your next application design.
Reference Sources:
