INSERT, UPDATE & DELETE in Drupal: Writing Data with DBTNGfor Drupal 11 , and 10

In the last lesson you saw how to read rows out of a custom database table. Reading is only half the job — real modules also need to write data: saving a new record, editing an existing one, removing one that's no longer needed. This lesson covers all three operations, using the same repository service and two real Drupal forms that put it to work.

What you'll learn in this lesson

• How to insert a new row with $connection->insert(), and why it should always be wrapped in try/catch
• How to update an existing row safely, and why forgetting a condition() on an UPDATE is dangerous
• How to delete a row by its primary key
• How a real Drupal form collects user input and hands it to the repository to persist
• Two different ways to build a Drupal form class — implementing FormInterface directly, versus extending the more familiar FormBase

The source files

This lesson covers three files together: the same repository from the last lesson, plus the two forms that call its write methods.

• modules/dbtng_example/src/DbtngExampleRepository.php
• modules/dbtng_example/src/Form/DbtngExampleAddForm.php
• modules/dbtng_example/src/Form/DbtngExampleUpdateForm.php

DbtngExampleRepository.php

The full repository class again, in full — the same one from the last lesson, since insert(), update(), and delete() live right alongside the load() and advancedLoad() methods you already read.

<?php

namespace Drupal\dbtng_example;

use Drupal\Core\Database\Connection;
use Drupal\Core\Messenger\MessengerInterface;
use Drupal\Core\Messenger\MessengerTrait;
use Drupal\Core\StringTranslation\StringTranslationTrait;
use Drupal\Core\StringTranslation\TranslationInterface;

/**
 * Repository for database-related helper methods for our example.
 *
 * This repository is a service named 'dbtng_example.repository'. You can see
 * how the service is defined in dbtng_example/dbtng_example.services.yml.
 *
 * For projects where there are many specialized queries, it can be useful to
 * group them into 'repositories' of queries. We can also architect this
 * repository to be a service, so that it gathers the database connections it
 * needs. This way other classes which use the repository don't need to concern
 * themselves with database connections, only with business logic.
 *
 * This repository demonstrates basic CRUD behaviors, and also has an advanced
 * query which performs a join with the user table.
 *
 * @ingroup dbtng_example
 */
class DbtngExampleRepository {

  use MessengerTrait;
  use StringTranslationTrait;

  /**
   * The database connection.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * Construct a repository object.
   *
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection.
   * @param \Drupal\Core\StringTranslation\TranslationInterface $translation
   *   The translation service.
   * @param \Drupal\Core\Messenger\MessengerInterface $messenger
   *   The messenger service.
   */
  public function __construct(Connection $connection, TranslationInterface $translation, MessengerInterface $messenger) {
    $this->connection = $connection;
    $this->setStringTranslation($translation);
    $this->setMessenger($messenger);
  }

  /**
   * Save an entry in the database.
   *
   * Exception handling is shown in this example. It could be simplified
   * without the try/catch blocks, but since an insert will throw an exception
   * and terminate your application if the exception is not handled, it is best
   * to employ try/catch.
   *
   * @param array $entry
   *   An array containing all the fields of the database record.
   *
   * @return int
   *   The number of updated rows.
   *
   * @throws \Exception
   *   When the database insert fails.
   */
  public function insert(array $entry) {
    try {
      $return_value = $this->connection->insert('dbtng_example')
        ->fields($entry)
        ->execute();
    }
    catch (\Exception $e) {
      $this->messenger()->addMessage($this->t('Insert failed. Message = %message', [
        '%message' => $e->getMessage(),
      ]), 'error');
    }
    return $return_value ?? NULL;
  }

