Courses

Structuring Databases in Laravel 11

JSON Column Type: in Which Scenarios?

Summary of this lesson:
- Understanding JSON column usage
- Evaluating performance implications
- Managing flexible data structures
- When to use JSON vs relationships

Now, let's discuss JSON column type in the database. Because it is fully supported in Laravel, you can create a migration file using the JSON column type. And why or why not you should use that?

Typically, the JSON field is used for a flexible structure, with a high probability of that structure being changed. In other words, you need a field for some properties, but you don't precisely know what properties will be there in the future. To avoid creating a new database table or changing the structure in the future, you just save properties as a flexible JSON.

Let's take a look at examples.


Example 1: Spatie Medialibrary

The first example is from a well-known spatie/laravel-medialibrary package.

Part of this package to upload the images is conversions. A conversion is any variant of the image, thumbnail, crop thumbnail, etc.

You define conversions with width, height, and other parameters, and it saves a separate file performing all the conversions.

use Illuminate\Database\Eloquent\Model;
use Spatie\MediaLibrary\MediaCollections\Models\Media;
use Spatie\MediaLibrary\HasMedia;
use Spatie\MediaLibrary\InteractsWithMedia;
 
class YourModel extends Model implements HasMedia
{
use InteractsWithMedia;
 
public function registerMediaConversions(?Media $media = null): void
{
$this->addMediaConversion('thumb')
->width(368)
->height(232)
->sharpen(10);
}
}

But in the database, in the media table for that package, we have a column generated_conversions where the conversion name and whether it's generated are saved.

Conversions can have many names, and each Model that can have some media can have conversions with different names. So, this case is perfect for the JSON column type.

The conversions are defined in the Model.

use Spatie\Image\Enums\Fit;
use Spatie\MediaLibrary\MediaCollections\Models\Media;
 
class User extends Authenticatable implements HasMedia
{
// ...
 
public function registerMediaConversions(Media $media = null): void
{
$this
->addMediaConversion('thumb')
->fit(Fit::Crop, 50, 50)
->nonQueued();
 
$this
->addMediaConversion('preview')
->fit(Fit::Crop, 120, 120)
->nonQueued();
}
}

In the database, we have conversion names in a JSON format.

Now, it has its disadvantages. First, it's not a relational database. So, there are no relationships, and it's hard to ensure 100% that the data is correct. You don't easily see what's inside.

Next, there could be performance issues. You can perform operations like finding where JSON is and searching inside that JSON, but of course, it is slower.


Example 2: Personal Experiment

As an experiment, I made a simple project with a list of...

The full lesson is only for Premium Members.
Want to access all 18 lessons of this course? (81 min read)

You also get:

  • 69 courses (majority in latest Laravel 11)
  • Premium tutorials
  • Access to repositories
  • Private Discord