3. PostgreSQL setup

Two databases need to exist before any code changes: a local one for development and a Railway one for production. We create both, apply the schema_postgres.sql file from the previous page to each, and end with a connection test confirming Python can reach both.

We split the work in two: local first (where iteration is cheap), then production (where the same schema script runs against a different connection string). Both databases share one schema file, which is the durable shape we want: changes to the database go through that file, not through ad-hoc psql commands.

Creating the local database

Chapter 24 walked through the PostgreSQL install. Assuming the server is running locally, create a fresh database for the Music Time Machine. Keep it separate from anything you set up in the previous chapter so the two don't interact:

Terminal

# Create the database
createdb music_time_machine_pg

# Verify it was created
psql -l | grep music_time_machine

Empty database, no tables. Next, apply the schema with a small Python script that reads schema_postgres.sql from disk and executes it against the connection. Save this at the project root as create_schema.py:

create_schema.py

"""
Create PostgreSQL schema for Music Time Machine
Run this once locally, once on production
"""
import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

def create_schema():
    """Create all tables and indexes from schema file"""
    
    # Connect to PostgreSQL
    conn = psycopg2.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        database=os.getenv('DB_NAME', 'music_time_machine_pg'),
        user=os.getenv('DB_USER', os.getenv('USER')),  # OS username by default
        password=os.getenv('DB_PASSWORD', ''),
        port=int(os.getenv('DB_PORT', 5432))
    )
    
    try:
        with conn.cursor() as cursor:
            # Read schema file
            with open('schema_postgres.sql', 'r') as f:
                schema_sql = f.read()
            
            # Execute schema (creates all tables and indexes)
            cursor.execute(schema_sql)
        
        conn.commit()
        print("Schema created successfully")
        
        # Verify tables were created
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema = 'public'
                ORDER BY table_name
            """)
            tables = cursor.fetchall()
            
            print(f"\nCreated {len(tables)} tables:")
            for table, in tables:
                print(f"  - {table}")
    
    except psycopg2.Error as e:
        print(f"Database error: {e}")
        conn.rollback()
        raise
    
    finally:
        conn.close()

if __name__ == '__main__':
    create_schema()

Run from the project root:

Terminal

python create_schema.py

Terminal

Schema created successfully

Created 2 tables:
  - tracks
  - snapshots

The two tables exist with no rows. The migration script in section 4 fills them.

A quick connection check from Python

Before writing the migration code, confirm the application can connect to the new database with the same credentials the rest of the chapter will use. Save this as test_connection.py:

test_connection.py

import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

# Test connection
conn = psycopg2.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    database=os.getenv('DB_NAME', 'music_time_machine_pg'),
    user=os.getenv('DB_USER', os.getenv('USER')),
    password=os.getenv('DB_PASSWORD', ''),
    port=int(os.getenv('DB_PORT', 5432))
)

# Test queries
with conn.cursor() as cursor:
    cursor.execute("SELECT COUNT(*) FROM tracks")
    count = cursor.fetchone()[0]
    print(f"Tracks table: {count} rows")

    cursor.execute("SELECT COUNT(*) FROM snapshots")
    count = cursor.fetchone()[0]
    print(f"Snapshots table: {count} rows")

conn.close()
print("\nConnection test successful")

Terminal

Tracks table: 0 rows
Snapshots table: 0 rows

Connection test successful

Zero rows is correct, we haven't migrated yet; what matters is that the connection works and the two table names come back.

Standing up the production database on Railway

The same schema needs to exist on a production database too. Railway's managed PostgreSQL takes about five minutes to provision and runs around $5/month for a starter database. Four steps:

Create a Railway account. Sign in at railway.app with GitHub. New accounts get a small free credit; after that, billing is usage-based.
Create a new project. "New Project" then "Deploy PostgreSQL". The database is up in about 30 seconds; the project dashboard shows the host, port, database name, username, and password.
Copy the external connection URL. It looks like postgresql://postgres:<password>@<region>.railway.app:5432/railway. The "external" URL (rather than the internal one) is what your laptop will use to run the schema script and migration.
Create .env.production at the project root with the Railway credentials. Add the filename to .gitignore; this file holds production secrets and must never end up in version control.

.env.production

# Production PostgreSQL on Railway
DB_HOST=monorail.proxy.rlwy.net
DB_NAME=railway
DB_USER=postgres
DB_PASSWORD=your_railway_password_here
DB_PORT=12345  # Railway assigns a port

# Same credentials in single-URL form
DATABASE_URL=postgresql://postgres:your_railway_password_here@monorail.proxy.rlwy.net:12345/railway

# Spotify credentials (same as local)
SPOTIPY_CLIENT_ID=your_spotify_client_id
SPOTIPY_CLIENT_SECRET=your_spotify_client_secret
SPOTIPY_REDIRECT_URI=https://your-app.railway.app/callback

The same credentials appear twice deliberately: create_schema.py reads the discrete DB_* variables, while the migration script and connection pool you'll write in sections 4 and 5 read the single DATABASE_URL form. Run the same create_schema.py against Railway by loading the production environment first:

Terminal

# Load production environment and create schema
set -a
source .env.production
set +a
python create_schema.py

Two databases now exist with identical schemas: local for the rest of the chapter's development work, Railway for the eventual deploy. Both are empty. Next, in section 4, we write the script that copies every row from the SQLite source into the local PostgreSQL, in dependency order, with raw_json TEXT-to-JSONB conversion.