Getting Small: Building Lightweight Web Applications with Small-Footprint Databases
Not every application needs a full-featured enterprise-scale database. In such cases, you can reduce costs and save resources by using a small-footprint database.
by DEVX Staff
Jul 20, 2007
Page 1 of 5
f you've done any open-source database development recently, you probably already know that when it comes to selecting a database for your application, you have a plethora of choices. You are no longer limited to commercial products such as Microsoft SQL Server or Oracle; open-source products such as MySQL and PostgreSQL are viable alternatives, offering similar features at a fraction of the cost.
However, while these products have rich, robust feature sets and reduce costs, they're not smallthe latest downloadable versions of PostgreSQL and MySQL weigh in at 12 MB and 57 MB respectively. For small-scale applications that have minimal database needs, using any large feature-rich database products is often overkill; it's often more appropriate to use a small-footprint database instead. Even though small-footprint databases may lack sophisticated features such as triggers, views, and stored procedures, they make up for the reduced feature set by requiring minimal resources and disk space.
But what small-footprint databases are available, and how do you use them in a project? This article attempts to answer that very question by describing some of the options available and building a sample application. The sample web application that accompanies this article is a personal to-do list, which allows an individual user to log items "to do" in a database. A browser-based interface supports commands to add, edit, or delete items from the to-do list, and displays a list of completed and pending items.
What You Need
The sample application uses PHP, running under the Apache Web server. Both are open-source projects that you can freely download and install to your development environment.
Choosing A Database
Here's a quick overview of six small-footprint database choices available. All the contenders described here are lightweight open source products suitable for small-to-medium complexity applications.
Apache Derby is possibly the best-known of the small-footprint Java database engines. As an open-source project, it is freely available for download and use (in personal or commercial projects) under the Apache License. Derby is fully ACID-compliant, meets the ANSI SQL standards, and is specifically designed to be embedded directly into a Java application. It runs in the same JVM as the source application and uses an embedded JDBC driver for database communication. Derby supports multiple concurrent users (even in embedded mode), integrates well with IDEs such as Eclipse and NetBeans and server environments such as Tomcat and WebSphere, and includes various interactive, Java-based, command-line tools for database manipulation.
H2 is another small, fast Java-based database engine, usable via both JDBC and ODBC APIs. It can be used in both embedded and server modes, and includes support for triggers, joins, views, stored procedures, and encryption. Concurrent use is supported, and H2 also supports a simple clustering mechanism, which makes it suitable for use in mission-critical applications that have high uptime requirements. H2 is freely available online under the Mozilla Public License.
Ocelot is a Windows-only database engine that provides full compliance with SQL-92 and SQL-99. Packaged as a 32-bit Windows DLL, Ocelot integrates easily into Windows applications, and is accessible via the standard ODBC API in both single-user and multi-user mode. Ocelot fully supports triggers, stored procedures, and views, and comes with a graphical administration tool for database maintenance and query construction.
Firebird is a full-featured client/server RDBMS available for both Windows and *NIX platforms. It's based on the open-source version of the InterBase database by Borland. Firebird also provides an embedded single-user database engine as a library file, which you can integrate directly with any Windows or Linux application (there are some constraints on the Linux version). This embedded engine is fully compliant with SQL-92 and most of SQL-99; it supports ACID-compliant transactions, sequences, triggers, sub-selects, and referential integrity constraints, and includes various command-line tools for database interaction. The embedded Firebird engine is available under a license equivalent to the Mozilla Public License.
One$DB is an open-source version of a commercial Java RDBMS called DaffodilDB, and is embeddable into any Java application. It is compliant with SQL-99, and is accessible via JDBC (a PHP extension is also available). One$DB includes support for encrypted tables, triggers, views, and stored procedures, and is available for both personal and commercial use under the LGPL.
SQLite is a free, single-user, embeddable database engine implemented as a standalone C library. It uses a single disk file per database. It supports "most of SQL-92," according to the documentation, but does not include support for foreign key constraints, triggers, or stored procedures. It also supports only a limited version of the ALTER TABLE command and a small subset of field data types. However, it is the smallest and lightest of all the database engines in this collection, and is natively supported in PHP 5.x, both via a SQLite-specific driver and through the PDO data abstraction layer.
Which of these databases is best suited to the sample application being developed? Of course, the answer depends on your needs. Using the open-source LAMP stack helps keep costs down, so the Windows-specific Ocelot is out immediately if you take that route. Of the remaining options, SQLite is my choice for this application, for a couple of reasons. First, it's natively supported in PHP, so PHP developers can begin using it for development immediately, without any additional configuration or installation requirements. Second, although it lacks many of the more sophisticated features (such as triggers, stored procedures, and foreign key constraints) found in other databases, it's light and resource efficient, which translates into better performance. And third, a subjective factor: I'm not as familiar with Java as I'd like to be, and therefore find SQLite easier to use than an equivalent Java-based RDBMS engine.