Two languages in one row: { en, mn } columns in Postgres

This site is in English and Mongolian. The blog started with one row per post and a language column. That looked simple until an article existed in both languages: two slugs, two publish dates, two covers, and nothing tying them together.

One row, both languages

Now every text column is a { en, mn } object in jsonb, with an empty string where a language has nothing yet:

export const posts = snakeCase.table('posts', {
  id,
  slug: text().notNull(),
  title: jsonb().$type<Localized<string>>().notNull(),
  excerpt: jsonb().$type<Localized<string>>().notNull().default({ en: '', mn: '' }),
  content: jsonb().$type<Localized<unknown[]>>().notNull().default({ en: [], mn: [] }),
  contentHtml: jsonb().$type<Localized<string>>().notNull().default({ en: '', mn: '' }),
  readingMinutes: integer().notNull().default(1)
  // …
})

One slug, one cover, one publish date. A translation is just the other key filled in.

Which languages does a post have?

That is not a column. It is derived: a language counts when its title is not empty.

languages: LOCALES.filter((locale) => post.title[locale])

A stored list could drift from the content. A derived one cannot.

Reading with a fallback

The website asks for the visitor’s language and falls back to the other one, so nothing disappears:

/** The text in `locale`, or in the other language when that one is empty. */
export const pickLocalized = (text: Localized<string> | null | undefined, locale: Locale) =>
  text?.[locale] || LOCALES.map((other) => text?.[other] ?? '').find(Boolean) || ''

The blog has no language filter. Every post is listed, and switching the language changes the text, not the list. A post shown in the other language says so in its small line.

Querying jsonb

Plain SQL stays readable:

select slug, title ->> 'mn' as title
from posts
where is_published and title ->> 'mn' <> ''
order by published_at desc;

The edge cases

Reading time comes from the longer of the two bodies.

An update sends the whole { en, mn } object, and may not empty the title in both languages.

The size limit checks both languages’ content together, so a post cannot double its budget.

The slug comes from the English title, or the Mongolian one when English is empty.

When not to do this

Two or three fixed languages fit in one object. Ten languages, or languages added by users, belong in a translations table with one row per language and a foreign key.