SQLite Database on a Raspberry Pi. Best DB for IoT ?

Most of you are quite familiar with Database types, SQL and NoSQL. When it comes to choosing one, A good developer does not take sides of either one but chooses as per the requirements and most efficient way to solve the problem. Today, many IoT solutions are designed using Raspberry Pi. So there must be an ideal DB to store logs and data from all the connected devices.
Let me introduce you to SQLite DB, which, unlike most other SQL databases DOES NOT have a separate server process.
So ? What does that mean ?
It means it will read and writes directly to ordinary disk files.
SO basically, you get a complete SQL database, containing multiple tables, indices, triggers, and views, in a single disk file.

This makes it the most suitable SQL DB to implement on Raspberry Pi (If you think otherwise, Kindly let me know the reason in comments. It can surely help someone)

Lets Start with the Set-up

Preparing Raspberry pi:

Start with updating and upgrading your Raspberry Pi:

sudo apt-get update

sudo apt-get upgrade

Keeping your Raspberry updated makes your Raspbian system more secure and bug free.

Getting Started with SQLite:

To install SQLite on your Raspberry Pi, just open the terminal and type the following command:

sudo apt-get install sqlite3

Playing with SQLite :

After installing SQLite CLI, you can create a DB from terminal using the following command:

sqlite3 DB_FILE_NAME

Create Table

To create a table use the following command:

create table TABLE_NAME (FIELD1 NAME DATATYPE, FIELD2 NAME DATATYPE, …… );

Insert Data

after creating the table, you can add data to it by using following command:

insert into TABLE_NAME (FIELD1, FIELD2, FIELD3, ….) values(VALUE1, VALUE2, VALUE3, …);

One important thing here is to match the data types.
For example: do not surround integer values with quotation marks.

Reading from Table:

You can read the data from the table using following query:

For Complete Table :

select * from TABLE_NAME;

For a particular Column:

select COLUMN_NAME from TABLE_NAME

Removing Data:

To remove a particular entry (Row) from DB use :

select from TABLE_NAME where CONDITION;

you can see that now the item with name “Alex” has been removed from the table.

To delete entire table, you can use the following command:

drop table if exists TABLE_NAME;

Exit CLI:

To exit sqlite cli and back to terminal , simply type

.exit

Conclusion

These were just the basics of SQLite to get you started with SQL databases on Raspberry Pi.
SQLite can to be a really useful tool for IoT based systems which include a Raspberry Pi. You can easily maintain the logs from connected devices and as I explained earlier, Its only one file in the end, so it’s super easy to move across multiple platforms.

I hope you find this useful. Happy coding :) .