Writing data

insert(), update() and delete() compile and run immediately, returning the number of affected rows.

$database->table('users')->insert(['name' => 'Ada', 'active' => 1]);

$database->table('users')->where('id', '=', 7)->update(['active' => 0]);

$database->table('users')->where('active', '=', 0)->delete();

Inserting

One row is an associative array; several rows are a list of them.

$database->table('users')->insert(['name' => 'Ada']);

$database->table('users')->insert([
    ['name' => 'Ada', 'active' => 1],
    ['name' => 'Grace', 'active' => 0],
]);

The column list comes from the first row, and every other row has to carry the same columns in the same order:

$database->table('users')->insert([
    ['name' => 'Ada', 'active' => 1],
    ['active' => 0, 'name' => 'Grace'],
]);
// InvalidArgumentException: Every inserted row needs the same columns in the same order.

note

The alternative would be to reorder each row against the first, which quietly turns a mismatched row into a row of misplaced values. Refusing is louder and the fix is one line at the call site.

An empty array inserts nothing and returns 0 without touching the database. A null value is a value — ['name' => null] binds null, which is what a nullable column wants.

Values must be scalar|null. Anything else throws when the query is compiled, so encode an array and format a DateTimeInterface before you insert it.

Reading back the generated key

insertGetId() inserts one row and returns the key the database generated for it, as a ?string:

$id = $database->table('users')->insertGetId(['name' => 'Ada']);

The key column defaults to id. Pass another when it differs:

$code = $database->table('regions')->insertGetId(['name' => 'North'], 'code');

It returns a string for the same reason lastInsertId() does — a 64-bit key does not always fit a PHP int, and some keys are not numeric. Cast it at the call site if you know better.

note

How the key is read differs per database, which is why the key column has to be named. PostgreSQL and SQL Server return it from the statement that generated it (RETURNING and OUTPUT INSERTED), because their connection-level answer is about the last sequence or identity the session produced — a trigger inserting into another table makes that the wrong row. MySQL and SQLite have neither clause, and their connection-level answer is unambiguous, so they use it.

Only one row can return one key, so a multi-row insert is refused:

$database->table('users')->insertGetId([['name' => 'Ada'], ['name' => 'Grace']]);
// LogicException: An insert that returns a key must have exactly one row.

Updating

update() takes the columns to set and applies the builder's conditions.

$database->table('users')
    ->where('active', '=', 0)
    ->update(['active' => 1, 'confirmed_at' => $now]);

An empty array updates nothing and returns 0. As with insert, the values are scalar|null.

danger

update() with no condition updates every row, and so does delete(). Neither requires a where(), because "set a flag on everything" is a real query — but it means a forgotten condition is a full-table write.

Deleting

$database->table('sessions')->where('expires_at', '<', $cutoff)->delete();

Which clauses a mutation accepts

A mutation uses the table, the conditions, and — where the database supports them — a limit and an ordering. Anything else is refused rather than ignored.

Clause	On an update or delete
where*()	Always.
limit()	MySQL and SQL Server only. Others throw.
orderBy*()	MySQL only. Others throw.
offset()	Never. Always throws.
join*()	Not supported yet. Always throws.

// MySQL: deletes the oldest matching row
$database->table('users')->where('active', '=', 0)->orderBy('created_at')->limit(1)->delete();

caution

This is the part worth knowing before you rely on it. An ordered, limited delete is a MySQL feature. On PostgreSQL, SQLite, or SQL Server the same code throws a LogicException at compile time rather than deleting an arbitrary row, so a query that works in development will not silently pick the wrong row in production on another database.

The exception names what it could not do:

A limited delete query is not supported by this driver.
An ordered delete query is not supported by this driver.
Delete queries cannot skip rows with an offset.
Joined delete queries are not supported yet.

Why each one behaves that way is in Grammars. The short version: SQL Server can limit with TOP (n) but cannot order; PostgreSQL has neither; SQLite has both only when its library was compiled with an optional flag, so relying on it would work on one machine and fail on the next.

offset() is refused by the builder rather than by a grammar, because no supported database can express it on a mutation. To delete all but the newest rows, select the keys first and delete by them:

$keep = $database->table('sessions')->select('id')->orderByDesc('created_at')->limit(100)->get();

$database->table('sessions')->whereNotIn('id', array_column($keep, 'id'))->delete();

Raw statements

For anything the builder does not cover — TRUNCATE, an upsert, a RETURNING clause — go through the connection or Database::execute():

$database->execute('TRUNCATE TABLE sessions');

$id = $database->execute('INSERT INTO users (name) VALUES (?) RETURNING id', ['Ada'])
    ->first()['id'];

See Executing queries for the binding rules that apply there.