Download the PHP package mavinoo/laravel-batch without Composer

Laravel BATCH (BULK)

Bulk database operations for Laravel: update, insert, upsert, delete, sync, import and export many rows in a few queries, on MySQL, MariaDB, PostgreSQL and SQLite.

What it does

• Update many rows with different values in one query, with increments, raw SQL and JSON paths.
• get their ids, or skip duplicates and see which.
• conditions and the stored rows returned.
• Delete rows matched on several key columns.
• Sync part of a table with a list: insert, update and delete in one go.
• Import CSV, TSV and JSON Lines files or any iterable with constant memory, own bulk loader.
• split large files.
• Delete, update or archive millions of rows a chunk at a time.
• Preview the SQL of any call without running it.

Requirements

Upgrading from 2.x? Read UPGRADE.md.

Install

The service provider and the Batch facade are registered automatically.

Update

Each row holds the index value (here id) and the columns to set. Rows can set different columns.

The index defaults to the model's primary key. update() returns the number of affected rows. MySQL and MariaDB only count rows whose values really changed; PostgreSQL and SQLite count every matched row.

If the same index value appears in more than one row of a call, only the first of those rows is applied.

Values can be strings, numbers, booleans, null, dates and enums (backed enums are stored by their value, other enums by their name).

Increment / decrement

Raw SQL values

Wrap a value in DB::raw() to use it as SQL instead of as a string:

Never put user input inside DB::raw(). Every other value is sent as a bound parameter and is safe.

JSON columns

Set keys inside a JSON column with ->, like Laravel's update():

• Other keys in the document are kept. Missing objects along the path are created, and a NULL column starts as an empty object.
• Values keep their JSON type: false is stored as false, arrays as JSON arrays or objects. DB::raw() values are used as given.
• Path keys are object keys; they can't contain quotes or backslashes.
• A row can't set a column and a path inside it in the same call.
• Works with every update method on MySQL, MariaDB, PostgreSQL and SQLite.

For columns the model casts to JSON (array, json, object, collection, AsArrayObject, AsCollection, ...), arrays and collections are encoded the way the model would store them, in every method:

Without such a cast, an array value is read as an increment / decrement.

Update with two index columns

Rows are matched on both columns:

Update with any number of key columns

Rows are matched on every column in the third argument:

Update with different conditions per row

Each item has its own conditions and columns. Every item's conditions must include the key column (the third argument, the primary key by default).

A condition column can be updated in the same call, as status is above. On MySQL and MariaDB, two columns that are both updated and used in each other's conditions can't be updated in one call, because MySQL assigns columns one after another. The call throws an InvalidArgumentException; split it into two calls.

updated_at

If the model uses timestamps, updated_at is set to the current time only for rows where a value actually changes. Pass updated_at in a row to set it yourself.

Insert

• Each row lists its values in the same order as the columns.
• created_at and updated_at are filled in when the model uses timestamps.
• The fourth argument is the number of rows per query (at least 100, default 500).
• Pass true as the fifth argument to skip rows that hit a unique key (INSERT IGNORE). Not supported on SQL Server.

Insert rows given as column => value pairs

insertRows() takes the same options as insert(), but each row names its columns. Every row needs the same columns; their order doesn't matter.

Insert and get the new ids

insertGetIds() takes the same rows as insertRows() and returns the new auto-increment ids, in the same order as the rows:

• PostgreSQL returns the ids with INSERT ... RETURNING. On MySQL and MariaDB they are computed from LAST_INSERT_ID() and auto_increment_increment, and on SQLite from last_insert_rowid(): these databases give the rows of one multi-row INSERT consecutive ids, also while other connections insert at the same time.
• The model's primary key must be auto-incrementing, and the rows can't set it.
• It's a separate method, so insert() and insertRows() don't pay for fetching ids.
• Inside pretend() nothing runs, so it returns [].

Insert and report the skipped rows

insertOrIgnoreRows() inserts rows, skips the ones that hit a unique key, like insertOrIgnore(), and tells you exactly which ones were skipped:

• The second argument names the columns that identify a row; the skipped rows are returned as given.
• A key given twice in the list is inserted once and reported as skipped the second time.
• PostgreSQL and SQLite report with RETURNING. MySQL and MariaDB need an auto-incrementing primary key that the rows don't set.
• It is exact also while other connections insert the same keys. Like any INSERT, inserting the same keys from several connections at once can deadlock: retry the call when it does.

