Tuesday, March 19, 2024
Data AnalyticsExplainerIoT Software&Tools

SQLite – Suitable Database Engine for Embedded and IoT Devices

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 (247 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:

  1. What is a Time Series Database?
  2. How to Install InfluxDB on Ubuntu
  3. Getting Started with InfluxDB
  4. How to install MongoDB in ubuntu

 

Harshvardhan Mishra

Hi, I'm Harshvardhan Mishra. Tech enthusiast and IT professional with a B.Tech in IT, PG Diploma in IoT from CDAC, and 6 years of industry experience. Founder of HVM Smart Solutions, blending technology for real-world solutions. As a passionate technical author, I simplify complex concepts for diverse audiences. Let's connect and explore the tech world together! If you want to help support me on my journey, consider sharing my articles, or Buy me a Coffee! Thank you for reading my blog! Happy learning! Linkedin

One thought on “SQLite – Suitable Database Engine for Embedded and IoT Devices

Leave a Reply

Your email address will not be published. Required fields are marked *