Install and configure SQLite on your server

By Gary Sieling

SQLite is the most deployed relational database because it is designed to be embedded within software applications, rather than accessed as a server database. It is available within most browsers through the Web SQL API and is commonly used in mobile applications.

Because SQLite is used in embedded systems, it is designed to be predictable and robust. While many SQLite instances may be small, it boasts the ability to handle fairly large datasets and high read volumes (e.g., websites with 100,000 page loads per day).

Installation and Testing

If you would like to learn how SQLite works, you can easily deploy it on a Linode server:

sudo apt-get install sqlite

When you install you’ll notice that this is only about 50k to install — SQLite is designed to be very light-weight due to it’s use in embedded systems.

Once this installs, you can test it by running some commands. If you invoke the sqlite command line with a filename, it will store the data you insert in the requested file:

sqlite test.db
sqlite> create table test_table(column1 int);
sqlite> insert into test_table select 1 column1;
sqlite> select * from test_table;
1
sqlite> .exit

All of the sqlite commands start with a period — you can view them all with “.help”.

Compiling from Source

You can also compile directly from source by downloading the sqlite source and unzipping it:

curl -o sqlite.tar.gz https://sqlite.org/2017/sqlite-autoconf-3190300.tar.gz
tar xvf sqlite.tar.gz
cd sqlite-autoconf-3190300/
apt install gcc make
./configure
make
make install
./sqlite3

If you want to use SQLite within an application, you can use the ‘amalgamation’ source code download. The amalgamation mode is a set of three files, containing the entire SQLite codebase. These are intended for situations where you wish to build SQLite into an existing product, and make it easy to drag and drop the files into your project.

If you’re really kicking the tires on SQLite, you should explore the various compiler options — there is a long list, allowing you to limit the size of the compiler output and disable unused features to improve performance. For instance, you can disable thread safety, power loss safety, and the progress indicator for SQL. You can also enable or disable SQL features like full text search, autovacuum, and even specific language or execution plan features.

Backup / Restore script

If you would like to make a backup of the database, you can do .dump, which produces the a backup for the database:

BEGIN TRANSACTION;
create table test_table(column1 int);
INSERT INTO test_table VALUES(1);
COMMIT;

By putting this into a bash script, you can create a file:

.output test.sql
.dump
.exit

Then run it with sqlite:

sqlite test.db < backup.commands

You can restore this like so:

cat backup.sql | sqlite test2.db

Alternative Uses

Because SQLite is so popular and well-supported, there are a few interesting alternative distributions:

SQLite has been compiled to a pure JavaScript solution using Emscripten, called SQL.js.

There is a REST Web API for SQLite called ArrestDB, which allows you to create tables and modify data through HTTP.

It is also available for app development within Android and iOS.

Maintenance

SQLite offers two commands to perform maintenance, ANALYZE and VACUUM. While most databases will change the algorithms used to compute queries as time progresses, SQLite guarantees stable execution plans, so that it behaves the same in the future as it does today. If you use SQLite in an application, you should make sure these commands are being used according to the guidance in the manual.

SQLite has a reputation for reliability and consistency. It is a fantastic light-weight database, used in mobile phones, applications, and industrial embedded systems. While many well-known databases have processes that clean and introspect to maintain performance over time, SQLite offers a simple file-based database, allowing you, the developer, full control to use just the database features you want.

Gary Sieling is a Software Architect in Philadelphia, PA. He runs a search engine for conference talks and historic speeches, called https://www.findlectures.com. You can find him on Twitter at Gary Sieling.