Chapter 25: Migrating the Music Time Machine to PostgreSQL

1. Migrating the Music Time Machine to PostgreSQL

The Music Time Machine has outgrown "single SQLite file on localhost" in one specific way: the web app now wants production hosting, concurrent requests, connection pooling, and richer queries inside the cached Spotify track payload. Chapter 25 moves the project to PostgreSQL and turns tracks.raw_json from SQLite text into PostgreSQL JSONB.

This is not a rewrite of the project. It is the same data model from Chapter 16: tracks for stable track entities, snapshots for dated appearances in your top-track history, and raw_json for the original provider response. PostgreSQL keeps the same shape but gives the raw payload first-class query support.

Chapter plan

What you'll learn
  • Audit the SQLite schema before migration
  • Map SQLite columns to PostgreSQL types, including JSONB for raw_json
  • Create a PostgreSQL schema with foreign keys, indexes, and timestamp types
  • Copy tracks first, then snapshots, preserving primary-key semantics
  • Update Flask database code for psycopg2 and connection pooling
  • Query nested track payload fields with PostgreSQL JSONB operators
  • Deploy the PostgreSQL-backed app to Railway
What you'll build
  • schema_postgres.sql for the PostgreSQL target schema
  • migrate_to_postgresql.py to move rows and convert raw_json
  • database.py with a PostgreSQL connection pool
  • Updated dashboard queries using raw_json JSONB extraction
  • Verification checks for row counts, JSONB queryability, and concurrent writes

The source schema

The SQLite source has two core tables:

SQLite source 
tracks
  track_id: TEXT PRIMARY KEY
  name: TEXT
  artist_name: TEXT
  album_name: TEXT
  duration_ms: INTEGER
  spotify_url: TEXT
  raw_json: TEXT

snapshots
  track_id: TEXT
  snapshot_date: TEXT
  time_range: TEXT
  rank: INTEGER
  PRIMARY KEY (track_id, snapshot_date, time_range)

SQLite stores raw_json as text. That is fine for capture and export, but awkward for queries. If you want "tracks released in 2026" or "tracks whose original payload has an ISRC", SQLite makes you parse every row in Python. PostgreSQL lets the database answer those questions directly.

Why PostgreSQL now?

  • Concurrency. A deployed Flask app can serve overlapping requests. PostgreSQL handles concurrent readers and writers better than a single SQLite file.
  • Connection pooling. The app can reuse database connections rather than opening a new file connection per request.
  • JSONB. The cached Spotify track object becomes queryable and indexable without adding a column for every nested provider field.
  • Production deployment. Railway can provision PostgreSQL as a managed service with environment variables and persistent storage handled for you.

Migration order

Tracks have to move first because snapshots reference them. Then snapshots can be inserted with the same composite primary key they had in SQLite. Finally, verification checks row counts and proves raw_json is real JSONB, not a string-shaped wrapper.

  1. Audit SQLite: tables, columns, row counts, and sample raw_json.
  2. Create PostgreSQL schema: tracks, snapshots, foreign key, and indexes.
  3. Migrate tracks: parse SQLite raw_json text into Python objects, then insert into PostgreSQL JSONB.
  4. Migrate snapshots: preserve (track_id, snapshot_date, time_range).
  5. Update Flask: switch from sqlite3 to a psycopg2 connection pool.
  6. Verify: row counts match, JSONB queries work, concurrent writes do not lock the app.

Next, in section 2, we write the PostgreSQL schema that preserves the Chapter 16 data model while making raw_json queryable.