DEV Community

Cover image for MySQL invisible columns and how to define it with Laravel and Doctrine
Matheus Lopes Santos
Matheus Lopes Santos

Posted on

MySQL invisible columns and how to define it with Laravel and 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;
Enter fullscreen mode Exit fullscreen mode

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

alter table employees alter column ssn set visible;
Enter fullscreen mode Exit fullscreen mode

Querying Data

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

Querying all columns

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

Querying all columns with ssn

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();
    });
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

Now, we have an additional security layer to prevent the exposure of our sensitive data!

That's all folks! See you on the next post πŸ™‚ πŸ‘πŸ»

Top comments (3)

Collapse
 
mauriciomueller profile image
Mauricio Mueller

I believe this is also a good case for columns that use json or blob and you want to avoid from select all queries.

Collapse
 
mhraihan profile image
Mahmudul Haque Raihan

if I want to query it from Laravel, how can I do it?

Collapse
 
devlopez profile image
Matheus Lopes Santos

Hello, my friend. In this case, you should specify the field in the query. For example:

Employee::find(1, ['*', 'ssn']);
Enter fullscreen mode Exit fullscreen mode

πŸ™‚πŸ‘πŸ»