INSERT, UPDATE, DELETE builders

Advanced5 min read·fw-07-005

sql

Concept

INSERT, UPDATE, DELETE builders complete the Query Builder's CRUD capabilities. While SELECT is the most complex, the write operations also benefit from a fluent interface and safe parameter binding.

INSERT:

Single row: INSERT INTO users (name, email) VALUES (?, ?).
Multiple rows: INSERT INTO users (name, email) VALUES (?, ?), (?, ?) — compile all rows into one statement.
INSERT OR IGNORE / INSERT IGNORE: Skip duplicates without error.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) / ON CONFLICT DO UPDATE (PostgreSQL): Upsert.

UPDATE:

UPDATE users SET name = ?, email = ? WHERE id = ?.
ALWAYS include a WHERE clause. update() without where() updates ALL rows.
Return affected row count.
UPDATE users SET count = count + 1 WHERE id = ? — increment without reading first.

DELETE:

DELETE FROM users WHERE id = ?.
Return affected row count.
ALWAYS include WHERE clause. delete() without where() deletes ALL rows.
Soft delete is an UPDATE: UPDATE users SET deleted_at = NOW() WHERE id = ?.

Safety check: Some builders refuse to execute UPDATE or DELETE without a WHERE clause. This prevents accidental full-table modifications.

lastInsertId(): After INSERT, get the auto-incremented primary key: $pdo->lastInsertId().

Code Example

php

<?php
namespace Framework\Database;

class QueryBuilder
{
    private array $sets  = [];  // for UPDATE: ['column' => value]
    private array $bindings = ['where' => [], 'insert' => [], 'update' => []];

    // INSERT
    public function insert(array $values): bool
    {
        $columns     = implode(', ', array_keys($values));
        $placeholders = implode(', ', array_fill(0, count($values), '?'));
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";
        return $this->connection->insert($sql, array_values($values));
    }

    public function insertGetId(array $values): string|false
    {
        $this->insert($values);
        return $this->connection->lastInsertId();
    }

    public function insertMany(array $rows): bool
    {
        if (empty($rows)) return true;
        $columns      = implode(', ', array_keys($rows[0]));
        $rowPlaceholders = array_fill(0, count($rows),
            '(' . implode(', ', array_fill(0, count($rows[0]), '?')) . ')'
        );
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES " . implode(', ', $rowPlaceholders);
        $flat = array_merge(...array_map('array_values', $rows));
        return $this->connection->insert($sql, $flat);
    }

    // UPDATE
    public function update(array $values): int
    {
        $sets = implode(', ', array_map(fn($col) => "{$col} = ?", array_keys($values)));
        $sql  = "UPDATE {$this->table} SET {$sets}";
        if (!empty($this->wheres)) {
            [$whereClause, $whereBindings] = $this->compileWheres();
            $sql .= " WHERE {$whereClause}";
            return $this->connection->update($sql, array_merge(array_values($values), $whereBindings));
        }
        return $this->connection->update($sql, array_values($values));
    }

    public function increment(string $column, int $amount = 1): int
    {
        return $this->update([$column => $this->raw("{$column} + {$amount}")]);
    }

    // DELETE
    public function delete(): int
    {
        $sql = "DELETE FROM {$this->table}";
        if (!empty($this->wheres)) {
            [$whereClause, $whereBindings] = $this->compileWheres();
            $sql .= " WHERE {$whereClause}";
            return $this->connection->delete($sql, $whereBindings);
        }
        return $this->connection->delete($sql, []);
    }

    private function raw(string $expr): RawExpression
    {
        return new RawExpression($expr);
    }
}

// Usage
$builder->table('users')->insert(['name' => 'Alice', 'email' => 'alice@example.com']);
$id = $builder->table('users')->insertGetId(['name' => 'Bob', 'email' => 'bob@example.com']);

$affected = $builder->table('users')->where('id', 42)->update(['name' => 'Alice Smith']);
$builder->table('posts')->where('user_id', 42)->increment('view_count');

$deleted = $builder->table('sessions')->where('expires_at', '<', date('Y-m-d H:i:s'))->delete();

PreviousJOIN clause compilationQuery Builder from Scratch NextPagination — LIMIT/OFFSET vs cursor-basedQuery Builder from Scratch