Daily Stock Analysis Database: SQLite and SQLAlchemy Implementation

The Daily Stock Analysis tool uses SQLite as its sole persistent storage solution, accessed through SQLAlchemy's ORM layer with a default database file at ./data/stock_analysis.db that can be overridden via the DATABASE_PATH environment variable.

The ZhuLinsen/daily_stock_analysis repository is a Python-based stock analysis platform that stores historical prices, LLM usage logs, news intel, and portfolio snapshots. Unlike client-server architectures, this application embeds SQLite directly as its storage engine, leveraging SQLAlchemy as the abstraction layer for all data persistence.

SQLite Configuration and File Storage

All persistent data resides in a single SQLite file configured in src/config.py. The default path is ./data/stock_analysis.db, though the application reads the DATABASE_PATH environment variable to allow relocation.

As implemented in src/config.py at lines 889-895, the configuration logic builds the database URL:

database_path = os.getenv("DATABASE_PATH", "./data/stock_analysis.db")

# Constructs sqlite:///path/to/stock_analysis.db

To override the storage location, export the variable before execution:

export DATABASE_PATH=/mnt/nas/stock_analysis.db
python main.py --run

The configuration automatically applies SQLite-specific PRAGMA settings including WAL (Write-Ahead Logging) mode and busy-timeout intervals to optimize for both read-heavy analysis and write-heavy data ingestion operations.

The DatabaseManager Architecture

The storage layer centers on the DatabaseManager class defined in src/storage.py (lines 27-45). This singleton wrapper encapsulates a SQLAlchemy Engine and manages session lifecycles through a context manager pattern.

The class initializes with a SQLite URL by default but accepts any SQLAlchemy-compatible connection string:

from src.storage import DatabaseManager

# Returns singleton instance configured for SQLite

db = DatabaseManager.get_instance()

# Acquire managed session scope for ORM operations

with db.session_scope() as session:
    # Query StockDaily, PortfolioSnapshot, LLMUsageLog tables, etc.

    latest_price = session.execute(
        select(StockDaily).where(StockDaily.code == "AAPL")
        .order_by(StockDaily.date.desc())
        .limit(1)
    ).scalar_one_or_none()

While the DatabaseManager is deliberately generic to support any SQLAlchemy backend, the codebase exclusively instantiates SQLite engines in both production and testing contexts.

In-Memory SQLite for Testing

The test suite utilizes in-memory SQLite databases to eliminate filesystem dependencies during CI/CD runs. According to tests/test_storage.py (lines 75-89), tests instantiate the manager with a sqlite:///:memory: URL:

# Pattern from tests/test_storage.py

db = DatabaseManager(db_url="sqlite:///:memory:")
db.initialize_schema()  # Create tables in memory

with db.session_scope() as session:
    # Insert test fixtures, run assertions

    # No files written to disk

    pass

This pattern appears across tests/test_llm_usage.py and repository-specific test files, ensuring fast, isolated unit tests without requiring temporary file cleanup.

Data Access Layer Implementation

All data access flows through repository classes in src/repositories/ (e.g., stock_repo.py, portfolio_repo.py). These modules rely exclusively on the SQLite-backed DatabaseManager for CRUD operations via SQLAlchemy ORM.

No PostgreSQL, MySQL, MongoDB, or other database systems are referenced anywhere in the codebase. Every service layer component—from daily price ingestion to LLM cost tracking—depends on the singleton database connection managed through src/storage.py.

Summary

• SQLite is the sole database technology used by the Daily Stock Analysis tool, with no bundled support for PostgreSQL, MySQL, or MongoDB.
• The default database file location is ./data/stock_analysis.db, configurable via the DATABASE_PATH environment variable defined in src/config.py.
• The DatabaseManager class in src/storage.py provides a singleton wrapper around SQLAlchemy's engine and session management.
• Unit tests leverage in-memory SQLite databases (sqlite:///:memory:) for fast, isolated validation without filesystem side effects.

Frequently Asked Questions

Can I use PostgreSQL or MySQL instead of SQLite?

No. While the DatabaseManager class architecturally accepts any SQLAlchemy-compatible URL, the codebase contains no dialect-specific configurations, migration tools, or connection pool settings for PostgreSQL or MySQL. All repository implementations assume SQLite-specific file paths and transaction behaviors.

How do I change the database file location?

Set the DATABASE_PATH environment variable before launching the application. As defined in src/config.py (lines 889-895), the configuration loader reads this variable to determine the SQLite file path, defaulting to ./data/stock_analysis.db if undefined.

What is the DatabaseManager class responsible for?

The DatabaseManager class in src/storage.py acts as a singleton factory and lifecycle manager for SQLAlchemy engines and sessions. It handles connection initialization, provides the session_scope() context manager for transaction handling, and automatically applies SQLite-specific optimizations like WAL mode and busy-timeout settings.

Is SQLite suitable for production stock analysis workloads?

Yes, for the intended use case of this application. The tool configures SQLite with WAL (Write-Ahead Logging) enabled and appropriate busy-timeout values to handle the expected concurrency of single-user or batch analysis workflows. However, the current architecture would require significant refactoring to support high-concurrency multi-user scenarios typical of client-server database deployments.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}