Moving from PostgreSQL to SQLite in Django: Handling Locale-Aware Sorting

#sqlite #django #localization

TL;DR

Migrating from PostgreSQL to SQLite in production is now a realistic option — even for Django apps.
Rails has already embraced this, and Django has good support too.

However, beware of string sorting based on user language.
PostgreSQL handles locale-aware collation natively, while SQLite does not.
You’ll need to manually register collations to ensure proper sorting of localized text.

Below is a working Django implementation that restores correct sorting by language when using SQLite.

👉 Full source code: https://github.com/valasek/kicoma

Introduction

When Heroku became a paid service, I migrated my Django application to Hetzner.
As part of that move, I also decided to switch the database from PostgreSQL to SQLite — primarily for simplicity and lower maintenance.

Everything worked as expected… until I noticed that s*trings were sorted incorrectly*.
For example, Czech-specific characters like č, ř, or ž appeared at the end or in unexpected positions.

This happens because SQLite uses binary (byte-based) ordering by default — it doesn’t know how to sort according to your locale.

The Fix: Locale-Aware Sorting with Custom Collations

To solve this, we can register custom locale collations in SQLite and apply them automatically based on the current Django language.

The implementation below:

Defines locale-specific collations for English and Czech
Hooks into Django ORM queries to ensure they use the correct collation
Works transparently across your models

Implementation

1. Collation Manager (project/app-name/manager.py)

from django.db import models
from django.db.models import F
from django.db.models.functions import Lower
from django.db.models.functions.comparison import Collate
from django.utils import translation


def get_collation():
    lang = translation.get_language()
    return "cs_collate" if lang == "cs" else "en_collate"


class CollatableQuerySet(models.QuerySet):
    def _resolve_ordering_field(self, field_name):
        """
        Resolve a field name to the correct field path.
        - Handles related fields (FKs).
        - Picks the first CharField/TextField in related model if needed.
        """
        parts = field_name.split("__")
        model = self.model
        resolved_parts = []

        for part in parts:
            field = model._meta.get_field(part)
            if field.is_relation and field.related_model:
                related_model = field.related_model
                string_fields = [
                    f.name for f in related_model._meta.get_fields()
                    if isinstance(f, (models.CharField, models.TextField))
                ]
                if string_fields:
                    resolved_parts.append(part)
                    resolved_parts.append(string_fields[0])
                else:
                    resolved_parts.append(part)
                    resolved_parts.append(related_model._meta.pk.name)
                model = related_model
            else:
                resolved_parts.append(part)

        return "__".join(resolved_parts)

    def with_language_ordering(self):
        collation = get_collation()
        ordering = self.model._meta.ordering or []
        if not ordering:
            return self

        exprs = []
        for field in ordering:
            desc = field.startswith("-")
            field_name = field[1:] if desc else field
            resolved_field = self._resolve_ordering_field(field_name)
            try:
                expr = Collate(F(resolved_field), collation)
            except Exception:
                expr = Lower(F(resolved_field))
            exprs.append(expr.desc() if desc else expr.asc())
        return self.order_by(*exprs)


class CollatableManager(models.Manager):
    def get_queryset(self):
        return (
            CollatableQuerySet(self.model, using=self._db)
            .with_language_ordering()
        )

    def with_language_ordering(self):
        return self.get_queryset().with_language_ordering()

2. Register Collations (project/app-name/app.py)

import locale
import logging

from django.apps import AppConfig
from django.utils.translation import gettext_lazy as _


def register_collations(conn):
    try:
        locale.setlocale(locale.LC_COLLATE, "cs_CZ.utf8")

        def collate_cs(x, y):
            return locale.strcoll(x or "", y or "")

        locale.setlocale(locale.LC_COLLATE, "en_US.utf8")

        def collate_en(x, y):
            return locale.strcoll(x or "", y or "")

        conn.create_collation("cs_collate", collate_cs)
        conn.create_collation("en_collate", collate_en)

        logging.info("SQLite collations registered successfully")

    except Exception as e:
        pass
        # logging.warning("Could not register SQLite collations: %s", e)


class KitchenConfig(AppConfig):
    name = "kicoma.kitchen"
    verbose_name = _("Kitchen")

    def ready(self):
        from django.db import connections
        from django.db.backends.signals import connection_created

        # register for all *future* connections
        connection_created.connect(
            lambda sender, connection, **kwargs: register_collations(connection.connection),
            weak=False,
        )

        # also loop over all *existing* connections and register immediately
        for conn in connections.all():
            try:
                register_collations(conn.connection)
            except Exception as e:
                import logging
                logging.warning("Could not register collations immediately: %s", e)

        try:
            import kicoma.kitchen.signals
        except ImportError:
            pass

Locale Setup in Docker (and Other Deployment Environments)

If you’re deploying inside Docker, make sure your image has the necessary locales installed and generated.
Without this step, the locale.setlocale() calls will silently fail, and sorting will fall back to ASCII order.

Here’s an example snippet for your Dockerfile:

RUN export DEBIAN_FRONTEND=noninteractive && \
    apt-get update && \
    apt-get clean && \
    apt-get install -y --no-install-recommends --no-install-suggests \
        locales && \
    sed -i '/en_US.UTF-8/s/^# //g' /etc/locale.gen && \
    sed -i '/cs_CZ.UTF-8/s/^# //g' /etc/locale.gen && \
    locale-gen && \
    echo "Locales generated successfully."

# Set locale environment variables
ENV LANG=en_US.UTF-8
ENV LC_ALL=en_US.UTF-8
ENV LANGUAGE=en_US.UTF-8

Why this matters

Without these locales, locale.setlocale() will raise an exception or do nothing.
SQLite will then perform binary string comparison, not respecting language-specific order.
This setup ensures your Django app behaves consistently in Docker and production.

You can verify the available locales inside your container:

locale -a

⸻

Caveats and Pitfalls

Thread Safety:
locale.setlocale() is not thread-safe.
For multi-threaded environments (like Gunicorn workers), use separate processes per locale or cache sorted results.
Cross-Platform Differences:
Locale behavior differs slightly between Linux, macOS, and Windows.
Always test collation behavior in your target environment.
Performance Considerations:
Collations in SQLite are applied dynamically at query time.
For large datasets or frequent sorting operations, PostgreSQL remains more efficient.

⸻

Conclusion

With custom collations and proper locale setup, SQLite can provide correct, language-aware sorting in Django.

This approach makes SQLite a lightweight, production-ready option for small to medium apps — especially when hosting costs or simplicity are priorities.

👉 Full source code: https://github.com/valasek/kicoma