SQL and Database Standards
Reaching directly for the database is almost never the right move in Drupal — the Database API and the Entity Query API exist specifically so that code doesn't have to, and using them consistently is what keeps queries safe, portable across database engines, and readable.
1. Use the Database API, Not Raw SQL
Raw SQL strings built by hand bypass Drupal's query builder and its protections along with it. The Database API's select(), insert(), update(), and delete() methods cover the overwhelming majority of what a module needs, and produce queries that work the same way across MySQL, PostgreSQL, and SQLite.
// ❌ Incorrect: a hand-built SQL string.
$result = db_query("SELECT * FROM users WHERE name = '$name'");
// ✅ Correct: the query builder, with a placeholder.
$result = \Drupal::database()->select('users_field_data', 'u')
->fields('u')
->condition('name', $name)
->execute();2. Placeholders Prevent SQL Injection
User input is never concatenated directly into a query string — it's passed as a placeholder value instead, and the Database API takes care of escaping it correctly for whichever database engine is running underneath.
// ❌ Incorrect: user input concatenated directly into the SQL.
$query = "SELECT * FROM users WHERE name = '$name'";
$result = db_query($query);
// ✅ Correct: a placeholder, escaping left to the Database API.
$result = \Drupal::database()->query('SELECT * FROM {users_field_data} WHERE name = :name', [':name' => $name]);3. The Entity Query API
When the data being queried is content entities rather than raw tables, the Entity Query API is preferred over both raw SQL and the Database API — it stays valid across storage backends, and it automatically respects access checks that a hand-written query would silently skip.
// ❌ Incorrect: a raw query against the entity's base table.
$result = \Drupal::database()->select('node_field_data', 'n')
->fields('n', ['nid'])
->condition('status', 1)
->execute();
// ✅ Correct: the Entity Query API.
$nids = \Drupal::entityQuery('node')
->condition('status', 1)
->accessCheck(TRUE)
->execute();4. Table Prefixing with Curly Braces
A table name inside a raw query string is wrapped in curly braces, {table_name}, so Drupal can apply any table prefix configured for the site. Leaving the braces off works fine on a site with no prefix and breaks silently on one that has it configured.
// ✅ Correct: curly braces let Drupal apply the site's table prefix.
$result = \Drupal::database()->query('SELECT nid FROM {node_field_data} WHERE status = :status', [':status' => 1]);5. Defining a Schema
A module that creates its own database table defines its structure in hook_schema(), describing every field, its type, and its keys and indexes — this is what drush updatedb and the installer both read to actually create the table.
/**
* Implements hook_schema().
*/
function product_import_schema(): array {
$schema['product_import_log'] = [
'fields' => [
'id' => [
'type' => 'serial',
'not null' => TRUE,
],
'product_id' => [
'type' => 'int',
'not null' => TRUE,
],
],
'primary key' => ['id'],
];
return $schema;
}6. Transactions
A set of writes that need to succeed or fail together are wrapped in a transaction. Drupal's transactions are scope-based — the transaction commits when the variable holding it goes out of scope, and rolls back automatically if an exception is thrown before that point.
$transaction = \Drupal::database()->startTransaction();
try {
// Multiple related writes here.
}
catch (\Exception $e) {
$transaction->rollBack();
throw $e;
}