Search

• Introduction
• Declaring Search Fields
  • Search on SQLite
  • Indexes as Methods
• Full-Text Search
  • Query Syntax
  • Differences Between Databases
• Trigram Similarity
• Vector Search
  • Nearest Neighbours
• Hybrid Search

Introduction

You can search a model's rows by text, by similarity to a misspelt word, or by the distance between embedding vectors. Each kind of search is written for the database the query runs on.

The table shows how text search, trigram similarity and vector search (pgvector's) are written on each database:

	Postgres	MySQL	SQLite
text	tsvector, websearch_to_tsquery, GIN	MATCH … AGAINST, FULLTEXT	an FTS5 table <table>_fts, kept by triggers
rank	ts_rank	relevance	-bm25(…), the document's weights per column
headline	ts_headline	a substring about the first word	snippet() / highlight()
trigrams	pg_trgm (%, similarity, GIN/GiST)	LIKE	similarity in Go, unindexed
vectors	pgvector (<->, <=>, <#>, HNSW/IVFFlat)	stored, not searched	distances in Go, unindexed (O(n))

NOTE

A program developed on SQLite searches the same way on Postgres in production — with indexes there.

Declaring Search Fields

Search attributes on a model's fields declare what migrations make. This model has a document, a trigram index and an embedding:

type Article struct {
	orm.Model[Article]
	ID        int64
	Title     string
	Body      string
	Search    orm.TSVector
	Embedding orm.Vector
}

func (a *Article) Schema(s *orm.Schema) {
	s.Field(&a.Title, orm.Char{Max: 200}, orm.Trigram{})
	s.Field(&a.Body, orm.LongText{})
	s.Field(&a.Search, orm.SearchOf{Fields: []string{"title:A", "body:B"}, Config: "english"}, orm.FullText{Config: "english"})
	s.Field(&a.Embedding, orm.Dims(3), orm.HNSW{Metric: orm.Cosine, M: 16})
}

// MySQL matches a document by a FULLTEXT index of its columns.
func (Article) Indexes() []orm.Index { return []orm.Index{orm.FullTextIndex("title", "body")} }

Attribute (a value given to s.Field)
@orm.SearchOf{Fields, Config}	on an orm.TSVector field: a column generated from the fields, each with its weight ("title:A")
@orm.FullText{Config}	a full-text index of the field (GIN on Postgres)
@orm.Trigram	a trigram index (pg_trgm GIN on Postgres)
@orm.Dims(n)	an orm.Vector's dimensions
@orm.HNSW{Metric, M, EfConstruction}, @orm.IVFFlat{Metric, Lists}	a pgvector index; Metric is orm.L2, orm.Cosine or orm.IP

Search on SQLite

On SQLite a document becomes an external-content FTS5 table (articles_fts) over its fields, with triggers keeping it in step; migrations make and drop it like an index. An english config stems with FTS5's porter tokenizer. A model with no document is searched by LIKE on SQLite, unindexed.

Indexes as Methods

The same indexes can be written as an Indexes() method (orm.GinIndex("search"), orm.FullTextIndex(…), orm.HnswIndex("embedding").Ops(orm.Cosine), orm.GinIndex("title").Trigram()) and a Generated() method for the TSVector column.

Full-Text Search

orm.SearchVector(fields…) is a document of fields; orm.Match is its condition and orm.SearchRank its relevance, an expression to order by. This fragment finds and ranks articles:

doc, q := orm.SearchVector(ArticleFields.Title.Name(), ArticleFields.Body.Name()), orm.SearchQuery("go command")
found, err := orm.Objects[Article]().Filter(orm.Match(doc, q)).Order(orm.SearchRank(doc, q).Desc()).All(ctx)

Query Syntax

The query is web search syntax: every word must match, "a phrase" in order, or between alternatives, -word must not, and word* matches words starting with it — go -java "web server" or rust*. The text is always a bound argument; on SQLite it is rewritten into an FTS5 query of quoted phrases, never FTS5 syntax of the user's.

The functions are:

orm.SearchVector(fields…), .Weight("A"), .Config("english"), .Add(doc)	a document
orm.SearchQuery(text)	the query
orm.Match(doc, q)	the condition
orm.SearchRank(doc, q)	the rank, an OrderedExpr[float64]
orm.Headline(field, q)	an excerpt with the words marked

Differences Between Databases

Where the databases differ:

• Stopwords. Postgres's english drops its stopwords from queries; SQLite drops the same list from an english document's queries. MySQL drops InnoDB's, and words shorter than innodb_ft_min_token_size.
• Exclusions only. -java alone matches the rows without it on Postgres and SQLite; MySQL's boolean mode matches nothing.
• Ranking. ts_rank weighs A 1, B 0.4, C 0.2, D 0.1; SQLite passes the same weights to bm25, which also normalises by length.
• Headlines. orm.Marks, orm.MaxWords (at most 64 on SQLite) and orm.HighlightAll map onto ts_headline's options and onto snippet/highlight.

Trigram Similarity

orm.Similarity(field, text) is how alike a field is to a text, from 0 to 1 — for typos and near matches. This fragment finds titles close to a phrase:

like, err := orm.Objects[Article]().Annotate("sim", orm.Similarity(ArticleFields.Title.Name(), "go tools")).
	Filter(orm.Q{"sim__gt": 0.3}).OrderBy("-sim").All(ctx)

Vector Search

orm.Vector is a []float32 column. orm.L2Distance, orm.CosineDistance and orm.InnerProduct (negated, as pgvector's <#>: smaller is nearer) are OrderedExpr[float64]s, so they filter and order:

v, emb := orm.Vector{0.9, 0.1, 0}, ArticleFields.Embedding.Name()
near, err := orm.Objects[Article]().Filter(orm.CosineDistance(emb, v).Lt(0.3)).
	Order(orm.CosineDistance(emb, v).Asc()).All(ctx)

Nearest Neighbours

Nearest is the k-nearest-neighbour query: ordered by distance, nearest first, annotated distance:

top, err := orm.Objects[Article]().Nearest(emb, v, orm.Cosine).Limit(10).All(ctx)

On Postgres the ORDER BY is pgvector's operator, so an HNSW or IVFFlat index of that metric serves it. Options: a metric, orm.K(n), orm.MaxDistance(d), orm.DistanceAs(name), orm.EfSearch(n) (hnsw.ef_search) and orm.Probes(n) (ivfflat.probes), the last two set with SET LOCAL in a transaction of the query's own:

top, err = orm.Objects[Article]().Nearest(emb, v, orm.K(10), orm.MaxDistance(0.4), orm.EfSearch(100)).All(ctx)

WARNING

MySQL stores vectors but doesn't search them: a VECTOR(n) column where the server has the type (MySQL 9, MariaDB 11.7), else VARBINARY(4n) holding the same little-endian float32s, so an upgraded server converts it in place. Vector indexes are left out there, and a distance, Nearest or Hybrid fails when the query is written, naming the field.

Hybrid Search

Hybrid ranks rows by a text match and by a vector distance and fuses the ranks by reciprocal rank (a row scores w/(k + rank) per ranking), annotated rank, distance and score, best first. It runs on Postgres and on SQLite:

best, err := orm.Objects[Article]().Hybrid(doc, orm.SearchQuery("web server"), emb, v,
	orm.K(10), orm.Candidates(100), orm.HybridWeights(1, 0.5), orm.RRF(60)).All(ctx)

Candidates(n) narrows the vector side to the n nearest (found by the index) plus the text's matches. orm.Fuse("-rank", "distance") is the fusion on its own, over any two orderings by name.