SQLite3 (https://www.sqlite.org) is an ubiquitous relational database written in C. The main thing that stands out about SQLite versus other databases is the fact that the whole database is contained in a single file with no server or configuration needed making it very easy to set up and use. It is licensed as public domain which is even more free than typical open source libraries like MIT or GPL. It uses a mostly SQL compliant language so if you are familiar with any other standard SQL engine the language should be something you are familiar with.
SQLite has proven to be stable in production environments, but is often used for development environments due to its ease of use. There are language bindings to Sqlite written in almost every language. Reads can be done concurrently but there is a database-wide lock for write events. For that reason, it does not perform well for write-heavy applications. A database that provides row-level locking would be more efficient for performing lots of concurrent writes. The official website has more information on when it is a good choice to use.
For a more exhaustive list of features, check out the official page, Distinctive Features Of SQLite.
For an example of using SQLite with a programming language, check out my Ruby SQLite Tutorial.
To install SQLite, you have a few options depending on what kind of system you have. Some options include: building from source, downloading pre-built binaries, or installing using your system's package manager.
One option for all systems is to build from source.
To build with
gcc, download the source code from https://www.sqlite.org/download.html and then extract the source.
If you download the amalgamation version, it only comes with two
You can build it as simple as:
gcc shell.c sqlite3.c -o sqlite3 ./sqlite3 --help # You can place the sqlite3 file anywhere you want # Update your PATH environment variable if you want to run it from anywhere
If you download the autoconf version, you will also need make and autoconf tools. To build the autoconf version, run:
./configure make ./sqlite3 --help # Optional step to install: `make install` make install
In Windows, the easiest thing to do is download the pre-built binaries from https://www.sqlite.org/download.html and extract them.
The executable will be ready to use, but if you want to use it from the command line anywhere, you will need
to update your
PATH environment variable to include the directory that contains the SQLite executable/DLL.
The easiest way to install SQLite3 on a Mac is to use use https://brew.sh/.
brew install sqlite
In Ubuntu, you can use
apt to install the necessary packages.
The main package is
sqlite3 but you can optionally also install the
development headers and the documentation as well.
sudo apt install sqlite3 sudo apt install libsqlite3-dev sudo apt install sqlite3-doc
In Fedora, you can use
dnf to install the packages.
The main package in Fedora is
sqlite but you can optionally also install the
development headers and the documentation as well.
sudo dnf install sqlite sudo dnf install sqlite-devel sudo dnf install sqlite-doc
There are a few places to get help. If you have an internet connection you can read the online documentation, but if you are offline you will need to find offline documentation. You have a few options for getting help including:
- Read online at https://www.sqlite.org/docs.html
- Download the HTML documentation from https://www.sqlite.org/download.html
- From the command line with
- From the man page with
- In the command line shell with
- From your system's SQLite doc package (e.g.
/usr/share/doc/sqlite-doc/in Fedora from
Command line shell
One of the primary interfaces to a SQLite database is through the command line
sqlite3. Launch the shell with no arguments, or pass it the name of
the database file you want to use. By default, if no database name is provided
it uses an in-memory database.
sqlite3 # or sqlite3 my.db
Once in the command line shell, you can open databse files, inspect schemas, query, and execute statements.
List all commands
You can get a list of all available commands with the
Open a file
To open a database use the
.open command. For example, to open a database
my.db you would run:
Identify current database
To see which database is currently being used, you can use the
command. This will output the full path to the database file being used,
unless you are using the in-memory database then it will only output
List database tables
To see information about the tables in a database, you can use the
command. This will list the names of tables in the database.
You could also query the special
sqlite_master table for a list of tables
SELECT name FROM sqlite_master;
If you want to see the details about a table, like what columns it has,
you can use the
.schema command. Using
.schema by itself will describe all
You can also specify a table name as an argument. For example:
.schema -- Or a specific table .schema sqlite_master
To quit the interactive command line shell, call
Execute directly from command line
Instead of using the interactive command line shell, you can execute a query directly and get the results in STDOUT. This is useful if you want to output the data to a file or you want to the pipe the output to another program.
sqlite3 test.db "select * from sqlite_master"
Backing up and restoring databases
Since databases are fully contained in a single file, creating a backup is as simple as creating a copy of the file. Restoring a database is also as simple as renaming or replacing the database file.
SQLite supports most standard SQL but not everything. For a full list of the keywords and syntax it supports, refer to https://www.sqlite.org/lang.html. Most of these examples here are just standard SQL and not unique to SQLite. Here are some practical examples.
SQLite supports single-line and multi-line comments. It supports C-style comments and comments that go until the end of the line.
-- Single line comment SELECT * FROM sqlite_master; -- Comment at end of line /* This is a Multi line comment */
This is a basic example of creating a table. It uses basic SQL syntax
without anything too special.
IF NOT EXISTS clause is optional.
You can also add unique contrains with the
CREATE TABLE IF NOT EXISTS images(path TEXT, thumbs_up INT); CREATE TABLE my_table ( some_int INT, some_text TEXT, some_float REAL, some_blob BLOB, CONSTRAINT enforce_unique_numbers UNIQUE (some_int, some_float) );
Here are a few simple examples of how to query data from a database.
SELECT * FROM mytable; SELECT field1, field2 FROM mytable; SELECT COUNT(*) FROM mytable; SELECT * FROM mytable ORDER BY create_date DESC;
There is nothing special about the insert statements either, just standard SQL.
INSERT INTO images (path, thumbs_up) VALUES ("image1.png", 0);
Update statements also work as you would generally expect with most SQL dialects.
UPDATE images SET thumbs_up=5 WHERE path="image1.png"; # To set multiple fields at once: UPDATE images SET thumbs_up=10, description="an image" WHERE path="image1.png";
To delete rows, use a delete statement like the following:
DELETE FROM images WHERE path="image1.png";
To match partial strings, you can use a
LIKE clause. For example:
SELECT FROM images WHERE path LIKE '%.png'; SELECT FROM images WHERE path LIKE '%.jp_g'; SELECT FROM images WHERE path like '%dog%'
% means match zero or more characters,
_ means match up to a single character.
Indices are used to speed up read operations, but they slow down write operations.
You can create an index on any column and a table may have more than one index.
UNIQUE if you want to enforce unique values on the column.
CREATE INDEX image_path_index ON images (path); CREATE INDEX IF NOT EXISTS image_path_index ON images (path); CREATE UNIQUE INDEX unique_image_path_index ON images (path);
To delete an index, use the
DROP INDEX command and pass it the index name.
DROP INDEX image_path_index; DROP INDEX [IF EXISTS] image_path_index;
SQLite does support transactions, allowing you to execute a series
of statements that can either be rolled back and undone, or finalized and committed.
Start a transaction with
BEGIN and then execute the statements you want
like deleting a row. Then end the transaction by executing
COMMIT depending on whether you want to undo or complete the transaction.
BEGIN TRANSACTION; -- Perform whatever actions you want now.
-- Undo anything done since `BEGIN` and end transaction ROLLBACK TRANSACTION;
-- Save changes done since `BEGIN` to the database and end transaction COMMIT TRANSACTION;
You can restrict fields from ever being null by adding a
NOT NULL clause
CREATE TABLE users ( id INT, profile_id INT NOT NULL );
You can provide a default value for a field if no value is provided by
DEFAULT clause like this:
CREATE TABLE users ( id INT, profile_id INT DEFAULT NULL );
Primary and Foreign Keys
Foreign keys will require a valid value or it won't accept the entry.
You can optionally add an
ON UPDATE clause that will specify what to do if the
referenced foreign object is deleted. You can choose from the following:
- NO ACTION - Do nothing
- RESTRICT - Do not allow deleting when foreign key reference exists
- SET NULL - Set to null
- SET DEFAULT - Use the fields default value
- CASCADE - Delete the foreign reference too
CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, profile_id INT, FOREIGN KEY(profile_id) REFERENCES profiles(id) ); CREATE TABLE profiles ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INT, bio TEXT, FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE CASCADE ); CREATE TABLE posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INT, content TEXT, FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE TABLE clans ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ); CREATE TABLE clans_users ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INT, clan_id INT, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(clan_id) REFERENCES clans(id) );
You can optionally set fields to auto increment, like on a primary key field
AUTOINCREMENT clause like this:
CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL );
Note that by default, each table automatically comes with a slightly hidden
rowid that acts as a unique, autoincremented field. You have
to explicitly query for it otherwise it does not come back with queries.
You can have a table NOT create the
rowid field by using the
WITHOUT ROWID clause
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ) WITHOUT ROWID;
Note that when specifying
WITHOUT ROWID you must provide an alternative
Programming language bindings
While SQLite is written in C and comes with TCL bindings, there are also language bindings for virtually every language out there. Some languages, like Python, even include SQLite in the standard library.
Database browsing tools
Sometimes the interactive command line shell can be a bit tedious. There are a few graphical tools for browsing and managing SQLite databases. Some of the ones I would recommend include:
- SQLite Browser
- Visual Studio Code Extension: SQLTools
- JetBrains DataGrip
- JetBrains Professional IDEs like PyCharm Professional and IntelliJ Ultimate