HACKER Q&A
📣 majkinetor

Do you make your apps database agnostic?


I used all mainstream relational databases so far. While they all follow SQL standards to some level there are vendor differences and extensions on many features.

It seems to me that need for database agnosticism makes sense for specific types of apps, mostly those to be used in general context and by random stakeholders. However, this is typically not the case for majority of projects which are tailored to specific customers and imposing agnosticism on those may very well lead to inelegant and unoptimized code, not to mention constant mental overhead in trying to achieve SQL standard and extra development if support for multiple vendors is desired.

It looks to me that premature agnosticism is similar to premature optimization - there is no need to do it unless your app really fit a specific niche. DB frameworks may make this easier to some extent, but this is also not universal solution since most of those are several times slower then direct database access and thus may not be acceptable, not to mention the overhead of learning to use and troubleshoot it.

What are your thoughts on it ?

  👤 axisofpleasure Accepted Answer ✓
As a 25 year career DBA, moving to a DB agnostic methodology has merits as it allows very re-usable code, however the downside is that you never get to realise the strengths a particular DB technology can offer in terms of performance. Wriing in ORMS and high-level frameworks may be good for making your product very adaptable but don't expect your product to be lightning fast when it's performance pounded and tested. I've worked on performance testing DB techs and seen what happens when a vendor tries make a "one-size fits all" solution, it quickly loses scalability. If raw performance is your goal, don't even try to be agnostic, tweak every last advantage you need. When you have a trading app that's making thousands of upserts a minute you need every bit of help you can get in your app. If simply allowing data storage is all you need, then agnostic may be right for you. However to throw a curve ball in, buying raw processing power is dirt cheap these days, and you'll get something by throwing more CPU and memory at a problem, but your scalability will tail of at somepoint and you have to decide if that platau point is acceptable.
👤 davismwfl
Unless there is a very specific and valid reason I never try to make applications database agnostic, but my designs usually are to some degree. I do like to avoid features when I know they are non-standard and other options/designs exist which are more standard. In the past on projects that has meant we wrote a bit more code a few times but it has paid off every time. At least what I have found generally is features which have no common analog in other systems are originally added for an Enterprise client and so the stability, integration and support are much less friendly to work with, so avoiding them is just smart IMO. Of course, once those features hit mainstream and other systems mimic them then obviously things change.

But in most cases we used "special" features where they just made life easier, better or both, a good example of this is back when SQLServer released CDC, we used it heavily for a system we built. Yet, not all competitive DB systems had a direct analog to it at the time. More common examples are some of the stored procedure functions available in SQLServer that weren't in other systems that we used regularly. Any of those could have made a DB porting job a pain in the ass.

Overall though, almost every system I have ever had input on or control over architecture we have hidden the database behind either an API or data layer of some sort that made the application ignorant to how data was stored, accessed and where it was located. The idea behind this isn't that I think switching databases is a normal thing, but more so we could change data location, structure etc all without affecting application design. I've been doing that since the early 90's, it was something I was just ingrained with because when I started writing code all our data was stored in flat files and so our code was the database, so isolation was critical to not affect consumers.