Skip to content

Add SQLAlchemy 2.0 with Repository Pattern #59

@Presmanes3

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
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions