CRUD Operations

CRUD Operations

The Database exposes five CRUD helpers plus their batch variants. They are the most idiomatic way to talk to a database in this package — you almost never need to write SQL by hand for the common cases.

Shared rules

All CRUD helpers obey the same contract:

• The builder is the source of truth. Anything you chain (where(), select(), orderBy(), …) is folded into the compiled SQL.
• State resets after every call. The builder structure and parameter bag are wiped in a finally block, so the next call starts clean — even if execution threw.
• bool true on success. Failures throw; true always means "executed without error".
• Use affectedRows() to learn how many rows changed; the bool tells you whether it ran, not whether it did anything.
• Parameters are auto-bound. You never concatenate user input into SQL.

For the rest of this page, assume:

DB::query('
    CREATE TABLE users (
        id     INT AUTO_INCREMENT PRIMARY KEY,
        name   VARCHAR(80)  NOT NULL,
        email  VARCHAR(120) NOT NULL,
        active TINYINT(1)   NOT NULL DEFAULT 1,
        score  INT
    )
');

Create

Insert one row.

DB::create('users', [
    'name'   => 'Alice',
    'email'  => 'alice@example.com',
    'active' => 1,
    'score'  => 42,
]);

echo DB::insertId(); // "1"

Generated SQL: INSERT INTO users (name, email, active, score) VALUES (:name, :email, :active, :score)

Two equivalent styles — pick whichever reads better:

// Helper-first
DB::create('users', $data);

// Builder-first
DB::from('users')->set($data)->create();

Create Batch

Insert multiple rows in a single statement. Far faster than three separate inserts under MySQL.

DB::createBatch('users', [
    ['name' => 'Bob',   'email' => 'bob@example.com',   'active' => 1, 'score' => 13],
    ['name' => 'Carol', 'email' => 'carol@example.com', 'active' => 0, 'score' => 99],
    ['name' => 'Dan',   'email' => 'dan@example.com'], // missing 'active' / 'score' → NULL
]);

Compiled SQL (one statement, multi-row):

INSERT INTO users (name, email, active, score) VALUES
    (:name,   :email,   :active,   :score),
    (:name_1, :email_1, :active_1, :score_1),
    (:name_2, :email_2, NULL,      NULL)

The column union is collected across all rows; missing columns in any row compile to the literal NULL.

Chunking large batches (see Recipes):

foreach (array_chunk($hugeArray, 500) as $chunk) {
    DB::createBatch('users', $chunk);
}

Read

read() returns a DataMapperInterface you fetch from.

Simple read

$result = DB::read('users');

foreach ($result->asAssoc()->rows() as $row) {
    echo $row['name'] . PHP_EOL;
}

Read with selectors + conditions

$result = DB::read('users', ['id', 'name'], ['active' => 1]);

Generated SQL: SELECT id, name FROM users WHERE active = :active

Read with builder chain

$rows = DB::select('id', 'name', 'score')
    ->where('active', '=', 1)
    ->whereIn('score', [10, 20, 30, 40, 50])
    ->orderBy('score', 'DESC')
    ->limit(10)
    ->offset(20)
    ->read('users')
    ->asAssoc()
    ->rows();

Fetch modes

The DataMapper supports several fetch modes:

$result->asAssoc();                      // array<string, mixed>
$result->asObject();                     // stdClass
$result->asObject($existingInstance);    // FETCH_INTO an instance
$result->asClass(MyEntity::class);       // FETCH_CLASS
$result->asLazy();                       // PDORow
$result->asArray();                      // FETCH_BOTH (string + int keys)

Combine with row() (next row, or null), rows() (everything left), or iterate:

$result = DB::read('users');
$result->asAssoc();
while (($row = $result->row()) !== null) {
    // streams one row at a time
}

Update

DB::update('users', ['active' => 0], ['id' => 2]);

Generated SQL: UPDATE users SET active = :active WHERE id = :id

Or builder-first:

DB::where('email', 'LIKE', '%@example.com')
    ->update('users', ['active' => 0]);

You can use a RawQuery on the right-hand side for in-place calculations:

DB::update('counters', [
    'value'      => DB::raw('value + 1'),
    'updated_at' => DB::raw('NOW()'),
], ['id' => 1]);

Generated SQL: UPDATE counters SET value = value + 1, updated_at = NOW() WHERE id = :id

Update Batch (CASE/WHEN per row)

Update many rows to different values in one statement.

DB::where('status', '!=', 0)
    ->updateBatch('id', 'users', [
        ['id' => 1, 'score' => 100],
        ['id' => 2, 'score' => 200],
    ]);

Generated SQL (formatted):

UPDATE users SET
    score = CASE
        WHEN id = :id   THEN :score
        WHEN id = :id_1 THEN :score_1
        ELSE score
    END
WHERE status != :status AND id IN (:id_2, :id_3)

The first argument is the reference column. Every row in $set must contain it; the compiler folds row IDs into an automatic WHERE … IN (…) clause so unrelated rows aren't touched.

Delete

DB::delete('users', ['id' => 3]);

Generated SQL: DELETE FROM users WHERE id = :id

A delete without WHERE is allowed but the compiler falls back to WHERE 1 (i.e. "all rows") — be explicit:

// Both compile to: DELETE FROM users WHERE 1
DB::delete('users');
DB::delete('users', null);

Most of the time you want a guard:

DB::delete('users', ['active' => 0]);

Affected Rows

DB::update('users', ['active' => 0], ['active' => 1]);

echo DB::affectedRows(); // e.g. "42" — number of rows the previous UPDATE actually changed

affectedRows() returns the rowCount() of the most recent CRUD call on the same Database. It's 0 when no CRUD call has executed yet, and reliable for INSERT/UPDATE/DELETE on common drivers. For SELECT, it depends on whether the driver buffers results — prefer numRows() on the returned DataMapper for SELECT counts.

Conditions shortcut

The $conditions parameter on read / update / updateBatch / delete accepts a mixed-key array:

Key type	Effect
String	where(key, '=', value)
Integer	where(value) — value is a RawQuery or a literal column expression

// String keys → equality
DB::read('users', null, ['active' => 1, 'role' => 'admin']);
// WHERE active = :active AND role = :role

// Integer key with RawQuery
DB::read('users', null, [DB::raw('score > 50')]);
// WHERE score > 50

// Mix both
DB::read('users', null, [
    'active' => 1,
    DB::raw('score > 50'),
]);
// WHERE active = :active AND score > 50

For anything more complex (LIKE, IN, OR, …) drop into the builder:

DB::where('active', '=', 1)
    ->orWhere('role', '=', 'admin')
    ->whereIn('id', [1, 2, 3])
    ->read('users');

See Query Builder for the full WHERE / JOIN / GROUP BY / ORDER BY surface.

Return values cheat sheet

Method	Returns	When it throws
create()	true on success	QueryBuilderException, SQLExecuteException, ConnectionException
createBatch()	true on success	same
read()	DataMapperInterface	same + DataMapperException
update()	true on success	same
updateBatch()	true on success	same
delete()	true on success	same
query()	DataMapperInterface	SQLExecuteException, ConnectionException
insertId()	string|false	ConnectionException
affectedRows()	int	never

See also

• Query Builder — for the full WHERE / JOIN / GROUP BY / sub-query surface
• Raw Queries — when CRUD helpers aren't expressive enough
• Transactions — wrap multiple CRUD calls atomically
• Recipes — pagination, bulk insert chunking, soft-delete, upsert