Adding a Database to the Raspberry Pi Zero

This morning, after someone added SQL Developer to a Raspberry Pi 3, Jeff Smith pinged me and the question was posed, as it often is, can you run Oracle database server on a Raspberry Pi, (RPI)?

The answer is no, you can’t, as there are no binaries for the ARM processor platform that you can compile, so its nothing to do with power, (although it is low powered for a very good reason…) but really due to the processor type.  Hopefully that’ll stop those of you building out massive RPI clusters in hopes of creating their own home servers with these inexpensive computers.

With this said, you can run a database, so to show how easy this is, I’ll show you how to install and work with an installation of SQLite on a Raspberry Pi Zero, the version that’s less than 1/2 the size of a credit card.

Other people have a beer and take the evening off.  Me?  I have a beer and start installing stuff on single board computers… 🙂

Install SQLite

Installation is easy.  Ensure you have the latest update, so if you haven’t run your update in awhile, run that first from the command line.  Remember you must have root or sudo privileges to perform these tasks:

$sudo apt-get update

Once complete and you know you’re up to date, then simply get and install SQLite:

$sudo apt-get install sqlite3

Create a Database

Let’s create a database:

$sqlite3 mydb.db

This creates the database using the mydb.db file as the logical container for it.

Note: If you need help at any time, you can type in .help or .show from the sqlite prompt and it will display information similar to man pages in linux.  It’s very helpful and user friendly.

If you’re out there comparing features, about to complain about all the ways that SQLite is NOT Oracle, well, you can stop right here.  On the support page for SQLite is the quote:

Small. Fast. Reliable.  Choose Any Three.

SQLite isn’t trying to be Oracle, but if you need a database and you’d like to put one on a RPI, this is the one to use that is small, fast and reliable.

Working with SQLite

Of course there are some syntax differences and SQLite has most standard SQL syntax at it’s disposal. Remembering to type BEGIN for the implicit transactions and to COMMIT after each is commonly the biggest challenge.  This includes for data dictionary objects, (aka ddl).

As this isn’t a full RDBMS client/server database, there aren’t any roles or privileges that reside outside of the OS level privileges to the database file.  This database works very well in the support of RPI projects and this is the goal of what I’m hoping to demonstrate here.

So let’s start by just creating a table and adding a few rows.

begin;
create table tbl1(col1_id text, date_c1 date, time_c1 time, range_1 numeric);
commit;

Now you can insert rows into your new table:

begin:
insert into tbl1 values('user1', date('now'), time('now'), 12);
insert into tbl1 values('user2', date('now'), time('now'), 7);
insert into tbl1 values('user3', date('now'), time('now'), 20);
commit;

You can then select from your new table and the row(s) will be returned, separated by the pipe symbol:

select * from tbl1 where col1_id='user2';
user2|2016-03-22|00:12:237

So there you have it.  Database software installed-  check.  Database created- check.  Object created and rows added- check.  Table queried- check.  All done in less than five minutes.

If you’d like to learn more, you can check out SQLite’s home page.