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

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

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