Open Source Database solutions part I

I’m throwing up my hands. Y’all will just have to live with overlapping series. I have too many ideas be sequential. I promise (!) more on Open Standards and Benkler (actually, Benkler is up next – I’ve got two chapters to review).

I’ve been using databases since I was a grad student in the 80s, and I’ve been designing and developing database-driven applications for the web since 1995. I’ve been using varied Unix-based databases since then (as well as others including Access and Filemaker Pro), and most have been open source.

Although I’ve been using databases for a while, I’ve decided that I’m going to focus specifically on open source databases for the next while, and, in particular, the different kinds of open source solutions that are possible for desktop database systems, or systems that might be server-based, but need a desktop front end. I’m particularly interested in the open source technologies that are coming down the pike that might bump Access from it’s perch as general-purpose nonprofit desktop database king, and that can provide nonprofits with flexible, robust data management solutions.

So here is my current survey of the landscape. I’ll be working a lot with Open Office, and hope to design some screencasts using Open Office Base sometime in the next few months. I’m starting this series off with just a list of the server-based DBMS. I’ll be talking next about desktop DB options (which mostly use these as backends,) and then last about ways to put this all together in an all open-source landscape.

Server-based DBMS (DataBase Management Systems)

• MySQL – MySQL is, I think, the most popular, and best known open source DBMS. It is cross-platform. It is the most popular because historically, it has been the fastest of the open source DBMS, but it has always lagged behind in terms of ACID compliance and other features. You can access a MySQL database via many many different drivers that people have written for just about any programming language. It is also possible to access MySQL databases via ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity)
• PostgreSQL – PostgreSQL has always been my favorite. I’ve been using it since it was called Postgres95 – before version 6. (Wikipedia has a great entry on PostgreSQL, including some history). PostgreSQL has always been ahead of MySQL in terms of ACID compliance and robustness, and still is. It lagged behind MySQL for years because of speed issues (it was much slower,) but that has changed with the newest versions, such that in fact PostgreSQL is faster and more scalable than MySQL. PostgreSQL is also cross-platform, with binaries available for Linux and Win32 from Postgresql.org, and Mac OS via Darwin Ports. A PostgreSQL database can, like MySQL, be accessed via APIs written for just about all programming languages, JDBC, and ODBC (which I have quite a bit of experience with.)
• Firebird – this is a newer kid on the block, sort of. It has a very long history, though, since it is based on Borland’s InterBase codebase. It’s doesn’t have nearly the user base, or the amount of available tools as the others, but InterBase is a pretty interesting product, with some good features (like a small footprint, server performance tuning, and a great rollback and recovery system.) It is also cross platform.
• Apache Derby – a DBMS written entirely in Java. This project has a small footprint, and is designed to be easily embedded in other Java projects. It comes with a scripting language and interpreter, called ‘ij’ which is how you can interact with Derby on the command line. Also, of course, you can use JDBC is a way to access Derby. I’ll be doing a fair bit of experimentation with Derby (’cause I’m curious.)
• SQLite – a small footprint C library that implements an ACID compliant DB engine. It has a command-line tool, and it is possible to use C/C++ and Tcl for database access. Unlike the others, that are released under varied open source licenses, the code for SQLite is public domain.
• There are a few others (see list here,) but they are either research-focused (like Ingres,) developed very little, or have small user bases, and seem not relevant to nonprofit technology.

Nonprofit technology take home lesson: MySQL is certainly the leader – it’s most commonly thought of as the “M” in LAMP (Linux, Apache, MySQL, PHP/Perl/Python), which is a nptech web mainstay. I’d argue that PostgreSQL is a better choice, but for most nptech applications, it doesn’t matter – what matters is what your tech/consultant knows, and that’s much more likely to be MySQL. The others are most likely of interest to pretty small niche groups, for specific kinds of projects.

Technorati Tags: databases, opensource