SQLite Setting-Up Guide

6 min readOct 26, 2022

SQLite is a built-in library that implements a serverless, zero-configuration SQL Database Management System (DBMS). It is a database set to null, which means there is no need to set it up on your system. Unlike other database management systems, SQLite is not a standalone process. It accesses its storage files directly.

How to Install SQLite

All the SQLite necessities are compiled and available for download and installation from the official website. If you wish, you can also compile the source code on your own. In general, SQLite code is open and can be freely modified.

To write and execute queries for SQLite databases, you can use a simple command-line shell program — sqlite3. But there are also many free (e.g., SQLiteStudio) and commercial GUI tools for managing SQLite databases.

Installing and Running SQLite on Windows

Go to the SQLite download page and get the files that make SQLite work on Windows, including sqlite3:

2. Create a new folder on your computer, for example, C:\sqlite.

3. Extract the contents of the downloaded file to the C:\sqlite folder. There should be three tools:

Sqlite3.exe
Sqlite3_analizer.exe
sqldiff.exe

4. In the command line, go to the folder with sqlite3.exe and run it. You can also specify the name of the database:

C:\>cd C:\sqlite
sqlite3 <SQLite database name>

If a file with this name does not exist, it will be created automatically. If no database file name is specified in the command line, a temporary database will be created and automatically deleted when you exit sqlite3.

5. Windows users can double-click the sqlite3.exe icon to open a terminal pop-up window with SQLite running. However, since double-clicking launches sqlite3 with no arguments, no database file will be specified, and you will use a temporary database, which will be deleted when the session ends.

Installing and Running SQLite on Linux

Let’s see how to install SQLite on Linux using Ubuntu as an example.

To install sqlite3 on Ubuntu, first, update the package list:

$ sudo apt update

2. Then install sqlite3:

$ sudo apt install sqlite3

3. To define whether the installation was successful, you can check the version:

$ sqlite3 — version

If successful, you’ll get something like this:

Output
3.38.3 2022–04–27 12:03:15 3bfa9cc97da10589251b342961df8f5f68c7399fa117345eeb516bee837balt1

How to Create a Database in SQLite

There are several ways to create a database in SQLite:

As mentioned above, when running sqlite3, you can specify the name of the database:

$ sqlite3 my_first_db.db

Or you can provide the full path to the file:

$ sqlite3 C:/sqlite/my_first_db.db

If my_first_db.db database already exists, it will be opened. If not, it will be created in local memory. The created database will be automatically deleted on the sqlite3 exit if no queries have been sent to the database. Therefore, to make sure the database is written on disk, you can run an empty query by typing ; and pressing Enter:

sqlite> ;

After the work session, you can save changes in the database using the special SQLite command “.save” with the name of the database:

sqlite> .save my_first_db.db

Or the full path to the base:

sqlite> .save C:/sqlite/my_first_db.db

You should be careful when using the “.save” command, as this command will overwrite any pre-existing files with the same name without asking for confirmation.

2. In SQLite, you can create a database using the “.open” command:

sqlite> .open my_first_db.db

As in the first case, the database with an existing name will be opened. If it does not exist, it will be created. With the current creation method, the new SQLite database will not disappear when sqlite3 is closed, but all changes must be saved using the “.save” command before exiting the program, as in the case above.

3. As already mentioned, when running sqlite3 with no arguments, a temporary database in local memory will be used. It will be deleted when the session ends. However, this database can be saved to disk using the “.save” command.

$ sqlite3
SQLite version 3.38.3 2022–04–27 12:03:15
Enter “.help” for usage hints.
Connected to a transient in-memory database.
Use “.open FILENAME” to reopen on a persistent database.
sqlite> … many SQL commands omitted …
sqlite> .save db1.db
sqlite>

SQLite. Create a Table

Information in SQLite databases is stored as tables. SQLite uses the CREATE TABLE query to create tables. This query must contain the table name and field (column) names and may also contain data types, field descriptions (key field), and default values. For example, let’s create a table with descriptions of the parameters of different dog breeds using CREATE TABLE in SQLite.