Upsert

Insert rows, or update them when a row with the same $uniqueBy values already exists, in one query:

• The third argument lists the columns that identify an existing row. They need a primary or unique index. On MySQL and MariaDB, any unique index decides what counts as an existing row.
• The fourth argument lists the columns to update on existing rows; leave it out (null) to update every given column.
• created_at is only set on new rows and updated_at on every row, like Eloquent's upsert().
• Unlike Model::upsert(), large batches are split into several queries automatically, in one transaction.
• The returned count comes from the database: MySQL and MariaDB count an updated row as 2.

Counters

Combine the stored value with the new one instead of replacing it, for stock levels, totals or high scores:

['+'] adds, ['-'] subtracts, ['max'] keeps the larger value and ['min'] the smaller one. A stored NULL counts as 0 for + and -, and is replaced for max and min. Counter and plain columns can be mixed: ['name', 'qty' => ['+']].

Update only when the new row wins

onlyIf updates an existing row only when the new value compares that way with the stored one, or the stored one is NULL. New rows are always inserted:

The operators are >, >=, <, <= and <>. Several columns must all match. On MySQL and MariaDB at most one onlyIf column can also be updated.

Get the rows back

upsertReturning() takes the same arguments as upsert(), plus the columns to return, and gives back every row of the batch as it is stored afterwards, in the order of the input:

The rows are read back by their uniqueBy values in the same transaction, so this works the same on every database, MySQL included. Rows an onlyIf condition left alone are returned as stored.

Delete

Delete rows matched on one or more key columns:

Models that use SoftDeletes are soft deleted (only deleted_at, and updated_at, are set). Pass true as the fourth argument to delete them for real.

Sync a table with a list

sync() makes the rows a query matches look like a list, in one transaction: rows that are new are inserted, rows that exist are updated, and rows the list doesn't have are deleted.

• Only rows in the scope query can be deleted, so the scope is always explicit: pass Product::query() to sync a whole table. The rows of the list should belong to the scope (here, have supplier_id 5).
• The third argument lists the columns that identify a row; they need a primary or unique index. The fourth lists the columns to update on existing rows (default: every given column).
• An empty list would delete every row in the scope, so it throws unless allowEmpty: true.
• The keys are compared by the database itself, through a temporary table, so its collation decides which keys are equal. On MySQL, "ABC" and "abc" are the same key by default, and sync() won't delete a row it just updated.
• When a key appears twice in the list, the later row wins.
• The list can be any iterable, such as a generator or a LazyCollection, and is written a chunk at a time: syncing 100,000 rows into a 100,000-row table took 1.4 to 4.8 seconds and 15 MB of memory.
• Models that use SoftDeletes are soft deleted unless force: true, and soft-deleted rows that are in the list are restored. Model events are not fired.
• The database user needs permission to create temporary tables.

Import from a file or any iterable

import() reads rows one at a time and writes them a chunk at a time, so files of any size are imported with constant memory. Importing a 500,000-row CSV file used 7 MB of memory instead of the 765 MB that reading it into an array for insertRows() took, and was 30 to 45% faster.

The source can be:

• a CSV, TSV or JSON Lines file (.csv, .tsv, .jsonl / .ndjson, also gzipped as .csv.gz);
• an open stream, such as Storage::disk('s3')->readStream('users.csv'), with the format option;
• any iterable of rows: an array, a generator, a LazyCollection, or Model::cursor() to copy rows between tables or connections.

• CSV files are read the RFC 4180 way: quotes inside a field are doubled, and quoted fields can span lines. A UTF-8 BOM is removed.
• Every row needs the same columns. A row that can't be read (wrong number of fields, invalid JSON, different columns) stops the import with its row number, unless onError is given.
• Never pass a path that comes from user input.

Validate rows

Rows are checked with Laravel's validator after map and nullValues, and before transform. Rows that fail are skipped and their messages are returned by row number:

Fast imports

'fast' => true hands each chunk to the database's own bulk loader: COPY on PostgreSQL and LOAD DATA LOCAL INFILE on MySQL and MariaDB. Importing a 1,000,000-row CSV file took 6.6 s instead of 18.7 s on PostgreSQL, 8.4 s instead of 18.7 s on MySQL and 6.4 s instead of 12.6 s on MariaDB.

