Using Computed Columns in Laravel

MySQL and SQLite (from version 3.31.0) support the generated column definitions. Let’s see how can we use computed columns in our database schema and in what scenarios should we add them to our migrations.

Virtual and Stored Columns

Basically there are two types of computed columns: virtual and stored. The main difference between the two is virtual is getting computed every time when the user runs the query but it does not take any space, however, the stored takes some space for its data, but it will be updated every time when the row is getting inserted or updated. In brief: virtual is “smaller” but “slower”, stored is “bigger” but faster.

Let’s see some SQL, how to create computed columns:

drop table if exists users;

create table users (
    id int auto_increment primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    salary int(10) not null,
    name varchar(101) as (concat(first_name, ' ', last_name)),
    insurance int(10) as (salary * 0.1) stored
);

As we can see, we can generate columns based on other columns in the row. In some scenarios, this can be very handy, especially if we want to make these computations automatic.

Need a reliable web application developer? We can help you achieve your goals with Laravel and Vue.js.

Computed Columns in the Migration Schema

Now, let’s take a look at how can we add computed columns to our Laravel migration.

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->integer('price');
    $table->integer('tax')->virtualAs('price * 0.27')->nullable();
    $table->integer('discount')->storedAs('price - 100')->nullable();
});

The great thing here is,  it’s really easy to create computed columns in our migrations. This also means we can easily append these to our models and no need to add computed properties to the models themselves.

Summary

Using generated columns is a great approach in some cases. It’s up to you and the situation if you want to use the virtual or the stored way, but both of them offer a nice functionality and potentially can make the code smaller and cleaner in the framework itself.