24. Localization in Databases. Approaches to Storing Localized Data in a Database. Caching Database Tables

Video version
(Leave your feedback on YouTube)

Separate Column for Each Language

For each localized field, separate columns are created in the table, for example, title_en, title_ua. This is a very straightforward approach, and it works.

However, it has several drawbacks:

  • When adding a new language, you will have to create numerous migrations for each entity and each localized column.
  • Managing a table with many additional columns for different languages is inconvenient.

I recommend using this approach in very limited cases, when you have few entities and do not plan to scale the project.

Separate Table with Localization Texts

A separate polymorphic table is created with a field that defines a unique type of localization (title, description, etc.) and a field that specifies the locale.

Then, either by using join or with, we retrieve the necessary fields.

This option is more practical, but:

  • It requires significant query optimization, both for regular selection and for searching fields. Or you can use caching.
  • The localization table should be accessible in all models and services where a localized field may exist. Alternatively, it can be moved to a separate service, which further increases the need for optimization.
  • There's an option to create a separate table or tables for each entity that requires translation. This isolates the data within the module, but sorting and searching will still be done via join, so optimization remains an issue.
  • The column with the text will usually have a Text type, which will cover any translation options, but it has an impact on optimization.

This option looks much better than separate columns. At the very least, it scales more easily.

Be cautious with ready-made solutions, especially in high-load projects.

JSON

This is straightforward — we store the localized field as JSON (jsonb for Postgres).

example
{
  "en": "Title in English",
  "ua": "Заголовок українською"
}

This is my choice. It allows you to store any number of languages, implement unique fallback logic, and most importantly, isolate all data within the entity. Of course, there are downsides, such as optimization when sorting and searching for a localized field.

I strongly recommend paying attention to the functionality of ready-made solutions. Often, they retrieve all localization variants for every action, which is not ideal if you have many languages.

For reliable optimization, I implemented my own solution for Laravel Eloquent.

I formatted the available locales in an Enum. This makes it easy to manipulate languages and store the selected language or languages in the database.

My solution:

Locale Middleware in Laravel

This is straightforward: either take the locale from the header or set the default locale.

app/Http/Middleware/Localize.php
<?php

namespace App\Http\Middleware;

use App\Enums\Locale;
use Closure;
use Illuminate\Http\Request;

class Localize
{
    public function handle(Request $request, Closure $next): mixed
    {
        $locale = $request->header('X-Locale') ?? Locale::default();
        $locale = Locale::tryFrom($locale)?->value;
        if ($locale) {
            app()->setLocale($locale);
        }

        return $next($request);
    }
}

Mandatory add middleware to api calls

app/Http/Kernel.php
  protected $middlewareGroups = [
        'api' => [
            \App\Http\Middleware\Localize::class,
        ],
    ];

Caching the Entire Table (Dictionaries)

All entity lists that change infrequently (categories, tags, groups, etc.) can be considered "dictionaries". It's advisable to cache dictionaries, as this gives a significant optimization boost. The main thing is not to forget to clear the cache when updating, deleting, or adding records to the dictionary.

As for eager loading, fully utilizing the cache here won’t work; you'll still need to query the database to retrieve the data, and query builders don't work with caches, and there's not much sense in it if the query is already there.

Some dictionary get list method
 return Cache::rememberForever(Dictionary::LIST_CACHE_KEY, function () {
            return Dictionary::select([
                'id', 
                'name'
            ])
                ->get();
        });
    }

Some dictionary model
public const string LIST_CACHE_KEY = 'genre_list';

public static function boot(): void
{
    parent::boot();

    static::created(function () {
        Cache::forget(self::LIST_CACHE_KEY);
    });

    static::updated(function () {
        Cache::forget(self::LIST_CACHE_KEY);
    });

    static::deleted(function () {
        Cache::forget(self::LIST_CACHE_KEY);
    });
}

Be cautious with packages that cache every query. Remember, cache is usually stored in RAM. However, it can make sense in some situations.

Array Enums

Laravel has a built-in cast for an array of enumerations. Unfortunately, it doesn't work with Postgres. Here is an example of a cast for Postgres: