Feature/v3 sqlalchemy by GeiserX · Pull Request #4 · GeiserX/Telegram-Archive

GeiserX · 2025-12-18T20:57:04Z

Summary

Complete rewrite of the database layer from synchronous sqlite3 to async SQLAlchemy, enabling PostgreSQL support while maintaining full backward compatibility with existing SQLite databases.

Key Features:
🗄️ Multi-database support (SQLite default, PostgreSQL optional)
⚡ All database operations are now async/non-blocking
🔄 Zero-downtime upgrade - existing v2.x SQLite databases work automatically
📦 Built-in SQLite → PostgreSQL migration utility
🛠️ Alembic migrations for schema versioning

Changes:
New src/db/ package: models, adapter, base manager, migrations (5 files, +1671 lines)
Removed legacy src/database.py (-833 lines)
Converted telegram_backup.py, web/main.py, export_backup.py to async
Updated docker-compose.yml with PostgreSQL configuration
Updated all tests for async architecture

New Dependencies:
sqlalchemy[asyncio]>=2.0.36
aiosqlite>=0.20.0
asyncpg>=0.30.0
alembic>=1.14.0
greenlet>=3.1.0

Bug Fixes:
Add server_default=func.now() to timestamp columns (fixes NULL values with Core inserts)
URL-encode PostgreSQL credentials for special characters
Auth cookie 30-day persistence with max_age
Avatar selection uses newest available
Atomic upsert for sync status

Test plan
[x] SQLite backward compatibility with existing databases
[x] PostgreSQL connection and CRUD operations
[x] Migration utility (SQLite → PostgreSQL)
[x] Web viewer async endpoints
[x] Backup process async operations
[x] Auth cookie persistence

- Add SQLAlchemy 2.0 with asyncio support (aiosqlite + asyncpg) - Create ORM models matching existing v2.x schema exactly - Implement DatabaseManager for connection handling - Add DatabaseAdapter with all operations from old Database class - Support both SQLite and PostgreSQL via configuration - Preserve SQLite PRAGMAs (WAL mode, busy_timeout, cache_size) - Add retry decorator for handling database locks This is step 1 of the v3.0 migration. Next steps: - Set up Alembic for migrations - Update application code to use async adapter - Add PostgreSQL to docker-compose

- Configure Alembic for async SQLAlchemy migrations - Support both SQLite and PostgreSQL via environment config - Add initial migration matching v2.x schema exactly - Initial migration will be auto-stamped for existing databases Migration commands: alembic upgrade head # Apply all migrations alembic stamp head # Mark existing DB as up-to-date alembic revision -m "x" # Create new migration

- Add factory method TelegramBackup.create() for async initialization - Convert all 18 database calls to use await - Update run_backup() to use async factory and close - Maintains full compatibility with existing backup functionality

- get_messages_paginated: Messages with user/media joins, search, pagination - find_message_by_date_with_joins: Date picker with full message info - get_chat_by_id: Single chat lookup - get_messages_for_export: Async streaming for JSON export

web/main.py: - Use FastAPI lifespan for database init/cleanup - Convert all endpoints to async - Remove direct cursor access, use adapter methods - Simplify error handling (adapter handles retries) export_backup.py: - Add factory method for async initialization - Convert all database operations to async - Use asyncio.run() in main entry point

- Add database config options to .env.example - Add DATABASE_URL and DB_TYPE variables to docker-compose - Add optional PostgreSQL service with health checks - Both services (backup + viewer) share database configuration - Default remains SQLite for backward compatibility

- Add PostgreSQL support to features list - Add database configuration table - Add PostgreSQL setup instructions - Add v2.x to v3.0 upgrade guide - Document that existing SQLite databases work automatically

Dependencies: - Update SQLAlchemy to >=2.0.36 - Update Alembic to >=1.14.0 - Update aiosqlite to >=0.20.0 - Update asyncpg to >=0.30.0 - Update FastAPI to >=0.115.0 - Update Uvicorn to >=0.32.0 - Update other dependencies to latest stable versions Tests: - Remove old sync Database tests - Add tests for DatabaseAdapter methods - Simplify tests to work with async architecture - Remove direct database manipulation in tests Breaking: - Delete src/database.py (replaced by src/db/ package)

Adds migrate_sqlite_to_postgres() function for users who want to switch from SQLite to PostgreSQL after upgrading to v3.0. Features: - Batch migration with configurable batch size - Proper foreign key ordering (users/chats before messages) - verify_migration() to compare record counts - Progress logging for large datasets

cursor

This is the final PR Bugbot will review for you during this billing cycle

Your free Bugbot reviews will reset on January 18

Details

You are on the Bugbot Free tier. On this plan, Bugbot will review limited PRs each billing cycle.

To receive Bugbot reviews on all of your PRs, visit the Cursor dashboard to activate Pro and start your 14-day free trial.

The default database path in migrate.py was '/data/backups/telegram_backup.db' (absolute) while base.py used 'data/telegram_backup.db' (relative). This caused migration failures when users didn't explicitly set DB_PATH since the migration tool would look in a different location than the main application. Changed migrate.py defaults to match base.py: 'data/telegram_backup.db'

- Add @asynccontextmanager decorator to get_session method in base.py - Fix auth check endpoint from /api/auth/status to /api/auth/check in index.html

- web: Use max(matches, key=os.path.getmtime) to select the most recently modified avatar file instead of first glob match, ensuring current profile photos are displayed when multiple avatars exist - db: Refactor update_sync_status to use atomic upsert (ON CONFLICT) instead of select-then-update pattern for better concurrency

- DatabaseManager now converts sync driver URLs (sqlite://, postgresql://) to async drivers (sqlite+aiosqlite://, postgresql+asyncpg://) when URLs are passed directly to the constructor, not just from environment variables - This fixes migration functions that construct URLs with sync prefixes - Also fix export_backup.py to use try/finally for proper cleanup - Remove duplicate media_path key in telegram_backup.py

Passwords containing special characters like @, :, /, or # would cause malformed connection URLs. For example, password `my@secret` produced `postgresql://user:my@secret@host:5432/db`, parsing `secret@host` as the hostname instead of the password. Use urllib.parse.quote_plus() to properly encode user and password values before URL interpolation.

Without max_age, the authentication cookie was a session cookie that gets deleted when the browser closes. Users had to log in again after every browser restart. Added max_age=30*24*60*60 to persist auth for 30 days as originally intended.

- Add server_default=func.now() to all created_at/updated_at columns in models to fix NULL values when using Core-style inserts (sqlite_insert/pg_insert) - Update default DB_PATH from data/telegram_backup.db to /data/backups/telegram_backup.db for Docker backward compatibility with v2 - Fix migrate.py to use async driver prefixes (sqlite+aiosqlite, postgresql+asyncpg)

Feature/v3 sqlalchemy

GeiserX added 9 commits December 18, 2025 21:40

docs: Update README for v3.0 with database configuration

68b065b

- Add PostgreSQL support to features list - Add database configuration table - Add PostgreSQL setup instructions - Add v2.x to v3.0 upgrade guide - Document that existing SQLite databases work automatically