Lightweight databases that work well on Raspberry Pi: I made this list of the best lightweight databases I found to run on Raspberry Pi after I had trouble setting up a database for a recent data analysis project – hopefully this guide saves you a lot of the trouble I went through.
The best databases to use on Raspberry Pi are TinyDB, SQLite, or MariaDB. These databases are compatible with Raspberry Pi hardware and operating systems, and do not require significant resources to run. They are also simple to interface with directly or from other applications.
I’ve included below when to use each database, how to install it, and some of the other database options available for Raspberry Pi. Also, if you’re interested in how to get the data to put into the database, check out my guide to all the ways a Raspberry Pi can collect data (including from sensors): chipwired.com/collect-data-with-raspberry-pi/
I wrote a separate guide for cloud storage, if you’re interested in how Raspberry Pi can store data in the cloud, check it out here: chipwired.com/cloud-connections-raspberry-pi
TinyDB is an easy to install and simple to use database that stores data in a JSON/document arrangement. It is designed to be included inside a Python app and offer a simple interface to its underlying data storage.
A Raspberry Pi can run TinyDB as it runs anywhere that Python does. It does require Python installed on the Raspberry Pi to run. I tested TinyDB using Ubuntu for Raspberry Pi, it also works on the Raspbery Pi OS.
When to use TinyDB on Raspberry Pi:
- Collecting and storing information from sensors using Python
- Serving simple files, a website, or a web interface
- Store configuration information, such as building a configurable app for home automation
Installation of TinyDB is also really simple, follow these steps:
Step 0 (if Python and pip aren’t installed) – Install Python and pip by opening up a Terminal and typing in this:
sudo apt install python3 pip
Step 1 – Use pip to install TinyDB by typing this into the Terminal:
pip install tinydb
Step 2 – Open Python:
Step 3 – Import TinyDB and open up a database for it to use:
>>> from tinydb import TinyDB, Query >>> db = TinyDB('db.json')
A TinyDB database is now loaded and ready to run queries on! For more information about the queries that can be run, check out the full guide on their website here.
If you’re interested in learning more about analyzing data with Raspberry Pi, I wrote a whole guide (including data collection and dashboards) here: chipwired.com/analyze-data-raspberry-pi/
SQLite is an easy to install and easy to use SQL database. It’s designed to have a light footprint so that it can run on devices such as phones, cars, and other low power / low CPU applications. SQLite works great with Raspberry Pi.
When to use SQLite on Raspberry Pi:
- Quickly automating storing data in a table format
- Use when your Raspberry Pi is acting as a server for other devices
- Learning about SQL and relational databases
To install SQLite on Raspberry Pi:
Step 1 – Open the Terminal and update the apt package manager:
sudo apt update
Step 2 – Install SQLite:
sudo apt install sqlite3
The 3 at the end means you will get the latest version of the third SQLite.
I like to browse SQLite using a visual database browser, it gives me a way to visualise the tables and see the effect of different SQL statements. For SQLite, I found a good visual tool to be DB Browser.
To install DB Browser, use the following command in a Terminal:
sudo apt install sqlitebrowser
SQLite can be used from many programming languages. Once SQLite is running on your Raspberry Pi, it can be accessed using the relevant library/package for whichever programming language your app is written in. The table below lists the library/package to include to connect a language to the SQLite database running on your Pi:
|Language||Library or Package|
|Java||Use the SQLite JDBC driver (found here)|
|C / C++|
|R||Install RSQLite then include: |
Details on the different queries that can be used with SQLite can be found on their website here.
MariaDB is a full-featured advanced database designed as a drop-in replacement for the popular MySQL database. MariaDB works on Raspberry Pi OS and Ubuntu 21.04.
When to use MariaDB on Raspberry Pi:
- Hosting an interactive website from your Raspberry Pi
- To learn about how full-featured SQL databases work
- Advanced query support is required
I’ve found MariaDB too complicated to make it worth installing for most Raspberry Pi applications. For some projects, such as web hosting, MariaDB may already be included as part of the installation process for a larger web application – if this saves you from the complicated MariaDB setup, then it might not be too bad to use MariaDB.
To install MariaDB on Raspberry Pi:
Step 1 – Open the terminal and update the apt package manager:
sudo apt update
Step 2 – Install MariaDB:
sudo apt install mariadb-server
Step 4 – Login to MariaDB (we need to create a user that other apps can use to login):
sudo mysql -u root
This is not a mistake – MariaDB is run using the command
Step 4 – Create a user that we can use in our other apps (note the semicolon at the end of the command, and the single quotes; case does not matter):
CREATE USER chris identified by 'secretpassword';
MariaDB is now running and setup for an app to connect to it and store data. To use it from here you’ll need to setup tables and add data.
I’ve found a graphical tool to be the easiest way to do this (since I’m not planning on putting the Raspberry Pi into production).
I used Beekeeper Studio to work with MariaDB on my Raspberry Pi. Beekeeper studio only works with 64-bit operating systems (such as Ubuntu 21.04); it will not work with the default Raspberry Pi OS as it is 32-bit.
Installation instructions for Beekeeper Studio can be found on their website here. I followed these steps and managed to get Beekeeper Studio working on my Raspberry Pi when it was running Ubuntu 21.04. To run Beekeeper Studio after installation, look for it in the list of apps (I tried to run it from the Terminal and couldn’t figure it out).
MongoDB is a free, open-source database that stores data in a document structure (as opposed to the more common SQL-table structure).
When to use MongoDB on Raspberry Pi:
- When you don’t know what your app is going to need from a database when you first start writing it (it’s really easy to change the way data is stored in MongoDB) – such as a web application
- To learn about the document storage model for databases
- To store data without worrying about SQL
To install MongoDB on Raspberry Pi: Follow the instructions on their website here. I found these instructions to be comprehensive and really helpful if something goes wrong in the installation or setup process.
I found that while MongoDB can work on Ubuntu version 20.04 (Focal Fossa), it wouldn’t run for me on Ubuntu 21.04 (Hirsute Hippo).
PostgreSQL is an advanced, free, open-source database which is incredibly popular. It is designed to handle large complicated workloads quickly and efficiently. It uses a relational model where data is stored in tables.
When to use PostgreSQL on Raspberry Pi:
- To learn about about how to administer a PostgreSQL server
- To learn about SQL, in particular how a DBMS can help you with analysis and stored procedures
- If you’re running another app which requires PostgreSQL
PostgreSQL is designed for large complicated data management applications. It’s hard to fit such a large amount of data onto a Raspberry Pi, let-alone have the processor to analyse it. That said, I did find end up installing PostgreSQL on my Raspberry Pi.
To install PostgreSQL on Raspberry Pi, check out my guide here: chipwired.com/sql-databases-for-raspberry-pi/.
Firebird is another advanced open-source database which works on Ubuntu for Raspberry Pi. It does not work with Raspberry Pi OS (it needs a 64-bit processor).
I couldn’t find any advantages of using Firebird for Raspberry Pi over the other databases in this guide. As far as Raspberry Pi is concerned, there’s no advantage of using Firebird over MariaDB. Firebird I found to be harder to install.
To install Firebird on Raspberry Pi, I’ve included some basic steps in my SQL database guide here: chipwired.com/sql-databases-for-raspberry-pi/.
Chris recently had to install a database as part of a data analysis project on his Raspberry Pi. He was impressed that the Raspberry Pi was able to handle these advanced database applications smoothly.