Internationalization (i18n)

pgbo has built-in support for translated fields. Translation tables are auto-generated, and views can resolve translations via LEFT JOIN.

Setup

import { configureI18n } from '@pgbo/core/schema'

configureI18n({
  localeTable: 'locale',       // table holding supported locales
  localeColumn: 'code',        // column with locale codes ('en', 'de', ...)
  fallbackLocale: 'en',        // fallback when requested locale has no translation
})

Built-in localeCode Domain

pgbo provides a localeCode domain: text(2) matching ^[a-z]{2}$. Include it in your migration definitions:

import { localeCode } from '@pgbo/core/schema'

const schema = {
  domains: [localeCode],
  // ...
}

Translation Tables

Declare translated fields on a table:

const area = table('area', {
  columns: {
    slug: text().notNull(),
    tenantId: integer(),
    sortOrder: integer().default(0),
  },
  primaryKey: ['slug', 'tenantId'],
  translations: ['name'],
})

This auto-generates area_translation:

CREATE TABLE area_translation (
  slug text NOT NULL,
  tenant_id integer,
  locale locale_code NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (slug, tenant_id, locale),
  FOREIGN KEY (slug, tenant_id) REFERENCES area(slug, tenant_id) ON DELETE CASCADE
);

The translation table is available at area.translationTable and is automatically included in migrations.

Views with Translations

Use translated() in a view's column selection to auto-join the translation table:

import { view, col, translated } from '@pgbo/core/schema'

const areaView = view('area_view').from(area).columns({
  slug: col('slug').filterable().immutable(),
  sortOrder: col('sortOrder'),
  name: translated('name').label('crud.name').searchable(),
})

Generated DDL:

CREATE VIEW area_view AS
SELECT area.slug, area.sort_order, area_translation.name
FROM area
LEFT JOIN area_translation ON area.slug = area_translation.slug AND area.tenant_id = area_translation.tenant_id

Querying by Locale

To filter by locale, add a WHERE condition on the translation table's locale column:

SELECT a.slug, t.name
FROM area a
LEFT JOIN area_translation t ON a.slug = t.slug AND t.locale = 'de'

For fallback (use 'en' when 'de' is missing):

SELECT a.slug, COALESCE(t.name, fb.name) AS name
FROM area a
LEFT JOIN area_translation t ON a.slug = t.slug AND t.locale = 'de'
LEFT JOIN area_translation fb ON a.slug = fb.slug AND fb.locale = 'en'