import logging import time from contextlib import contextmanager from datetime import datetime from uuid import UUID, uuid4 import psycopg from psycopg import errors as pg_errors from psycopg.rows import dict_row from psycopg_pool import ConnectionPool, PoolTimeout import settings from errors import bad_request, conflict, not_found, unavailable log = logging.getLogger("messenger.db") pool = ConnectionPool( conninfo=settings.DATABASE_URL, min_size=settings.DB_POOL_MIN, max_size=settings.DB_POOL_MAX, timeout=settings.DB_POOL_WAIT_SECONDS, kwargs={ "connect_timeout": settings.DB_CONNECT_TIMEOUT_SECONDS, "row_factory": dict_row, "options": f"-c statement_timeout={settings.DB_STATEMENT_TIMEOUT_MS}" " -c idle_in_transaction_session_timeout=10000 -c TimeZone=UTC", }, check=ConnectionPool.check_connection, open=False, ) @contextmanager def connection(wait_seconds: float | None = None): """Соединение из пула. В конце блока COMMIT, при исключении ROLLBACK. Если база недоступна, клиент получает 503 без подробностей. """ try: with pool.connection(timeout=wait_seconds) as conn: yield conn except (psycopg.OperationalError, PoolTimeout) as exc: log.warning("PostgreSQL is unavailable: %s", type(exc).__name__) raise unavailable() from None def ping() -> None: with connection(wait_seconds=1) as conn: conn.execute("SELECT 1") MIGRATIONS_LOCK = 20260202 def run_migrations() -> list[str]: files = sorted(settings.MIGRATIONS_DIR.glob("*.sql")) applied = [] with psycopg.connect(settings.DATABASE_URL, autocommit=True, connect_timeout=settings.DB_CONNECT_TIMEOUT_SECONDS) as conn: conn.execute("SELECT pg_advisory_lock(%s)", (MIGRATIONS_LOCK,)) try: conn.execute("""CREATE TABLE IF NOT EXISTS schema_migrations ( version text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now())""") done = {row[0] for row in conn.execute("SELECT version FROM schema_migrations")} for path in files: if path.name in done: continue with conn.transaction(): conn.execute(path.read_text(encoding="utf-8")) conn.execute("INSERT INTO schema_migrations (version) VALUES (%s)", (path.name,)) applied.append(path.name) finally: conn.execute("SELECT pg_advisory_unlock(%s)", (MIGRATIONS_LOCK,)) return applied def wait_and_migrate(max_wait_seconds: int = 60) -> None: """При запуске стенда PostgreSQL может стартовать позже API: ждём его, а не падаем.""" deadline = time.monotonic() + max_wait_seconds while True: try: applied = run_migrations() log.info("Migrations applied: %s", ", ".join(applied) if applied else "none (schema is up to date)") return except psycopg.OperationalError: if time.monotonic() > deadline: raise log.warning("PostgreSQL is not ready yet, retry in 1 s") time.sleep(1) USER_COLUMNS = "id, username, display_name, created_at" def create_user(username: str, display_name: str, password_hash: str) -> dict: try: with connection() as conn: return conn.execute( f"""INSERT INTO users (id, username, display_name, password_hash) VALUES (%s, %s, %s, %s) RETURNING {USER_COLUMNS}""", (uuid4(), username, display_name, password_hash), ).fetchone() except pg_errors.UniqueViolation: raise conflict("Username already exists") from None def get_user(user_id: UUID) -> dict | None: with connection() as conn: return conn.execute(f"SELECT {USER_COLUMNS} FROM users WHERE id = %s", (user_id,)).fetchone() def get_user_with_password(username: str) -> dict | None: with connection() as conn: return conn.execute( f"SELECT {USER_COLUMNS}, password_hash FROM users WHERE username = %s", (username,) ).fetchone() CHAT_COLUMNS = """c.id, c.title, c.created_at, ARRAY(SELECT m.user_id FROM chat_members m WHERE m.chat_id = c.id ORDER BY m.user_id) AS member_ids""" def create_chat(title: str, member_ids: list[UUID]) -> dict: chat_id = uuid4() members = sorted(member_ids) try: with connection() as conn: with conn.transaction(): row = conn.execute( "INSERT INTO chats (id, title) VALUES (%s, %s) RETURNING id, title, created_at", (chat_id, title), ).fetchone() with conn.cursor() as cur: cur.executemany( "INSERT INTO chat_members (chat_id, user_id) VALUES (%s, %s)", [(chat_id, member_id) for member_id in members], ) except pg_errors.ForeignKeyViolation: raise bad_request("Unknown member") from None return {**row, "member_ids": members} def get_chat(chat_id: UUID, user_id: UUID) -> dict: with connection() as conn: chat = conn.execute( f"""SELECT {CHAT_COLUMNS} FROM chats c JOIN chat_members me ON me.chat_id = c.id AND me.user_id = %s WHERE c.id = %s""", (user_id, chat_id), ).fetchone() if chat is None: raise not_found("Chat not found") return chat def list_chats(user_id: UUID, after: tuple[datetime, UUID] | None, limit: int) -> list[dict]: """Чаты пользователя после курсора. Возвращает до limit+1 строк: лишняя значит «есть ещё».""" where, params = "", [user_id] if after is not None: where = "WHERE (c.created_at, c.id) > (%s, %s)" params += list(after) with connection() as conn: return conn.execute( f"""SELECT {CHAT_COLUMNS} FROM chats c JOIN chat_members me ON me.chat_id = c.id AND me.user_id = %s {where} ORDER BY c.created_at, c.id LIMIT %s""", (*params, limit + 1), ).fetchall() MESSAGE_COLUMNS = "id, chat_id, sender_id, text, created_at" def add_message(chat_id: UUID, sender_id: UUID, text: str) -> dict: try: with connection() as conn: return conn.execute( f"""INSERT INTO messages (id, chat_id, sender_id, text) VALUES (%s, %s, %s, %s) RETURNING {MESSAGE_COLUMNS}""", (uuid4(), chat_id, sender_id, text), ).fetchone() except pg_errors.ForeignKeyViolation: raise not_found("Chat not found") from None def list_messages(chat_id: UUID, user_id: UUID, after: tuple[datetime, UUID] | None, limit: int) -> list[dict]: """История чата после курсора (keyset-пагинация), до limit+1 строк.""" where, params = "chat_id = %s", [chat_id] if after is not None: where += " AND (created_at, id) > (%s, %s)" params += list(after) with connection() as conn: member = conn.execute( "SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s", (chat_id, user_id) ).fetchone() if member is None: raise not_found("Chat not found") return conn.execute( f"""SELECT {MESSAGE_COLUMNS} FROM messages WHERE {where} ORDER BY created_at, id LIMIT %s""", (*params, limit + 1), ).fetchall()