SQLite3 Tutorial

Advertisement

Advertisement

Submitted by nanodano on Thu, 06/27/2019 - 01:17

Introduction

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.

Install SQLite

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.

From Source

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 .c files. 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

To learn how to compile SQLite3 in to a C++ application, check out my tutorial Compiling SQLite3 with C++.

Windows

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.

Mac

The easiest way to install SQLite3 on a Mac is to use use https://brew.sh/.

brew install sqlite

Ubuntu

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

Fedora

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

Read documentation

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 sqlite3 --help
  • From the man page with man sqlite3
  • In the command line shell with .help
  • From your system's SQLite doc package (e.g. /usr/share/doc/sqlite-doc/ in Fedora from sqlite-doc package)

Command line shell

One of the primary interfaces to a SQLite database is through the command line shell 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 .help command.

.help

Open a file

To open a database use the .open command. For example, to open a database file named my.db you would run:

.open my.db

Identify current database

To see which database is currently being used, you can use the .database 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 main.

.database

List database tables

To see information about the tables in a database, you can use the .tables command. This will list the names of tables in the database.

.tables

You could also query the special sqlite_master table for a list of tables that exist:

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 the tables. You can also specify a table name as an argument. For example:

.schema
-- Or a specific table
.schema sqlite_master

Exit shell

To quit the interactive command line shell, call .exit.

.exit

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.

There is however one command available that is useful inside the sqlite shell:

This .backup command is like a "Save as..." feature when run in sqlite3. It will store the current database to a file named myfile.db.

.backup myfile.db

SQL dialect

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.

Comments

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
*/

Create and delete tables

This is a basic example of creating a table. It uses basic SQL syntax without anything too special. The IF NOT EXISTS clause is optional.

You can also add unique contrains with the CONSTRAINT clause.

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)
);

There are only a few Sqlite3 datatypes.

  • NULL - Empty value
  • INTEGER - Basic whole number
  • REAL - 64-bit floating point
  • TEXT - String
  • BLOB - Binary or raw data

To drop or delete a table, use:

DROP TABLE mytable;

Modify tables

To make changes to an existing table you use the ALTER TABLE statement. You use this statement to rename tables, rename columns within tables, to add columns and

-- Rename table
ALTER TABLE mytable RENAME to mynewtable;
-- Add a column
ALTER TABLE ADD COLUMN username TEXT;

Queries

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;

Insert statements

There is nothing special about the insert statements either, just standard SQL.

INSERT INTO images (path, thumbs_up) VALUES ("image1.png", 0);

Update statements

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";

Delete statements

To delete rows, use a delete statement like the following:

DELETE FROM images WHERE path="image1.png";

Like operator

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%'

The % means match zero or more characters, and the _ means match up to a single character.

Manage index

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. Include the 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;

Transactions

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 ROLLBACK or 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;

Null fields

You can restrict fields from ever being null by adding a NOT NULL clause like this:

CREATE TABLE users (
    id INT,
    profile_id INT NOT NULL
);

Default values

You can provide a default value for a field if no value is provided by using a 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)
);

Autoincrement fields

You can optionally set fields to auto increment, like on a primary key field using the 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 field named 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 like this:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) WITHOUT ROWID;

Note that when specifying WITHOUT ROWID you must provide an alternative primary key.

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:

References

Advertisement

Advertisement