• SQLite logo. Source: Wikipedia 2019.
    image
  • Conn2 and Conn3 shared a single cache. Source: SQLite 2019j, fig. 1.
    image
  • Syntax diagram for ALTER TABLE statement. Source: SQLite 2019p.
    image
  • SQLite doesn't need a server and database is just a file. Source: Kreibich 2010, sec. 1.1.
    image
  • Architecture diagram of SQLite. Source: SQLite 2019g.
    image

SQLite

userimg
mohanjo
1065 DevCoins
userimg
tintin
6 DevCoins
userimg
arvindpdmn
4 DevCoins
3 authors have contributed to this article
Last updated by mohanjo
on 2019-01-14 17:50:03
Created by tintin
on 2018-06-27 05:54:16

Summary

 image
SQLite logo. Source: Wikipedia 2019.

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.

Milestones

Aug
2000

Version 1.0 of SQLite is released. The earliest public release of alpha code dates back to May 2000. D. Richard Hipp creates SQLite from a need to have a database that didn't need a database server or a database administrator. The syntax and semantics are based on PostgreSQL 6.5.

Sep
2001

Version 2.0.0 of SQLite is released. While 1.0 was based on GNU Database Manager (gdbm), 2.0.0 uses a custom B-tree implementation.

May
2003

Support for in-memory database is added in version 2.8.1. This is useful when performance is important and data persistence is not required. Data is lost when the database connection is closed.

Sep
2004

Version 3.0.7 of SQLite is released. An alpha release of 3.0.0 was available earlier in June 2004.

Jan
2006
image

In version 3.3.0, a shared-cache mode is introduced in which multiple connections from the same thread can share the same data and schema cache. This reduces IO access and memory requirements. In version 3.5.0 (September 2007), this is extended to an entire process rather than just a thread.

Sep
2006

Python standard library version 2.5 includes SQLite under the package name sqlite3.

Oct
2008
image

From version 3.6.4, SQLite language syntax is described as syntax diagrams rather than BNF notation.

Dec
2018

Version 3.26.0 of SQLite is released.

Discussion

  • How's SQLite different from traditional databases?
     image
    SQLite doesn't need a server and database is just a file. Source: Kreibich 2010, sec. 1.1.

    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 and fwrite. 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 is sqlite3_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?
     image
    Architecture diagram of SQLite. Source: SQLite 2019g.

    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 with sqlite. 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 264.
    • 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 231-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.

