Safe Sorting & Full-Text Search¶

Sorting — Whitelist the Columns¶

Sorting takes a column name from the client. As we saw in Chapter 7, you cannot parameterize an identifier, so you must whitelist:

10 lines

1const SORTABLE = {
2  created: "createdAt",
3  due: "dueDate",
4  title: "title",
5} as const;
6
7const sortKey = SORTABLE[req.query.sort] ?? "createdAt";
8const dir = req.query.dir === "asc" ? "asc" : "desc";
9
10prisma.task.findMany({ orderBy: { [sortKey]: dir } });

Anything not in the map falls back to a safe default. Never feed a raw client string into ORDER BY.

Stable Sorts Need a Tiebreaker¶

If two rows share a createdAt, their order is undefined and can flip between requests — breaking pagination. Add a unique tiebreaker:

1 line

1orderBy: [{ createdAt: "desc" }, { id: "desc" }]

Search: LIKE vs Full-Text¶

For simple "contains":

1 line

1where: { title: { contains: q, mode: "insensitive" } } // ILIKE '%q%'

ILIKE '%q%' works but can't use a normal index on big tables. For real search, use PostgreSQL full-text search:

sql

7 lines

1-- one-time: a generated, indexed search column
2ALTER TABLE tasks ADD COLUMN search tsvector
3  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || coalesce(description,''))) STORED;
4CREATE INDEX tasks_search_idx ON tasks USING GIN (search);
5
6-- query
7SELECT * FROM tasks WHERE search @@ plainto_tsquery('english', $1);

This handles stemming ("running" matches "run"), ranking, and stays fast via the GIN index.

When You Outgrow the Database¶

For fuzzy matching, typo tolerance, and relevance tuning at scale, dedicated engines (Elasticsearch, Meilisearch, Typesense, Postgres + pg_trgm) take over. Start with database search; reach for an engine when search becomes a core feature.