Setting up an SQLite Database on a Raspberry Pi

This project will show you how you can set up an SQLite database on a Raspberry Pi.

SQLite is a self-contained relational database management system that does not rely on the typical client-server based system.

This database system differs from MySQL and MariaDB in that it is embedded directly into a program. It does not rely on a server to function.

Instead of relying on an external system, SQLite writes its SQL data to a file that sits alongside your program.

There are various reasons why SQLite is a good option for the Raspberry Pi, some of them being the following.

SQLite has a relatively low overhead.
It is a self-contained system. No external dependencies are required to make it function.
No separate server process. SQLite won’t chew up your Raspberry Pi’s RAM and CPU when not being utilized.
Zero configuration is needed making it easy to use right out of the box.

This guide will walk you through the process of installing SQLite to your Raspberry Pi as well as some simple steps on how to utilize it.

Equipment

Below is a list of the equipment we used when setting up SQLite on the Raspberry Pi.

Optional

Raspberry Pi Case ( Amazon )
USB Keyboard ( Amazon )
USB Mouse ( Amazon )

This tutorial was tested using a Raspberry Pi 4, running the latest release of Raspberry Pi OS Buster.

Installing SQLite to the Raspberry Pi

The process of installing SQLite on the Raspberry Pi is straightforward and quick.

The reason for this is that SQLite is easily obtainable from the default Raspberry Pi package repository.

1. To make sure we don’t run into any issues when installing SQLite, we should first update the operating system.

You can update your Raspberry Pi’s operating system by running the following two commands>

sudo apt update
sudo apt full-upgrade

These commands will update the list of packages on your device and then upgrade any out-of-date packages.

2. Once the update process finishes, you can install SQLite to your Raspberry Pi.

All you need to do to install SQLite is to run the following command.

sudo apt install sqlite3

You will notice that we are using SQLite 3 which is the latest major version at the time of publishing.

The exact version the Raspbian repository provides is, at the time of publishing, “3.27.2“.

Using SQLite on your Raspberry Pi

Now that we have installed SQLite to the Raspberry Pi let us quickly show you how to utilize it.

Don’t worry, as SQLite is incredibly simple to use and requires little to no configuration.

You will need to understand the SQL language, but that is about it. We will walk you through some of the basics of SQL in our next section.

1. As SQLite stores all of its data within a single file on your disk, we will need to reference that file when launching the command line interface.

If the file does not exist, the SQLite command-line interface will generate the file for you.

sqlite3 pimylifeupSQLite.db

In this example, we will be creating a new SQLite database that will be stored in a file called “pimylifeupSQLite.db“.

2. You will now have access to the SQLite, command line interface.

Using this tool, you will be able to issue SQL calls to your database file.

Additionally, you can also utilize SQLite’s inbuilt functions to do stuff such as backup the database, dump the database, and more.

To list out all of the commands, all you need to do is type in “.help“. All of SQlite’s inbuilt commands start with a dot (.) to prevent a mix-up with the SQL language.

.help

3. Once you have finished using the SQLite command-line interface on your Raspberry Pi, it is possible to quit out of the tool.

To quit out of the CLI, all you need to do is type in the following command.

.quit

Basic SQL to Use with SQLite3

Now that you have SQLite up and running on your Raspberry Pi, we can now try using some SQL.

We aren’t going to go super deep into this but will show a couple of commands you can use to see that your database is working correctly.

SQLite has support for the vast majority of the SQL language. You can view what is supported by the database software on their website.

SQL for those who do not know stands for Structured Query Language and is designed for interacting with relational databases like SQLite, MySQL, and MariaDB.

Using this language, you can create new tables, insert new rows in the tables, update existing rows and even delete and search through them.

Creating a Table

Before you can add any data to your brand new SQLite database, you will need to have at least one table.

Creating a table is a reasonably straightforward process and uses a syntax like below.

CREATE TABLE yourtablename(name type, name type);

When you create a table, it must always start with “CREATE TABLE“, which will then be followed by your table’s name.

The column names and data type are then stored within the brackets. Each column needs to have a name and a data type and be separated by a comma (,).

You can see the data types that are officially supported by the SQLite software in the datatype documentation.

Example of Creating a Table

For our example, we are going to create a table called “weather“.

Within this table, we will have four columns that will hold all the data we need.

