A NOTE ABOUT ARDUINO: This is one area that (arguably) isn’t really suited to an Arduino. While Arduino is fantastic at many things, holding and storing complex data isn’t really one of them. Sure it’s “possible”, and depending on what you’re working with, but an Arduino really isn’t suited to real databasing.

If you need an Arduino to store data, you really should consider sending the data (via WiFi or Ethernet) to a database server to do the heavy lifting there. This is really the way an Arduino will best store data, without risk of overloading, and will quickly release you from the limitations of the small memory in an Arduino.

If you’re ever storing or manipulating data, then you may find yourself choosing between database options. In this installment of Secret Code, we’re going to look at a few options for running and using databases. This can be done with virtually any computer, and indeed a Raspberry Pi.

Let’s look at ways to store data, using a database approach. There are a host of database options out there, but they really fall into a few categories. We’ll look at two of the leading options, MySQL/MariaDB and MongoDB, as well as take you through installing them on a Raspberry Pi.

DATABASE TYPES

Plain Text

Before you embark on your first database journey, it’s important to consider whether or not you actually need a database at all!

Storing data in a plain text manner is still technically a database, albeit one that is relatively limited. The data that can be stored in plain text is generally still the same as most databases, but there’s a lack of data arrangement and retrievability of that data.

However, if you are purely storing a log of temperatures or sensor data across a time period, for instance, you probably don’t really need any of that. You could, therefore, consider text files. Depending on your application, you can probably grab the text files and put them into your favourite spreadsheet software anyway.

PROS: Exceptionally simple to implement, easy to import into a spreadsheet for manipulation.

CONS: Lack of searchability, query structure, or sorting.

SQL

Perhaps the most common structured database around. SQL stands for Structured Query Language, and its SQL acronym can be spoken as a word like “sequel”.

The data structure of a SQL database can be thought of like a spreadsheet. There are columns, rows, and tables (like additional sheets where the rows and tables are unique from one another).

PROS: Fairly low learning curve, great querying power.

CONS: Very rigid data structure.

NoSQL

If this is your first encounter with the term “NoSQL”, it may seem like it’s a little anti-MySQL. However, that’s not what it means (though advocates of NoSQL can quickly become anti-SQL).

NoSQL is sometimes referred to as document-style storage. If MySQL is a spreadsheet, NoSQL is a text document. But wait, didn’t we cover plain text?

Sure, however, that’s really where the similarities between Plain Text and NoSQL end. While they are generally a collection of documents using a JSON (JavaScript Object Notation) format, they

are still held against keys, to assist with querying, sorting, and retrieval.

PROS: Totally flexible storage method, the ultimate in scalability.

CONS: Reasonable learning curve, less broadly implemented.

HYBRID DATA STORAGE

Along the way, developers started to recognise the need for more flexibility in their SQL databases, to cater for the rapidly expanding demands of their client applications. It wasn’t always feasible to add more columns to the database for every little thing.

As the complexity of online systems grew, so did the need for additional parameters and data storage within the tables of MySQL, and developers started storing JSON objects and other such data into structured databases as a way to store more than a simple value. This is perhaps what led to more interest and development into document database systems!

DATABASING ON A PI

Let’s look at how to install and run MySQL (SQL obviously) and MongoDB onto a Raspberry Pi running Raspbian. Our Pi is using Raspbian GNU/Linux 9.4 (Stretch) specifically.

Before installing additional software it’s a good idea to ensure everything is up to date:

sudo apt-get update

MySQL/MariaDB

The latest release of Debian/Raspbian (Stretch) has moved away from Oracles MySQL in favour of an open-source variant called MariaDB which is fully MySQL compatible. Since MariaDB is forked from MySQL, it looks and feels the same. MariaDB also has performance improvements compared to MySQL, making it a great choice for your Raspberry Pi where resources are limited.

Install by running the following terminal command:

sudo apt-get install mariadb-server

Or :

sudo apt-get install mysql-server

When you install mysql-server package on Stretch you will actually get MariaDB instead of MySQL so users are encouraged to use mariadb-server instead.

Once installed you can access MySQL/MariaDB using the command:

sudo mysql

Note: To simplify this tutorial we will use sudo here for root access avoiding user and permission config. Usually, you would create a MySQL user to connect to the client.

Once you’ve logged into the SQL client you can list the available databases with:

SHOW DATABASES;

To create a new database:

CREATE DATABASE pets;

Now that we have a database we can select to use it with:

USE pets;

Next, we can create a table within our pets database and define the columns. We will define 3 columns: id, name, breed and set what data type the columns can contain.

CREATE TABLE dogs (
  id          INT NOT NULL AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  breed        VARCHAR(100) NOT NULL,
  PRIMARY KEY  (id)
);

To show created tables, type:

SHOW TABLES;

Now, we can add a new record:

INSERT INTO dogs ( name, breed ) VALUES ( 'Lucky', 'Labrador' );

You can also insert multiple records in a single command:

INSERT INTO dogs ( name, breed ) VALUES
  ( 'Max', 'Bulldog' ),
  ( 'Lassie', 'Collie' );

We can display all records by making use of a wildcard '*'.

SELECT * FROM dogs;

Or search for particular records:

SELECT name FROM dogs WHERE breed = 'Bulldog';

To exit from the MySQL client, type exit and hit enter.

Congratulations, you've just completed some basic MySQL queries!

MongoDB

To install MongoDB:

sudo apt-get install mongodb

Next, create a directory to store the mongo databases:

sudo mkdir -p /data/db/

You may also have to set the permissions if you have trouble connecting to the client:

sudo chown -R `id -u` /data/db

Connect to the mongoDB client:

mongo

You're now in the MongoDB command shell, just like we were in the MySQL/MariaDB shell in the previous examples. Now it's time to insert some dummy data and make a few things happen!

As you can see, there aren't yet any DBs - but that's ok, we'll create one! To list available databases:

show dbs

Create a new database and select it to use. Or if the database already exists, the "use" command will switch over to it.

The one thing that can take some getting used to with MongoDB is this auto-creation type approach, where things are created on the fly in many instances.

use pets

To create a collection (in MySQL this would be a table):

db.createCollection("dogs")

Next we want to display created collections:

show collections

As we noted, in MongoDB, you don’t actually need to create a collection, MongoDB creates them automatically - try it by inserting a document:

db.dogs.insert({ name: 'Lucky',  breed: 'Labrador' })

To insert multiple documents in a single command, you can pass an array of documents:

db.dogs.insert([
  { name: 'Max',  breed: 'Bulldog' },
  { name: 'Lassie',  breed: 'Collie' }
])

You can choose to find all documents:

db.dogs.find()

Or search for documents, using:

db.dogs.find({ breed: 'Bulldog' })

Congratulations, you have now completed queries with MongoDB!

WHERE TO FROM HERE?

Dive deeper and read the documentation and learn other actions such as removing or updating a record.