The new drupal database API (known as DB:TNG) is built atop PHP's native PDO engine. The main objective behind Drupal’s database API is to allow developers to write one query that will work across different types of databases. So instead of writing specific queries for MSSQL, PostgreSQL, MongoDB, you can write one query. Then, a plugin is written to translate that query into native style and write the query in the initial language which is Database Abstraction layer.
What are the new changes in Drupal 7’s new database API?
One of the biggest changes is Drupal 7's new database API, an object-oriented system which change the way most developers will build database queries in Drupal. Every developer is familiar with basic sql query, Where we are used to writing a query in usual fashion.
E.g: select field1, field2 from table_name where value
In Drupal 7 we do the same thing using database API, here, a developer has to create a query as an object, fields, condition, order, range and other element should be added by calling methods. Thereafter, we need to call the execute() method.
Below query is about drupal db select ‘student‘ of fields matched condition, order by student’s dob in descending order & limit would be first 10 result. And executing them by calling execute() method
$query = db_select('student', 's')
->fields('s', array('id', 'name', 'DOB'))
->condition('s.uid', $uid)
->orderBy('s.dob', 'DESC')
->range(0, 10)
->execute();
As per the Drupal Community “Drupal database API provides certain standards, where we should not make any database call directly until and unless we are in CORE APIs development.”
Database API is designed in such a way that any newbies can also construct the drupal database query without any extra effort. Hence, it preserves the syntax making it more powerful like never before.
Some of points that I would like to point out are,
- Support across multiple server. Like MySQL,MariaDB,Percona Server, PostgreSQL,SQLite,Microsoft SQL Server, Oracle, MongoDB
- Provides a structured interface for dynamic queries.
- Follow security guideline.
- Provides clean interface to module, really good when we want to differentiate between functional codebase and query.
How to construct a query in Drupal using Database API
- Enclosing table name in {} adds one level of security layer, where drupal need to select the right table for you.
- Conditions in the query is added by putting placeholder, this protects your system against sql injection.
- LIMIT syntax varies between databases if you have limit in your query then use drupal_query_range() instead of drupal_query()
While working with Database Abstraction layer we have two types of queries - Static and Dynamic queries. Static queries are similar to PHP native function, there are just minor changes.
Static queries support SELECT for faster retrieving and execution. On the other hand Dynamic queries have a lot of differences, it allows you to build a query step by step. Also, based on the different conditions you can manipulate your query.
So, let’s have a look at query. From Drupal 6 to Drupal 7 there's not much difference. It’s just a shift of those who are used to manually writing the queries.
Static SELECT queries
A simple SELECT queries that do not involve entities, the Drupal database abstraction layer provides the functions db_query() and db_query_range(), which execute SELECT queries (optionally with range limits) and return result sets that you can iterate over using foreach loops.
/* Drupal 6 query */
$sql = "SELECT n.nid, n.title, n.uid FROM {node} n WHERE n.type = 'article'";
$results = db_query_range($sql, 0, 3);
/* Drupal 7 query */
$query = db_select('node', 'n');
$query->condition('n.type', 'article', '=')
->fields('n', array('nid', 'title', 'uid'))
->range(0, 3);
$result = $query->execute();
Both of the queries are doing the same thing, i.e., retrieving the id, title,uid from the Node table that match certain criterias, then sorting the results and ensuring only first three matches to be returned.
In addition, the new object-oriented approach of running raw queries can be done in Drupal 7 with the db_query() function, and in mostly its more efficient. However, the advantage doesn't always help someone to write raw text queries. Some modules need to build a large number of similar queries.
Dynamic SELECT queries
For Dynamic SELECT queries where the simple query API described in Simple SELECT queries will not work well, you need to use the dynamic query API. However, you should still use the Entity Query API if your query involves entities or fields.
$result = db_select('example', 'e')
->fields('e', array('id', 'title', 'created'))
->condition('e.uid', $uid)
->orderBy('e.created', 'DESC')
->range(0, 10)
->execute();
db_select() and similar functions are wrappers on connection object methods. Mostly, in classes, you should use dependency injection and the database connection object instead of these wrappers.
What I have shared with you is just an introduction on Database API as well as Dynamic queries and Static queries. Below is the Drupal Database structure
for Drupal 7 db schema:
For Drupal 8 db schema:
To get more detailed information on Database API Abstraction layer, visit drupal.org official site.
