SQLite

Article Info

Contributed by
4 authors

Last updated on
2022-02-15 11:51:13

embedded data database

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

Article Versions

7 2022-02-15 11:51:13
3337,1124 7,3337

By devbot5S

Migrating blockquotes to markdown syntax
6 2019-01-14 17:50:03
1124,1121 6,1124

By mohanjo

Added milestone with image on syntax diagrams.
5 2019-01-14 15:01:04
1121,1120 5,1121

By mohanjo

Corrected order of milestones.
4 2019-01-14 10:45:59
1120,1119 4,1120

By mohanjo

Sample Code section: adding Java code.
3 2019-01-14 10:39:18
1119,1118 3,1119

By arvindpdmn

Looks complete. Publishing.

Chat Room

Submitting ...

You are editing an existing chat message.

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.

Discussion

How's SQLite different from traditional databases?
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?
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 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

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

Conn2 and Conn3 shared a single cache. Source: SQLite 2019j, fig. 1.

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

Syntax diagram for ALTER TABLE statement. Source: SQLite 2019p.

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.

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', 'xyz@gmail.com', 'shivammg.blogspot.com', 'Great tutorial for beginners.' );
 
SELECT post_id, name, email, website_url, comment
    FROM comments;
SELECT *
    FROM comments;
 
UPDATE comments
    SET email = 'zyx@email.com'
    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;

# Source: https://docs.python.org/3.7/library/sqlite3.html
# Accessed: 2019-01-14
 
import sqlite3
conn = sqlite3.connect('example.db')
 
c = conn.cursor()
 
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')
 
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
 
conn.commit()
conn.close()

// Source: http://www.sqlitetutorial.net/sqlite-java/create-table/
// Accessed: 2019-01-13
 
// Example showing how to open a connection and create a table
 
package net.sqlitetutorial;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 *
 * @author sqlitetutorial.net
 */
public class Main {
 
    /**
     * Create a new table in the test database
     *
     */
    public static void createNewTable() {
        // SQLite connection string
        String url = "jdbc:sqlite:C://sqlite/db/tests.db";
 
        // SQL statement for creating a new table
        String sql = "CREATE TABLE IF NOT EXISTS warehouses (\n"
                + "	id integer PRIMARY KEY,\n"
                + "	name text NOT NULL,\n"
                + "	capacity real\n"
                + ");";
 
        try (Connection conn = DriverManager.getConnection(url);
                Statement stmt = conn.createStatement()) {
            // create a new table
            stmt.execute(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
 
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        createNewTable();
    }
 
}

References

Article Stats

1512

Words

Authors

Edits

Chats

Likes

12K

Hits

Cite As

Devopedia. 2022. "SQLite." Version 7, February 15. Accessed 2024-06-26. https://devopedia.org/sqlite

Contributed by
4 authors

Last updated on
2022-02-15 11:51:13

embedded data database

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

SQLite

Discussion

Milestones

Sample Code

References

Further Reading

Article Stats

Cite As

See Also

SQLite

Discussion

Milestones

Sample Code

References

Further Reading

Article Stats

Author-wise Stats for Article Edits

Cite As

See Also

Login