Case Study 1.5: How do Spotify-like data apps work on your smartphone?

Case Study 1.5 Reading Time: 7 mins

How do Spotify-like data apps work on your smartphone?

Goal: Learn how to build data apps on smartphones and portable devices

Spotify users don't just listen online. They listen in "Offline Mode"—on airplanes, in subways, or when they want to save data. This creates a technical challenge: How do you keep a database in your pocket?

The Smallest Powerhouse: SQLite

The solution for nearly every mobile app is SQLite.

Built-in: It's preinstalled on over 3 billion phones (Android and iOS).
Serverless: Unlike the cloud databases that run on massive clusters, SQLite is a lightweight database that lives right on your device.
Transactional: It still follows the strict rules of SQL, ensuring your data doesn't get corrupted if your phone battery dies mid-update.

With SQLite, developers can create, read, and synchronize data without needing custom code for every platform.

Example: The Offline Schema

On your device, the schema looks a lot like the cloud, but likely with additional columns for mobile life:

-- Songs on your device
CREATE TABLE Songs (
    id INTEGER PRIMARY KEY,
    title TEXT,
    artist TEXT,
    file_location TEXT -- Stores the path to the file on your phone's storage
);

-- Local Playlists
CREATE TABLE Playlists (
    id INTEGER PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP -- Crucial for knowing when to sync with the cloud
);

-- Tracks in Playlists
CREATE TABLE Playlist_tracks (
    playlist_id INTEGER,
    song_id INTEGER,
    added_at TIMESTAMP,
    PRIMARY KEY (playlist_id, song_id) -- A compound key
);

The Synchronization Challenge: Keeping in Sync

When you are offline, you might add a song to your "Road Trip" playlist. Once you get a Wifi connection, the app needs to tell the cloud what changed (for later updates).

Spotify can use Common Table Expressions (CTEs) to find only the data that changed recently. Here is how you might find everything updated in the last 10 minutes (600 seconds):

WITH RecentPlaylists AS (
    SELECT * FROM Playlists 
    WHERE updated_at > datetime('now', '-600 seconds')
),
RecentTracks AS (
    SELECT * FROM Playlist_tracks 
    WHERE added_at > datetime('now', '-600 seconds')
)
SELECT * FROM RecentPlaylists;

Note: datetime('now', '-600 seconds') is a SQLite-specific way to calculate the "sync window."

Takeaway: By using the same SQL language on both your phone (SQLite) and in the cloud, developers can build a seamless "Offline Mode". Ditto with other features to speed up your listens on the app.