Query insert

Insert queries should always use the query builder object. Some databases require special handling for LOB (Large OBject, e.g., TEXT in MySQL) and BLOB (Binary Large OBject) fields, so an abstraction layer is necessary for individual database drivers to implement any required special handling.

Insert queries are initiated using the insert() method like this:

$query = $connection->insert('mytable', $options);

This creates an insert query object that will insert one or more records into the mytable table. Note that curly braces are not required around the table name because the query builder handles that automatically.

The insert query object uses a fluent API, meaning that all methods (except execute()) return the query object itself, allowing method calls to be chained. In many cases, this means you don’t need to save the query object to a variable.

The insert query object supports several usage patterns to accommodate different needs. Generally, the workflow consists of specifying the fields to insert into, defining the values to be inserted into those fields, and executing the query. The most common recommended usage patterns are listed below.

Compact Form

The preferred form for most insert queries is the compact form:

$result = $connection->insert('mytable')
  ->fields([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])
  ->execute();

This is equivalent to:

INSERT INTO {mytable} (title, uid, created) VALUES ('Example', 1, 1221717405);

This snippet combines the key parts of the insert process.

$connection->insert('mytable')

This line creates a new insert query object for the mytable table.

  ->fields([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])

The fields() method accepts several forms of parameters, but most commonly an associative array is used. The keys are the table columns and the values are the corresponding values to insert.

 ->execute();

The execute() method tells the query to execute. If this method is not called, the query is not executed.

Unlike other insert query object methods that return the query object itself, execute() returns the value of the auto-increment field (a serial type in hook_schema()), if any. That’s why its return value is assigned to $result above. If there’s no auto-increment field, the return value is undefined and should not be trusted.

Degenerate Form

$result = $connection->insert('mytable')
  ->fields(['title', 'uid', 'created'])
  ->values([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])
  ->execute();

This is a slightly more verbose equivalent of the previous query and will have the same result.

  ->fields(['title', 'uid', 'created'])

When fields() is called with an indexed array, it sets only the fields to be used in the query without setting any values. This is useful for multi-row inserts later.

  ->values([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])

This sets the values to insert. The array can be indexed or associative, but if indexed, the order must match that specified in fields(). Associative arrays are generally preferred for readability.

Multiple Insert Form

The insert query object can also queue multiple value sets. That is, values() can be called multiple times. How this is handled depends on the database. Most databases will execute the inserts within a transaction for data integrity and performance. In MySQL, it uses multi-value insert syntax.

$values = [
  [
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ],
  [
    'title' => 'Example 2',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ],
  [
    'title' => 'Example 3',
    'uid' => 2,
    'created' => \Drupal::time()->getRequestTime(),
  ],
];
$query = $connection->insert('mytable')->fields(['title', 'uid', 'created']);
foreach ($values as $record) {
  $query->values($record);
}
$query->execute();

The three inserts are handled efficiently as one batch, using the best method for the database driver. The query object is stored in a variable to allow reuse inside the loop.

INSERT INTO {mytable} (title, uid, created) VALUES ('Example', 1, 1221717405);
INSERT INTO {mytable} (title, uid, created) VALUES ('Example2', 1, 1221717405);
INSERT INTO {mytable} (title, uid, created) VALUES ('Example3', 2, 1221717405);

For multi-inserts, the return value of execute() is undefined and should not be relied on.

Insert from Select

If you want to populate a table from the results of a SELECT query, you can either iterate over the data in PHP or use INSERT INTO ... SELECT FROM syntax. Here's an example:

select('node', 'n');
$query->join('users', 'u', 'n.uid = u.uid');
$query->addField('n', 'nid');
$query->addField('u', 'name');
$query->condition('type', 'page');

// Perform the insert.
$connection->insert('mytable')
  ->from($query)
  ->execute();
?>

Default Values

Normally, if you don’t specify a value for a field and it has a default in the table schema, the database will insert that default value. Sometimes, though, you need to explicitly tell the database to use the default. To do that, use useDefaults():

$query->useDefaults(['field1', 'field2']);

Do not use the same field in both useDefaults() and fields() or values()—this will throw an exception.

$connection->insert() or $connection->query()?

This is a common question. insert() allows each column to be specified individually, with values that can be sanitized. query() takes a raw SQL string. With query() and placeholders, values can be checked, but it’s less safe overall. insert() also triggers hook mechanisms that allow other modules to alter queries. query() skips these, gaining performance but sacrificing interoperability.

insert() is more portable across databases and future Drupal versions.