i’m using htmx and sqlmodel to filter and return data via fastapi.

the data i’m filtering are episodes, which have many-to-many relationships with gurus which each have a name

htmx search input

<div class="search">
    <p>Filter By Guru</p>
    <form>
        <div class="form-input">
            <input type="hidden" name="search_kind" value="guru">
            <input class="form-control" type="search"
                   name="search_str" placeholder="Begin Typing To Filter..."
                   hx-post="/eps/get_eps/"
                   hx-trigger="input changed delay:500ms, search"
                   hx-target="#search-results">
        </div>
    </form>
</div>

a search query arrives at my fastapi route with a SearchKind and a search_str

in the case of SearchKind == ‘guru’ i first filter for gurus that contain the search_str, then thanks to sqlmodel use simple python to get the related episodes.

from sqlalchemy import func
from sqlmodel import select

stmt = select(Guru).where(
    func.lower(Guru.name).like(f'%{search_str.lower()}%')
)
matching_gurus = session.exec(stmt).all()
matching_episodes = {ep for guru in matching_gurus for ep in guru.episodes}
return sorted(list(matching_episodes), key=lambda ep: ep.date, reverse=True)

i haven’t used sqlalchemy directly very much, but am using func to enable case-insensitive searches in combination with a sqlmodel Session. is it ok to mix and match sqlmodel and sqlalchemy like this? i mean it works fine….

also can i reduce this to a single db call for more efficiency? is there a sqlmodel way to search case-insensitive? i feel like the db is a better place to do these operations, but maybe sqlmodel is doing that under the hood anyway?

and more importantly, does sqlmodel implicitly shield me from sql injection? even when using the sqlalchemy func?

and finally, any other problems / improvements you can see?

thanks so much!