SQLite – Suitable Database Engine for Embedded and IoT Devices

29th January 2019 Harshvardhan Mishra

What is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is a relational database management system. 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 the most used database engine in the world. It is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

SQLite is ACID-compliant and implements most of the SQL standard, generally following PostgreSQL syntax. However, SQLite uses a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity. This means that one can, for example, insert a string into an column defined as an integer. SQLite will attempt to convert data between formats where appropriate, the string “123” into an integer in this case, but does not guarantee such conversions, and will store the data as-is if such a conversion is not possible.

You May also like: What is a Time Series Database?

It is a popular choice as 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), among others. SQLite has bindings to many programming languages.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, It does not have a separate server process. It reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform – you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. SQLite database files are a recommended storage format by the US Library of Congress. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

SQLite is a compact library. With all features enabled, the library size can be less than 600KiB, depending on the target platform and compiler optimization settings. (64-bit code is larger. And some compiler optimizations such as aggressive function inlining and loop unrolling can cause the object code to be much larger.) There is a tradeoff between memory usage and speed. It generally runs faster the more memory you give it. Nevertheless, performance is usually quite good even in low-memory environments. Depending on how it is used, SQLite can be faster than direct filesystem I/O.

Source code is in the public-domain and is free to everyone to use for any purpose.

Key Points

Full-featured SQL
Billions and billions of deployments
Single-file database
Public domain source code
All source code in one file (sqlite3.c)
Small footprint
Max DB size: 140 terabytes (2⁴⁷ bytes)
Max row size: 1 gigabyte
Faster than direct file I/O
Aviation-grade quality and testing
Zero-configuration
ACID transactions, even after power loss
Stable, enduring file format
Extensive, detailed documentation
Long-term support

Features

8+3 Filenames → How to make SQLite work on filesystems that only support 8+3 filenames.
Autoincrement → A description of the AUTOINCREMENT keyword in SQLite, what it does, why it is sometimes useful, and why it should be avoided if not strictly necessary.
Backup API → The [sqlite3_backup_init | online-backup interface] can be used to copy content from a disk file into an in-memory database or vice versa and it can make a hot backup of a live database. This application note gives examples of how.
Error and Warning Log → Supports an “error and warning log” design to capture information about suspicious and/or error events during operation. Embedded applications are encouraged to enable the error and warning log to help with debugging application problems that arise in the field. This document explains how to do that.
Foreign Key Support → This document describes the support for foreign key constraints introduced in version 3.6.19.
Indexes On Expressions → Notes on how to create indexes on expressions instead of just individual columns.
Internal versus External Blob Storage → Should you store large BLOBs directly in the database, or store them in files and just record the filename in the database? This document seeks to shed light on that question.
Limits In SQLite → This document describes limitations of SQLite (the maximum length of a string or blob, the maximum size of a database, the maximum number of tables in a database, etc.) and how these limits can be altered at compile-time and run-time.
Memory-Mapped I/O → Supports memory-mapped I/O. Learn how to enable memory-mapped I/O and about the various advantages and disadvantages to using memory-mapped I/O in this document.
Multi-threaded Programs and SQLite → It is safe to use in multi-threaded programs. This document provides the details and hints on how to maximize performance.
Null Handling → Different SQL database engines handle NULLs in different ways. The SQL standards are ambiguous. This (circa 2003) document describes how SQLite handles NULLs in comparison with other SQL database engines.
Partial Indexes → A partial index is an index that only covers a subset of the rows in a table. Learn how to use partial indexes in SQLite from this document.
Shared Cache Mode → Version 3.3.0 and later supports the ability for two or more database connections to share the same page and schema cache. This feature is useful for certain specialized applications.
Unlock Notify → The “unlock notify” feature can be used in conjunction with [shared cache mode] to more efficiently manage resource conflict (database table locks).
URI Filenames → The names of database files can be specified using either an ordinary filename or a URI. Using URI filenames provides additional capabilities, as this document describes.
WITHOUT ROWID Tables → The WITHOUT ROWID optimization is a option that can sometimes result in smaller and faster databases.
Write-Ahead Log (WAL) Mode → Transaction control using a write-ahead log offers more concurrency and is often faster than the default rollback transactions. This document explains how to use WAL mode for improved performance.

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 this DB Engine 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.

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. DB Engine 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.

Official Link: https://www.sqlite.org

You May also like: