RUEN
Package · cdo

Inserting records

How to insert one row, insert thousands efficiently, and read back the id the database generated — plus the two rules that decide what actually gets written.

Insert a single row

insert() takes a table name and an array (or object) of column → value pairs. It returns the generated primary key.

php
$id = $cdo->insert('users', [
  'name'  => 'Alice',
  'email' => 'alice@example.com',
]);

The returned id is the value of the first key of the entity you passed, fetched via RETURNING on PostgreSQL/MariaDB or lastInsertId() on MySQL, SQLite or Oracle. See Driver detection for why the two paths exist.

The primary key is the first key

insert() derives the returned column from array_key_first() of your entity — before NULLs are stripped. Put your primary-key column first (even as 'id' => null) so the right value comes back. A null/absent auto-id column still returns the generated id via the driver.

Two rules for what gets written

  1. null values are dropped. Any column whose value is null is removed from the INSERT, so the column takes its database default or auto-generated value.
  2. Objects are flattened. An object entity is converted with get_object_vars(), so its public properties become columns.
php
$user = new stdClass();
$user->name  = 'Bob';
$user->email = 'bob@example.com';
$user->note  = null;   // dropped — column keeps its default

$id = $cdo->insert('users', $user);

Insert a batch

insertBatch() writes many rows with far fewer round-trips. It automatically splits the input into chunks and emits one multi-row INSERT per chunk.

php
$users = [
  ['name' => 'Alice', 'email' => 'alice@example.com'],
  ['name' => 'Bob',   'email' => 'bob@example.com'],
  // ... thousands more
];

$inserted = $cdo->insertBatch('users', $users);              // default chunk size 1000
$inserted = $cdo->insertBatch('users', $users, chunkSize: 500);

insertBatch() returns an int — the total number of rows inserted. It is a bulk write, not an id fetch. An empty input is a no-op and returns 0.

The second argument takes any iterable, so the rows need not sit in an array. A generator yields them one at a time and only the current chunk lives in memory — this is how files too large to load are imported:

php
$inserted = $cdo->insertBatch('users', (function () use ($csv) {
  while (($line = fgetcsv($csv)) !== false) {
      yield ['name' => $line[0], 'email' => $line[1]];
  }
})());

The details are in Batches and chunking.

Rows are grouped by shape

You don’t need to normalise your rows first. Before chunking, rows are grouped by their column signature (the set of non-null columns), so rows of different shapes are batched into separate statements that always line up. One consequence: rows are re-ordered by group, so don’t rely on auto-increment ids following the input array order when rows differ in shape. Details in Batch & chunking.

Choosing a chunk size

chunkSize caps how many rows go into one statement, which keeps you under the database’s bound-parameter and packet limits. Smaller chunks use less memory per statement; larger chunks mean fewer round-trips. The default of 1000 is a safe starting point — lower it if rows are very wide (many columns).

Handling duplicate keys

A plain insert into a row that violates a unique constraint throws a CDOException. If you instead want insert-or-update (or insert-or-ignore), use upsert / upsertBatch.