sqlite> CREATE TABLE dog_params (id integer PRIMARY KEY,
 dog_breed text,
 speed_km_per_h integer,
 weight_kg integer);

In our table, the id column is marked as PRIMARY KEY, which means that id will be a key column (index) and that an integer for it will be generated automatically.

Adding Records to a Table

To make a new record in a table, you need to use an INSERT INTO SQL query, specifying the table and the fields to enter new values. The request’s structure is:

sqlite> INSERT INTO table (column1, column2) 
VALUES (value1, value2);

If the number of values corresponds to the number of columns in the table, the field names can be excluded from the query. Table columns that don’t appear in the column list are filled with the column’s default value (specified as part of the CREATE TABLE instruction) or NULL if no default value was specified.

For example:

sqlite> INSERT INTO dog_params (dog_breed, speed_km_per_h, weight_kg) 
VALUES (“Greyhound”, 72, 29);
sqlite> INSERT INTO dog_params VALUES (2, “Jack Russell Terrier”, 61, 5);
sqlite> INSERT INTO dog_params VALUES (3, “Dalmation”, 59, 24);

In the first case, the id was self-generated since this field is assigned with an index. You will need to enter the id numbers manually if you want to enter lines without specifying column names.

Using the following SQL query, you can insert several records at the same time. The id will be automatically generated.

sqlite> INSERT INTO dog_params (dog_breed, speed_km_per_h, weight_kg) 
VALUES (“Borzoi”, 58, 39), (“Standard Poodle”, 48, 27);

SQLite. Table View

To view the entire contents of a table, use the SELECT query:

sqlite> SELECT * FROM dog_params;

The result will look like this:

1|Greyhound|72|29
2|Jack Russell Terrier|61|5
3|Dalmation|59|24
4|Borzoi|58|39
5|Standard Poodle|48|27

Using the WHERE command, you can view only those rows that satisfy some condition. For example, let’s display breeds whose speed is less than 60 km/h:

sqlite> SELECT * FROM dog_params WHERE speed_km_per_h < 60;
3|Dalmation|59|24
4|Borzoi|58|39
5|Standard Poodle|48|27

Changing Records in a Table

With the ALTER TABLE query and additional commands, you can modify the table as follows:

RENAME TABLE,
ADD COLUMN,
RENAME COLUMN,
DROP COLUMN — to delete.

For example, let’s add a column with the dogs’ height at the withers to our table:

sqlite> ALTER TABLE dog_params ADD COLUMN height_cm integer;

To change the values in existing table records, you need a query in SQLite — Update. In this case, it is possible to change both the values of a cell in a group of rows and the value of a cell in a separate row. As an example, let’s enter the values of the dogs’ height at the withers in our table:

sqlite> UPDATE dog_params SET height_cm=71 WHERE id=1;
sqlite> UPDATE dog_params SET height_cm=28 WHERE id=2;
sqlite> UPDATE dog_params SET height_cm=53 WHERE id=3;
sqlite> UPDATE dog_params SET height_cm=69 WHERE id=4;
sqlite> UPDATE dog_params SET height_cm=61 WHERE id=5;

Our final table will look like this:

sqlite> SELECT * FROM dog_params:
1|Greyhound|72|29|71
2|Jack Russell Terrier|61|5|28
3|Dalmation|59|24|53
4|Borzoi|58|39|69
5|Standard Poodle|48|27|61

How to Use SQLiteStudio

You can work with SQLite databases not only from the command line but also with GUI tools, one of which is SQLiteStudio.

The SQLiteStudio tool is free, portable, intuitive, and cross-platform. It provides many of the most important features for working with SQLite databases, such as importing and exporting data in various formats, including CSV, XML, and JSON.

You can download the SQLiteStudio installer or its portable version from the official site https://sqlitestudio.pl. Then you need to extract (or install) the downloaded file to a folder, for example, C:\sqlite\gui\, and run it. You can find detailed instructions for installing and working with SQLiteStudio on the website.