Update file system paths in Navidrome's database

change_path.py

# This script changes the path for a folder or file in Navidrome's database, allowing music files to be
# moved or renamed on the file system without losing associated metadata in Navidrome. Since the original
# version, it has been updatd to account for the media_file IDs, which are calculated from the path value
# and referenced in several tables.
#
# This script is based on Navidrome version 0.49.2. If you are running an older version of Navidrom, it
# will likely fail. If you are running a newer version of Navidrome, your mileage may vary.
#
# It does NOT make any modifications to the file system - only to the Navidrome database. 
#
# It does not rescan the file; it assumes nothing has changed but the path. If you're moving files
# and also updating their contents (e.g. tags or bitrate), run this to change the path(s) in the
# database, and then run a full scan to update the metadata.
#
# Place this file in the same directory as navidrome.db, which is /var/lib/navidrome on Linux, and be sure  
# to use fully qualified paths for arguments. It must be run as a user that has write access to the
# navidrome.db file.
#
# Generic use - note that you may need to use python3 instead of python, depending on your system:
#   python change_path.py FROM_PATH TO_PATH
#
# Example: Rename/move a folder (note trailing slashes):
#   python change_path.py /mnt/music/artists/Bjork/ /mnt/music/artists/Björk/
# 
# Example: Rename a song file (use quotes for paths with spaces):
#   python change_path.py "/mnt/music/artists/Test 1/song.mp3" "/mnt/music/artists/Test 2/01 - Song.mp3"
#
# The script's output lists each path updated along with the MD5 ID calculated from it and the row
# count updates for each table referencing the old ID.
#
# Note that Navidrome's scanner will automatically remove files from its database if they're found to 
# be missing, so it's important to stop the Navidrome server process before moving files, and to update
# the database with this script prior to restarting it.
#
# Steps to use:
# 1. Stop the Navidrome server.
# 2. Make a backup of your navidrome.db file so you can roll back any unwanted changes.
# 3. Move or rename folders and files as needed on the file system.
# 4. Run this script to update the Navidrome database for any files/folders moved or renamed.
# 5. Start Navidrome service.
# 6. Optionally run a full scan if file contents have changed.
#
# Source: https://gist.github.com/bagaag/3d64e3349b6ed3bfd6e01813222db055
#

import sqlite3
import hashlib
import os
import sys

def path_clause(path):
    if path.endswith('/'):
        return ('LIKE', '%')
    else:
        return ('=', '')

def get_matching_media(path):
    clause_and_suffix = path_clause(path)
    clause = clause_and_suffix[0]
    path = path + clause_and_suffix[1]
    sql = f"""
        SELECT id, path FROM media_file 
        WHERE path {clause} ? 
        ORDER BY path;"""
    cur = con.cursor()
    res = cur.execute(sql, [path])
    ret = res.fetchall()
    cur.close()
    return ret

def exec_update(sql, params):
    cur = con.cursor()
    cur.execute(sql, params)
    con.commit()
    rc = cur.rowcount
    cur.close()
    return rc

def replace_values(res):
    replaced = []
    for row in res:
        old_id = row[0]
        old_path = row[1]
        new_path = to_path
        if from_path.endswith('/'):
            new_path = old_path.replace(from_path, to_path)
        else:
            new_path = to_path
        new_id = md5(new_path)
        sql = f"""
            UPDATE media_file 
            SET path = ?, id = ?
            WHERE path = ? and id = ?;
            """
        media_file = exec_update(sql, (new_path, new_id, old_path, old_id))
        sql = f"""
            UPDATE annotation
            SET item_id = ? 
            WHERE item_id = ? 
            AND item_type='media_file';
            """
        annotation = exec_update(sql, (new_id, old_id))
        sql = f"""
            UPDATE media_file_genres
            SET media_file_id = ?
            WHERE media_file_id = ?;
            """
        media_file_genres = exec_update(sql, (new_id, old_id))
        sql = f"""
            UPDATE playlist_tracks
            SET media_file_id = ?
            WHERE media_file_id = ?;
            """
        playlist_tracks = exec_update(sql, (new_id, old_id))
        sql = f"""
            UPDATE bookmark
            SET item_id = ?
            WHERE item_id = ?
            AND item_type = 'media_file';
            """
        bookmark = exec_update(sql, (new_id, old_id))
        sql = f"""        
            UPDATE album
            SET embed_art_path = ?
            WHERE embed_art_path = ?;
            """
        album = exec_update(sql, (new_path, old_path))
        replaced.append({
            'old_id': old_id,
            'old_path': old_path,
            'new_id': new_id,
            'new_path': new_path,
            'media_file': media_file,
            'annotation': annotation,
            'media_file_genres': media_file_genres,
            'playlist_tracks': playlist_tracks,
            'bookmark': bookmark,
            'album': album
        })
    return replaced

def md5(s):
    return hashlib.md5(s.encode('utf-8')).hexdigest()

#
# main
#
if len(sys.argv) < 3:
    print("Usage: python change_path.py FROM_PATH TO_PATH")
    exit()

from_path = sys.argv[1]
to_path = sys.argv[2]