  /**
   * Update an entry in the database.
   *
   * @param array $entry
   *   An array containing all the fields of the item to be updated.
   *
   * @return int
   *   The number of updated rows.
   */
  public function update(array $entry) {
    try {
      // Connection->update()...->execute() returns the number of rows updated.
      $count = $this->connection->update('dbtng_example')
        ->fields($entry)
        ->condition('pid', $entry['pid'])
        ->execute();
    }
    catch (\Exception $e) {
      $this->messenger()->addMessage($this->t('Update failed. Message = %message, query= %query', [
        '%message' => $e->getMessage(),
        '%query' => $e->query_string,
      ]
      ), 'error');
    }
    return $count ?? 0;
  }

  /**
   * Delete an entry from the database.
   *
   * @param array $entry
   *   An array containing at least the person identifier 'pid' element of the
   *   entry to delete.
   *
   * @see Drupal\Core\Database\Connection::delete()
   */
  public function delete(array $entry) {
    $this->connection->delete('dbtng_example')
      ->condition('pid', $entry['pid'])
      ->execute();
  }

  /**
   * Read from the database using a filter array.
   *
   * The standard function to perform reads for static queries is
   * Connection::query().
   *
   * Connection::query() uses an SQL query with placeholders and arguments as
   * parameters.
   *
   * Drupal DBTNG provides an abstracted interface that will work with a wide
   * variety of database engines.
   *
   * The following is a query which uses a string literal SQL query. The
   * placeholders will be substituted with the values in the array. Placeholders
   * are marked with a colon ':'. Table names are marked with braces, so that
   * Drupal's' multisite feature can add prefixes as needed.
   *
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
   *   \Drupal::database()->query(
   *     "SELECT * FROM {dbtng_example} WHERE uid = :uid and name = :name",
   *     [':uid' => 0, ':name' => 'John']
   *   )->execute();
   * @endcode
   *
   * For more dynamic queries, Drupal provides Connection::select() API method,
   * so there are several ways to perform the same SQL query. See the
   * @link http://drupal.org/node/310075 handbook page on dynamic queries. @endlink
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
   *   \Drupal::database()->select('dbtng_example')
   *     ->fields('dbtng_example')
   *     ->condition('uid', 0)
   *     ->condition('name', 'John')
   *     ->execute();
   * @endcode
   *
   * Here is select() with named placeholders:
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
   *   $arguments = array(':name' => 'John', ':uid' => 0);
   *   \Drupal::database()->select('dbtng_example')
   *     ->fields('dbtng_example')
   *     ->where('uid = :uid AND name = :name', $arguments)
   *     ->execute();
   * @endcode
   *
   * Conditions are stacked and evaluated as AND and OR depending on the type of
   * query. For more information, read the conditional queries handbook page at:
   * http://drupal.org/node/310086
   *
   * The condition argument is an 'equal' evaluation by default, but this can be
   * altered:
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE age > 18
   *   \Drupal::database()->select('dbtng_example')
   *     ->fields('dbtng_example')
   *     ->condition('age', 18, '>')
   *     ->execute();
   * @endcode
   *
   * @param array $entry
   *   An array containing all the fields used to search the entries in the
   *   table.
   *
   * @return object
   *   An object containing the loaded entries if found.
   *
   * @see Drupal\Core\Database\Connection::select()
   */
  public function load(array $entry = []) {
    // Read all the fields from the dbtng_example table.
    $select = $this->connection
      ->select('dbtng_example')
      // Add all the fields into our select query.
      ->fields('dbtng_example');

    // Add each field and value as a condition to this query.
    foreach ($entry as $field => $value) {
      $select->condition($field, $value);
    }
    // Return the result in object format.
    return $select->execute()->fetchAll();
  }

