MySQL invisible columns and how to define it with Laravel and Doctrine

#mysql #php #laravel #doctrine

Many developers don't know that MySQL now has support for invisible columns.

But what exactly is an invisible column?

When it arrives?

This attribute is available on MySQL since v8.0.23. According to the documentation:

An invisible column is normally hidden to queries, but can be accessed if explicitly referenced. Prior to MySQL 8.0.23, all columns are visible.

The syntax to create an invisible column is very simple:

create table employees (
    id int not null primary key auto_increment,
    name varchar(45) not null,
    ssn char(11) not null INVISIBLE
) ENGINE = InnoDB;

Certainly, we can use DDL to switch a column between visible and invisible.

alter table employees alter column ssn set visible;

Querying Data

Now, what happens if I execute select * from employees?

Wow, where is my ssn column? Well, it's hidden 🙂! To retrieve that column, we need to explicitly specify it in our query:

This is a good approach to exclude columns that don't need to be displayed in our query, such as those containing sensitive data.

Generating an invisible column with a Laravel Migration

If you wish to utilize this feature in a Laravel application, you can define your column as follows:

public function up(): void
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('name', 45);
        $table->char('ssn', 11)->invisible();
    });
}

Generating an invisible column with Doctrine

I've defined an entity called Employee with the following configuration

#[ORM\Entity]
#[ORM\Table(name: 'employees')]
class Employee
{
    #[ORM\Id]
    #[ORM\Column(type: 'integer')]
    #[ORM\GeneratedValue]
    private int | null $id;

    #[ORM\Column(type: 'string', length: 45)]
    private string $name;

    #[ORM\Column(columnDefinition: 'char(11) INVISIBLE')]
    private string $ssn;

    // The other methods of this entity
}