Database abstraction layer
|This article needs additional citations for verification. (September 2014)|
A database abstraction layer is an application programming interface which unifies the communication between a computer application and databases such as SQL Server, DB2, MySQL, PostgreSQL, Oracle or SQLite. Traditionally, all database vendors provide their own interface tailored to their products, which leaves it to the application programmer to implement code for all database interfaces he or she would like to support. Database abstraction layers reduce the amount of work by providing a consistent API to the developer and hide the database specifics behind this interface as much as possible. There exist many abstraction layers with different interfaces in numerous programming languages. If an application has such a layer built in, it is called database-agnostic.
- 1 Database levels of abstraction
- 2 Database abstraction in the API
- 3 Language-level abstraction
- 4 Arguments for
- 5 Arguments against
- 6 References
Database levels of abstraction
Physical level (lowest level)
It defines how data is stored. It is very complex and used by developers.
Conceptual or logical level (middle or next highest level)
It defines data in terms of a data model. It tells what data is stored and described in small numbers. This level is usually used by DBA (Database administrator)
External or view level
It defines a number of simplified domain-specific views. It describes only part of databases. This level is used by users. 
Database abstraction in the API
Libraries unify access to databases by providing a single low-level programming interface to the application developer. Their advantages are most often speed and flexibility because they are not tied to a specific query language (subset) and only have to implement a thin layer to reach their goal. As all SQL dialects are similar to one another, application developers can use all the language features, possibly providing configurable elements for a database-specific cases, such as, typically, user-IDs and credentials. A thin-layer allows the same queries and statements to run on a variety of database products with a negligible overhead.
Popular use for database abstraction layers are among object-oriented programming languages, which are similar to API-level abstraction layers. In an object-oriented language like C++ or Java, a database can be represented through an object, whose methods and members (or the equivalent thereof in other programming languages) represent various functionalities of the database. They also share advantages and disadvantages with API-level interfaces.
An example of a database abstraction layer on the language level would be ODBC. ODBC is a platform-independent implementation of a database abstraction layer. The user installs specific driver-software, through which ODBC can communicate with a database or set of databases. The user then has the ability to have programs communicate with ODBC, which then relays the results back and forth between the user programs and the database. The downside of this abstraction level is the increased overhead to transform statements into constructs understood by the target database.
Software developers only have to know the database abstraction layer's API instead of all APIs of the databases his application should support. The more databases should be supported the bigger is the time saving.
Wider potential install-base
Using a database abstraction layer means that there is no requirement for new installations to utilise a specific database, i.e. new users who are unwilling or unable to switch databases can deploy on their existing infrastructure.
As new database technologies emerge, software developers won't have to adapt to new interfaces.
A production database may be replaced with a desktop-level implementation of the data for developer-level unit tests.
Any abstraction layer will reduce the overall speed more or less depending on the amount of additional code that have to be executed. The more a database layer abstracts from the native database interface and tries to emulate features not present on all database backends, the slower the overall performance. This is especially true for database abstraction layers that try to unify the query language as well like ODBC.
A database abstraction layer provides yet another functional dependency for a software system, i.e. a given database abstraction layer, like anything else, may eventually become obsolete, outmoded or unsupported.
Database abstraction layers likely limit the number of available database operations to a subset of those supported by the supported database backends. In particular, database abstraction layers may not fully support database backend-specific optimizations or debugging features. These problems magnify significantly with database size, scale, and complexity.