SQLite: A Light OpenSource Relational Database (RDBMS and Library)

SQLite Is Designed for a Single User

One of the biggest differences between SQLite and most DBMSs is that SQLite is designed for a single user. Most DBMSs manage multiple users including various security features that allow or block access to specific SQL commands and features. Even more important to many people, a DBMS manages contention for resources so that several users can apparently use the same data concurrently.

Apparently is the key word here because although each user typically thinks that he or she has unique access to the database, in fact, behind the scenes the DBMS manages concurrency so that in some cases, it has locked a record to prevent access to it by a second user while another user is updating it.

Single User Doesn't Mean Single-Thread

SQLite manages concurrency within its own environment. This means that it may have multiple threads running at the same time to perform its own tasks, but those threads are managed within the SQLite environment itself. They do not represent separate users.

Using SQLite with Multiple Users

How can you have multiple users when SQLite is designed for a single user? The answer is simple: you manage multiple users yourself. There are a number of ways of doing this, but in general, what you do is to push the multiuser management onto the app, class, or language into which SQLite is embedded. Apps typically have the ability to communicate with one another (subject to security and platform constraints). Thus, although SQLite is not going to manage the case of User A and User B attempting to modify the same data at the same time, your app can do so.

Typical concurrency strategies involve either having a master process that manages the concurrency or having a mechanism whereby multiple independent processes communicate without a master process. You find many examples of multiple independent processes in apps such as Dropbox and in many cloud-based apps. (In the case of Dropbox and cloud-based apps, there may be a process continually running somewhere in the cloud and, perhaps separate processes running on active clients. Whether or not control resides in a central process or is distributed among the clients (and master) varies depending on the specific implementation.)

Thus, SQLite is perfectly capable of functioning in a multiuser world; it just needs to be runninginside apps or other processes that themselves implement the multiuser features.

SQLite Is Self-Contained

SQLite is self-contained in two ways.

• The code itself is self-contained ANSI-C code. It makes minimal use of C libraries. In fact, the only ones it uses are:

  • memset()
  • memcpy()
  • memcmp()
  • strcmp()
  • malloc()
  • free()
  • realloc()
• The data store itself is self-contained, portable, and platform-agnostic.

Self-Contained Code

Self-contained code means that when you include the SQLite library in your project (either directly or through a language, class, or framework), you have everything you need. You don't need to include additional libraries.

You don't have to worry about versions, and, once you have a compiled SQLite library, you can generally reuse it without being dependent on changes in components. (Remember, though, that in most cases SQLite is embedded in a language, class, framework, or DBMS so the container is what you will need to update from time to time.)

Because SQLite is in the public domain, you don't have to worry about licenses or license fees. (You can, however, obtain a license as described on sqlite.org. That option is provided for some users who need to demonstrate to their management that they actually have the right to use SQLite.)

Remember that SQLite is often contained within a class, framework, or language that you are using so this feature (which to a large extent makes such containability possible) may not be visible to you.

Note: If you go to sqlite.org, you'll find the various download options for the SQLite source code. There are options to download it in sections that are combined (amalgamated) in the final build. This is done primarily to accommodate development environments that have trouble handling the full build at one time.

Self-Contained Data

The data store that SQLite uses for a database is designed to be self-contained and cross-platform. This means that you can transfer a SQLite file from one environment to another without problems in most cases. (That caveat is necessary because you may encounter issues in specific configurations, but for many if not most SQLite users, the first step in verifying that a database can be moved is to actually move it—this usually works.)

In its most basic (and original) form, each SQLite database is stored in a single file. The file is readable and writable on any platform

With the database in a single file, users can see it and move it around if they want. (If they do so, the app that you are building must be able to find the file—perhaps with the user's help.) Users can also delete the database file. Because it is self-contained, that's very simple, and it doesn't corruptother SQLite databases. Of course, the fact that a user may be able to delete the database means that in your implementation, you may want to hide it or place it in protected places. If you do that, you often take advantage of platform-specific features such as hidden folders, but the SQLite file itself remains an ordinary file that can be moved around and copied to other devices.

Because the database file is self-contained, it contains both the database data and its schema (structure if you're not from the database world). In part because each database is self-contained, SQLite is referred to as serverless (there is no separate server process).

The fact that a SQLite database can be stored in a single file leads to a common use of SQLite to implement structure and database functionality inside what appears to the user to be a simple file.

Note: Although SQLite began with the one-database/one-file structure, it now supports write-ahead logging (WAL) as an option. WAL is a technique that optimizes database performance using multiple files.

Architecture

SQLite has an elegant, modular architecture that takes some unique approaches to relational database management. It consists of eight separate modules grouped within three major subsystems. These modules divide query processing into discrete tasks that work like an assembly line. The top of the stack compiles the query, the middle executes it, and the bottom handles storage and interfacing with the operating system.

SELECT name FROM episodes LIMIT 10;

SQLite on Linux

First I install the following packages:

• sqlite3: provides same-named executable sqlite3
• sqlite3-doc: found at /usr/share/doc/sqlite3/

• sqlite-utils, documented at /usr/share/doc/sqlite-utils/ and below, provides features like:

  • Pipe JSON (or CSV or TSV) directly into a new SQLite database file, automatically creating a table with the appropriate schema
  • Run in-memory SQL queries, including joins, directly against data in CSV, TSV or JSON files and view the results.
  • Configure SQLite full-text search against your database tables and run search queries against them, ordered by relevance
  • Run transformations against your tables to make schema changes that SQLite `ALTER TABLE` does not directly support, such as changing the type of a column
  • Extract columns into separate tables to better normalize your existing data

I check up on JSON support...