Who is this article for?
Anyone looking for how to validate unique database column with Laravel Soft-Delete options.
In this article, I’m going to walk through, how to create migration with indexes, create two separate requests for creating and updating a resource with one column as unique and using soft-delete.
Before You Begin
In this article, we are going to create a new vendor table via migration. The table will have
deleted_at columns. The
name column will be unique. Which means that vendor name should be unique. But this unique validation should exclude the records that are soft-deleted by Laravel.
The API routes will look something like this -
Step 1 – Create the migration.
As you can see in the migration, rather than having the
name as a unique column, we are using
deleted_at as composite unique keys.
Step 2 - Create a Store Vendor Request Class
We will be using the Laravel Requests to validate the input sent by frontend before creating a new resource.
This request will look into the database where
deleted_at is null and then compare the names with the name that was sent via the request.
Behind the scene, Laravel runs this SQL query -
SELECT COUNT(*) AS aggregate FROM vendors WHERE name = "CVS" -- This is a test name sent by the request and deleted_at IS NULL;
Step 3 - Create an Update Vendor Request Class
So, our save request validation look good, what about the update request. A user can update the name of the vendor too. So now we need to create a validation for the update.
As you can see above in the API route for an update, we are passing the
id of the vendor and laravel resolve it to object. We can leverage the object in the request validation and use it like -
Laravel fires the following SQL Query to validate the update request name param.
SELECT COUNT(*) AS aggregate FROM vendors WHERE name = "CVS" AND id <> 1 AND deleted_at IS NULL;
And so this is how you can use unique column along with soft-deletes in Laravel.
I know, this article was supposed to be how to test a multi-database Laravel project using SQLite in-memory DB for all databases, I will be covering that in a future article.