Localize_ecto - locale-aware Postgres collation for Ecto queries

localize_ecto is the forgotten member of the elixir-localize family. When I announced Localize_mcp yesterday I had forgotten that I hadn’t actually published this either!

The most common database text sorting is usually byte or code-point order - Zebra before apple, öl after zebra - which isn’t a great experience for developers or users.

Postgres, however, ships the full set of ICU collations, and localize_ecto connects them to Ecto
with a collate/1,2 query macro that applies a COLLATE clause using the collation that best matches a Localize locale,

Sorting in ORDER BY

import Ecto.Query
import Localize.Ecto

# Sort by the current locale (Localize.get_locale/0)
from p in Product, order_by: collate(p.name)

# Or an explicit locale - runtime values pin as usual
from p in Product, order_by: collate(p.name, "sv")
# SELECT ... ORDER BY p0."name" COLLATE "sv-x-icu"`

The same data sorts differently per locale, as it should. German orders ä with a; Swedish puts it after z:

de: ["Apfel", "Äpfel", "öl", "Zebra"]
sv: ["Apfel", "Zebra", "Äpfel", "öl"]

Any valid locale finds its best available collation — "zh-TW" resolves to Postgres predefined zh-Hant-x-icu, "de-DE" to de-x-icu, "pt-BR" to pt-BR-x-icu.

Collating in SELECT and WHERE

collate/2 works anywhere a query expression is accepted, and it also collates comparisons like Postgres’s a < b COLLATE "de-x-icu" form:

# A collated expression in a select
from p in Product, select: collate(p.name, "de")

# A collated comparison in a select
from p in Product, select: collate(p.name < p.brand, "de")
# SELECT p0."name" < p0."brand" COLLATE "de-x-icu"

# And in a where clause`
from p in Product, where: collate(p.name < "münchen", "de"), select: p.name

German phonebook order, via a migration

Locales can carry a collation type in their -u-co- extension — German phonebook order treats ü as ue. PostgreSQL doesn’t predefine these, so localize_ecto provides reversible migration helpers:

defmodule MyApp.Repo.Migrations.AddPhonebookCollation do
  use Ecto.Migration
  import Localize.Ecto.Migration

  def change do
    create_collation("de-u-co-phonebk")
  end
end

After the migration, the locale just works in queries:

from p in Person, order_by: collate(p.surname, "de-u-co-phonebk")

If collated with the default collator would return ["Mahler", "Mueller", "Muller", "Müller"] but with the phonebook collator returns ["Mahler", "Mueller", "Müller", "Muller"].

More migration examples

# A custom name, used in queries with collate(p.name, collation: "german_phonebook")
create_collation("de-u-co-phonebk", name: "german_phonebook")`

# Natural sort: file1, file2, file10 — instead of file1, file10, file2`
create_collation("und-u-kn", name: "natural_sort")`

# Case-insensitive equality: 'HELLO' = 'hello' without citext or lower()`
create_collation("und-u-ks-level2", name: "case_insensitive", deterministic: false)`

# A collated index, so ORDER BY ... COLLATE is an index scan, not a sort`
create index("products", [collated(:name, "de")], name: :products_name_de)`

Notes

• Postgres only.
• The collations are deterministic (PostgreSQL’s default), so normalize your Unicode text (NFC) for expected equality semantics — the README has the details.
• Two guides ship with the docs: Using Localize Ecto and Collations in PostgreSQL — the latter covers choosing a database default collation (short version: builtin C.UTF-8 on PostgreSQL 17+, C before that, and COLLATE in queries and indexes for linguistic behaviour).