Mastering I18n in Golang with PostgreSQL’s JSONB

A comprehensive Guide

In the increasingly interconnected digital world, creating applications that cater to a global audience is paramount. Internationalization (i18n) is the key to unlocking this potential, enabling you to adapt your content seamlessly to different languages and regions. This in-depth guide will walk you through building a robust i18n system in Go, leveraging the power of PostgreSQL’s versatile jsonb data type.

article cover

Why PostgreSQL and jsonb for i18n?

PostgreSQL is a remarkable database that effortlessly bridges the relational and NoSQL paradigms. Its jsonb data type is a shining example of this flexibility, allowing you to store and query structured data in JSON format while enjoying the benefits of a robust relational database.

Here’s why jsonb is a perfect fit for i18n:

• Flexibility: Store translations in a single database field, eliminating the need for numerous locale-specific columns. This simplifies your database schema and makes it easier to add or modify translations.
• Performance: jsonb offers exceptional performance for querying JSON data. PostgreSQL provides specialized operators and functions for efficiently extracting and manipulating data within jsonb fields. Additionally, you can create GIN (Generalized Inverted Index) indexes on jsonb columns to accelerate complex queries.
• Ease of Use: PostgreSQL’s JSON functions streamline working with jsonb data. You can easily access specific elements, search for values, and modify JSON structures using intuitive SQL syntax.

Designing Your i18n Data Model

Let’s imagine a versatile translation system for a content-driven application, such as a blog platform. Our primary entity will be a BlogPost, which will store translations as JSON objects within a jsonb field called translations.

Example: Blog Post Entity

package entities

type BlogPost struct {
    ID        uuid.UUID                 `gorm:"primary_key;type:uuid"`
    CreatedAt time.Time                 `gorm:"not null"`
    UpdatedAt time.Time                 `gorm:"not null"`
    Translations Translation[Content] `gorm:"type:jsonb"`
    // ... other fields (e.g., author, categories)
}

type Content struct {
    Title       string `json:"title"`
    Slug        string `json:"slug"`
    Description string `json:"description"`
    Body        string `json:"body"`
}

In this model, the translations field will store a map-like structure where keys represent locales (e.g., "en", "fr", "es") and values are Content structs containing the translated title, slug, description, and body of the blog post.

Integrating with GORM (Custom Data Type)

GORM, a popular Go ORM, provides a mechanism for defining custom data types, simplifying database interactions. Let’s adapt your Translation[T] type to seamlessly integrate with GORM:

package entities

type Locale string

const (
 LocaleTr Locale = "tr"
 LocaleEn Locale = "en"
)

type Translation[T any] map[Locale]T

// Value return json value, implement driver.Valuer interface
func (m Translation[T]) Value() (driver.Value, error) {
 if m == nil {
  return nil, nil
 }
 ba, err := m.MarshalJSON()
 return string(ba), err
}

// Scan scan value into Jsonb, implements sql.Scanner interface
func (m *Translation[T]) Scan(val interface{}) error {
 if val == nil {
  *m = make(Translation[T])
  return nil
 }
 var ba []byte
 switch v := val.(type) {
 case []byte:
  ba = v
 case string:
  ba = []byte(v)
 default:
  return errors.New(fmt.Sprint("Failed to unmarshal JSONB value:", val))
 }
 t := map[Locale]T{}
 rd := bytes.NewReader(ba)
 decoder := json.NewDecoder(rd)
 decoder.UseNumber()
 err := decoder.Decode(&t)
 *m = t
 return err
}

// MarshalJSON to output non base64 encoded []byte
func (m Translation[T]) MarshalJSON() ([]byte, error) {
 if m == nil {
  return []byte("null"), nil
 }
 t := (map[Locale]T)(m)
 return json.Marshal(t)
}

// UnmarshalJSON to deserialize []byte
func (m *Translation[T]) UnmarshalJSON(b []byte) error {
 t := map[Locale]T{}
 err := json.Unmarshal(b, &t)
 *m = Translation[T](t)
 return err
}

// GormDataType gorm common data type
func (m Translation[T]) GormDataType() string {
 return "Translation[T]"
}

// GormDBDataType gorm db data type
func (Translation[T]) GormDBDataType(db *gorm.DB, field *schema.Field) string {
 switch db.Dialector.Name() {
 case "sqlite":
  return "JSON"
 case "mysql":
  return "JSON"
 case "postgres":
  return "JSONB"
 case "sqlserver":
  return "NVARCHAR(MAX)"
 }
 return ""
}

func (jm Translation[T]) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
 data, _ := jm.MarshalJSON()
 switch db.Dialector.Name() {
 case "mysql":
  if v, ok := db.Dialector.(*mysql.Dialector); ok && !strings.Contains(v.ServerVersion, "MariaDB") {
   return gorm.Expr("CAST(? AS JSON)", string(data))
  }
 }
 return gorm.Expr("?", string(data))
}

This custom type handles the conversion between Go’s map[Locale]T and PostgreSQL's jsonb format, making it effortless to work with translated data in your Go application.

Querying Translated Data with GORM

PostgreSQL’s JSON operators and GORM’s expressiveness combine to enable powerful queries on translated data.

1. Filtering by Title (Case-Insensitive):

func GetPostsByTitle(db *gorm.DB, locale, searchTerm string) ([]BlogPost, error) {
    var posts []BlogPost
    err := db.Model(&BlogPost{}).Where(
        "translations ->> ? ILIKE ?", locale, "%"+searchTerm+"%",
    ).Find(&posts).Error
    return posts, err
}

This query retrieves all blog posts where the title in the specified locale contains the searchTerm, using the case-insensitive ILIKE operator.

2. Fetching by Slug:

func GetPostBySlug(db *gorm.DB, locale, slug string) (*BlogPost, error) {
    var post BlogPost
    err := db.Model(&BlogPost{}).Where(
        "translations ->> ? ->> 'slug' = ?", locale, slug,
    ).First(&post).Error
    return &post, err 
}

This query fetches a single blog post where the slug in the specified locale matches the given slug.

Optimizing with Indexes

To ensure optimal performance when querying your translated data, it’s crucial to create a GIN (Generalized Inverted Index) on the translations field:

CREATE INDEX blog_posts_translations_idx ON blog_posts USING GIN (translations);

This index allows PostgreSQL to quickly identify which rows contain specific keys or key-value pairs within the jsonb data, significantly speeding up your queries.

jsonb Operators Demystified:

Conclusion and Recommendations

PostgreSQL’s jsonb data type, combined with GORM's flexibility and PostgreSQL's powerful JSON operators, provides a robust foundation for building scalable and efficient i18n systems in Go. By following the patterns and techniques outlined in this guide, you can empower your applications to seamlessly cater to a global audience, enhancing user experience and engagement across diverse linguistic and cultural landscapes.

Remember that effective i18n involves more than just translating text. Consider factors like locale-specific formatting, date/time conventions, and cultural nuances to provide a truly localized experience.

As you embark on your i18n journey, keep in mind these additional recommendations:

• Caching: For high-traffic applications, consider caching frequently accessed translations to improve performance.
• Data Validation: Implement robust validation to ensure the accuracy and consistency of your translated content.
• Locale Fallbacks: Devise a fallback mechanism to gracefully handle missing translations, ensuring a smooth user experience even when translations are incomplete.
• Translation Management: Explore tools and libraries for managing translations, especially as your content grows and evolves.

By embracing these best practices and leveraging the power of PostgreSQL and Go, you can build applications that resonate with users worldwide, fostering a more inclusive and accessible digital ecosystem.