Text search is often a core feature of web APIs. Postgres and SQLAlchemy can fill the need without having to add additional technology (such as ElasticSearch or Solr). We can lean on Postgres’ pg_trgm to provide “good enough” search in early stages of API development.

We will be adding the trigram index on a text column for quicker searches.

We are using Alembic and SQLAlchemy for the database migration. The table name is books. The column that will be searchable is title.

From the above link, the Postgres documentation tells us:

These index types support … trigram-based index searches for LIKE, ILIKE, ~ and ~* queries.

So let’s implement a search with ILIKE in Flask-SQLAlchemy:

Happy Coding!