# Using Localize SQL

This guide covers the day-to-day API: the `collate/1,2` query macros, how locales resolve to PostgreSQL collations, and how to create collations that PostgreSQL does not provide out of the box. For background on PostgreSQL collation itself, see [Collations in PostgreSQL](https://hexdocs.pm/localize_sql/collations_in_postgres.html).

The examples here are PostgreSQL. Everything in the first four sections applies unchanged to SQLite by importing [Localize.Ecto.SQLite3](https://hexdocs.pm/localize_sql/Localize.Ecto.SQLite3.html) instead — the same locale resolves to the same collation name on both databases. See [Collations in SQLite](https://hexdocs.pm/localize_sql/collations_in_sqlite.html) for the setup and the differences, the largest being that SQLite needs no migration for the collation types covered below.

## Setup

Import `Localize.Ecto` wherever you build queries, alongside `Ecto.Query`:

```elixir
import Ecto.Query
import Localize.Ecto
```

On SQLite, import `Localize.Ecto.SQLite3` instead, and load the `localize_icu` extension as described in [Collations in SQLite](https://hexdocs.pm/localize_sql/collations_in_sqlite.html).

## Collating a query expression

`collate/2` wraps any string-valued query expression in a `COLLATE` clause for a locale. It can appear anywhere Ecto accepts a query expression — `order_by`, `select`, `where`, `distinct`, `group_by`:

```elixir
from p in Product, order_by: collate(p.name, "sv"), select: p.name
```

The generated SQL orders by `p0."name" COLLATE "sv-x-icu"`. The collation name is emitted through Ecto's `literal/1` fragment, so it is always a quoted identifier and never interpolated text.

`collate/1` does the same using the current locale, read from `Localize.get_locale/0` at the time the query is built:

```elixir
Localize.put_locale("da")
from p in Product, order_by: collate(p.name)
```

The locale argument accepts anything `Localize.validate_locale/1` accepts — a string, an atom, or a `Localize.LanguageTag` — and runtime values may be pinned, which reads naturally in query syntax:

```elixir
def sorted_products(locale) do
  from p in Product, order_by: collate(p.name, ^locale)
end
```

An invalid locale raises when the query is built, not when it runs.

## Collating a comparison

Passing a comparison to `collate/1,2` collates the comparison itself, which is how PostgreSQL expresses "compare these two values under this collation":

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

This emits `p0."name" < 'münchen' COLLATE "de-x-icu"` — `COLLATE` binds tighter than any operator, so it governs the comparison. All six comparison operators are supported: `<`, `<=`, `>`, `>=`, `==` (emitted as `=`) and `!=` (emitted as `<>`).

## How locales resolve to collations

Resolution is implemented by `Localize.Ecto.Collation.collation_for/2` and follows the [CLDR Language Matching](https://www.unicode.org/reports/tr35/tr35.html#LanguageMatching) algorithm via `Localize.LanguageTag.best_match/3`. The requested locale is matched against the set of ICU collation locales PostgreSQL provides, and the closest match wins:

```elixir
iex> Localize.Ecto.Collation.collation_for!("en-GB")
"en-GB-x-icu"

iex> Localize.Ecto.Collation.collation_for!("de-DE")
"de-x-icu"

iex> Localize.Ecto.Collation.collation_for!("zh-TW")
"zh-Hant-x-icu"

iex> Localize.Ecto.Collation.collation_for!("und")
"und-x-icu"
```

Two things are worth noting about these results. First, a match to a broader locale — `de-DE` matching `de`, or `zh-TW` matching `zh-Hant` — selects an identical collator, because CLDR collation tailorings are defined per language and script, never per territory. Second, `und` (the undetermined locale) resolves to `und-x-icu`, the ICU root collation, which is also the fallback of last resort for locales with no meaningful match.

Resolutions are cached, so the cost of matching is paid once per distinct locale.

## Using a collation name directly

When you know exactly which collation you want — one you created in a migration, or a server collation outside the bundled snapshot — pass its name with the `:collation` option and resolution is bypassed entirely:

```elixir
from p in Product, order_by: collate(p.name, collation: "german_phonebook")
```

## Collation types and migrations

Locales can carry a BCP 47 collation type in their `-u-co-` extension: `de-u-co-phonebk` is German phonebook order, `zh-u-co-stroke` is Chinese stroke-count order. PostgreSQL does not preload collations for these, so they must be created once per database. That belongs in a migration:

```elixir
defmodule MyApp.Repo.Migrations.AddPhonebookCollation do
  use Ecto.Migration

  import Localize.Ecto.Migration

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

`create_collation/2` is reversible — rolling back drops the collation. The default name is exactly the name the resolver produces for the same locale, so after the migration runs, queries need no configuration:

```elixir
iex> Localize.Ecto.Collation.collation_for!("de-u-co-phonebk")
"de-u-co-phonebk-x-icu"
```

```elixir
from p in Product, order_by: collate(p.name, "de-u-co-phonebk")
```

The difference is real: standard German sorts `Mueller, Muller, Müller` while phonebook order treats `ü` as `ue` and sorts `Mueller, Müller, Muller`.

Custom names and other ICU tailorings are supported through options, using the same vocabulary as `Localize.Collation.Options`:

```elixir
create_collation("de-u-co-phonebk", name: "german_phonebook")

# Case-insensitive: secondary strength defaults to a
# nondeterministic collation, the only mode in which case
# variants actually compare equal
create_collation("und", strength: :secondary)

# Natural sort: "file2" before "file10"
create_collation("en", numeric: true)
```

Query-time resolution carries the same `-u-` keywords, so a tailored collation created under its default name is found automatically — `collate(f.name, "en-u-kn-true")` after `create_collation("en", numeric: true)`. See [Collations in PostgreSQL](https://hexdocs.pm/localize_sql/collations_in_postgres.html) for the caveats around nondeterministic collations.

## Matching against a different server

The bundled snapshot of collation locales comes from PostgreSQL 17. If your server provides a different set, query it and pass the result with `:available`:

```elixir
locales =
  MyApp.Repo
  |> Ecto.Adapters.SQL.query!("SELECT colllocale FROM pg_collation WHERE collprovider = 'i'")
  |> Map.fetch!(:rows)
  |> List.flatten()

Localize.Ecto.Collation.collation_for!("de-AT", available: locales)
```

## Indexes

An `ORDER BY ... COLLATE` clause only uses an index created with the same collation. If a collated sort is on a hot path, add a matching index in a migration with `Localize.Ecto.Migration.collated/2`, which builds a collated column expression for `Ecto.Migration.index/3`:

```elixir
import Localize.Ecto.Migration

create index("products", [collated(:name, "de")], name: :products_name_de)

create index("products", [collated(:name, collation: "german_phonebook")], name: :products_name_phonebook)
```

With such an index in place, `order_by: collate(p.name, "de")` is satisfied directly by an index scan with no sort step. All of `index/3`'s options compose as usual — `unique: true`, `concurrently: true`, `where:` and so on. Passing an explicit `:name` is recommended, since Ecto derives index names poorly from expression columns.

One maintenance note: ICU collation data occasionally changes between ICU releases. If a PostgreSQL upgrade links a newer ICU, PostgreSQL warns of a collation version mismatch, and indexes built with that collation should be rebuilt — `REINDEX INDEX products_name_de` followed by `ALTER COLLATION "de-x-icu" REFRESH VERSION` clears the warning. This is uncommon, and it is the trade-off accepted by scoping linguistic collation to explicit expressions rather than the database default.