Skip to content

Full-Text Search

Model.search() runs a real full-text query — not a LIKE '%...%' scan — using whichever full-text engine your database already ships: SQLite's FTS5, or Postgres's tsvector/GIN indexes. No new dependency, no separate search service to run and keep in sync.

Setup

Declare which columns are searchable on the model:

# app/Models/post.py
from sqlalchemy import String, Text
from sqlalchemy.orm import Mapped, mapped_column
from zeython import Model

class Post(Model):
    __tablename__ = "posts"
    __searchable__ = ("title", "body")

    title: Mapped[str] = mapped_column(String(255))
    body: Mapped[str] = mapped_column(Text)

Then create the matching index once, from a migration — Model.search() queries an index, it doesn't create one for you:

zeython db revision -m "add posts search index"

# migrations/versions/xxxx_add_posts_search_index.py
from alembic import op
from zeython.search import create_fts5_index  # SQLite

def upgrade() -> None:
    create_fts5_index(op, "posts", ["title", "body"])

def downgrade() -> None:
    from zeython.search import drop_fts5_index
    drop_fts5_index(op, "posts")

zeython db migrate

That's it — search away:

results = await Post.search("async orm")

SQLite: create_fts5_index()

Creates an external-content FTS5 virtual table (posts_fts) plus three triggers that keep it in sync with posts on every INSERT/UPDATE/DELETE — including writes that don't go through Model at all (a raw INSERT, a script, another process), since the triggers live in the database itself, not in Python. Backfills every existing row when the migration runs.

Model.search() queries it with SQLite's MATCH operator, ordered by FTS5's own built-in rank column (best matches first) — no extra configuration.

Postgres: create_tsvector_index()

Adds a search_vector tsvector column, GENERATED ALWAYS ... STORED from the given columns, plus a GIN index on it. Postgres keeps a generated column in sync automatically on every write — no triggers needed here.

from zeython.search import create_tsvector_index

def upgrade() -> None:
    create_tsvector_index(op, "posts", ["title", "body"])

Model.search() queries it with plainto_tsquery()/@@, ordered by ts_rank(). language defaults to "english" on both the index and the query side — pass a different one to both create_tsvector_index(..., language="french") and set Post.__search_language__ = "french" if you need it, and keep them in sync.

Excluding soft-deleted rows

search() excludes soft-deleted rows by default, the same as all()/ find_by() — pass include_deleted=True to include them:

await Post.search("draft", include_deleted=True)

Multi-tenant apps

A model that declares a tenant_id column (see Multi-Tenancy) gets search() scoped to current_tenant_id(), the same as find/all/find_by/paginate — no separate opt-in needed.

Limiting results

await Post.search("async orm", limit=10)  # default 20

query is always treated as plain text

Pass through whatever a search box actually receives — a stray quote, a hyphenated word, a colon, a lone AND — and it's searched for as literal text, not parsed as a query mini-language:

await Post.search("well-known movie")   # matches "well-known" and "movie"
await Post.search("what's new?")        # the apostrophe/quote is just text
await Post.search("")                   # [] -- nothing to search for, no query run

Both engines are safe here for the same reason (each does its own thing to get there): Postgres's plainto_tsquery() already always treats its input as plain text; on SQLite, Model.search() quotes each whitespace-separated term as an FTS5 string literal before it reaches MATCH, so a raw query string can never be parsed as FTS5's own operator syntax (AND/OR/NOT, col: filters, unbalanced "/() and crash the request with a syntax error the way it would unquoted.

What this isn't

Model.search() is a thin, honest dispatch over each database's own full-text SQL — not a search engine. It doesn't give you: relevance tuning beyond what rank/ts_rank compute, typo tolerance/fuzzy matching, faceted search, or a shared ranking model across SQLite and Postgres (each engine ranks its own way). For requirements like those, or for an index too large for your primary database to carry alongside everything else, point a dedicated search service (Elasticsearch, Meilisearch, Typesense) at the same data instead — Model.search() covers the common case of "let me search this table" without a second service to run, not every case.

MySQL isn't supported yet (Model.search() raises a clear RuntimeError naming the dialect) — its own full-text indexes work differently enough from SQLite/Postgres's that adding a third dispatch branch is deliberately left for when someone actually needs it, not implemented speculatively.

API reference

See zeython.search for the full API, and Model.search() alongside the rest of Model.