backend/core/database.py

from sqlmodel import SQLModel, create_engine
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
import os

# Using local Postgres.app
# Format: postgresql+asyncpg://user:password@host/dbname
# Postgres.app usually defaults to the current user with no password
user = os.getenv("USER", "postgres")
DATABASE_URL = f"postgresql+asyncpg://{user}:@localhost/geoquery"

engine = create_engine(
    DATABASE_URL,
    echo=True, 
    future=True,
    poolclass=NullPool  # Disable pooling for asyncpg if needed, or adjust
)

# Async Engine for AsyncPG
from sqlalchemy.ext.asyncio import create_async_engine
async_engine = create_async_engine(DATABASE_URL, echo=True, future=True)

async def get_session() -> AsyncSession:
    async_session = sessionmaker(
        async_engine, class_=AsyncSession, expire_on_commit=False
    )
    async with async_session() as session:
        yield session

async def init_db():
    async with async_engine.begin() as conn:
        # await conn.run_sync(SQLModel.metadata.drop_all)
        await conn.run_sync(SQLModel.metadata.create_all)