The database

Nodes and users are handled through entities. But for your own simple data — subscribers, ratings, a log — a custom table and direct queries are more convenient. Drupal offers a query builder for that: it escapes values itself and works the same with MySQL, PostgreSQL and SQLite.

A custom table

The structure of module tables is described by the blog_schema() function in the blog.install file in the root of the module folder. Create it by hand:

// web/modules/custom/blog/blog.install
 
/**
 * Implements hook_schema().
 */
function blog_schema(): array
{
    return [
        'blog_subscribers' => [
            'description' => 'Blog subscribers.',
            'fields' => [
                'id' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
                'email' => ['type' => 'varchar', 'length' => 255, 'not null' => TRUE],
                'created' => ['type' => 'int', 'not null' => TRUE, 'default' => 0],
            ],
            'primary key' => ['id'],
            'unique keys' => ['email' => ['email']],
        ],
    ];
}

In the real file the first line is <?php. serial is an auto-incrementing number, varchar a string, int an integer. The unique keys entry forbids two identical addresses.

Tables are created at the moment the module is enabled. The module is already enabled, so enable it again:

vendor/bin/drush pmu blog -y
vendor/bin/drush en blog -y

The blog_subscribers table appears in the database. How to change the structure without reinstalling is covered in the lesson about updates.

Inserting

The database connection is the database service. Pass it into the subscription form and save the address:

// web/modules/custom/blog/src/Form/SubscribeForm.php
use Drupal\Core\Database\Connection;
use Symfony\Component\DependencyInjection\ContainerInterface;
 
public function __construct(private Connection $database) {}
 
public static function create(ContainerInterface $container): static
{
    return new static($container->get('database'));
}
 
public function submitForm(array &$form, FormStateInterface $form_state): void
{
    $this->database->insert('blog_subscribers')
        ->fields([
            'email' => $form_state->getValue('email'),
            'created' => time(),
        ])
        ->execute();
 
    $this->messenger()->addStatus($this->t('You are subscribed.'));
}

After drush cr submit the /blog/subscribe form — a row appears in the table. fields() takes an array of column and value pairs, execute() runs the query.

Selecting

// web/modules/custom/blog/src/Controller/BlogController.php
$rows = \Drupal::database()->select('blog_subscribers', 's')
    ->fields('s', ['email', 'created'])
    ->orderBy('created', 'DESC')
    ->range(0, 20)
    ->execute()
    ->fetchAll();
 
foreach ($rows as $row) {
    $emails[] = $row->email;
}

The second argument of select() is a short name for the table inside the query. fetchAll() returns an array of objects whose properties are the columns. In classes the connection is passed through the constructor, as in the form above; \Drupal::database() is written here for brevity.

Conditions

// web/modules/custom/blog/src/Form/SubscribeForm.php
$exists = $this->database->select('blog_subscribers', 's')
    ->condition('s.email', $email)
    ->countQuery()
    ->execute()
    ->fetchField();

condition() works as in entity queries: a column, a value, an optional operator. countQuery() turns the query into a row count. Such a check goes into validateForm() to tell the visitor that the address is already subscribed.

Updating and deleting

// in any class with a database connection
$this->database->update('blog_subscribers')
    ->fields(['email' => 'anna@example.org'])
    ->condition('id', 5)
    ->execute();
 
$this->database->delete('blog_subscribers')
    ->condition('created', strtotime('-2 years'), '<')
    ->execute();

The first query changes the address in the row with number 5, the second deletes subscriptions older than two years. Both return the number of affected rows.

Common mistake

Do not read or change the tables of nodes and users with direct queries — node_field_data, users_field_data and the like. Writing around the Entity API does not call hooks, does not clear the cache and ignores access and translations: the site keeps showing old data. Direct queries are only for the module's own tables.