SQLite (https://www.sqlite.org/) is a library for implementing a self-contained embedded SQL Database, and bills itself as the most [widely used] database in the world.
In this module, we will discuss how SQLite differs from things like PostgreSQL and MySQL, and the basics of getting started and creating a database
What is an embedded database? An embedded database means that the database is tightly integrated with the application using the database, as opposed to the database being a separate application, or an application stored on a separate server.
Systems like MySQL and PostgreSQL run a database as a server. Even when the database server is physically on the same hardware as the application, interactions with MySQL and PostgreSQL are done in a client-server nature. SQLite, on the other hand, is invoked directly, with no need for a separate server application running locally.
Specifically, in SQLite, the “database” is stored as literal files on the disk. These files are typically stored with the extension “.sqlite3” or “.db” - example “sde_database.sqlite3”, (you’ll also see “.sqlite” as well). Be aware, however, that we don’t want to store a database file as
SQLite is great for local storage of data, and is frequently used to store settings and local data for desktop and mobile applications. For instance, many web browsers use SQLite for storing history, bookmarks, and settings. SQLite is included by default in Android, iOS, Mac OS X 10.4 and onwards, and Windows 10 and onwards. Many web application frameworks support using SQLite for storage.
SQLite database files are portable across platforms and operating systems, making data easy to share.
In general, SQLite is ill-suited for high-traffic client-server situations. Usually, when building a website that you expect a large degree of concurrent traffic, MySQL and PostgreSQL are going to be preferred for their scalability. Specifically, SQLite is worse when there are a large degrees of data writes (that is, adding new data or changing existing data).
SQLite can function as a database for a website on the server side, but in general if I were hosting a web application, I’d prefer to use PostgreSQL.
SQLite is also ill-suited for very large datasets. While SQLite can theoretically store ~281 Terabytes, the entire database is a single file on disk, and most file systems have significantly smaller limits.
Additionally, if your software requires a high degree of concurrency, with several processes needing to write at the same time, SQLite can become a bottleneck. While SQLite allows for an unlimited number of reading processes, for data synchronicity it only allows one writer at a time, and so writers have to queue, even when writing to different unrelated areas of the same database.
A full guide of the command line can be found here, but I will highlight some keys.
First, you need to install sqlite3 and add the sqlite3 command to your system PATH variable - a full guide for Windows and Mac OS X can be found on TutorialsPoint here
Be aware when using SQLite command line that the command is sqlite3, NOT sqlite! You can ensure sqlite3 is installed and added to the Path correctly using from your command line program of choice:
> sqlite3 --version
Note that for this module, I will include the > symbol to indicate the start of a terminal command. You would not type the “>”. So above, you would simply type “sqlite3 –version”
To create or open an existing SQLite database, we simply use sqlite3 [database_filename.sqlite3]. For example:
> sqlite3 data.sqlite3
Would either:
A that point, you will enter the SQLite Shell, which is it’s own command line program. You will see something like:
SQLite version 3.39.0 2022-06-25 14:57:57
sqlite>
If we use sqlite3 data.sqlite3 (or whatever filename our database is), that database will automatically be opened when the sqlite shell begins. If we just use the sqlite3 with no filename, the sqlite shell will open with an in-memory database (more on that later)
Before we get into creating and storing tables, let’s review some basic commands. Many shell commands start with a period “.” to distinguish these system commands from sql queries such as “create”, “select”, “insert”, etc.
.helpsqlite> .help
This will list all commands available in .sqlite shell. You can follow .help with a specific command to get additional information about that command. For example:
sqlite> .help open
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
Options:
--append Use appendvfs to append database to the end of FILE
--deserialize Load into memory using sqlite3_deserialize()
--hexdb Load the output of "dbtotxt" as an in-memory db
--maxsize N Maximum size for --hexdb or --deserialized database
--new Initialize FILE to an empty database
--nofollow Do not follow symbolic links
--readonly Open FILE readonly
--zip FILE is a ZIP archive
.openIn the SQLite shell, .open is used to open a different database file, closing the currently open file if one exists. For instance, if we currently have a memory-database open, but we want to switch to an existing database named “data.sqlite3” in our current directory, we can use:
sqlite> .open data.sqlite3
.quit.quit is used to quit the SQLite shell
sqlite> .quit
This will exit the sqlite shell and bring your terminal back to its default state. For instance, on my Windows computer, this would look like:
sqlite> .quit
PS C:\Users\pm8fc\fall24\sqlite_example>
And now I’m back in Powershell, the terminal I used to open sqlite3
.tablesThis command is used to list all Tables in the current database (if you are following along, you won’t have any tables yet, as we will create those in the next module).
.dumpCreates a sql script that can be run to recreate the contents of the array in its current state. By default, this script will print to the console. Note that this does not modify the database nor delete any tables.
This is often used with the .output command to dump the contents to a file, and the .read command to run the dumped out .sql file as a script.
.outputBy default, sqlite displays writes all contents to the console. For instance:
sqlite> .output myFile.txt
sqlite> .help
slqite> .quit
> cat myFile.txt
This will result in myFile.txt containing the output of the .help command. Be aware that all commands after .output will be output to the same file. To stop writing to a file and write to the console again, use:
sqlite> .output dump.sql
sqlite> .dump
sqlite> .output
The above commands, in order, mean:
1) .output dump.sql - send the display of all future commands to the file dump.sql (Be aware this overwrites any existing file - it does not append!)
2) .dump - generate a sql script that will recreate all data in the database (written to dump.sql)
3) .output - write all future content to the console again (no more writing to file)
SQLite has a number of quirks where it behaves different than other database systems, or differently than you might expect. SQLite has a full article here, but I will highlight some important ones.
Note that many of these quirks reference content covered in upcoming modules, so you may not know what they mean yet. That is okay.
First, many database systems provide robust “user access” control. For instance, some database user accounts may be admin accounts with access to the complete database, including read and write access on all data and all tables. Other accounts may only be able to insert data into certain tables, or only query information from other tables. Additionally, when connecting to the database, users will “log in” with a username and password. (Note that by database user account, we mean an account specifically for the database, not for an account for an application that uses the database)
SQLite does not support this. SQLite has no access control permissions, nor any concept of a “database user”. Additionally, by default, sqlite files are not encrypted, although there is an extension to add encryption. You should be aware of this when storing any secure information.
SQLite is “flexibly typed”. This means that, by direct query usage, you can insert Text into an attribute that is an Integer or Real. Additionally, while most database systems allow you to fix a maximum length for datatypes (for instance, VARCHAR(20) would be a String of max length 20), SQLite does not enforce this limitation. This is an intended feature of SQLite.
SQLite does allow users to enable enforcement of datatypes in tables by using STRICT tables.
So, in general, a primary key should never be null. However, due to a bug in the early days of SQLite, null primary keys were allowed, provided there was only one null primary key (as the uniqueness constraint was still enforced). Because many legacy systems that use SQLite may have one or more tables with a null primary key, this bug was never fixed. However, this can be worked around in a couple ways.
INTEGER PRIMARY KEY in your tables. You should generally do this anyways, since primary keys should almost always be integers. If a table has an INTEGER PRIMARY KEY, attempting to insert null will result in a unique integer automatically being stored in the primary key attributeNOT NULLABLE constraint to Primary Key when not using IntegersSQLite initially doesn’t support foreign key constraint enforcement. While support was added later, like Nullable Primary Keys, there was a fear that adding enforcement would break existing databases. As such, foreign key enforcement must be turned on with the following SQLite query:
PRAGMA foreign_keys = ON
In general, you will be expected to do this on all assignments where foreign key constraints are enforced.
1 = '1' (that is, the integer 1 equals the Text “1”) is FALSE. In nearly all other SQL systems, this would be TRUE.
SQLite doesn’t have a “true” Boolean Datatype. Instead, if an attribute’s data type is stated as Boolean, it’s actually an integer (1 for TRUE, 0 for FALSE). TRUE and FALSE are reserved words which represent 1 and 0 respectively.
Additionally, there is no DateTime datatype. Instead, SQLite uses built-in DateTime functions for converting Text, Integer, or Real to a DateTime and vice versa (the most common usage is using an Integer with Unix Time, which represents the number of seconds since January 1, 1970, midnight UTC)