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.
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
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. Learn how to 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.
Very nice introduction. So nice I just might try it for replacing some of my old tired but rather small MSAccess db stuff.
Depending on where you intend to run the database it might make sense to use SQLite.
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.
Exactly, a lot of stuff should be stored on a database if we’re doing things right.
The next post will put all the concepts together.
Thanks for reading,
Rui
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?
The SQLite database is a single file and it’s cross platform, you can simply copy the file to your computer.
Looks very interesting. For the moment I have to concentrate myself on ESP8266.
The next project will integrate the ESP to send the temperature and humidity readings to the database.
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
Hi Jay,
Thanks for your continuous support. I’m glad it also works with the Orange Pi, I’ve never tested it before 🙂
Regards,
Rui
Great! Looking forward to your next post related to SQLite. Thank you!
Thanks for reading, the next 2 or 3 new project will put all the concepts together.
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
I don’t have any tutorial on that exact subject.
thank you
Its very interesting tools to archive data
You’re welcome!
Thanks for reading,
Rui
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?
I don’t have the procedure for Centos…
Thanks for the useful tutorial to use the database on the raspberry pi.
You’re welcome!
Thanks for reading,
Rui
Thank you for this tutorial. Have you managed to write announced sensor reading ESP8266 database update?
“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.”
Hi.
Here is the tutorial: https://randomnerdtutorials.com/esp8266-publishing-dht22-readings-to-sqlite-database/
Regards,
Sara
Starting my first project on a raspberry pi using sensor data. Does SQLite work well with photo storage?
Hi Jessica.
We haven’t tried that.
Regards,
Sara
I have been trying to learn how to save (4) DS18b20 and (1) BME280 data into an SQLite.
it seems there is a massive conspiracy to tell people how to install SQLite and then show one simple, single node save to the DB.
Please add a tutorial on Node-Red and SQLite on how to read multiple MQTT inputs, then save them to an SQLite database
Hi.
Take a look at this tutorial: https://randomnerdtutorials.com/sqlite-with-node-red-and-raspberry-pi/
I hope it helps.
Regards,
Sara