Upsert Function In Laravel

Posted by

In Laravel, “upsert” refers to the combination of “insert” and “update” operations within a single database query. The “upsert” functionality allows you to insert a new record into a database table if it doesn’t exist, or update an existing record if it does.

The upsert() function in Laravel provides a convenient way to perform this operation. It is available in Laravel’s query builder and Eloquent ORM.

Syntax:

The basic syntax for using the upsert() function in Laravel is as follows:

Using Query Builder:

DB::table('table_name')->upsert($data, ['column1', 'column2'], ['column3']);

Using Eloquent:

ModelName::upsert($data, ['column1', 'column2'], ['column3']);

Function:

The upsert() function accepts three parameters:

  1. $data: It represents the data to be inserted or updated. It can be an array or a collection containing the values for the columns.
  2. $uniqueKeys: It specifies the columns that define the uniqueness of a record. If a record already exists with the same values in these columns, it will be updated; otherwise, a new record will be inserted.
  3. $updateColumns: It represents the columns to be updated when a record already exists. Only these columns will be updated; the rest of the columns will remain unchanged.

The upsert() function performs the following steps:

  • It checks if a record already exists based on the values in the unique key columns.
  • If a record exists, it updates the specified columns with the provided values.
  • If a record doesn’t exist, it inserts a new record with the provided values.

The upsert() function is particularly useful in scenarios where you want to avoid performing separate checks for existence and then performing inserts or updates accordingly. It helps streamline the database operations and improves performance by reducing the number of queries.

By utilizing the upsert() function, you can efficiently handle the insertion and updating of records in a single database query, simplifying your code and improving the database interaction in your Laravel application.