Data Persistence & SQLite Operations

Relevant source files

The following files were used as context for generating this wiki page:

• app/db.py
• app/database.py
• app/track.py
• service/data_processor.py
• app/config.py
• README.md

The Data Persistence layer in this project is responsible for the structured storage and retrieval of Spotify track metadata. It utilizes a local SQLite database to ensure the idempotence of data collection and to provide a fast, indexed backend for the FastAPI web interface. This system allows the application to collect data over multiple years (2020-2024) without duplicating records and facilitates offline analysis via CSV exports.

The persistence architecture is built on a modular design: a low-level Database manager handles connections and raw SQL execution, while a high-level database.py module defines the schema and provides domain-specific CRUD operations for Track objects.

Database Architecture and Connectivity

The core of the persistence layer is the Database class, which manages the lifecycle of SQLite connections. It utilizes the Python sqlite3 library and provides a context manager for safe connection handling, ensuring that transactions are committed or rolled back appropriately in case of errors.

The database path is determined by the DATABASE_URL configuration environment variable, defaulting to sqlite:///spotify_data.db.
Sources: app/db.py, app/config.py:15

Connection Management

The Database class implements a get_connection() method using the @contextlib.contextmanager decorator. This ensures that:

• row_factory is set to sqlite3.Row to allow dictionary-like access to results.
• Transactions are automatically committed upon successful completion of a block.
• Rollbacks are performed if an exception occurs during the transaction.
  Sources: app/db.py:35-58

Data Model and Schema Definition

The primary entity stored in the database is the Track. The schema is designed to flatten the hierarchical data received from the Spotify API (Tracks and Artists) into a single, efficient table.

The tracks Table

The schema includes 13 fields covering track identification, popularity, album details, and artist metadata.

Field	Type	Description
id	INTEGER	Primary Key (Autoincrement)
track_id	TEXT	Unique Spotify Identifier (Indexed)
track_name	TEXT	Name of the track
track_popularity	INTEGER	Popularity score (0-100)
album_name	TEXT	Name of the album
album_release_date	TEXT	Release date of the album
duration_ms	INTEGER	Track duration in milliseconds
year	INTEGER	Release year (Indexed)
artist_id	TEXT	Spotify Artist ID
artist_name	TEXT	Artist name (Indexed)
artist_followers	INTEGER	Number of artist followers
artist_genres	TEXT	Comma-separated list of genres
artist_popularity	INTEGER	Artist popularity score
created_at	TIMESTAMP	Record creation timestamp

Sources: app/database.py:13-30, README.md:31-46

Performance Optimization

To ensure performant queries for the API endpoints, the system creates three specific indexes during initialization:

1. idx_tracks_year on the year column.
2. idx_tracks_artist_name on the artist_name column.
3. idx_tracks_popularity on the track_popularity column.

Sources: app/database.py:32-34

Data Flow: Collection to Storage

The DataProcessor service orchestrates the flow of data from the Spotify Crawler into the SQLite database. It performs batch insertions to optimize performance during large data crawls.

The use of INSERT OR IGNORE in both insert_track and insert_tracks_batch ensures that if a track_id already exists, the operation is skipped without error, maintaining data idempotence.
Sources: app/database.py:53-88, service/data_processor.py:65-69

Core Persistence Operations

The app/database.py module provides the functional interface for interacting with the data.

Write Operations

• init_db(db): Creates the tracks table and necessary indexes.
• insert_track(db, track): Inserts a single Track instance.
• insert_tracks_batch(db, tracks): Uses cursor.executemany for high-performance bulk insertion of track lists.

Query Operations

• get_all_tracks(db, limit, offset): Retrieves tracks with support for pagination (ORDER BY year and popularity DESC).
• get_tracks_by_year(db, year): Filters tracks for a specific year, ordered by popularity.
• get_tracks_by_artist(db, artist_name): Performs a case-insensitive search for tracks by a specific artist using the LOWER() SQL function.
• track_exists(db, track_id): A helper function to check for record existence.

Sources: app/database.py:91-125

Conclusion

The persistence layer provides a robust foundation for the Spotify Data Collector. By combining the Database connection utility with domain-specific operations in database.py, the system achieves a clean separation of concerns. This setup supports the high-level requirements of idempotence, efficient batch processing, and indexed search capabilities required by the FastAPI frontend.