  /**
   * Load dbtng_example records joined with user records.
   *
   * DBTNG also helps processing queries that return several rows, providing the
   * found objects in the same query execution call.
   *
   * This function queries the database using a JOIN between users table and the
   * example entries, to provide the username that created the entry, and
   * creates a table with the results, processing each row.
   *
   * SELECT
   *  e.pid as pid, e.name as name, e.surname as surname, e.age as age
   *  u.name as username
   * FROM
   *  {dbtng_example} e
   * JOIN
   *  users u ON e.uid = u.uid
   * WHERE
   *  e.name = 'John' AND e.age > 18
   *
   * @see Drupal\Core\Database\Connection::select()
   * @see http://drupal.org/node/310075
   */
  public function advancedLoad() {
    // Get a select query for our dbtng_example table. We supply an alias of e
    // (for 'example').
    $select = $this->connection->select('dbtng_example', 'e');
    // Join the users table, so we can get the entry creator's username.
    $select->join('users_field_data', 'u', 'e.uid = u.uid');
    // Select these specific fields for the output.
    $select->addField('e', 'pid');
    $select->addField('u', 'name', 'username');
    $select->addField('e', 'name');
    $select->addField('e', 'surname');
    $select->addField('e', 'age');
    // Filter only persons named "John".
    $select->condition('e.name', 'John');
    // Filter only persons older than 18 years.
    $select->condition('e.age', 18, '>');
    // Make sure we only get items 0-49, for scalability reasons.
    $select->range(0, 50);

    $entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);

    return $entries;
  }

}

DbtngExampleAddForm.php

<?php

namespace Drupal\dbtng_example\Form;

use Drupal\Core\DependencyInjection\ContainerInjectionInterface;
use Drupal\Core\Form\FormInterface;
use Drupal\Core\Form\FormStateInterface;
use Drupal\Core\Messenger\MessengerTrait;
use Drupal\Core\Session\AccountProxyInterface;
use Drupal\Core\StringTranslation\StringTranslationTrait;
use Drupal\dbtng_example\DbtngExampleRepository;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Form to add a database entry, with all the interesting fields.
 *
 * @ingroup dbtng_example
 */
class DbtngExampleAddForm implements FormInterface, ContainerInjectionInterface {

  use StringTranslationTrait;
  use MessengerTrait;

  /**
   * Our database repository service.
   *
   * @var \Drupal\dbtng_example\DbtngExampleRepository
   */
  protected $repository;

  /**
   * The current user.
   *
   * We'll need this service in order to check if the user is logged in.
   *
   * @var \Drupal\Core\Session\AccountProxyInterface
   */
  protected $currentUser;

  /**
   * {@inheritdoc}
   *
   * We'll use the ContainerInjectionInterface pattern here to inject the
   * current user and also get the string_translation service.
   */
  public static function create(ContainerInterface $container) {
    $form = new static(
      $container->get('dbtng_example.repository'),
      $container->get('current_user')
    );
    // The StringTranslationTrait trait manages the string translation service
    // for us. We can inject the service here.
    $form->setStringTranslation($container->get('string_translation'));
    $form->setMessenger($container->get('messenger'));
    return $form;
  }

  /**
   * Construct the new form object.
   */
  public function __construct(DbtngExampleRepository $repository, AccountProxyInterface $current_user) {
    $this->repository = $repository;
    $this->currentUser = $current_user;
  }

  /**
   * {@inheritdoc}
   */
  public function getFormId() {
    return 'dbtng_add_form';
  }

  /**
   * {@inheritdoc}
   */
  public function buildForm(array $form, FormStateInterface $form_state) {
    $form = [];

    $form['message'] = [
      '#markup' => $this->t('Add an entry to the dbtng_example table.'),
    ];

    $form['add'] = [
      '#type' => 'fieldset',
      '#title' => $this->t('Add a person entry'),
    ];
    $form['add']['name'] = [
      '#type' => 'textfield',
      '#title' => $this->t('Name'),
      '#size' => 15,
    ];
    $form['add']['surname'] = [
      '#type' => 'textfield',
      '#title' => $this->t('Surname'),
      '#size' => 15,
    ];
    $form['add']['age'] = [
      '#type' => 'textfield',
      '#title' => $this->t('Age'),
      '#size' => 5,
      '#description' => $this->t("Values greater than 127 will cause an exception. Try it - it's a great example why exception handling is needed with DTBNG."),
    ];
    $form['add']['submit'] = [
      '#type' => 'submit',
      '#value' => $this->t('Add'),
    ];

    return $form;
  }