Sample Code

  • -- Source: https://www.sitepoint.com/getting-started-sqlite3-basic-commands/
    -- Accessed: 2019-01-13
     
    -- Example SQLite commands
     
    CREATE TABLE comments (
        post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        website_url TEXT NULL,
        comment TEXT NOT NULL );
     
    INSERT INTO comments ( name, email, website_url, comment )
        VALUES ( 'Shivam Mamgain', This email address is being protected from spambots. You need JavaScript enabled to view it.', 'shivammg.blogspot.com', 'Great tutorial for beginners.' );
     
    SELECT post_id, name, email, website_url, comment
        FROM comments;
    SELECT *
        FROM comments;
     
    UPDATE comments
        SET email = This email address is being protected from spambots. You need JavaScript enabled to view it.'
        WHERE name = 'Shivam Mamgain';
     
    DELETE FROM comments
        WHERE post_id = 9;
     
    ALTER TABLE comments
        ADD COLUMN username TEXT;
     
    ALTER TABLE comments
        RENAME TO Coms;
     
    DROP TABLE Coms;

References

  1. Cunningham, Elmer. 2015. "A Database for the Edge of the Network." CMU Talk, via SlidePlayer, September 17. Accessed 2019-01-13.
  2. DB-Engines. 2019. "DB-Engines Ranking - Trend Popularity." DB-Engines. Accessed 2019-01-14.
  3. 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.
  4. Gupta, Lipika. 2018. "How Realm is Better Compared To SQLite." DZone, June 21. Accessed 2019-01-13.
  5. Hipp, D. Richard. 2004. "SQLite and Tcl." 11'th Annual Tcl/Tk Conference, October 11-15, New Orleans, Louisiana. Accessed 2019-01-14.
  6. KeyCDN. 2018. "SQLite vs MySQL - Comparing 2 Popular Databases." KeyCDN, October 04. Accessed 2019-01-13.
  7. Kreibich, Jay A. 2010. "Using SQLite." O'Reilly Media, Inc., August 10. Accessed 2019-01-13.
  8. Paczuski, Paul. 2018. "SQLite vs Pandas: Performance Benchmarks." Blog, The Data Incubator, May 23. Accessed 2019-01-13.
  9. Python. 2019. "Python Downloads." Python. Accessed 2019-01-14.
  10. Python Docs. 2019. "What's New in Python 2.5." Python Docs, January 01. Accessed 2019-01-14.
  11. SQLite. 2019a. "Release History." SQLite. Accessed 2019-01-13.
  12. SQLite. 2019b. "Command Line Shell For SQLite." SQLite. Accessed 2019-01-13.
  13. SQLite. 2019c. "The sqlite3_analyzer.exe Utility Program." SQLite. Accessed 2019-01-13.
  14. SQLite. 2019d. "Most Widely Deployed and Used Database Engine." SQLite. Accessed 2019-01-13.
  15. SQLite. 2019e. "Well-Known Users of SQLite." SQLite. Accessed 2019-01-13.
  16. SQLite. 2019f. "About SQLite." SQLite. Accessed 2019-01-13.
  17. SQLite. 2019g. "Architecture of SQLite." SQLite. Accessed 2019-01-13.
  18. SQLite. 2019h. "Limits In SQLite." SQLite. Accessed 2019-01-13.
  19. SQLite. 2019i. "In-Memory Databases." SQLite. Accessed 2019-01-13.
  20. SQLite. 2019j. "SQLite Shared-Cache Mode." SQLite. Accessed 2019-01-13.
  21. SQLite. 2019k. "Appropriate Uses For SQLite." SQLite. Accessed 2019-01-13.
  22. SQLite. 2019l. "Features Of SQLite." SQLite. Accessed 2019-01-13.
  23. SQLite. 2019m. "SQL Features That SQLite Does Not Implement." SQLite. Accessed 2019-01-13.
  24. SQLite. 2019n. "Quirks, Caveats, and Gotchas In SQLite." SQLite. Accessed 2019-01-13.
  25. SQLite. 2019o. "35% Faster Than The Filesystem." SQLite. Accessed 2019-01-13.
  26. SQLite. 2019p. "Syntax Diagrams For SQLite." SQLite. Accessed 2019-01-14.
  27. Tabona, Andrew. 2015. "Top 10 free database tools for sys admins." TechTalk, July 15. Accessed 2019-01-13.
  28. Wikipedia. 2019. "SQLite." Wikipedia, January 10. Accessed 2019-01-13.

Milestones

Aug
2000

Version 1.0 of SQLite is released. The earliest public release of alpha code dates back to May 2000. D. Richard Hipp creates SQLite from a need to have a database that didn't need a database server or a database administrator. The syntax and semantics are based on PostgreSQL 6.5.

Sep
2001

Version 2.0.0 of SQLite is released. While 1.0 was based on GNU Database Manager (gdbm), 2.0.0 uses a custom B-tree implementation.

May
2003

Support for in-memory database is added in version 2.8.1. This is useful when performance is important and data persistence is not required. Data is lost when the database connection is closed.

Sep
2004

Version 3.0.7 of SQLite is released. An alpha release of 3.0.0 was available earlier in June 2004.

Jan
2006
image

In version 3.3.0, a shared-cache mode is introduced in which multiple connections from the same thread can share the same data and schema cache. This reduces IO access and memory requirements. In version 3.5.0 (September 2007), this is extended to an entire process rather than just a thread.

Sep
2006

Python standard library version 2.5 includes SQLite under the package name sqlite3.

Oct
2008
image

From version 3.6.4, SQLite language syntax is described as syntax diagrams rather than BNF notation.

Dec
2018

Version 3.26.0 of SQLite is released.

Tags

See Also

  • Android SQLite
  • Embedded Databases
  • In-Memory Databases
  • SQL Databases
  • Types of Databases
  • Realm

Further Reading

  1. 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.
  2. Mamgain, Shivam. 2015. "Getting Started with SQLite3 - Basic Commands." SitePoint, March 23. Accessed 2019-01-13.
  3. Cunningham, Elmer. 2015. "A Database for the Edge of the Network." CMU Talk, via SlidePlayer, September 17. Accessed 2019-01-13.
  4. Kreibich, Jay A. 2010. "Using SQLite." O'Reilly Media, Inc., August 10. Accessed 2019-01-13.
  5. Madushan, Dhanushka. 2018. "How the SQLite Database Works." DZone, December 19. Accessed 2019-01-13.
  6. Feinstein, Jason. 2017. "Squeezing Performance from SQLite: Insertions." Medium, April 14. Accessed 2019-01-13.

Article Stats

Author-wise Stats for Article Edits

Author
No. of Edits
No. of Chats
DevCoins
4
0
1065
1
0
6
1
0
4
1512
Words
0
Chats
6
Edits
4
Likes
966
Hits

Cite As

Devopedia. 2019. "SQLite." Version 6, January 14. Accessed 2019-08-21. https://devopedia.org/sqlite