Fixing SQLite ORDER BY When Numeric IDs Are Stored as Text: Use CAST for Correct Sorting (with SQLAlchemy)

SQLite can sort numeric IDs lexically when stored as TEXT, returning wrong rows. Learn to enforce numeric ORDER BY with CAST and SQLAlchemy for correct results

When SQLite stores numeric identifiers as text, ORDER BY stops being numeric and becomes lexical. The result is counterintuitive: an id like 100567 sorts ahead of 15470, because strings are compared alphabetically, not by value. If your selection logic depends on the smallest numeric id, you will fetch the wrong row.

Repro: the model and the query

Consider a minimal ORM mapping and a query that selects a city by name and returns the first row by id. The logic looks correct at a glance.

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import Integer, String, select

class BaseModel(DeclarativeBase):
    pass

class TownRecord(BaseModel):
    __tablename__ = "city"
    uid: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    name_label: Mapped[str] = mapped_column(String, index=True)

def build_stmt(input_name: str):
    stmt = (
        select(TownRecord)
        .where(TownRecord.name_label == input_name.upper())
        .order_by(TownRecord.uid)
        .limit(1)
    )
    return stmt

With data like the following, the query may return the row with the larger textual id instead of the smaller numeric one:

15470  Paris
100567 Paris

What actually goes wrong

In SQLite, if the id column is stored as TEXT rather than INTEGER, ORDER BY applies string comparison rules. That means '100567' sorts before '15470' because the first character '1' ties, the second character '0' is less than '5', and so on. The declared type in your ORM model does not change how SQLite sorts if the underlying storage ends up being text.

Fix: force numeric ordering

The most direct, database-level fix is to cast the sorting expression to an integer, ensuring numeric ordering regardless of how the data is stored.

SELECT id, city_name
FROM city
WHERE city_name = 'Paris'
ORDER BY CAST(id AS INTEGER)
LIMIT 1;

The same idea applies cleanly with SQLAlchemy by wrapping the ORDER BY expression in a cast. The selection logic remains identical; only the ordering expression changes.

from sqlalchemy import cast, Integer, select

def build_stmt_fixed(input_name: str):
    stmt = (
        select(TownRecord)
        .where(TownRecord.name_label == input_name.upper())
        .order_by(cast(TownRecord.uid, Integer))
        .limit(1)
    )
    return stmt

Why this detail matters

Sorting semantics control which row you pick when you limit a result set. If your application assumes numeric ordering but the database applies lexical ordering, subtle bugs appear in production: wrong entities fetched, inconsistent pagination, and hard-to-reproduce behavior. The mismatch is especially tricky because the ORM type annotation can suggest one thing, while the actual storage type in SQLite drives the real ordering behavior.

Takeaways

When ordering by identifiers or any numeric-looking field in SQLite, ensure the database treats the sort key as a number. Casting in ORDER BY is a precise fix when the stored values are text. If you see unexpected orderings where '100567' precedes '15470', verify the column’s storage and make the ordering explicit by casting to INTEGER.