  /**
   * {@inheritdoc}
   */
  public function validateForm(array &$form, FormStateInterface $form_state) {
    // Verify that the user is logged-in.
    if ($this->currentUser->isAnonymous()) {
      $form_state->setError($form['add'], $this->t('You must be logged in to add values to the database.'));
    }
    // Confirm that age is numeric.
    if (!intval($form_state->getValue('age'))) {
      $form_state->setErrorByName('age', $this->t('Age needs to be a number'));
    }
  }

  /**
   * {@inheritdoc}
   */
  public function submitForm(array &$form, FormStateInterface $form_state) {
    // Gather the current user so the new record has ownership.
    $account = $this->currentUser;
    // Save the submitted entry.
    $entry = [
      'name' => $form_state->getValue('name'),
      'surname' => $form_state->getValue('surname'),
      'age' => $form_state->getValue('age'),
      'uid' => $account->id(),
    ];
    $return = $this->repository->insert($entry);
    if ($return) {
      $this->messenger()->addMessage($this->t('Created entry @entry', ['@entry' => print_r($entry, TRUE)]));
    }
  }

}

DbtngExampleUpdateForm.php

<?php

namespace Drupal\dbtng_example\Form;

use Drupal\Core\Form\FormBase;
use Drupal\Core\Form\FormStateInterface;
use Drupal\dbtng_example\DbtngExampleRepository;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Sample UI to update a record.
 *
 * @ingroup dbtng_example
 */
class DbtngExampleUpdateForm extends FormBase {

  /**
   * Our database repository service.
   *
   * @var \Drupal\dbtng_example\DbtngExampleRepository
   */
  protected $repository;

  /**
   * {@inheritdoc}
   */
  public function getFormId() {
    return 'dbtng_update_form';
  }

  /**
   * {@inheritdoc}
   */
  public static function create(ContainerInterface $container) {
    $form = new static($container->get('dbtng_example.repository'));
    $form->setStringTranslation($container->get('string_translation'));
    $form->setMessenger($container->get('messenger'));
    return $form;
  }

  /**
   * Construct the new form object.
   */
  public function __construct(DbtngExampleRepository $repository) {
    $this->repository = $repository;
  }

  /**
   * Sample UI to update a record.
   */
  public function buildForm(array $form, FormStateInterface $form_state) {
    // Wrap the form in a div.
    $form = [
      '#prefix' => '<div id="update-form">',
      '#suffix' => '</div>',
    ];
    // Add some explanatory text to the form.
    $form['message'] = [
      '#markup' => $this->t('Demonstrates a database update operation.'),
    ];
    // Query for items to display.
    $entries = $this->repository->load();
    // Tell the user if there is nothing to display.
    if (empty($entries)) {
      $form['no_values'] = [
        '#value' => $this->t('No entries exist in the table dbtng_example table.'),
      ];
      return $form;
    }

    $keyed_entries = [];
    $options = [];
    foreach ($entries as $entry) {
      $options[$entry->pid] = $this->t('@pid: @name @surname (@age)', [
        '@pid' => $entry->pid,
        '@name' => $entry->name,
        '@surname' => $entry->surname,
        '@age' => $entry->age,
      ]);
      $keyed_entries[$entry->pid] = $entry;
    }

    // Grab the pid.
    $pid = $form_state->getValue('pid');
    // Use the pid to set the default entry for updating.
    $default_entry = !empty($pid) ? $keyed_entries[$pid] : $entries[0];

    // Save the entries into the $form_state. We do this so the AJAX callback
    // doesn't need to repeat the query.
    $form_state->setValue('entries', $keyed_entries);

    $form['pid'] = [
      '#type' => 'select',
      '#options' => $options,
      '#title' => $this->t('Choose entry to update'),
      '#default_value' => $default_entry->pid,
      '#ajax' => [
        'wrapper' => 'update-form',
        'callback' => [$this, 'updateCallback'],
      ],
    ];

    $form['name'] = [
      '#type' => 'textfield',
      '#title' => $this->t('Updated first name'),
      '#size' => 15,
      '#default_value' => $default_entry->name,
    ];

    $form['surname'] = [
      '#type' => 'textfield',
      '#title' => $this->t('Updated last name'),
      '#size' => 15,
      '#default_value' => $default_entry->surname,
    ];
    $form['age'] = [
      '#type' => 'textfield',
      '#title' => $this->t('Updated age'),
      '#size' => 4,
      '#default_value' => $default_entry->age,
      '#description' => $this->t('Values greater than 127 will cause an exception'),
    ];

    $form['submit'] = [
      '#type' => 'submit',
      '#value' => $this->t('Update'),
    ];
    return $form;
  }

