Quick start#


SQLAlchemy-Searchable is available on PyPI. It can be installed using pip:

pip install SQLAlchemy-Searchable

SQLAlchemy-Searchable requires Python 3.8 or newer, either the cPython or PyPy implementation.


The first step to enable full-text search functionality in your app is to configure SQLAlchemy-Searchable using make_searchable() function by passing it your declarative base class:

from sqlalchemy.orm import declarative_base
from sqlalchemy_searchable import make_searchable

Base = declarative_base()

Define models#

Then, add a search vector column to your model and specify which columns you want to be included in the full-text search. Here’s an example using an Article model:

from sqlalchemy import Column, Integer, String, Text
from sqlalchemy_utils.types import TSVectorType

class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    content = Column(Text)
    search_vector = Column(TSVectorType("name", "content"))

The search vector is a special column of TSVectorType data type that is optimized for text search. Here, we want the name and content columns to be full-text indexed, which we have indicated by giving them as arguments to the TSVectorType constructor.

Create and populate the tables#

Now, let’s create the tables and add some sample data. Before creating the tables, make sure to call sqlalchemy.orm.configure_mappers() to ensure that mappers have been configured for the models:

from sqlalchemy import create_engine
from sqlalchemy.orm import configure_mappers, Session

engine = create_engine("postgresql://localhost/sqlalchemy_searchable_test")
configure_mappers()  # IMPORTANT!

session = Session(engine)

article1 = Article(name="First article", content="This is the first article")
article2 = Article(name="Second article", content="This is the second article")


Performing searches#

After we’ve created the articles and populated the database, we can now perform full-text searches on them using the search() function:

from sqlalchemy import select
from sqlalchemy_searchable import search

query = search(select(Article), "first")
article = session.scalars(query).first()
# Output: First article


sqlalchemy_searchable.make_searchable(metadata, mapper=<class 'sqlalchemy.orm.mapper.Mapper'>, manager=<sqlalchemy_searchable.SearchManager object>, options={})[source]#

Configure SQLAlchemy-Searchable for given SQLAlchemy metadata object.

  • metadata – SQLAlchemy metadata object

  • options – Dictionary of configuration options

sqlalchemy_searchable.search(query, search_query, vector=None, regconfig=None, sort=False)[source]#

Search given query with full text search.

  • search_query – the search query

  • vector – search vector to use

  • regconfig – postgresql regconfig to be used

  • sort – Order the results by relevance. This uses cover density ranking algorithm (ts_rank_cd) for sorting.