Skip to content

Add SQLAlchemy 2.0 with Repository Pattern #59

Description

@Presmanes3

Problem

src/climatevision/db.py (551 lines) contains all database access with raw SQL strings and standard sqlite3. This has several issues:

  • No type safety in queries
  • No reusable ORM models
  • No schema migrations
  • SQLite in production (no PostgreSQL support)
  • Boilerplate CRUD code
  • Hard to test with in-memory database

Proposed solution

Implement SQLAlchemy 2.0 async with Repository Pattern:

src/climatevision/
└── database/
   ├── __init__.py
   ├── models.py            # SQLAlchemy ORM models (Run, Organization, Alert, etc.)
   ├── connection.py        # Async engine + session factory
   ├── repositories/
   │   ├── __init__.py
   │   ├── base.py         # Abstract BaseRepository[T]
   │   ├── run_repository.py
   │   ├── org_repository.py
   │   └── alert_repository.py
   └── migrations/        # Alembic migrations

Tasks

  • [ ] Add SQLAlchemy and alembic to dependencies
  • [ ] Create ORM models for all tables
  • [ ] Implement generic BaseRepository
  • [ ] Implement specific repositories
  • [ ] Configure Alembic for migrations
  • [ ] Replace db.py references in API layer
  • [ ] Update tests with in-memory database fixtures
  • [ ] Support PostgreSQL via DATABASE_URL env var

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions