async def test_citext_integration(sess: AsyncSession) -> None:
await sess.execute(text("create extension if not exists citext"))
Base = declarative_base(metadata=MetaData())
class MyTable(Base):
__tablename__ = "citext_table"
id = Column(Integer(), primary_key=True)
email = Column(CITEXT) # type: ignore
def __repr__(self) -> str:
return f'MyTable(id={self.id}, email="{self.email}")'
conn = await sess.connection()
await conn.run_sync(Base.metadata.create_all)
row = MyTable(id=1, email="*****@*****.**") # type: ignore
sess.add(row) # type: ignore
await sess.commit()
# Expunge the row from the session so we don't fetch it from the cache
# If you don't do this, the retrieved row will come from the local session
# and will not have any preprocessor rules applied
sess.expunge(row) # type: ignore
from_db = await (sess.execute(
select(MyTable).where(MyTable.email == "*****@*****.**")))
(res, ) = from_db.one()
assert res.id == 1
async def get_tasks_with_done(db: AsyncSession) -> List[Tuple[int, str, bool]]:
result: Result = await (db.execute(
select(
task_model.Task.id,
task_model.Task.title,
task_model.Done.id.isnot(None).label("done"),
).outerjoin(task_model.Done)))
return result.all()
async def health(session: AsyncSession = Depends(get_session)):
try:
await asyncio.wait_for(session.execute("SELECT 1"), timeout=1)
except (asyncio.TimeoutError, socket.gaierror):
return Response(status_code=503)
return Response(status_code=204)