SQLAlchemy Searchable

Common errors

• sqlalchemy.exc.ProgrammingError: type "tsvector" does not exist

  cause This error typically occurs when using `sqlalchemy-searchable` with PostgreSQL without the `sqlalchemy_utils` package installed or without explicitly defining the `TSVectorType` column in your model, or if the PostgreSQL database itself is missing the required language configuration or `pg_trgm` extension.
  fix Install `sqlalchemy-utils` (`pip install sqlalchemy-searchable[sqlalchemy_utils]`) and consider defining a `TSVectorType` column in your model (e.g., `search_vector = Column(TSVectorType('column1', 'column2'))`). Ensure your PostgreSQL database has the necessary extensions enabled (e.g., `CREATE EXTENSION pg_trgm;`) and relevant language dictionaries configured.

• AttributeError: 'Query' object has no attribute 'search'

  cause This error means that the `.search()` method was not added to your SQLAlchemy query object. This usually happens if `make_searchable(Base.metadata)` was not called, or if it was called *after* your models were defined.
  fix Verify that `make_searchable(Base.metadata)` is called correctly and, crucially, that it is executed *before* any of your SQLAlchemy models that use `__searchable__` are defined.

• No module named 'sqlalchemy_searchable'

  cause The `sqlalchemy-searchable` library is not installed in your current Python environment.
  fix Install the package using pip: `pip install sqlalchemy-searchable`.

Warnings

• breaking Version 3.0.0 drops support for PostgreSQL 11, 12, and 13. Users on these older PostgreSQL versions must upgrade their database or stick to `sqlalchemy-searchable < 3.0.0`.
  Fix: Upgrade your PostgreSQL database to version 14 or newer, or downgrade `sqlalchemy-searchable` to a 2.x version (e.g., `pip install 'sqlalchemy-searchable<3'`).
• breaking Version 2.0.0 dropped support for Python 3.6, 3.7 and SQLAlchemy 1.3. Your application must use Python 3.8+ and SQLAlchemy 1.4+ (or 2.x) to use `sqlalchemy-searchable >= 2.0.0`.
  Fix: Upgrade your Python environment to 3.8 or newer and SQLAlchemy to 1.4 or newer. Alternatively, pin `sqlalchemy-searchable` to a version less than 2.0.0 (e.g., `pip install 'sqlalchemy-searchable<2'`).
• gotcha `sqlalchemy-searchable`'s advanced full-text search capabilities, particularly with `tsvector` types and linguistic features, are most robust and performant when used with PostgreSQL. While it can work with other databases, their FTS support might be limited or rely on generic string matching.
  Fix: For production full-text search, it is highly recommended to use PostgreSQL as your database. Ensure `psycopg2-binary` is installed and configure your PostgreSQL database with appropriate extensions (e.g., `pg_trgm`) and language dictionaries.
• gotcha The `make_searchable(Base.metadata)` function must be called *before* defining your SQLAlchemy models. If called afterwards, the models will not be properly instrumented for search capabilities, and you might encounter `AttributeError`.
  Fix: Ensure that `make_searchable(Base.metadata)` is executed immediately after `Base = declarative_base()` and before any `class MyModel(Base):` definitions.

Install

• pip install sqlalchemy-searchable Install core library
• pip install sqlalchemy-searchable[sqlalchemy_utils] Install with SQLAlchemy-Utils (recommended for TSVectorType)
• pip install sqlalchemy-searchable[psycopg2-binary] Install with PostgreSQL driver (recommended for PostgreSQL)

Imports

• make_searchable

  from sqlalchemy_searchable import make_searchable

  The primary function to integrate full-text search into your SQLAlchemy metadata.
• SearchQueryMixin

  from sqlalchemy_searchable import SearchQueryMixin

  Used for custom query classes if you need to extend search behavior.

Quickstart

This quickstart demonstrates how to integrate `sqlalchemy-searchable` into a basic SQLAlchemy application. It sets up a simple `Document` model, makes it searchable using `make_searchable`, and then performs a basic full-text search query. Note that while this example uses SQLite for simplicity, `sqlalchemy-searchable`'s full potential, especially for advanced full-text search, is realized with PostgreSQL.

import os
from sqlalchemy import create_engine, Column, Integer, String, Text
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy_searchable import make_searchable
# from sqlalchemy_utils import TSVectorType # Often used for explicit search vector column type

# Database setup (using in-memory SQLite for simplicity)
# For PostgreSQL, use 'postgresql://user:password@host:port/database'
engine = create_engine(os.environ.get('SQLALCHEMY_DATABASE_URL', 'sqlite:///:memory:'))
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

# IMPORTANT: Call make_searchable BEFORE defining your models
make_searchable(Base.metadata)

class Document(Base):
    __tablename__ = 'document'
    # __searchable__ defines which columns contribute to the search vector
    __searchable__ = ['title', 'content']

    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    content = Column(Text)
    # For PostgreSQL, you might define an explicit TSVectorType:
    # search_vector = Column(TSVectorType('title', 'content'))

    def __repr__(self):
        return f"<Document(id={self.id}, title='{self.title}')>"

Base.metadata.create_all(engine)

# Add some data
doc1 = Document(title="Python Programming Basics", content="Learn the fundamentals of Python, including variables, data types, and control flow.")
doc2 = Document(title="Advanced SQLAlchemy Techniques", content="Explore advanced features of SQLAlchemy like custom types, events, and performance tuning.")
doc3 = Document(title="Web Development with Flask", content="Build web applications efficiently using the Flask microframework and Jinja2 templates.")

session.add_all([doc1, doc2, doc3])
session.commit()

# Perform searches
print("Searching for 'Python':")
results_python = session.query(Document).search('Python').all()
for doc in results_python:
    print(f"- {doc.title}")

print("\nSearching for 'web applications':")
results_web = session.query(Document).search('web applications').all()
for doc in results_web:
    print(f"- {doc.title}")

session.close()