API Reference

All functions below are exported from each dialect entry point (drizzle-i18n/pg, drizzle-i18n/mysql, drizzle-i18n/sqlite) unless noted otherwise.

Schema helpers

translationTable(parent, columns, opts?)

Strategy: Separate table

Generates a companion translations table, a unique constraint on (parentId, locale), and Drizzle relations for both directions.

const productI18n = translationTable(products, {
  name: text("name").notNull(),
  description: text("description"),
});

Parameters:

Returns: An object with:

jsonTranslations(fields)

Strategy: JSON column

Generates locale-map columns to spread into a table definition. Each key produces a column with that same name (no suffix).

const categories = pgTable("categories", {
  id: serial("id").primaryKey(),
  ...jsonTranslations({
    name: { notNull: true },
    description: {},
  }),
});

Parameters:

Returns: An object of Drizzle column builders typed as Record<string, string>, ready to spread.

Query helpers

forLocale(column, locale, opts?)

Strategy: JSON column

Extracts a single locale value from a JSON locale-map column. Returns a flat SQL<string | null> expression, not the full JSON object.

db.select({
  name: forLocale(categories.name, "ar", { fallback: "en" }),
}).from(categories);

Parameters:

withTranslation(db, parent, i18nResult, opts)

Strategy: Separate table

Builds a locale-aware LEFT JOIN query. The locale predicate is in the ON clause, so parent rows always appear (translatable fields are null when missing).

const rows = await withTranslation(db, products, productI18n, {
  locale: "ar",
  fallback: "en",
}).where(eq(products.id, 1));

Returns flat rows with scalar translatable fields. Chainable -- call .where(), .limit(), .orderBy(), etc. on the result.

Parameters:

localizeResults(rows, i18nResult, opts)

Strategy: Separate table

Post-processes relational query results (from db.query with with: { translations: true }). Removes the translations array and lifts the matching locale's fields to the top level.

const rows = await db.query.products.findMany({ with: { translations: true } });
const localized = localizeResults(rows, productI18n, { locale: "fr", fallback: "en" });

Parameters:

missingTranslations(db, parent, i18nResult, locale)

Strategy: Separate table

Returns parent rows that have no translation for the given locale. Uses LEFT JOIN + IS NULL.

const missing = await missingTranslations(db, products, productI18n, "fr");

Parameters:

orderByLocale(column, locale, direction?)

Strategy: JSON column

Generates an ORDER BY expression that sorts by a specific locale's value within a JSON column.

db.select().from(categories).orderBy(orderByLocale(categories.name, "en"));
db.select().from(categories).orderBy(orderByLocale(categories.name, "en", "desc"));

Parameters:

Mutation helpers

insertWithTranslations(db, parent, i18nResult, data)

Strategy: Separate table

Inserts a parent row and its translation rows in a single transaction. If any insert fails, the entire operation is rolled back.

const product = await insertWithTranslations(db, products, productI18n, {
  values: { sku: "MUG-01", price: 15 },
  translations: {
    en: { name: "Mug", description: "Ceramic mug" },
    ar: { name: "كوب", description: "كوب سيراميك" },
  },
});

Parameters:

Returns: The inserted parent row values including the generated primary key.

upsertTranslation(db, i18nResult, data)

Strategy: Separate table

Inserts or updates a single locale row using ON CONFLICT DO UPDATE on the (parentId, locale) unique constraint.

await upsertTranslation(db, productI18n, {
  product_id: 1,
  locale: "fr",
  name: "Telephone",
});

Parameters:

setTranslations(db, i18nResult, data)

Strategy: Separate table

Upserts translation rows for multiple locales at once. Only the provided locales are written. Within each locale, only the provided columns are included in the upsert SET clause -- omitted fields are not set to NULL.

await setTranslations(db, productI18n, {
  product_id: 1,
  translations: {
    en: { name: "Smartphone Pro" },
    fr: { name: "Telephone Pro", description: "Description en francais" },
  },
});

Parameters:

updateLocale(db, table, column, opts)

Strategy: JSON column

Patches a single locale key inside a JSON column using the database's native JSON set function. Other locale keys are untouched. Handles NULL columns by coalescing with an empty object.

await updateLocale(db, categories, categories.name, {
  where: eq(categories.id, 1),
  locale: "es",
  value: "Electronica",
});

Parameters:

Batch helpers

exportTranslations(rows, i18nResult, fkKey, localeKey?)

Strategy: Separate table

Converts flat translation table rows into a grouped format keyed by parent ID and locale.

const rows = await db.select().from(productI18n.table);
const exported = exportTranslations(rows, productI18n, "product_id");
// => { 1: { en: { name: "Phone" }, ar: { name: "هاتف" } } }

Parameters:

importTranslations(data, fkKey, localeKey?)

Strategy: Separate table

The inverse of exportTranslations(). Converts a grouped locale-keyed format into flat rows for bulk insert.

const data = { 1: { en: { name: "Phone" }, ar: { name: "هاتف" } } };
const rows = importTranslations(data, "product_id");
await db.insert(productI18n.table).values(rows);

Parameters:

Factory

createI18n(config)

Strategy: Both

Creates a locale-scoped wrapper object that re-exports all helpers with strict locale typing. See Strict Locales for full details.

const i18n = createI18n({
  defaultLocale: "en",
  locales: ["en", "ar", "fr"] as const,
  strict: true,
});

Parameters:

Returns: An object with all helpers (translationTable, jsonTranslations, forLocale, withTranslation, localizeResults, upsertTranslation, setTranslations, updateLocale, insertWithTranslations) -- each with locale parameters narrowed to the configured union.