Music DB

Introduction

Just putting my music in a DB

Database Setup

• Create User
• Create DB
• Grant Privileges
• Create Schema

CREATE TABLE music_metadata (
    path TEXT PRIMARY KEY,
    extension TEXT,
    song TEXT,
    artist TEXT,
    album TEXT,
    year TEXT,
    genre TEXT,
    format TEXT,
    duration TEXT,
    size BIGINT,
    bitrate TEXT,
    writing_library TEXT,
    has_cover BOOLEAN,
    mtime BIGINT,
    ctime BIGINT,
    btime BIGINT,
    mediainfo_empty BOOLEAN
);

Injest Data

Here is the script to read the json

#!/usr/bin/env bash
set -euo pipefail

###############################################################################
# Usage:
#   ./load_music_metadata.sh [--dry-run] [--reset] [--host HOST] <db_user> <db_name>
#
# Examples:
#   ./load_music_metadata.sh music_admin music_db
#   ./load_music_metadata.sh --dry-run music_admin music_db
#   ./load_music_metadata.sh --reset --host 192.168.1.50 music_admin music_db
###############################################################################

DRY_RUN=false
RESET=false
HOST="localhost"

# Parse flags
while [[ $# -gt 0 ]]; do
    case "$1" in
        --dry-run)
            DRY_RUN=true
            shift
            ;;
        --reset)
            RESET=true
            shift
            ;;
        --host)
            HOST="$2"
            shift 2
            ;;
        *)
            break
            ;;
    esac
done

# Remaining args: user + db
if [[ $# -lt 2 ]]; then
    echo "Usage: $0 [--dry-run] [--reset] [--host HOST] <db_user> <db_name>"
    exit 1
fi

DB_USER="$1"
DB_NAME="$2"

# Prompt for password securely
read -s -p "Postgres password for $DB_USER: " PGPASSWORD
echo
export PGPASSWORD

JSONL="/tmp/music_metadata.jsonl"
TSV="/tmp/music_metadata.tsv"

echo "Preparing TSV from JSONL…"

# Convert JSONL → TSV
jq -r '
    [
        .path,
        .extension,
        .song,
        .artist,
        .album,
        .year,
        .genre,
        .format,
        .duration,
        (.size | tonumber?),
        .bitrate,
        .writing_library,
        (.has_cover // false | tostring),
        (.mtime | tonumber?),
        (.ctime | tonumber?),
        (.btime | tonumber?),
        (.mediainfo_empty // false | tostring),
        (.mediainfo_crashed // false | tostring)
    ]
    | @tsv
' "$JSONL" > "$TSV"

echo "TSV ready: $TSV"

###############################################################################
# DRY RUN MODE
###############################################################################
if [[ "$DRY_RUN" == true ]]; then
    echo
    echo "────────────────────────────────────────────"
    echo " DRY RUN: No changes will be made to Postgres"
    echo "────────────────────────────────────────────"
    echo
    echo "Would load $(wc -l < "$TSV") rows into table: music_metadata"
    echo "User: $DB_USER"
    echo "Database: $DB_NAME"
    echo "Host: $HOST"
    echo
    exit 0
fi

###############################################################################
# REAL LOAD BEGINS
###############################################################################

echo
echo "Connecting to Postgres at host: $HOST…"

psql -h "$HOST" -U "$DB_USER" -d "$DB_NAME" <<EOF

-- Reset mode: drop + recreate table
$( if [[ "$RESET" == true ]]; then
cat <<'SQL'
DROP TABLE IF EXISTS music_metadata;

CREATE TABLE music_metadata (
    path TEXT PRIMARY KEY,
    extension TEXT,
    song TEXT,
    artist TEXT,
    album TEXT,
    year TEXT,
    genre TEXT,
    format TEXT,
    duration TEXT,
    size BIGINT,
    bitrate TEXT,
    writing_library TEXT,
    has_cover BOOLEAN,
    mtime BIGINT,
    ctime BIGINT,
    btime BIGINT,
    mediainfo_empty BOOLEAN,
    mediainfo_crashed BOOLEAN
);
SQL
fi )

-- Ensure table exists even without --reset
CREATE TABLE IF NOT EXISTS music_metadata (
    path TEXT PRIMARY KEY,
    extension TEXT,
    song TEXT,
    artist TEXT,
    album TEXT,
    year TEXT,
    genre TEXT,
    format TEXT,
    duration TEXT,
    size BIGINT,
    bitrate TEXT,
    writing_library TEXT,
    has_cover BOOLEAN,
    mtime BIGINT,
    ctime BIGINT,
    btime BIGINT,
    mediainfo_empty BOOLEAN,
    mediainfo_crashed BOOLEAN
);

-- Staging table for COPY
CREATE TEMP TABLE music_metadata_stage (LIKE music_metadata);

\copy music_metadata_stage FROM '$TSV' WITH (FORMAT text)

INSERT INTO music_metadata
SELECT * FROM music_metadata_stage
ON CONFLICT (path) DO UPDATE SET
    extension = EXCLUDED.extension,
    song = EXCLUDED.song,
    artist = EXCLUDED.artist,
    album = EXCLUDED.album,
    year = EXCLUDED.year,
    genre = EXCLUDED.genre,
    format = EXCLUDED.format,
    duration = EXCLUDED.duration,
    size = EXCLUDED.size,
    bitrate = EXCLUDED.bitrate,
    writing_library = EXCLUDED.writing_library,
    has_cover = EXCLUDED.has_cover,
    mtime = EXCLUDED.mtime,
    ctime = EXCLUDED.ctime,
    btime = EXCLUDED.btime,
    mediainfo_empty = EXCLUDED.mediainfo_empty,
    mediainfo_crashed = EXCLUDED.mediainfo_crashed;

EOF

echo
echo "Load complete."