• Only the default insert mode, and no DB::raw() values. Enums, JSON casts and timestamps work as usual. The chunk size defaults to 10,000 rows.
• MySQL and MariaDB need LOCAL INFILE allowed on both sides: add PDO::MYSQL_ATTR_LOCAL_INFILE => true to the connection's options in config/database.php, and set local_infile = ON on the server (MySQL 8 ships with it off).
• LOAD DATA LOCAL stores bad values (a word in a number column) with a warning instead of failing. Batch checks the warnings and throws, so with the default atomic import nothing is written.
• On SQLite and other databases, fast imports as usual.

Split a large file

• A record is never cut in half. In CSV files a quoted field that spans several lines stays one record, and the header row is repeated in every part ('header' => false turns that off).
• A record bigger than bytes gets a part of its own.
• Records are copied byte for byte, so quoting and line endings don't change.
• Parts go next to the file, or into the directory option. An existing part throws, unless 'overwrite' => true. A .gz file is read and split into plain parts.

Export

export() writes the rows a query matches to a CSV, TSV or JSON Lines file, also gzipped, a chunk at a time with constant memory:

Rows are read in primary key order (DB::table() queries use id), so the query can't have a limit or offset. CSV files are written the RFC 4180 way and can be imported again with import().

Large deletes, updates and archives

Deleting or updating millions of rows in one statement locks the table for a long time and fills the undo log. These methods do it a chunk at a time. Rows are walked in primary key order (WHERE id > ? ORDER BY id LIMIT ?), which works on every database and always ends, even when the update stops rows matching the query.

The same methods are available on every Eloquent and query builder:

• All three return the number of rows they changed. sleepMs pauses between chunks, to go easy on the server and its replicas, and onChunk is called after every chunk with the running total.
• Each chunk is its own statement, so the whole operation is not one transaction.
• Models that use SoftDeletes are soft deleted by deleteInChunks(), unless force: true. Model events are not fired.
• updateInChunks() sets updated_at for Eloquent queries, and accepts DB::raw(), enums and column->key JSON paths. Arrays are only allowed for columns the model casts to JSON; use DB::raw('balance + 1') to increment.
• archive() locks each chunk and copies it with INSERT ... SELECT before deleting it, in one transaction, so no row is lost or copied twice. It copies the columns both tables have, or the columns you pass; delete: false only copies. The source rows are deleted for real, also for soft-deleting models.
• archive() to a model on another connection can't use one transaction. It copies the chunk with INSERT IGNORE first and deletes it after, so running it again after a failure is safe. The target table needs the primary key column, as a primary or unique key.
• Queries can't have a limit or offset. DB::table() queries are walked on the id column.
• Inside pretend() nothing runs: the SELECT that picks the first chunk is recorded.

Turn timestamps off

Laravel's withoutTimestamps() works with every Batch method:

Preview the SQL without running it

pretend() returns the statements Batch would run, with their bindings. Nothing is executed and no database connection is opened.

Calls made inside the callback through the facade, batch() or the HasBatch trait are recorded too.

From a model

Add the HasBatch trait:

The query-based methods are available on every Eloquent query, with or without the trait:

(new User)->updateMultipleCondition() still works, but is deprecated in favour of the static User::batchUpdateMultipleCondition() and will be removed in 4.0.

Helper

Large batches and errors

• Batches that would go over the database's limit on bound parameters are split into several queries automatically, and each UPDATE holds at most 100 rows, which benchmarks showed to be the fastest size on every supported database. Several queries run in one transaction on the model's connection, so either every row is written or none is. The exceptions are the chunked methods, which commit every chunk on purpose, and import() with 'atomic' => false.
• Invalid input (a row that isn't an array, a row without its index value, an array where a single value to match on is expected, a row with the wrong number of values, an invalid increment array) throws an InvalidArgumentException before anything is written.
• An empty list of rows does nothing: updates return 0, and insert() returns totalRows 0.

Tests

The tests run on their own, from the root of this package:

They use an in-memory SQLite database by default. To run them against MySQL, MariaDB or PostgreSQL, create an empty batch_test database and set DB_CONNECTION (plus DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD or DB_SOCKET as needed):

On MySQL the fast-import tests turn local_infile on with SET GLOBAL, so the MySQL user needs that privilege (root has it).

Donate

USDT (BSC) Address: 0xe848f4a94adb70aba2f2da92181096b18aeb269b