SQLite Database on a Raspberry Pi

In this blog post I’ll introduce you to SQLite, which is an in-process light weight library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world and its source code is in the public domain.

Don’t have a Raspberry Pi board? read Best Raspberry Pi Starter Kits.

What is SQLite?

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

This tutorial shows you how to install SQLite on a Raspberry Pi and teach you some basic commands to use SQLite in your RPi projects.

Note: SQLite is more powerful and has a lot more features from what I’m about to show you, however the purpose of this tutorial is to share a basic example on how you would store temperature and humidity readings in a SQLite table. For more information you can read the official documentation here.

Learn Raspberry Pi, ESP8266, Arduino and Node-RED. This is a a step-by-step course to get you building a real world home automation system using open-source tools DOWNLOAD »

Learn Raspberry Pi, ESP8266, Arduino and Node-RED. This is a a step-by-step course to get you building a real world home automation system using open-source tools DOWNLOAD »

Basic Raspberry Pi Setup

Before you continue reading this project, please make sure you have Raspbian Operating System installed in your Raspberry Pi.

You can read my Getting Started with the Raspberry Pi Guide to install Raspbian and complete the basic setup.

Why SQLite?

Here’s some of the features that makes SQLite a great database:

  • A complete SQLite database is stored in a single cross-platform disk file
  • SQLite is very small and light weight
  • SQLite is self-contained (no external dependencies required)
  • SQLite does not require a separate server process or system to operate (serverless)
  • SQLite comes with zero-configuration (no setup needed)
  • SQLite is cross-platform. It’s available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT)

Databases are used to store information in a way that can be accessed quickly and easily. In this post you’re going to build a data logging application with a Raspberry Pi that stores temperature and humidity.

Installing SQLite on Raspberry Pi

You can install SQLite on a Raspberry Pi using this command:

pi@raspberry:~ $ sudo apt-get install sqlite3

You’ll have to type Y and press Enter to confirm the installation.

After the installation is completed, the SQLite libraries are supplied with an SQLite shell. Use this next command to invoke the shell and create a database:

pi@raspberry:~ $ sqlite3 sensordata.db

The file sensordata.db is now created. After typing the preceding command, a prompt appears where you can enter commands. The shell supports two types of commands. Commands that start with a ‘.’ are used to control the shell. Try typing these commands:

sqlite> .help

Download our Free eBooks and Resources

With the ‘.help‘ command you can be quickly reminded of all the supported commands and their respective usage.

To quit from the SQLite shell use the ‘.quit‘ command.

Note: you can use the up arrow to scroll through previous commands.

The shell also supports SQL commands which you’re going to try in the next section.

Using SQL to access databases

Structured Query Language (SQL) is a language that’s used for interacting with databases. It can be used to create tables, insert, update, delete and search for data.

SQL works with different database solutions such as SQLite, MySQL and others. SQL statements must end with a semicolon (;).

It’s common for SQL commands to be capitalized, but this isn’t strictly necessary. Most people prefer to use capitalized letters, because it increases readability.

SQL CREATE TABLE

I’m going to start by creating a simple table with 6 columns that could be used for temperature and humidity logging application in different parts of a home. Let’s start by creating a table:

sqlite> BEGIN;
sqlite> CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT);
sqlite> COMMIT;

To see all the tables, type:

sqlite> .tables
dhtreadings

It returns the newly created table named ‘dhtreadings’. You can see the fullschema of the tables when you enter:

sqlite> .fullschema
CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT);

SQL INSERT

To insert new temperature and humidity readings in the database, you could do something like this:

sqlite> BEGIN;
sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.4, 48, date('now'), time('now'), "manual");
sqlite> COMMIT;

In a future blog post, the ESP8266 is going to send the readings to a Python web server that inserts the data in the table.

SQL SELECT

To access the data stored in the database, you use the SELECT SQL statement:

sqlite> SELECT * FROM dhtreadings;
1|22.4|48|2017-01-26|23:43:13|manual

So far, you only have 1 reading inserted in the database. You can insert a new reading as follows:

sqlite> BEGIN;
sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.5, 48.7, date('now'), time('now'), "manual");
sqlite> COMMIT;

And when you SELECT the data stored in the table, it returns 2 readings:

sqlite> SELECT * FROM dhtreadings;
1|22.4|48|2017-01-26|23:43:13|manual
2|22.5|48.7|2017-01-26|23:43:54|manual

For an easier understanding, you can compare a SQL table to an Excel sheet that looks like this:

SQL DROP

If you want to completely delete the table from your database, you can use the DROP TABLE command.

Warning: the next command will completely delete the dhtreadings table:

sqlite> DROP TABLE dhtreadings;

Now, if you type the ‘.tables‘ command:

sqlite> .tables

It doesn’t return anything, because your table was completely deleted.

Wrapping up

We’ve just scratched the surface of what you can do with SQLite. In the next blog post, we will publish sensor readings with the ESP8266 that will be stored in the database and displayed in your Python web server.

Like home automation? Learn more about Node-RED, Raspberry Pi, ESP8266 and Arduino with my course: Build a Home Automation System for $100.

Do you have any questions? Leave a comment down below!

Thanks for reading. If you like this post probably you might like my next ones, so please support me by subscribing my blog.


Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »

Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »


Enjoyed this project? Stay updated by subscribing our weekly newsletter!

20 thoughts on “SQLite Database on a Raspberry Pi”

  1. Very nice introduction. So nice I just might try it for replacing some of my old tired but rather small MSAccess db stuff.

  2. Thanks! So many things will be more easy when you put results in a Db. And there is lot of information about how too manage a SQL Db.
    This is something I have missed! Great.

  3. Nice simple tutorial thanks; I’ve been attempting to do this for a couple of weeks with various success – Getting the sql to install and run is relatively easy, but how do I relocate the database to an external attached drive?

    Any advise?

  4. Hi Rui,
    Always enjoy your blog posts. A little info, everything on this recent blog also works great on the cheap Orangepi. I am running ubuntu Mate on an Orangepi C. Also it is running the ngok for the Virtual tunnel for my node MCU from your “Password Protected Website”course.

    Thanks again
    Jay

  5. pls help me
    i have waveshare 10 dof imu sensor b and neo 7m gps but i can’t measure the lat,long,angle with arduino due so how to do?pls

  6. I was playing with the SQLite as introduced here and have a few questions:
    I run this on Centos 6x
    – When I tried to yum install sqlite, I get a bunch of ERROR 6 – “Couldn’t resolve host ‘ bla bla bla’. How do I install sqlite on Centos?
    – Though I am not sure if it will work correctly, but I was able to download the SQLite zip file for Linux and run it directly on the Centos host without installing it.

    Does anyone have the same problem or any comment?

Leave a Comment

Download our Free eBooks and Resources

Get instant access to our FREE eBooks, Resources, and Exclusive Electronics Projects by entering your email address below.