if (from_path.endswith(os.sep) and not to_path.endswith(os.sep)) or (not from_path.endswith(os.sep) and to_path.endswith(os.sep)):
    print("One path has a trailing slash and the other doesn't. That's probably not right. Check your inputs.")
    exit()

con = sqlite3.connect('navidrome.db')
res = get_matching_media(from_path)
print(f'Found {len(res)} path matches.')
updated = replace_values(res)
con.close()

for update in updated:
    print('FROM: ' + update['old_path'])
    print('      ' + update['old_id'])
    print('TO:   ' + update['new_path'])
    print('      ' + update['new_id'])
    print('      media_file:        ' + str(update['media_file']))
    print('      annotation:        ' + str(update['annotation']))
    print('      media_file_genres: ' + str(update['media_file_genres']))
    print('      playlist_tracks:   ' + str(update['playlist_tracks']))
    print('      bookmark:          ' + str(update['bookmark']))
    print('      album:             ' + str(update['album']))

Kinda hard to answer that question without giving you the full scope of what I did, so I'm just going to do that.
Original : /music/Alessia Cara/[2015] Know-It-All (Album)/02 - Here.flac
That original file was an 800kbps flac right, I wanted to move the location of this file in the database and replace it with a higher quality file. So I turned off Navidrome and used the script to change the original filepath to
Changed : /music/Alessia Cara/[2015] Know-It-All (Deluxe) (Album)/02 - Here.flac
Before I turned navidrome back on I removed the old 800kbps version of the song and replaced it with one that was like 1600kbps, I changed the name and folder so it matches what was in the database and when I turned on Navidrome it showed me that

In the original image one version of Here was liked and one wasn't, here is the "Get Info" sections for both
Liked

Unliked

And here's actually what the folder looks like

So for some reason, I'm guessing since the bitrate isn't the same on the replacement file isn't the same as the original, Navidrome doesn't want to call these the same entry or something?

Thanks for this detailed report. I've had this same thing happen to me using this script. I end up with a duplicate entry for the file - two media_files with the same path. I've just tested it with several scenarios and I'm unable to reproduce it. So I can confirm this behavior, but I'm not sure how or why it's happening, and today I'm unable to reproduce it.

When this happens, you can rename the file with navidrome running and then do a full scan in navidrome. It should remove both the duplicates and add the renamed file. You can then repeat this process to rename the file back to its original name if you like. You do unfortunately lose the meta data collected for the file when doing this.

I'll keep an eye on this as I use the script and see if I can narrow down why it happens when it does.

Thanks for writing this script. It's does exactly something that I was looking to do, but I probably won't get to it for a couple months at least. A few months ago, I wrote a script that moves itunes metadata to the ND database, so I'm sort of familiar with how it works under the hood.

One thing you have to be careful of is that you cannot just change the path to a file in the media_file database table and call it a day. If you look at the database, each file has an id. That ID is derived from a hash of the PATH to the media file. So if you rename or move something, that would yield a new "id" and cause all sorts of chaos. If you browse the columns in the media_file table, you can see how many other tables that a media file references. annotation, media_file_genres, playlist_tracks and probably some other tables also.

So I have been trying to think of a user friendly way to move files, or replace say, shitty mp3 files with higher bitrate ones and the best I can think of is to make a copy database before adding the new files, then put the new files where you want them or rename them or whatever. Then you'd turn ND back on and let it generate its own media_file ID's for the new files, and THEN associate the OLD metadata with all the new "IDs" that Navidrome will generate.

I am trying to think how I would associate the old and new entries, maybe tag them identically ahead of time using Picard or something and then comparing the MP3's metadata? That part is up in the air but no matter how you slice it, the database is a bunch of interlocking pieces that you need to account for.

The ND devs hang out on Discord and I've found them very happy to chat and answer questions if you are going to work on this more.

Here is a cut & paste from my notes when I was asking them the best way to do what we are trying to do...

Me: Or maybe the best way would be to 1) add the new FLAC files, 2) let ND enter them into the database, and once they're in the DB, 3) have a script copy the corresponding info from the annotation table from the old files to the new files, then 4) delete the old mp3s.

Deluan: AH, yes!! You are right! The id is calculated as a md5 hash of the path, so it would mess things up for sure. Then your only option is what you wrote, just be aware of the referential integrity in the annotation, media_file_genres and playlist_tracks tables

Thank you for this insight, @Stampede. It explains the bizarre behavior I've seen using it. Using a hash of the path for an ID probably seemed handy when writing the file system scanner, but it assumes files are never moved, which of course we know is not the case. I'll have to think about this. It really makes it so there's no relatively clean way to do it. In the meantime, I'll make a comment at the top of this explaining it shouldn't be used.

I've updated the script to address the MD5 hash IDs that Navidrome uses. Unlike other databases I've used, updating IDs like this is thankfully quite simple in sqlite3. I reviewed the schema of every table in the database and believe I've got all of them. Having tested with a handful of updates, I'm not seeing any duplicates or other issues.

Note that the Navidrome album table schema seems to have changed between 0.48 and 0.49.2. You'll want to make sure you're running at least 0.49.2 with this script. You can test by running ./navidrome -v