
Ask HN: Do you make your apps database agnostic? - majkinetor
I used all mainstream relational databases so far. While they all follow SQL standards <i>to some level</i> there are vendor differences and extensions on many features.<p>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.<p>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.<p>What are your thoughts on it ?
======
axisofpleasure
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.