  /**
   * AJAX callback handler for the pid select.
   *
   * When the pid changes, populates the defaults from the database in the form.
   */
  public function updateCallback(array $form, FormStateInterface $form_state) {
    // Gather the DB results from $form_state.
    $entries = $form_state->getValue('entries');
    // Use the specific entry for this $form_state.
    $entry = $entries[$form_state->getValue('pid')];
    // Setting the #value of items is the only way I was able to figure out
    // to get replaced defaults on these items. #default_value will not do it
    // and shouldn't.
    foreach (['name', 'surname', 'age'] as $item) {
      $form[$item]['#value'] = $entry->$item;
    }
    return $form;
  }

  /**
   * {@inheritdoc}
   */
  public function validateForm(array &$form, FormStateInterface $form_state) {
    // Confirm that age is numeric.
    if (!intval($form_state->getValue('age'))) {
      $form_state->setErrorByName('age', $this->t('Age needs to be a number'));
    }
  }

  /**
   * {@inheritdoc}
   */
  public function submitForm(array &$form, FormStateInterface $form_state) {
    // Gather the current user so the new record has ownership.
    $account = $this->currentUser();
    // Save the submitted entry.
    $entry = [
      'pid' => $form_state->getValue('pid'),
      'name' => $form_state->getValue('name'),
      'surname' => $form_state->getValue('surname'),
      'age' => $form_state->getValue('age'),
      'uid' => $account->id(),
    ];
    $count = $this->repository->update($entry);
    $this->messenger()->addMessage($this->t('Updated entry @entry (@count row updated)', [
      '@count' => $count,
      '@entry' => print_r($entry, TRUE),
    ]));
  }

}

How it works

insert() — writing a new row, and why the try/catch matters

$return_value = $this->connection->insert('dbtng_example')
  ->fields($entry)
  ->execute();

Connection::insert() opens a fluent INSERT builder. ->fields($entry) takes your whole associative array — column name to value — in one call, and DBTNG automatically escapes every value for you. ->execute() runs the statement and returns the new row's auto-increment ID.