The first column will store the id.

This column will be set to “AUTOINCREMENT” and will be set as an “INTEGER“.

Additionally, this will be the “PRIMARY KEY” for our table. Keys are important for essential SQL lookups.
Our next column will track the temperature.

As the temperature will be a decimal number (a floating-point number), we will store this data as a “REAL“.
Next, we need a column to track the humidity.

Like the temperature, this will need to be stored as a “REAL” as the humidity will likely contain a decimal point.
We will also want to keep track of the date and time of when this recording was made. We will call this column date.

SQLite has a datatype just for handling things like dates, so we will set the type for this column to “DATETIME“.

Formatting this into a single SQL statement, we will end up with the following.

CREATE TABLE weather(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature REAL, humidity REAL, date DATETIME);

Listing all Created Tables

If you want to check what tables you have created for your Raspberry Pi’s SQLite database, you can utilize the following command.

Make sure that you run this command within the SQLite command line interface.

.tables

This command returns the name of every table that exists within this database file.

Inserting Data into a Table

Now that you know how to create a table, you will now have to insert data.

Inserting data to your SQLite table using SQL is a fairly straightforward process and uses the following syntax.

INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);

You need to use “INSERT INTO” followed by your table’s name. You must specify the name of the columns you are inserting data into within the brackets (( )).

Afterward, you need to use the “VALUES” keyword. In the brackets (( )), you will need to specify the values you want to insert. The order for the values is the same order as what you specify the column names.

For example, “value1” in the line above will be inserted into “column1“.

Example of Inserting Data into a Table

Let us show you an example of inserting data into an SQLite table running on your Raspberry Pi.

For this, we are going to continue off the table we created in the previous section.

This means we have to insert three pieces of data. We don’t need to worry about the id as this is set to auto-increment.

These three bits of information are the following.

Our first column will be for our “temperature“.

For the temperature, we are going to insert the value “20.2“.
We also need to insert a number for our “temperature“.

For this example, we will just be inserting the value “60“.
The final column we need to fill out is the one that stores our “date”.

This function will automatically generate the correct time. By specifying “now” in the parameter, it will use the Raspberry Pi’s current time.

You can read up more on the inbuilt data and time function from the SQLite website.

As each of these columns does not have a default value set, they must have a value defined when inserting data into the table.

If we were to insert all the data we know into the “INSERT INTO” syntax, you would end up with something like we have below.

INSERT INTO weather (temperature, humidity, date) VALUES (20.2, 60, datetime("now"));

Selecting Data from a Table

Now that we have touched on creating a table and adding data to it, we now need to take a quick look into selecting this data.

Selecting data is the final important piece of the puzzle as there is no point in adding data if you can’t see it again.

We are only going to be showing the very basics of selecting data. We won’t be exploring things such as where statements.

The select syntax, at its very basics, is straightforward to use.

SELECT column1,column2,... FROM tablename

All you need to do is type in “SELECT” followed by the columns you want to select from your table.

Afterward, you need to use the “FROM” keyword followed by the name of the table you are selecting these columns from.

Example of Selecting All Columns

If you want to select all of the columns for output, you can do that without typing in each column by hand.

To do this, we need to use the asterisk symbol (*) in place of the column names.

For example, if we wanted to see the values from all of our example table columns, we can utilize the following command.

SELECT * FROM weather;

Using this within the SQLite CLI on your Raspberry Pi, you should get a result like we have below.

1|20.2|60.0|2021-02-01 09:39:17

Example of Select Specific Columns

Likewise, it is also simple to select specific columns.

For this example, we are going to select both the temperature and humidity columns.

SELECT temperature,humidity FROM weather;

Using this SQL statement, we should get a result similar to what we have below, with the humidity and temperature columns only being shown.

20.2|60.0

Conclusion

At this point, you should now have SQLite set up and running on your Raspberry Pi.

SQLite is a good, self-contained, and lightweight alternative database management system.

It uses SQL like other popular services such as MySQL and MariaDB; however, it can operate without running a separate server.

We have also shown some of the simple SQL queries you can use within the SQLite command-line interface.

If you have run into any issues with getting SQLite to operate on your Raspberry Pi, leave a comment below.

If you are after more things to do with your device, be sure to check out our other Raspberry Pi projects.