EnterpriseDB - an Oracle face on PostgreSQL

I have been playing with the EnterpriseDB database over the past few weeks. To be honest, I would not have known about this new database had it not been for my winning an Amazon.com gift certificate for filling out a survey on the Java Developer's Journal, which was sponsored by EnterpriseDB Corporation. I mean, I hear about open source databases being released once in a while, such as Firebird and Apache Derby, and I have even used a few of them, such as HSQLDB (formerly HypersonicDB). But as an application developer, I dont go out of my way to look at new databases. Most of the time, I stick with the tried and tested databases I already have installed on my computer - MySQL and PostgreSQL. So in a sense, I was paid to look at EnterpriseDB. There...you have been warned.

What kept me looking further, and indeed, writing about it here, were the following:

• It is based on PostgreSQL, a database which I use and like.
• It allows Oracle based applications to run with little or no modification using its Oracle compatibility layer.
• It has a gentle learning curve with Database Designer GUI.
• It is open source and free for use. Support is based on an subscription model, which is quite affordable even for small businesses.
• It is integrated into the JBoss application server stack.
• I believe it has the potential to become a major open source database contender in the near future, and as such, application developers would benefit from learning it.

Based on PostgreSQL

I mostly use either PostgreSQL or MySQL for my personal projects. I started using PostgreSQL because it came pre-installed with RedHat systems. I came to PostgreSQL from using Informix at work, so there was a steep learning curve. What I liked about PostgreSQL is its almost infinite extensibility. Like most other relational databases, you can create tables, views and stored functions in PostgreSQL. Unlike most other relational databases, however, PostgreSQL allows you to create user defined types and even new stored procedure languages.

Even apart from its extension points, PostgreSQL is a very feature rich database. PostgreSQL has been fully ACID (Atomicity, Consistency, Isolation and Durability) compliant for a long time. It supports a very rich subset of SQL, including subqueries, triggers and stored procedures.

So, in a way, EnterpriseDB could not have chosen a better database to base their product on.

Oracle compatibility

While the SQL spec specifies the data types that should be available, database vendors frequently support proprietary extensions, and Oracle is no exception. EnterpriseDB maps non-standard SQL data types to PostgreSQL using aliases. Oracle also offers the richest array of system functions among commercial databases, which have been faithfully replicated in EnterpriseDB using PostgreSQL stored functions. There are other little touches such as the system table DUAL, which was actually created by Oracle to mask a shortcoming in its SQL syntax, but which is needed for compatibility with Oracle SQL scripts.

EnterpriseDB provides a stored procedure language EDB-SPL which closely resembles Oracle's PL/SQL. PL/SQL, in my opinion, is the nicest stored procedure language available today. PostgreSQL already has a PL/SQL like stored procedure language called plpgsql, but it is easy to spot which is which. EDB-SPL is a more faithful PL/SQL clone, and stored procedures and functions written in EDB-SPL are virtually indistinguishable from PL/SQL code.

EnterpriseDB has a data dictionary view called "Redwood View" that resembles the Oracle data dictionary. So DBA scripts making use of the data dictionary can run unchanged.

PostgreSQL provides drivers for a variety of languages, including Java, Perl, Python, C#/.NET, C/C++, etc and all of them can be used for EnterpriseDB, since it is really PostgreSQL under the hood. For Oracle applications, EnterpriseDB provides its own JDBC driver, and Java or ODBC is the recommeded way of connecting from Oracle based apps to EnterpriseDB. This is fine with me, since I am planning to use the Oracle compatibility layer from Java.

The EDB-SPL language is very exciting news for me personally, since now I have an open source database that will allow me to write and test "Oracle" stored procedures to test code in the SQLUnit project, a Java testing framework for testing database stored procedures. What is more, I can use EnterpriseDB to power the applications that are currently using PostgreSQL, keeping the number of daemons on my machine the same.

Gentle Learning Curve

The last time I used Oracle was over 4 years ago. Since then I have used Sybase, MySQL and PostgreSQL. Since I have always worked with Oracle in large corporations, where DBAs maintain the database and create the tables, I have never had to learn about the administration aspects of Oracle, including setting up schemas and such. So I was expecting to do a lot of reading to be able to start working with EnterpriseDB. The GUI Database Designer tool was a big time saver, allowing me to create and set up a database quickly without having to do any reading at all.

It may still be worthwhile to understand Oracle administration basics in order to fully harness the power of EnterpriseDB, though.

Open Source and Free to Use

Most commercial databases have free versions, but the biggest reason enterprising people would want to use them is for learning it if you were going to start using them at work. The other reason would be that you are too lazy to learn a new database on your own, so you use the free version of the same database you use at work. Open source people would probably not bother with the free versions, since it does not help them in their open source projects.

EnterpriseDB caters to all the above groups. Enterprising people may install it to learn Oracle or PostgreSQL, and may end up learning one or both, depending on interest. Lazy people may install it as an free alternative to Oracle at work, and may end up learning PostgreSQL. Open source people who would normally install PostgreSQL may also end up installing it and learning Oracle. Its a net win for all three parties - more people who know PostgreSQL, Oracle and EnterpriseDB.

There is a large pool of dedicated PostgreSQL developers (some of them working for EnterpriseDB, by the way), who would be able to benefit from the open source nature of EnterpriseDB, and be able to suggest improvements and bug fixes. Not me, though...

Integration with JBoss stack

I recently attended an webinar where I learned that EnterpriseDB has some sort of deal with JBoss (now part of RedHat) to include it as part of its default application stack. This is great news. Having EnterpriseDB be part of the default stack of the most popular open source Java application server will definitely drive up adoption. It helps that it looks like Oracle, since Oracle is the most popular database around, so there are more people who would be able to quickly pick it up.

Conclusion

EnterpriseDB is a solid database. It is based on PostgreSQL which has been around a long time and is very stable. On top of PostgreSQL is the Oracle compatibility layer which makes the database looks like Oracle. The EnterpriseDB folks have spent a great deal of effort in making it easy for the end-user to start using it with minimum effort and training, and it shows. Its inclusion as part of the JBoss stack has enhanced its own value as well as that of JBoss. I am personally very impressed with what the EnterpriseDB folks have done. If you haven't looked at EnterpriseDB already, do take a look, I am sure you will feel the same way too.