The whole thing sits inside a try/catch block, and that's not decoration — it's essential. If a value violates a database constraint (the form's own help text mentions that ages over 127 will do exactly this, because the column is a small integer type), the database driver throws a PHP exception. An uncaught exception in Drupal terminates the request with a fatal error page. The catch block turns that hard failure into a friendly, visible error message via the messenger service instead, and return $return_value ?? NULL; makes sure the method still returns something sensible even when the insert never happened.

update() — why the condition isn't optional

$count = $this->connection->update('dbtng_example')
  ->fields($entry)
  ->condition('pid', $entry['pid'])
  ->execute();

Same shape as insert, but with one addition that is absolutely mandatory: ->condition('pid', $entry['pid']). Without it, this statement would update every single row in the table with the new values — there'd be nothing telling the database which row you meant. execute() here returns the number of rows the condition actually matched, not an ID, which is why it's stored in $count rather than $return_value. The catch block also reads $e->query_string, a property Drupal's database exceptions expose specifically so you can see the raw SQL that failed while debugging.

delete() — the odd one out

$this->connection->delete('dbtng_example')
  ->condition('pid', $entry['pid'])
  ->execute();

Same condition() rule applies here — skip it and you delete the whole table. Notice this method has no try/catch, unlike its siblings. That's a deliberate teaching choice in the Examples module to show the contrast, not a recommendation: in a real module, wrapping deletes in try/catch (especially when foreign-key constraints might reject the delete) is good practice too.

Two ways to build a form class

DbtngExampleAddForm implements FormInterface and ContainerInjectionInterface directly, rather than extending the more familiar FormBase. This is more verbose — it has to declare its own create() factory method and manually wire up setStringTranslation() and setMessenger() — but it makes every dependency completely explicit, with nothing inherited or hidden.

DbtngExampleUpdateForm takes the more common route: it extends FormBase, which supplies a working $this->currentUser() helper and other boilerplate for free. You'll use the FormBase approach far more often in your own modules — the Add form here exists mainly to show you what FormBase is doing under the hood.

The Add form's submitForm() — the form never touches SQL

$entry = [
  'name' => $form_state->getValue('name'),
  'surname' => $form_state->getValue('surname'),
  'age' => $form_state->getValue('age'),
  'uid' => $account->id(),
];
$return = $this->repository->insert($entry);

Notice what's missing: no Connection, no select(), no SQL of any kind. The form's only job is to collect user input into a plain PHP array and hand it to $this->repository->insert(). This is the repository pattern paying off directly — the form doesn't need to know or care how the data actually gets persisted.

The Update form's AJAX dropdown

$form['pid'] = [
  '#type' => 'select',
  '#options' => $options,
  '#ajax' => [
    'wrapper' => 'update-form',
    'callback' => [$this, 'updateCallback'],
  ],
];

Choosing a different entry from the dropdown fires an AJAX request that re-populates the Name/Surname/Age fields with that entry's current values, without a full page reload. Look closely at updateCallback() and you'll notice it sets #value, not #default_value. That distinction matters specifically inside AJAX callbacks: #default_value only affects a field's initial render and won't override values already present in the request, while #value forcibly overrides whatever's there. You'll meet AJAX properly in an upcoming topic — for now, just notice how the entries loaded in buildForm() are cached into $form_state so the callback can reuse them without hitting the database a second time.

That's the dropdown mid-flight: switching the selection to Ada Lovelace's record instantly swapped in her name, surname, and age — all three fields updated in place, with no full-page reload and no extra query, thanks to the $form_state caching described above.

See it for yourself

Visit /examples/dbtng-example/add on your DDEV site, fill in a name, surname, and age, and submit.

The confirmation message shows you the exact array that was handed to insert() — proof that the form collected your input correctly and the repository saved it. Then visit /examples/dbtng-example/list to see your new row sitting alongside the others from the previous lesson.

Key takeaways

• The repository pattern centralizes all database write logic into a single injectable service, keeping forms and controllers free of raw SQL and making the code far easier to test.
• ->insert()->execute() returns the new row's auto-increment ID; ->update()->execute() returns the number of rows affected; ->delete()->execute() returns nothing — each write operation has a different return contract, so check accordingly.
• Always wrap insert() and update() calls in try/catch. A constraint violation throws an exception that will crash the request if left uncaught; the catch block lets you fail gracefully with a visible message instead.
• The condition() call on an UPDATE or DELETE is not optional — omit it and you affect every row in the table, not just the one you meant.
• You can implement FormInterface directly for full explicit control over every dependency, or extend FormBase for the common helpers most modules actually want — know that both options exist.
• Inside an AJAX callback, use #value instead of #default_value to force a field to show new data — #default_value only applies on the form's very first render.

Coming up next

You've now seen the full CRUD cycle against a hand-written table. The next lesson goes one level deeper into query building itself — joins, aliasing, and range limits — using the exact same repository you've now read end to end, so you can see how a single, well-designed service can serve both simple and advanced needs.