Webserver and database combination on Raspberry Pi

By Leo Gaggl

January 28, 2013

My normal combination on the big-server side would be Apache + MySQL (or PostgreSQL), but on the RPi this seems to be absolute overkill. For data-logging operations I would not use the local system anyway (looking at MQTT as well as Remote MongoDB datastore via REST Webservices).

After some poking around and reading up on the options I decided to go for the following combo: LightHTTPD + SQLite. Both are lightweight replacement of their fully-featured big-server counterparts (Apache HTTP & MySQL) and have very familiar configurations. There would be other options that have even less resource usage, but I really don’t have the time to start from scratch somewhere.

Another reason to go for this combination is that these are very well supported systems with regular security audits. Even though I am not planning to use my RPi’s for anything mission-critical this is always worth a consideration as you don’t need to unnecessarily introduce vulnerabilities to your network.

Install & configure LightHTTPD

sudo apt-get install lighttpd php5 php5-cgi php5-sqlite sudo lighty-enable-mod fastcgi sudo lighty-enable-mod fastcgi-php
Further config changes can be also made via the config file.
sudo vim /etc/lighttpd/lighttpd.conf sudo service lighttpd force-reload

Install & configure SQLite

sudo apt-get install sqlite3 sqlite3 /home/username/database_name.db
All other commands are standard SQL from the ‘sqlite>’ command prompt or via SQL scripts like
sqlite3 /home/username/database_name.db

Access Databases from the webserver (using PHP)

query('SELECT bar FROM foo'); while ($row = $results->fetchArray()) { var_dump($row); } ?>

See PHP: SQLite3::query – Manual for more details.