Skip to content

03-crud.md

Latest commit

 

History

History
201 lines (149 loc) · 6.47 KB

03-crud.md

File metadata and controls

201 lines (149 loc) · 6.47 KB

03 — CRUD

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 any Model subclass. The model variants apply soft-delete / timestamp / writability gates on top — see 04 — 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.

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.

Read

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

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

// Pick a fetch mode (chainable, all return the mapper):
$res->asAssoc();          // PDO::FETCH_ASSOC  (default for plain DB queries)
$res->asObject();         // PDO::FETCH_OBJ
$res->asClass('Entity');  // PDO::FETCH_CLASS, hydrating that 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

$res->numRows() is PDOStatement::rowCount() under the hood. On SQLite and on unbuffered MySQL connections 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]);

The signature is read(?string $table, ?array $selectors, ?array $conditions). $conditions accepts a column => value map (which becomes column = value) and integer-keyed entries (which become bare WHERE clauses for raw fragments).

Update

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

update($table, $set, $conditions = null) returns true on success. The $conditions argument is the same short-form map as in 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'], // partial update; content untouched
]);
/*
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 that 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 working through a Model with $useSoftDeletes = true, this becomes a soft delete by default — pass $purge = true to bypass the soft-delete path. See 04 — Models.

REPLACE INTO / 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. Reach for DB::query() with raw SQL when you need it; every driver-specific dialect goes through the same prepared-statement path.

MySQL / MariaDB / SQLite — REPLACE INTO

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

REPLACE INTO deletes a conflicting row and inserts the new one — be aware that this fires ON DELETE triggers and reissues auto-increment IDs. SQLite supports the same syntax (and also accepts INSERT OR REPLACE INTO as a synonym).

PostgreSQL — INSERT ... ON CONFLICT

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

ON CONFLICT is generally the better choice when it's available: no DELETE happens, triggers behave normally, and the conflict target is explicit.

MySQL 8 — INSERT ... ON DUPLICATE KEY UPDATE

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

Same intent as PostgreSQL's ON CONFLICT — preferred over REPLACE INTO for the same reasons.

Batching upserts

DB::query() takes one statement at a time. If you need to upsert a batch, run them inside a transaction so the round-trips are amortised and you get atomicity for free:

DB::transaction(function ($db) use ($rows) {
    foreach ($rows as $row) {
        $db->query(
            'REPLACE INTO items (id, name) VALUES (:id, :name)',
            [':id' => $row['id'], ':name' => $row['name']]
        );
    }
});

Raw SQL

When the query is faster to write by hand:

$res = DB::query(
    'SELECT id, title FROM posts WHERE user_id = :uid AND status > :s',
    [':uid' => 5, ':s' => 0]
);

foreach ($res->asAssoc()->rows() as $row) {
    echo $row['title'], PHP_EOL;
}

query() bypasses the builder entirely — it prepares the SQL, binds the parameters, returns a DataMapperInterface. Use it when the SQL is small and the builder would only obscure it.

Affected rows

After any of the four operations, DB::affectedRows() returns the row count from the underlying PDOStatement::rowCount(). Same SQLite-on-SELECT caveat as above applies to read paths; on common drivers it is reliable for write operations.