SQLite
- Summary
-
Discussion
- How's SQLite different from traditional databases?
- What are the use cases where SQLite is a suitable database?
- Where shouldn't I use SQLite?
- How's the adoption of SQLite?
- What tools are available for working with SQLite?
- What's the architecture of SQLite?
- How's the performance of SQLite?
- What are the limits and limitations SQLite?
- Milestones
- Sample Code
- References
- Further Reading
- Article Stats
- Cite As
Traditional databases are often difficult to setup, configure and manage. This includes both relational databases (MySQL, Oracle, PostgreSQL) and NoSQL databases (MongoDB). What if you needed a simple database to manage data locally within your application? What if you don't need to manage remote data across the network? What if you don't have lots of clients writing to the database at the same time? This is where SQLite offers a suitable alternative.
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It's open source. The library is compact, using only 600KiB with all features enabled. It can work even in low-memory environments at some expense of performance. It's even faster than direct filesystem I/O. The database is stored as a single file.
Discussion
-
How's SQLite different from traditional databases? Traditional databases are based on the client-server architecture. Database files are not directly accessed by client applications but via a database server. SQLite takes a different approach. There's no server involved. The entire database–all tables, indices, triggers, views–is stored in a single file.
By eliminating the server, SQLite eliminates complexity. There's no need for multitasking or inter-process communication support from the OS. SQLite only requires read/write to some storage. Taking backup is just a file-copy operation. The file can be copied to any other platform be it 32-bit, 64-bit, little-endian or big-endian.
While SQLite doesn't adopt a client-server architecture, it's common to call applications that read/write to SQLite databases as "SQLite clients".
-
What are the use cases where SQLite is a suitable database? For embedded devices, particularly for resource-constrained IoT devices, SQLite is a good fit. It has a small code footprint, uses memory and disk space efficiently, is reliable and requires little maintenance. Because it comes with a command line interface, it can be used for analysis on large datasets. Even in enterprises, SQLite can stand in for traditional databases for testing, for prototyping or as a local cache that can make the application more resilient to network outages.
Using SQLite directly over a networked file system is not recommended. It can still be used for web applications if managed by a web server. It's suited for small or medium websites that receive less than 100K hits/day.
Applications can use SQLite instead of file access commands such as
fopen
,fread
andfwrite
. These commands are often used to manage various file formats such as XML, JSON or CSV, and there's a need to write parsers for these. SQLite removes this extra work. Because SQLite packs data efficiently, it's faster than these commands. It's been noted that,SQLite does not compete with client/server databases. SQLite competes with fopen().
-
Where shouldn't I use SQLite? SQLite is not suitable for large datasets, such as exceeding 128 TiB. It's also not suited for high-volume websites, particularly for write-intensive apps. SQLite supports concurrent reads but writes are serialized. Each write typically locks database access for a few dozen milliseconds. If more concurrency is desired due to many concurrent clients, SQLite is not suitable.
If a server-side database is desired, traditional databases such as MS SQL, Oracle or MySQL have intrinsic support for multi-core and multi-CPU architectures, user management and stored procedures.
-
How's the adoption of SQLite? It's been claimed that SQLite is the most widely deployed database in the world with over one trillion database instances worldwide. It's present in every Android device, every iOS device, every Mac device, every Windows 10 device, every Firefox/Chrome/Safari browser, every Skype/Dropbox/iTunes client, and most set-top boxes and television sets.
Going by DB-Engines Ranking that's updated monthly, in January 2019, SQLite was seen in tenth position.
Some users of SQLite include Adobe, Airbus, Apple, Bentley, Bosch, Dropbox, Facebook, General Electric, Google, Intuit, Library of Congress, McAfee, and Microsoft.
-
What tools are available for working with SQLite? SQLite comes with a command line interface (CLI) called
sqlite3
to create, modify and query an SQLite database. Another useful CLI tool issqlite3_analyzer
that tells about memory usage by tables and indices.For those who prefer a graphical user interface (GUI), there are a number of utilities: SQLite Database Browser, SQLite Studio, SQLite Workbench, TablePlus, Adminer, DBeaver, DbVisualizer and FlySpeed SQL Query. Most are free and cross-platform with lots of useful features. DbVisualizer has Free and Pro editions. Navicat for SQLite is a paid product. Adminer is implemented in a single PHP file to be executed by a web server.
To embed SQLite into your own application, there are dozens of language bindings including C, C++, PHP, Python, Java, Go, MATLAB, and many more. SQLite's inventor noted that it was designed from the beginning to be used with Tcl. Tcl bindings have been present even before version 1.0 and regression tests are written in Tcl.
-
What's the architecture of SQLite? SQLite compiles SQL statements into bytecode, which runs on a virtual machine. To generate the bytecode, SQL statements go via the tokenizer (identify tokens), parser (associate meaning to tokens based on context) and finally to the code generator (generate the bytecode).
SQLite itself is implemented in C. SQL functions are implemented as callbacks to C routines. In terms of lines of code, the code generator is the biggest followed by the virtual machine.
Database itself is organized as a B-Tree. Each table and index has a separate tree but all trees are stored on the same file. Information from disk is accessed in default page size of 4096 bytes. Pager does the reading, writing and caching. It also does rollback, atomic commit and locking of the file.
-
How's the performance of SQLite? SQLite is 35% faster than filesystem I/O. When holding 10 KB blobs, an SQLite file uses 20% less disk space.
In one study, SQL CE 4.0 was compared against SQLite 3.6. SQLite fared slightly better for read operation but for everything else SQL CE was significantly faster. However, the hardware was a Dell Workstation with two Intel Xeons and 8 GB of RAM.
When compared against Realm, SQLite performed better with inserts but with counts and queries Realm was faster.
In Python environment, one study compared
pandas
withsqlite
. SQLite was used in two variants: file database and in-memory database. SQLite performed better with select, filter and sort operations. Pandas did better with group-by, load and join operations. There was no great performance gain with in-memory SQLite as compared to file-based SQLite. -
What are the limits and limitations SQLite? Limits are documented on the SQLite's official website. We mention a few of them:
- Maximum database size is 128 TiB or 140 TB.
- Maximum number of tables in a schema is 2147483646.
- Maximum of 64 tables can be used in a JOIN.
- Maximum number of rows in a table is 2<sup>64</sup>.
- Maximum number of columns in a table/index/view is 1000 but can be increased to 32767.
- Maximum string/BLOB length is 1 billion bytes by default but this can be increased to 2<sup>31</sup>-1.
- Maximum length of an SQL statement is 1 million bytes but can be increased to 1073741824.
SQLite implements SQL with some omissions. For example, RIGHT OUTER JOIN and FULL OUTER JOIN are not supported. There's partial support for ALTER TABLE. GRANT and REVOKE commands are not supported. This implies there's no user management support. BOOLEAN and DATETIME are examples of data types missing in SQLite. AUTOINCREMENT doesn't work the same way as in MySQL. SQLite has a flexible type system (string can be assigned to an integer column) but from the viewpoint of static typing this can be seen as a limitation.
Milestones
2000
2001
2003
2004
2006
2008
Sample Code
References
- Cunningham, Elmer. 2015. "A Database for the Edge of the Network." CMU Talk, via SlidePlayer, September 17. Accessed 2019-01-13.
- DB-Engines. 2019. "DB-Engines Ranking - Trend Popularity." DB-Engines. Accessed 2019-01-14.
- Galkin, Alexander. 2011. "Benchmarking the Performance of Embedded DB for .NET: SQL CE 4.0 vs SQLite." Code Project, July 03. Accessed 2019-01-13.
- Gupta, Lipika. 2018. "How Realm is Better Compared To SQLite." DZone, June 21. Accessed 2019-01-13.
- Hipp, D. Richard. 2004. "SQLite and Tcl." 11'th Annual Tcl/Tk Conference, October 11-15, New Orleans, Louisiana. Accessed 2019-01-14.
- KeyCDN. 2018. "SQLite vs MySQL - Comparing 2 Popular Databases." KeyCDN, October 04. Accessed 2019-01-13.
- Kreibich, Jay A. 2010. "Using SQLite." O'Reilly Media, Inc., August 10. Accessed 2019-01-13.
- Paczuski, Paul. 2018. "SQLite vs Pandas: Performance Benchmarks." Blog, The Data Incubator, May 23. Accessed 2019-01-13.
- Python. 2019. "Python Downloads." Python. Accessed 2019-01-14.
- Python Docs. 2019. "What's New in Python 2.5." Python Docs, January 01. Accessed 2019-01-14.
- SQLite. 2019a. "Release History." SQLite. Accessed 2019-01-13.
- SQLite. 2019b. "Command Line Shell For SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019c. "The sqlite3_analyzer.exe Utility Program." SQLite. Accessed 2019-01-13.
- SQLite. 2019d. "Most Widely Deployed and Used Database Engine." SQLite. Accessed 2019-01-13.
- SQLite. 2019e. "Well-Known Users of SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019f. "About SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019g. "Architecture of SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019h. "Limits In SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019i. "In-Memory Databases." SQLite. Accessed 2019-01-13.
- SQLite. 2019j. "SQLite Shared-Cache Mode." SQLite. Accessed 2019-01-13.
- SQLite. 2019k. "Appropriate Uses For SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019l. "Features Of SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019m. "SQL Features That SQLite Does Not Implement." SQLite. Accessed 2019-01-13.
- SQLite. 2019n. "Quirks, Caveats, and Gotchas In SQLite." SQLite. Accessed 2019-01-13.
- SQLite. 2019o. "35% Faster Than The Filesystem." SQLite. Accessed 2019-01-13.
- SQLite. 2019p. "Syntax Diagrams For SQLite." SQLite. Accessed 2019-01-14.
- Tabona, Andrew. 2015. "Top 10 free database tools for sys admins." TechTalk, July 15. Accessed 2019-01-13.
- Wikipedia. 2019. "SQLite." Wikipedia, January 10. Accessed 2019-01-13.
Further Reading
- Hipp, Richard. 2015. "SQLite: The Database at the Edge of the Network with Dr. Richard Hipp." Skookum, on YouTube, May 26. Accessed 2019-01-13.
- Mamgain, Shivam. 2015. "Getting Started with SQLite3 - Basic Commands." SitePoint, March 23. Accessed 2019-01-13.
- Cunningham, Elmer. 2015. "A Database for the Edge of the Network." CMU Talk, via SlidePlayer, September 17. Accessed 2019-01-13.
- Kreibich, Jay A. 2010. "Using SQLite." O'Reilly Media, Inc., August 10. Accessed 2019-01-13.
- Madushan, Dhanushka. 2018. "How the SQLite Database Works." DZone, December 19. Accessed 2019-01-13.
- Feinstein, Jason. 2017. "Squeezing Performance from SQLite: Insertions." Medium, April 14. Accessed 2019-01-13.
Article Stats
Cite As
See Also
- Android SQLite
- Embedded Databases
- In-Memory Databases
- SQL Databases
- Types of Databases
- Realm