rits-tecnologia/eloquent-insert-on-conflict

Laravel Insert On Conflict

This package provides macros to run INSERT ... ON CONFLICT DO NOTHING or DO UPDATE SET queries on models with Laravel's ORM Eloquent using PostgreSQL.

Installation

Install this package with composer.

composer require rits-tecnologia/eloquent-insert-on-conflict

If you don't use Package Auto-Discovery yet add the service provider to your Package Service Providers in config/app.php.

InsertOnConflict\InsertOnConflictServiceProvider::class,

Usage

Models

Call insertOnConflict from a model with the array of data to insert in its table.

$data = [
    ['id' => 1, 'name' => 'name1', 'email' => 'user1@email.com'],
    ['id' => 2, 'name' => 'name2', 'email' => 'user2@email.com'],
];

User::insertOnConflict($data);

Customizing the ON CONFLICT DO UPDATE clause

Update only certain columns

If you want to update only certain columns, pass them as the 2nd argument.

User::insertOnConflict([
    'id'    => 1,
    'name'  => 'new name',
    'email' => 'foo@gmail.com',
], ['name'], 'do update set', 'id');
// The name will be updated but not the email.

Update with custom values

You can customize the value with which the columns will be updated when a row already exists by passing an associative array.

In the following example, if a user with id = 1 doesn't exist, it will be created with name = 'created user'. If it already exists, it will be updated with name = 'updated user'.

User::insertOnConflict([
    'id'    => 1,
    'name'  => 'created user',
], ['name' => 'updated user'], 'do update set', 'id');

The generated SQL is:

INSERT INTO `users` (`id`, `name`) VALUES (1, "created user") ON CONFLICT (id) DO UPDATE SET `name` = "updated user"

You may combine key/value pairs and column names in the 2nd argument to specify the columns to update with a custom literal or expression or with the default VALUES(column). For example:

User::insertOnConflict([
    'id'       => 1,
    'name'     => 'created user',
    'email'    => 'new@gmail.com',
    'password' => 'secret',
], ['name' => 'updated user', 'email'], 'do update set', 'id');

will generate

INSERT INTO `users` (`id`, `name`, `email`, `password`)
VALUES (1, "created user", "new@gmail.com", "secret")
ON CONFLICT (id) DO UPDATE SET `name` = "updated user", `email` = EXCLUDED.`email`