A Beginner’s Guide to SQLite

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The source code for SQLite is publicly available, modifiable, and free. SQLite is known for its speed, minimalism, and reliability. You can install it on a VPS on Hostman.

How to Install SQLite

The SQLite library is precompiled and available for download and installation from the official website. If necessary, you can also compile the source code yourself.

You can use a simple command-line tool called sqlite3 to write and execute queries on SQLite databases. There are also many free (e.g., SQLiteStudio) and commercial graphical interface tools for managing SQLite databases.

Installing and Running SQLite on Windows

1. Go to the SQLite download page and download the files necessary for running SQLite on Windows, including sqlite3.

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

3. Extract the contents of the downloaded file into the C:\sqlite folder. You should see three programs:

• sqlite3.exe

• sqlite3_analyzer.exe

• sqldiff.exe

4. Open the command prompt, navigate to the folder containing sqlite3.exe, and run the file. You can specify the name of the database:

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

5. If a file with that name doesn’t exist, it will be created automatically. If no database file name is specified in the command line, a temporary database will be created, which will automatically be deleted when you exit sqlite3.

6. Windows users can also double-click the sqlite3.exe icon to open a terminal window with SQLite running. However, since double-clicking launches sqlite3 without arguments, no database file will be specified, and a temporary database will be used, which will be deleted at the end of the session.

Installing and Running SQLite on Linux

Here’s how to install SQLite on Linux using Ubuntu as an example:

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

sudo apt update

2. Then install sqlite3:

sudo apt install sqlite3

3. To confirm the installation, check the version:

sqlite3 --version

If successful, you will see something like:

3.46.1 2024-08-27 11:03:15 3bfa9cc97da10589251b342961df8f5f68c7399fa117345eeb516bee837balt1

How to Use SQLite

Now let's proceed to working with SQLite.

Creating a Database

There are several ways to create a database in SQLite:

Using the sqlite3 command

When launching sqlite3, you can specify the database name:

sqlite3 my_first_db.db

If the database my_first_db.db exists, it will open; if not, it will be created and automatically deleted upon exiting sqlite3 if no queries were made to the database. To ensure the database is saved to disk, you can run an empty query by typing ; and pressing Enter:

;

After working with the database, changes can be saved using the SQLite .save command with the database name:

.save my_first_db.db

Or with the full path to the database:

.save C:/sqlite/my_first_db.db

Be careful when using the .save command, as it will overwrite any existing file with the same name without asking for confirmation.

Using the .open command

You can also create a database in SQLite using the .open command:

.open my_first_db.db

As in the first case, if the database exists, it will open; otherwise, it will be created. Unlike the first method, the new database won’t disappear when exiting sqlite3, but changes must be saved using the .save command as shown above.

Creating a temporary database

If you run sqlite3 without any arguments, it will create a temporary database that will be deleted when the session ends. However, this database can be saved to disk using the .save command:

sqlite3

SQLite version 3.46.1 2024-08-27 11: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

Creating a Table

In SQLite, information is stored in tables which you can create with the CREATE TABLE statement. This command must include the table name and the names of the fields (columns). It can also include data types, field descriptions (e.g., primary key), and default values.

For example, let’s create a table to store details about different dog breeds using the CREATE TABLE statement:

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

In this table, the id column is marked as the primary key, meaning it will be the index column, and an integer value will be automatically generated for it.

Inserting Records into a Table

To insert new records into a table, use the INSERT INTO SQL command, where you specify the table and columns to insert the new values. The structure of the query is:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

If the number of values matches the number of columns in the table, you can omit the column names. Columns not listed will be filled with default values (if specified in the CREATE TABLE statement) or NULL if no default value was provided.

For example:

INSERT INTO dog_params (dog_breed, speed_km_per_h, weight_kg)
VALUES ("Greyhound", 72, 29);

Alternatively, you can insert all fields, including manually specifying the id:

INSERT INTO dog_params VALUES (2, "Jack Russell Terrier", 61, 5);
INSERT INTO dog_params VALUES (3, "Dalmatian", 59, 24);

In the first case, the id was automatically generated because it's the primary key, while in the second case, we manually specified the id.

You can also insert multiple records at once, and the id will be automatically generated:

INSERT INTO dog_params (dog_breed, speed_km_per_h, weight_kg)
VALUES ("Borzoi", 58, 39), ("Standard Poodle", 48, 27);

Viewing Tables

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

SELECT * FROM dog_params;

The result will look like this:

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

You can use the WHERE clause to filter rows that meet certain conditions. For example, let's display dog breeds with speeds less than 60 km/h:

SELECT * FROM dog_params WHERE speed_km_per_h < 60;

The output will be:

3|Dalmatian|59|24
4|Borzoi|58|39
5|Standard Poodle|48|27

Modifying Records in the Table

You can modify a table using the ALTER TABLE command along with additional options like:

• Rename the table: RENAME TABLE

• Add a column: ADD COLUMN

• Rename a column: RENAME COLUMN

• Delete a column: DROP COLUMN

For example, let's add a new column for the height of the dog at the withers:

ALTER TABLE dog_params ADD COLUMN height_cm integer;

To update existing records in the table, use the UPDATE query. This allows you to change the value of a single cell in one or more rows.

For instance, let's add height values for the dogs in our table:

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

The final table will look like this:

SELECT * FROM dog_params;

1|Greyhound|72|29|71
2|Jack Russell Terrier|61|5|28
3|Dalmatian|59|24|53
4|Borzoi|58|39|69
5|Standard Poodle|48|27|61

For more information, we recommend checking the SQLite documentation.

How to Use SQLiteStudio

You can work with SQLite databases from the command line and graphical interface tools like SQLiteStudio.

SQLiteStudio is free, portable, intuitive, and cross-platform. It provides many essential functions 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 portable version from the official website: https://sqlitestudio.pl. Then, extract (or install) the downloaded file into a folder, for example, C:\sqlite\gui\, and launch the program.