Filters from a query string

Turn ?q=launch&status=published&minViews=10&sort=popular&page=2 into a query, with every parameter validated and converted, and an error response for bad input.

Setup

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

import { postgres } from "@egon-orm/postgres";
import { applyAction, cast, createRepo, from, Schema, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

class Post extends Schema("posts", {
  id: t.integer().primaryKey().dbGenerated(),
  title: t.string(),
  status: t.enum(["draft", "published"]),
  views: t.integer(),
  createdAt: t.timestamp(),
}) {}

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("posts", (table) => {
      table.id({ type: "integer" });
      table.text("title").notNull();
      table.text("status").notNull();
      table.integer("views").notNull();
      table.timestamptz("created_at").notNull();
    });
  },
  down(m) {
    m.dropTable("posts");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021000201, name: "dynamic_filters_tables", migration: tables }],
  }).up();
  await admin.close();
}

Validating the parameters

A changeset does not need a table. Give it the parameters' types, and it casts and validates them like any form:

// ?q=launch&status=published&minViews=10&sort=popular&page=2
const filterTypes = {
  q: t.string(),
  status: t.enum(["draft", "published"]),
  minViews: t.integer(),
  sort: t.enum(["newest", "popular"]).default("newest"),
  page: t.integer().default(1),
};

export function parseFilters(search: URLSearchParams) {
  const params = Object.fromEntries(search); // every value is a string; cast converts them
  const changeset = cast({ data: {}, types: filterTypes }, params, [
    "q",
    "status",
    "minViews",
    "sort",
    "page",
  ])
    .validateLength("q", { max: 100 })
    .validateNumber("page", { greaterThan: 0, lessThanOrEqualTo: 1000 });
  return applyAction(changeset, "insert"); // { success: true, data } or the changeset with its errors
}

Every value of a query string is a string; cast converts "10" to 10 and refuses "lots" for an integer.
Parameters that are not listed are ignored, so a client cannot add filters you did not plan for.
default(1) fills in page when it is missing; the others are undefined when absent.

Building the query

const escapeLike = (text: string) => text.replace(/[\\%_]/g, (c) => `\\${c}`);

export async function listPosts(search: URLSearchParams, perPage = 20) {
  const parsed = parseFilters(search);
  if (!parsed.success) return { errors: parsed.error.errorMessages() };
  const { q, status, minViews, sort, page } = parsed.data;

  const posts = from(Post, "p")
    .pipe((query) => (q === undefined ? query : query.where(({ p }) => p.title.ilike(`%${escapeLike(q)}%`))))
    .pipe((query) => (status === undefined ? query : query.where({ status })))
    .pipe((query) => (minViews === undefined ? query : query.where(({ p }) => p.views.gte(minViews))))
    .orderBy(({ p }) =>
      sort === "popular" ? [p.views.desc(), p.id.desc()] : [p.createdAt.desc(), p.id.desc()],
    )
    .limit(perPage)
    .offset((page - 1) * perPage);

  return { posts: await repo.all(posts) };
}

Each .pipe adds a clause or leaves the query as it is. The query is immutable, so the steps can also live in separate functions and be combined.

The examples below start from these rows, after creating the tables:

await createTables();
await repo.insertAll(Post, [
  { title: "Launch day", status: "published", views: 40, createdAt: new Date("2026-10-01T00:00:00Z") },
  { title: "Launch plan", status: "draft", views: 2, createdAt: new Date("2026-10-02T00:00:00Z") },
  { title: "100% done", status: "published", views: 12, createdAt: new Date("2026-10-03T00:00:00Z") },
]);

const found = await listPosts(new URLSearchParams("q=launch&status=published"));
const invalid = await listPosts(new URLSearchParams("page=0&minViews=lots"));

invalid.errors is { minViews: ["is invalid"], page: ["must be greater than 0"] }, ready for a 400 response.

Good to know

undefined in the object form throws. .where({ status }) with status undefined would match every row, so it is refused; add the clause only when there is a value, as above.
Escape like patterns. A user's % or _ would otherwise be a wildcard.
Sorting by a column name from the request: map the allowed names to expressions, as sort does, or use field(p, name), which checks the name against the schema.
To build a condition before the query exists, see dynamic in Conditions built at runtime.

Pagination

Top N per parent