Egon ORM

Appearance

Full-text search ​

Search articles by words, ranked by relevance, with Postgres's full-text search. The operators have no builder methods, so the query writes them with fragment: a tagged template whose text is written into the SQL as it is, and whose interpolations (${terms}) are bound parameters, never SQL text (Fragments).

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.

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

class Article extends Schema("articles", {
  id: t.integer().primaryKey().dbGenerated(),
  title: t.string(),
  body: t.string(),
}) {}

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

// The table, created by a migration of its own.
export const articles = defineMigration({
  up(m) {
    m.createTable("articles", (table) => {
      table.id({ type: "integer" });
      table.text("title").notNull();
      table.text("body").notNull();
    });
  },
  down(m) {
    m.dropTable("articles");
  },
});

The index ​

A generated tsvector column keeps the searchable text up to date by itself, and a GIN index makes it fast:

ts
// A generated tsvector column, and a GIN index on it.
export const searchable = defineMigration({
  up(m) {
    m.execute(fragment`
      alter table articles add column search tsvector
        generated always as (to_tsvector('english', title || ' ' || body)) stored
    `);
    m.createIndex("articles", ["search"], { using: "gin" }); // articles_search_index
  },
  down(m) {
    m.execute(fragment`alter table articles drop column search`); // drops the index with it
  },
});

The column is not a field of the schema class: the database computes it, and the application never reads it.

Searching ​

ts
export function search(terms: string) {
  return repo.all(
    from(Article, "a")
      .where(() => fragment`search @@ websearch_to_tsquery('english', ${terms})`.as<boolean>())
      .orderBy(() => fragment`ts_rank(search, websearch_to_tsquery('english', ${terms}))`.as<number>().desc())
      .limit(20),
  );
}
  • websearch_to_tsquery reads what users type: words, "quoted phrases", or, and - to exclude. The search terms are a bound parameter.
  • .as<boolean>() gives the fragment a type, so where accepts it; .as<number>() lets orderBy sort by it.
  • Identifiers in a fragment (search here) are written as they are. For a name that comes from elsewhere, use fragment.ident(name), which checks and quotes it.

Showing where it matched ​

ts
export function searchWithSnippets(terms: string) {
  return repo.all(
    from(Article, "a")
      .where(() => fragment`search @@ websearch_to_tsquery('english', ${terms})`.as<boolean>())
      .select(({ a }) => ({
        id: a.id,
        title: a.title,
        snippet:
          fragment`ts_headline('english', ${a.body}, websearch_to_tsquery('english', ${terms}))`.as<string>(),
      })),
  );
}

ts_headline marks the matching words with <b> tags: escape the rest of the text before you render it as HTML.

The examples above run against these rows, after creating the tables:

ts
await createMigrator({
  adapter,
  migrations: [
    { version: 20261021000501, name: "articles", migration: articles },
    { version: 20261021000502, name: "searchable", migration: searchable },
  ],
}).up();
await repo.insertAll(Article, [
  { title: "Indexing tables", body: "A GIN index makes searches fast." },
  { title: "Cooking", body: "Rice, beans and searches for good spices." },
  { title: "Gardening", body: "Tomatoes need sun." },
]);

Good to know

  • This is Postgres only. SQLite has FTS5 virtual tables and MySQL FULLTEXT indexes with MATCH … AGAINST; both can be written with fragment the same way.
  • For a simple "contains" filter, ilike with an escaped pattern is enough; see Filters from a query string.

See also ​