CRUD Operations

CRUD Operations

Four method families do the heavy lifting:

Operation	Method	Batch sibling
Insert	create()	createBatch()
Select	read()	—
Update	update()	updateBatch()
Delete	delete()	—

All four are available on DB::, on a Database instance, and on every Model subclass. The model variants apply soft-delete / timestamp / writability gates on top — see Models.

Create

use InitPHP\Database\DB;

DB::create('posts', [
    'title'   => 'Hello world',
    'content' => 'First post.',
]);
// INSERT INTO `posts` (`title`, `content`) VALUES ('Hello world', 'First post.')

$id = DB::insertId(); // PDO::lastInsertId

create() returns true on success and throws on failure. Use DB::insertId() to read the autogenerated primary key. Pass the data through a builder chain when you need additional clauses:

DB::set('title', 'Hello')
  ->set('content', 'World')
  ->from('posts')
  ->create();

Batch insert

DB::createBatch('posts', [
    ['title' => 'Post #1', 'content' => 'Body 1', 'author_id' => 5],
    ['title' => 'Post #2', 'content' => 'Body 2'], // author_id missing — becomes NULL
]);

INSERT INTO `posts` (`title`, `content`, `author_id`) VALUES
    ('Post #1', 'Body 1', 5),
    ('Post #2', 'Body 2', NULL);

The compiler unions the keys across all rows; missing columns are emitted as NULL. This is one network round-trip and one prepared statement regardless of batch size.

Read

$res = DB::select('id', 'title')
    ->from('posts')
    ->where('status', '=', 1)
    ->orderBy('id', 'DESC')
    ->limit(20)
    ->read();

The call returns an InitORM\DBAL\DataMapper\Interfaces\DataMapperInterface. From there:

// Pick a fetch mode (chainable, all return the mapper):
$res->asAssoc();             // PDO::FETCH_ASSOC
$res->asObject();            // PDO::FETCH_OBJ
$res->asClass(MyEntity::class); // PDO::FETCH_CLASS
$res->asLazy();              // PDO::FETCH_LAZY

// Consume:
$first = $res->row();        // next row, or null
$all   = $res->rows();       // all remaining rows as an array

numRows() caveat — read it before you trust it

$res->numRows() is PDOStatement::rowCount() under the hood. On SQLite and unbuffered MySQL it is unreliable for SELECT statements — it can return 0 even when rows came back. For INSERT / UPDATE / DELETE on common drivers it works as expected. When in doubt, fetch with rows() and count() the array.

Short-form read

$res = DB::read('posts', ['id', 'title'], ['status' => 1]);

Signature: read(?string $table, ?array $selectors, ?array $conditions). The $conditions argument accepts:

• 'column' => value → column = value
• integer-keyed values → bare WHERE clauses (use for raw fragments)

DB::read('posts', ['*'], [
    'status'   => 1,
    DB::raw('created_at > NOW() - INTERVAL 7 DAY'),
]);

Update

DB::where('id', 13)->update('posts', [
    'title'   => 'New title',
    'content' => 'New body',
]);
// UPDATE `posts` SET `title` = :title, `content` = :content WHERE `id` = 13

update($table, $set, $conditions = null) returns true on success. $conditions shares the shape with read().

Batch update (CASE / WHEN)

DB::where('status', '!=', 0)->updateBatch('id', 'posts', [
    ['id' => 5,  'title' => 'New #5',  'content' => 'Body #5'],
    ['id' => 10, 'title' => 'New #10'],
]);

UPDATE `posts` SET
    `title` = CASE
        WHEN `id` = 5  THEN 'New #5'
        WHEN `id` = 10 THEN 'New #10'
        ELSE `title` END,
    `content` = CASE
        WHEN `id` = 5 THEN 'Body #5'
        ELSE `content` END
WHERE `status` != 0 AND `id` IN (5, 10);

The first argument is the reference column (usually the primary key). Rows are matched on it; columns some rows omit fall back to the existing value via ELSE column.

Delete

DB::where('id', 13)->delete('posts');
// DELETE FROM `posts` WHERE `id` = 13

delete($table, $conditions = null) shares the conditions-shape with read() / update().

When called through a Model with $useSoftDeletes = true, this becomes a soft delete — set deletedField to the current timestamp instead of issuing DELETE. Pass $purge = true to bypass the soft-delete path. See Soft Deletes.

Affected rows

After any of the four operations:

$count = DB::affectedRows(); // PDOStatement::rowCount() for the last call

Same SQLite-on-SELECT caveat applies. For write operations on MySQL / PostgreSQL the value is reliable.

REPLACE INTO and UPSERT

The query builder has no native REPLACE INTO (or upsert) method — the operation is not standard SQL and the exact spelling differs from one driver to the next. Use raw SQL through DB::query():

// MySQL / SQLite
DB::query(
    'REPLACE INTO items (id, name) VALUES (:id, :name)',
    [':id' => 1, ':name' => 'Alice']
);

// PostgreSQL
DB::query(
    'INSERT INTO items (id, name) VALUES (:id, :name)
     ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name',
    [':id' => 1, ':name' => 'Alice']
);

// MySQL 8
DB::query(
    'INSERT INTO items (id, name) VALUES (:id, :name)
     ON DUPLICATE KEY UPDATE name = VALUES(name)',
    [':id' => 1, ':name' => 'Alice']
);

For a batch upsert, wrap the loop in a transaction. See Recipe — Upsert / REPLACE INTO.

Errors

Every CRUD method throws on a real database failure — the exception type is one of:

• InitORM\DBAL\Connection\Exceptions\SQLExecuteException — the statement was prepared but execute() failed.
• InitORM\Database\Exceptions\DatabaseException — orchestration error (no shared facade, transaction nesting, etc.).
• InitORM\QueryBuilder\Exceptions\QueryBuilderException — the builder rejected something (bad operator, empty INSERT set, …).

There is no silent failure path. Wrap in try / catch only when you actually want to recover; otherwise let the exception propagate.

Next

• Raw Queries — bypass the builder entirely.
• Transactions — atomic write sequences.
• Models — same surface, with soft deletes and timestamps layered on top.