Basic SQL Queries (Non-Entity Queries) Using the Drupal Database API

Suppose you are working on a Drupal project that accesses custom tables from a secondary non-Drupal database. 

Often, you will use Drupal's Entity API with your custom table.  That means custom entity development so the secondary database can fully integrate with Drupal (many Drupal APIs, e.g. Views, are designed to expect Drupal content entities, and work best with full Drupal entities).  But not always.

In Drupal, sometimes we want to query a non-Drupal custom table that may not have been implemented as a custom entity.  Or, sometimes we just want to take a step back from the Entity API entirely, possibly to reduce overhead.  

Or you might be querying data that is not really a part of your Drupal content domain and has no real need to use Drupal APIs.  

Let us suppose you have read-only database like that a list of U.S. states that is expected to never change, and for whatever reason, the data isn't planned to be ever be managed by Drupal.  A small table of 58 records, maybe a list of U.S. States and territories, for example. Custom "state" entity would be overengineering, since you state data is not complex; a state doesn't sound like an important "thing" that should expose itself with different states, properties and behaviors. (But it could need to... in a government system for example) but usually we are not interested in U.S. states except as a person's location, postal address component, or that kind of use case.

So back to Drupal, we would most likely not need an array of state entity objects; an array of states would be fine.



So here is an example straight Drupal 11 database query using the Database API, for any cases when entity queries are not desired, or might not be an option:

e.g. Using a unique cust_id as a criteria:

$result = \Drupal::database()->select('my_custom_table', 'o')
  ->fields('o', ['customer_id', 'email'])
  ->condition('customer_id', $cust_id)
  ->execute()
  ->fetchField();

Breakdown:

• \Drupal::database() — retrieves the database connection service.
• ->select('my_custom_table', 'o') — targets your custom table with the alias o.
• ->fields('o', ['customer_id','email']) — selects only the customer_id and email columns.
• ->condition('customer_id', $cust_id) — applies the WHERE clause, replacing the ? placeholder with $cust_id.
• ->execute()->fetchField() — executes and returns a single scalar value (the cust_id string), since you're selecting one column from what is likely one row.

Fetching alternatives depending on your use case:

If you expect multiple matching rows, swap ->fetchField() for ->fetchCol() to get all values back as an array.