Faq: Why is LedgerSMB PostgreSQL-only?

We get asked a lot, why is LedgerSMB Postgresql-only?  Why not run on MySQL?  After all, since 5.0, MySQL has offered relatively decent type constraints, and offers a database that works sufficient to build ACID-compliant applications, and so forth.  After all, this line of reasoning goes, we'd get more market share by being able to run on a larger number of hosting providers.

This post discusses why we made the decision to go with Postgres and decline to support any other databases.  I still believe it has been the correct decision for us but it might not be the right decision for other projects.  I hope though that writing this up will help other projects weigh their options and choose appropriately.

The short version is that we decided to favor an application with many avenues for integration over the idea of an application that would run on any platform.  We chose the path of moving towards giving our users as rich an experience as possible, and as rich opportunities as possible in integrating this software with their business over the idea of having as many customers as possible.  If the software works wonders, people will make room for it.

Our Goal

In our case, our goal has generally been to be a major part of the business infrastructure of our users.  We want to create a flexible framework which supports extension and repurposing, allowing businesses to find new uses for what we are doing.

Additionally early on our decision was shaped by the desire to hook LedgerSMB into reporting tools written in other languages, and we generalized this to tools generally.  This has been a relatively safe bet, and with additional compatibility classes auto-detection of DB-level API's is possible.

Finally we started as a fork of a PostgreSQL-only application and so the choice was whether to deepen our commitment to PostgreSQL only or whether to move towards portability.

Considerations

Our overall approach  was based on the idea that financial and ERP systems are best when they are open to integration, and that ERP frameworks need to be open to extension as well.  After all, ERP software typically runs a wide variety of functions in a business, and these are often connected to yet more workflows where custom apps may be developed.  If those apps can be developed against LedgerSMB this delivers greater value to the customer.

In other words, we see ERP as much about development platform and tools as it is about application functionality and flexibility in adding features is key.

Our Decision

Our decision was to solidify our usage of PostgreSQL, use it to add additional security and tune performance.  We quickly made the decision to move much of the logic into stored procedures in order to support tools written in other languages as well.

One important benefit which helped push us this way was the fact that a stored procedure interface could be centrally controlled and checked against SQL injection while ad hoc query interfaces required careful auditing.

Our Approach and Its Challenges

Our current approach is to name arguments such that they can be mapped in. This works reasonably well with a single application, it is simple and requires relatively straight-forward queries.  However it is currently limited in two ways:  namespaces, and the fact that the interface is very heavily procedural and this makes maintenance a bit of a pain.  It also means that class attributes must be manually maintained across integration classes, which is sub-optimal.

We are currently exploring the possibility of moving to a more object-oriented interface in the db which would solve the above issues.  The interface would be dynamically discovered by querying system catalogs or views we would create, and results cached by the application.  This would allow dynamic API discovery and classes to export the db-level API to other systems in a purely db-centric way.  This would hence be expected to cut our maintenance effort significantly.

A major challenge here however has to do with quality of stored procedures.  The current quality of stored procedures varies quite a bit.   In general, one of the problems one faces is that application developers don't always make good stored procedure developers because the sorts of thinking are rather different.  However, over time we expect to be able to fix this issue.

Another key part of our strategy is that of layered API's.  Not only are we building discoverable db-level API's but also RESTful web ser

Final Words

LedgerSMB chose a db-centric model because we want to support a wide range of applications written in a wide range of languages, running in a wide range of environments.  This is very specific to our approach, and it is not without challenges.   However it has already had some benefits in our PHP compatibility classes and these are expected to multiply in the future.  In the end we choose a db for many apps over an app for many db's.