Calibre Sync for Android & iOS - Page 23

Terisa de morgan · 02-10-2026, 04:06 PM

Quote:

Originally Posted by KhenemetHeru

This works as a good stopgap with my library which resides on my computer and is shared on Dropbox. It also does not screw with Calibre 9 when the altered database is reopened, though I assume I'll have to run it every time I update the database with added books. Thank you.

Not necessary, structure does not change just for adding books.

GeniusJ · 02-12-2026, 02:29 AM

Quote:

Originally Posted by KhenemetHeru

This works as a good stopgap with my library which resides on my computer and is shared on Dropbox. It also does not screw with Calibre 9 when the altered database is reopened, though I assume I'll have to run it every time I update the database with added books. Thank you.

No, you shouldn't have to do that. If there's another version update that changes the db schema, then you'll have to run it then. Otherwise, once the db is updated, you're good to go for a while.

msr · 02-12-2026, 11:33 AM

As PeterT mentioned, one workaround for those who have the option is to switch to a Calibre Content Server. Syncing CalibreSync with that still works. I don't like it as much as syncing with my library in Dropbox, but at least it provides functionality while we wait (perhaps forever) to see if CalibreSync itself will be fixed, and doesn't require posthoc modification of the Calibre DB to add the removed columns back.

This means I can only update CalibreSync while the content server is running (something which I do rarely and only when necessary for particular tasks and only while I'm actively at home), rather than allowing me to update the DB on the fly from anywhere I have internet access, but it is better than having no access at all.

jtmart · 03-06-2026, 10:31 PM

Quote:

Originally Posted by GeniusJ

Glsparks2, your code didn't work for me as expected (I blame myself) so I tweaked it a bit. This is now working for me.

PowerShell script to restore missing columns in Calibre metadata.db across all libraries (with backups)

If a third-party tool expects older columns in Calibre’s metadata.db and they are missing, this PowerShell script will add them back to the books table if needed. It does this for every Calibre library under a root folder. It also makes timestamped backups of each metadata.db in a separate DBbackups folder.

What you need

Windows
This is for Windows PowerShell.

sqlite3.exe (the command-line tool)
Download the SQLite tools zip from sqlite.org. You want the zip named like: sqlite-tools-win-x64-<version>.zip
Extract it and make sure you have sqlite3.exe.

Place sqlite3.exe somewhere stable, then update the script path. Example location:
C:\Tools\sqlite\sqlite3.exe
You can also put it in another folder if you prefer. Just point the script at it.

Your Calibre library root folder
This is the folder that contains your library folders, each with a metadata.db inside.

Important safety note
Close Calibre before running this. Calibre can keep metadata.db open.

What it does

Finds every library folder under a root path that contains metadata.db

Creates a matching backup folder under DBbackups<LibraryName>\

Copies metadata.db to a timestamped backup file

Adds these columns to the books table if they do not exist:

flags INTEGER DEFAULT 1
isbn TEXT
lccn TEXT

You should only need to run this script once after Calibre updates to 9.x or any minor revision after that. So if you apply a Calibre update, open the updated version of Calibre, close it, then run this script to put the columns back in the database.

Script
Copy this into a file named something like: Calibre-Fix-AllLibraries.ps1

Code:

# -------------------------------------------------------------------
# Calibre-Fix-AllLibraries.ps1
#
# Purpose:
# - Scan a Calibre root folder for library directories ending in " Library"
# - For each library:
#   - Back up metadata.db into DBbackups\<LibraryName>\
#   - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run:
#   - If columns already exist, no schema change occurs
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\Windows\System32\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

# Folder where database backups are stored
# Each library gets its own subfolder under here
$BackupsRoot = Join-Path $CalibreRoot "DBbackups"

# Number of backups to keep per library
# Older backups beyond this count are deleted
$KeepBackups = 10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

# Ensure sqlite3.exe exists
if (-not (Test-Path $Sqlite3)) {
  throw "sqlite3.exe not found: $Sqlite3"
}

# Ensure the Calibre root folder exists
if (-not (Test-Path $CalibreRoot)) {
  throw "Calibre root not found: $CalibreRoot"
}

# Ensure the DBbackups root folder exists
# -Force means it will not error if it already exists
New-Item -ItemType Directory -Force -Path $BackupsRoot | Out-Null

# -------------------------------------------------------------------
# Discover library folders
# -------------------------------------------------------------------

# Find all directories under the Calibre root that:
# - End with " Library"
# - Contain a metadata.db file
$libraries = Get-ChildItem -Path $CalibreRoot -Directory -Force |
  Where-Object { $_.Name -like "* Library" } |
  Where-Object { Test-Path (Join-Path $_.FullName "metadata.db") }

# If no libraries are found, stop immediately
if (-not $libraries) {
  throw "No library folders ending with ' Library' containing metadata.db found under: $CalibreRoot"
}

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

foreach ($lib in $libraries) {

  # Friendly name of the library folder
  $libName = $lib.Name

  # Full path to this library's metadata.db
  $dbPath = Join-Path $lib.FullName "metadata.db"

  # Folder where backups for this library will live
  $backupDir = Join-Path $BackupsRoot $libName

  # Ensure the library-specific backup folder exists
  New-Item -ItemType Directory -Force -Path $backupDir | Out-Null

  # Generate a timestamp for the backup filename
  $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

  # Full path to the backup file
  $backupPath = Join-Path $backupDir "metadata.db.backup_$timestamp"

  try {

    # ---------------------------------------------------------------
    # Step 1: Back up metadata.db
    # ---------------------------------------------------------------

    # Copy metadata.db to the backup folder
    # -ErrorAction Stop ensures we do not proceed if backup fails
    Copy-Item -Path $dbPath -Destination $backupPath -ErrorAction Stop

    # ---------------------------------------------------------------
    # Step 2: Read existing columns from the books table
    # ---------------------------------------------------------------

    # Query SQLite for column names in the books table
    $cols = & $Sqlite3 $dbPath "SELECT name FROM pragma_table_info('books');"

    # Normalize output:
    # - Trim whitespace
    # - Remove empty lines
    $cols = $cols | ForEach-Object { $_.Trim() } | Where-Object { $_ }

    # Track whether any schema change is made
    $changed = $false

    # ---------------------------------------------------------------
    # Step 3: Add missing columns
    # ---------------------------------------------------------------

    # Add flags column if missing
    if ($cols -notcontains "flags") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
      $changed = $true
    }

    # Add isbn column if missing
    if ($cols -notcontains "isbn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN isbn TEXT;"
      $changed = $true
    }

    # Add lccn column if missing
    if ($cols -notcontains "lccn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN lccn TEXT;"
      $changed = $true
    }

    # ---------------------------------------------------------------
    # Step 4: Backup retention
    # ---------------------------------------------------------------

    # Get all backup files for this library, newest first
    $backups = Get-ChildItem -Path $backupDir -File -Filter "metadata.db.backup_*" -ErrorAction SilentlyContinue |
      Sort-Object LastWriteTime -Descending

    # If there are more backups than allowed, delete the oldest ones
    if ($backups.Count -gt $KeepBackups) {
      $backups | Select-Object -Skip $KeepBackups | Remove-Item -Force -ErrorAction SilentlyContinue
    }

    # ---------------------------------------------------------------
    # Step 5: Status output
    # ---------------------------------------------------------------

    if ($changed) {
      Write-Host "UPDATED:" $libName
    } else {
      Write-Host "NO CHANGE:" $libName
    }
  }
  catch {
    # Catch and report any error for this library without stopping the script
    Write-Host "FAILED:" $libName "-" $_.Exception.Message
  }
}

Can confirm this works brilliantly well, though it caused some anguish...and you only need to run the script once: https://www.mobileread.com/forums/sh...5&postcount=50 !

MysteryMan2 · 03-11-2026, 08:34 AM

I have been using Calibre Sync on my Android devices for quite a while and has been working fine. However, just tried to Sync my library, which is on Dropbox and getting an error.....

"Metadata: true: Failed to load library info, reason: Database Exception (no such column: isbn (code 1): while compiling: SELECT id, title, timestamp, pubdate, series_index, author_sort, isbn, iccn, path, has_cover, last_modified, uuid FROM books)....."

Any ideas of what has changed recently and/or how I can fix this?

I have added a user-defined column called isbn to my Calibre library, but this has not made any difference

MysteryMan2 · 03-11-2026, 11:39 AM

You beauty! This has saved me so much heartache! Thank you so much

Quote:

Originally Posted by GeniusJ

Glsparks2, your code didn't work for me as expected (I blame myself) so I tweaked it a bit. This is now working for me.

PowerShell script to restore missing columns in Calibre metadata.db across all libraries (with backups)

If a third-party tool expects older columns in Calibre’s metadata.db and they are missing, this PowerShell script will add them back to the books table if needed. It does this for every Calibre library under a root folder. It also makes timestamped backups of each metadata.db in a separate DBbackups folder.

What you need

Windows
This is for Windows PowerShell.

sqlite3.exe (the command-line tool)
Download the SQLite tools zip from sqlite.org. You want the zip named like: sqlite-tools-win-x64-<version>.zip
Extract it and make sure you have sqlite3.exe.

Place sqlite3.exe somewhere stable, then update the script path. Example location:
C:\Tools\sqlite\sqlite3.exe
You can also put it in another folder if you prefer. Just point the script at it.

Your Calibre library root folder
This is the folder that contains your library folders, each with a metadata.db inside.

Important safety note
Close Calibre before running this. Calibre can keep metadata.db open.

What it does

Finds every library folder under a root path that contains metadata.db

Creates a matching backup folder under DBbackups<LibraryName>\

Copies metadata.db to a timestamped backup file

Adds these columns to the books table if they do not exist:

flags INTEGER DEFAULT 1
isbn TEXT
lccn TEXT

You should only need to run this script once after Calibre updates to 9.x or any minor revision after that. So if you apply a Calibre update, open the updated version of Calibre, close it, then run this script to put the columns back in the database.

Script
Copy this into a file named something like: Calibre-Fix-AllLibraries.ps1

Code:

# -------------------------------------------------------------------
# Calibre-Fix-AllLibraries.ps1
#
# Purpose:
# - Scan a Calibre root folder for library directories ending in " Library"
# - For each library:
#   - Back up metadata.db into DBbackups\<LibraryName>\
#   - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run:
#   - If columns already exist, no schema change occurs
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\Windows\System32\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

# Folder where database backups are stored
# Each library gets its own subfolder under here
$BackupsRoot = Join-Path $CalibreRoot "DBbackups"

# Number of backups to keep per library
# Older backups beyond this count are deleted
$KeepBackups = 10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

# Ensure sqlite3.exe exists
if (-not (Test-Path $Sqlite3)) {
  throw "sqlite3.exe not found: $Sqlite3"
}

# Ensure the Calibre root folder exists
if (-not (Test-Path $CalibreRoot)) {
  throw "Calibre root not found: $CalibreRoot"
}

# Ensure the DBbackups root folder exists
# -Force means it will not error if it already exists
New-Item -ItemType Directory -Force -Path $BackupsRoot | Out-Null

# -------------------------------------------------------------------
# Discover library folders
# -------------------------------------------------------------------

# Find all directories under the Calibre root that:
# - End with " Library"
# - Contain a metadata.db file
$libraries = Get-ChildItem -Path $CalibreRoot -Directory -Force |
  Where-Object { $_.Name -like "* Library" } |
  Where-Object { Test-Path (Join-Path $_.FullName "metadata.db") }

# If no libraries are found, stop immediately
if (-not $libraries) {
  throw "No library folders ending with ' Library' containing metadata.db found under: $CalibreRoot"
}

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

foreach ($lib in $libraries) {

  # Friendly name of the library folder
  $libName = $lib.Name

  # Full path to this library's metadata.db
  $dbPath = Join-Path $lib.FullName "metadata.db"

  # Folder where backups for this library will live
  $backupDir = Join-Path $BackupsRoot $libName

  # Ensure the library-specific backup folder exists
  New-Item -ItemType Directory -Force -Path $backupDir | Out-Null

  # Generate a timestamp for the backup filename
  $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

  # Full path to the backup file
  $backupPath = Join-Path $backupDir "metadata.db.backup_$timestamp"

  try {

    # ---------------------------------------------------------------
    # Step 1: Back up metadata.db
    # ---------------------------------------------------------------

    # Copy metadata.db to the backup folder
    # -ErrorAction Stop ensures we do not proceed if backup fails
    Copy-Item -Path $dbPath -Destination $backupPath -ErrorAction Stop

    # ---------------------------------------------------------------
    # Step 2: Read existing columns from the books table
    # ---------------------------------------------------------------

    # Query SQLite for column names in the books table
    $cols = & $Sqlite3 $dbPath "SELECT name FROM pragma_table_info('books');"

    # Normalize output:
    # - Trim whitespace
    # - Remove empty lines
    $cols = $cols | ForEach-Object { $_.Trim() } | Where-Object { $_ }

    # Track whether any schema change is made
    $changed = $false

    # ---------------------------------------------------------------
    # Step 3: Add missing columns
    # ---------------------------------------------------------------

    # Add flags column if missing
    if ($cols -notcontains "flags") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
      $changed = $true
    }

    # Add isbn column if missing
    if ($cols -notcontains "isbn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN isbn TEXT;"
      $changed = $true
    }

    # Add lccn column if missing
    if ($cols -notcontains "lccn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN lccn TEXT;"
      $changed = $true
    }

    # ---------------------------------------------------------------
    # Step 4: Backup retention
    # ---------------------------------------------------------------

    # Get all backup files for this library, newest first
    $backups = Get-ChildItem -Path $backupDir -File -Filter "metadata.db.backup_*" -ErrorAction SilentlyContinue |
      Sort-Object LastWriteTime -Descending

    # If there are more backups than allowed, delete the oldest ones
    if ($backups.Count -gt $KeepBackups) {
      $backups | Select-Object -Skip $KeepBackups | Remove-Item -Force -ErrorAction SilentlyContinue
    }

    # ---------------------------------------------------------------
    # Step 5: Status output
    # ---------------------------------------------------------------

    if ($changed) {
      Write-Host "UPDATED:" $libName
    } else {
      Write-Host "NO CHANGE:" $libName
    }
  }
  catch {
    # Catch and report any error for this library without stopping the script
    Write-Host "FAILED:" $libName "-" $_.Exception.Message
  }
}

nephtys59 · 03-13-2026, 08:16 AM

Quote:

Originally Posted by MysteryMan2

I have been using Calibre Sync on my Android devices for quite a while and has been working fine. However, just tried to Sync my library, which is on Dropbox and getting an error.....

"Metadata: true: Failed to load library info, reason: Database Exception (no such column: isbn (code 1): while compiling: SELECT id, title, timestamp, pubdate, series_index, author_sort, isbn, iccn, path, has_cover, last_modified, uuid FROM books)....."

Any ideas of what has changed recently and/or how I can fix this?

I have added a user-defined column called isbn to my Calibre library, but this has not made any difference

No solution, the app creator doesn't answer emails requesting an update to the app. I even offered to pay a second time, if he fixes it, but nothing. Use the workarounds suggested in this thread.

bobkoure · 03-14-2026, 02:06 PM

Quote:

Originally Posted by GeniusJ

Code:

# - Scan a Calibre root folder for library directories ending in " Library"
# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

This assumes the file structure
[some path]\Calibre
\[some name] library
\[some name]library
\[some name] library
etc.

And most of us have a single (or maybe two) libraries, name created when we installed Calibre. I happened to name mine "Calibre Library" but it's in a root directory - so I need to make the line
$CalibreRoot ="D:"

------------------------------------------------------
The important bits (for me) were
1) columns got deleted (because Calibre wasn't using them?)
2) Calibre Sync does use those columns
3) here are the SQL statements to re-create them

At one point in my career (mid 80s) I was 'toolsmith' for a software development group and got to be really good at .BAT files. 40 years later I'm pretty rusty, but to the extent that they syntax is the same (exceptions are new) this looks really good.

For my own peace of mind I ran a test:
1) created c:\test
2) copied sqlite3.exe and Calibre-Fix-AllLibraries.ps1 to c:\test
3) created c:\test library
4) copied metadata.db to c:\test library
5) opened powershell
6) CD'd to c:\test
7) entered the line .\Calibre-Fix-AllLibraries.ps1 (note the ".\")
...and got "UPDATED: test library"

I copied the modified metadata.db to my calibre library folder, started Calibre and the app appears to run fine.
I copied metadata.db to the calibre library folders I have on my android tablet and phone and refresh is now working.

For anyone else looking to replicate what I did here are the lines I changed

Code:

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\test\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "C:\test"

And THANK YOU!!

bobkoure · 03-17-2026, 05:11 PM

Quote:

Originally Posted by MysteryMan2

I have added a user-defined column called isbn to my Calibre library, but this has not made any difference

Based on the fix that does work you need to add "flags, "isbn", and "lccn".
Simplest thing is to just modify the ps1 file to point at the root of your calibre library folder(s) and run it. But if you've already added 'isbn' maybe just add the other two?

Knightreader · 03-21-2026, 07:40 AM

Thanks for this script! Now I can use my library again via OneDrive and without a connection to a running Calibre Content Server on my Laptop. Great work!

justinmiller87 · 03-22-2026, 01:27 AM

For those of us on Linux, here's a bash script that I made based on your PS script that does the same thing:

Code:

#!/bin/bash
# -------------------------------------------------------------------
# calibre-fix-libraries.sh
#
# Purpose:
# - Back up metadata.db into DBbackups/<LibraryName>/
# - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
SQLITE3=$(command -v sqlite3)

# Calibre libraries to process
LIBRARIES=(
    "/path/to/Calibre_Library"
)

# Central folder where database backups are stored
BACKUPS_ROOT="/path/to/DBbackups"

# Number of backups to keep per library
KEEP_BACKUPS=10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

if [ -z "$SQLITE3" ] || [ ! -x "$SQLITE3" ]; then
    echo "Error: sqlite3 not found or not executable. Please install sqlite3."
    exit 1
fi

mkdir -p "$BACKUPS_ROOT"

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

for lib in "${LIBRARIES[@]}"; do
    if [ ! -d "$lib" ]; then
        echo "FAILED: Library directory not found: $lib"
        continue
    fi

    # Friendly name of the library folder
    libName=$(basename "$lib")

    # Full path to this library's metadata.db
    dbPath="$lib/metadata.db"

    if [ ! -f "$dbPath" ]; then
        echo "FAILED: metadata.db not found in $lib"
        continue
    fi

    # Folder where backups for this library will live
    backupDir="$BACKUPS_ROOT/$libName"
    mkdir -p "$backupDir"

    # Generate a timestamp for the backup filename
    timestamp=$(date +"%Y%m%d_%H%M%S")
    backupPath="$backupDir/metadata.db.backup_$timestamp"

    # Step 1: Back up metadata.db
    if ! cp "$dbPath" "$backupPath"; then
        echo "FAILED: $libName - Could not back up metadata.db"
        continue
    fi

    # Step 2: Read existing columns from the books table
    # Query SQLite for column names in the books table
    cols=$("$SQLITE3" "$dbPath" "SELECT name FROM pragma_table_info('books');" 2>/dev/null)
    if [ $? -ne 0 ]; then
         echo "FAILED: $libName - Could not read from database"
         continue
    fi

    changed=false

    # Step 3: Add missing columns
    # Check for 'flags'
    if ! echo "$cols" | grep -qx "flags"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
        changed=true
    fi

    # Check for 'isbn'
    if ! echo "$cols" | grep -qx "isbn"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN isbn TEXT;"
        changed=true
    fi

    # Check for 'lccn'
    if ! echo "$cols" | grep -qx "lccn"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN lccn TEXT;"
        changed=true
    fi

    # Step 4: Backup retention
    # Get all backup files for this library, newest first
    # using ls -t (sort by modification time, newest first)
    backups=($(ls -t "$backupDir"/metadata.db.backup_* 2>/dev/null))
    
    # If there are more backups than allowed, delete the oldest ones
    if [ "${#backups[@]}" -gt "$KEEP_BACKUPS" ]; then
        for (( i=$KEEP_BACKUPS; i<${#backups[@]}; i++ )); do
            rm -f "${backups[$i]}"
        done
    fi

    # Step 5: Status output
    if [ "$changed" = true ]; then
        echo "UPDATED: $libName"
    else
        echo "NO CHANGE: $libName"
    fi
done

You can confirm that they have been added with one or both of the following tests:

Code:

sqlite3 /path/to/Calibre_Library/metadata.db "PRAGMA table_info(books);" | grep -E "flags|isbn|lccn"

Code:

sqlite3 /path/to/Calibre_Library/metadata.db "Select name FROM pragma_table_info('books');"

Quote:

Originally Posted by GeniusJ

Glsparks2, your code didn't work for me as expected (I blame myself) so I tweaked it a bit. This is now working for me.

PowerShell script to restore missing columns in Calibre metadata.db across all libraries (with backups)

If a third-party tool expects older columns in Calibre’s metadata.db and they are missing, this PowerShell script will add them back to the books table if needed. It does this for every Calibre library under a root folder. It also makes timestamped backups of each metadata.db in a separate DBbackups folder.

What you need

Windows
This is for Windows PowerShell.

sqlite3.exe (the command-line tool)
Download the SQLite tools zip from sqlite.org. You want the zip named like: sqlite-tools-win-x64-<version>.zip
Extract it and make sure you have sqlite3.exe.

Place sqlite3.exe somewhere stable, then update the script path. Example location:
C:\Tools\sqlite\sqlite3.exe
You can also put it in another folder if you prefer. Just point the script at it.

Your Calibre library root folder
This is the folder that contains your library folders, each with a metadata.db inside.

Important safety note
Close Calibre before running this. Calibre can keep metadata.db open.

What it does

Finds every library folder under a root path that contains metadata.db

Creates a matching backup folder under DBbackups<LibraryName>\

Copies metadata.db to a timestamped backup file

Adds these columns to the books table if they do not exist:

flags INTEGER DEFAULT 1
isbn TEXT
lccn TEXT

You should only need to run this script once after Calibre updates to 9.x or any minor revision after that. So if you apply a Calibre update, open the updated version of Calibre, close it, then run this script to put the columns back in the database.

Script
Copy this into a file named something like: Calibre-Fix-AllLibraries.ps1

Code:

# -------------------------------------------------------------------
# Calibre-Fix-AllLibraries.ps1
#
# Purpose:
# - Scan a Calibre root folder for library directories ending in " Library"
# - For each library:
#   - Back up metadata.db into DBbackups\<LibraryName>\
#   - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run:
#   - If columns already exist, no schema change occurs
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\Windows\System32\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

# Folder where database backups are stored
# Each library gets its own subfolder under here
$BackupsRoot = Join-Path $CalibreRoot "DBbackups"

# Number of backups to keep per library
# Older backups beyond this count are deleted
$KeepBackups = 10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

# Ensure sqlite3.exe exists
if (-not (Test-Path $Sqlite3)) {
  throw "sqlite3.exe not found: $Sqlite3"
}

# Ensure the Calibre root folder exists
if (-not (Test-Path $CalibreRoot)) {
  throw "Calibre root not found: $CalibreRoot"
}

# Ensure the DBbackups root folder exists
# -Force means it will not error if it already exists
New-Item -ItemType Directory -Force -Path $BackupsRoot | Out-Null

# -------------------------------------------------------------------
# Discover library folders
# -------------------------------------------------------------------

# Find all directories under the Calibre root that:
# - End with " Library"
# - Contain a metadata.db file
$libraries = Get-ChildItem -Path $CalibreRoot -Directory -Force |
  Where-Object { $_.Name -like "* Library" } |
  Where-Object { Test-Path (Join-Path $_.FullName "metadata.db") }

# If no libraries are found, stop immediately
if (-not $libraries) {
  throw "No library folders ending with ' Library' containing metadata.db found under: $CalibreRoot"
}

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

foreach ($lib in $libraries) {

  # Friendly name of the library folder
  $libName = $lib.Name

  # Full path to this library's metadata.db
  $dbPath = Join-Path $lib.FullName "metadata.db"

  # Folder where backups for this library will live
  $backupDir = Join-Path $BackupsRoot $libName

  # Ensure the library-specific backup folder exists
  New-Item -ItemType Directory -Force -Path $backupDir | Out-Null

  # Generate a timestamp for the backup filename
  $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

  # Full path to the backup file
  $backupPath = Join-Path $backupDir "metadata.db.backup_$timestamp"

  try {

    # ---------------------------------------------------------------
    # Step 1: Back up metadata.db
    # ---------------------------------------------------------------

    # Copy metadata.db to the backup folder
    # -ErrorAction Stop ensures we do not proceed if backup fails
    Copy-Item -Path $dbPath -Destination $backupPath -ErrorAction Stop

    # ---------------------------------------------------------------
    # Step 2: Read existing columns from the books table
    # ---------------------------------------------------------------

    # Query SQLite for column names in the books table
    $cols = & $Sqlite3 $dbPath "SELECT name FROM pragma_table_info('books');"

    # Normalize output:
    # - Trim whitespace
    # - Remove empty lines
    $cols = $cols | ForEach-Object { $_.Trim() } | Where-Object { $_ }

    # Track whether any schema change is made
    $changed = $false

    # ---------------------------------------------------------------
    # Step 3: Add missing columns
    # ---------------------------------------------------------------

    # Add flags column if missing
    if ($cols -notcontains "flags") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
      $changed = $true
    }

    # Add isbn column if missing
    if ($cols -notcontains "isbn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN isbn TEXT;"
      $changed = $true
    }

    # Add lccn column if missing
    if ($cols -notcontains "lccn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN lccn TEXT;"
      $changed = $true
    }

    # ---------------------------------------------------------------
    # Step 4: Backup retention
    # ---------------------------------------------------------------

    # Get all backup files for this library, newest first
    $backups = Get-ChildItem -Path $backupDir -File -Filter "metadata.db.backup_*" -ErrorAction SilentlyContinue |
      Sort-Object LastWriteTime -Descending

    # If there are more backups than allowed, delete the oldest ones
    if ($backups.Count -gt $KeepBackups) {
      $backups | Select-Object -Skip $KeepBackups | Remove-Item -Force -ErrorAction SilentlyContinue
    }

    # ---------------------------------------------------------------
    # Step 5: Status output
    # ---------------------------------------------------------------

    if ($changed) {
      Write-Host "UPDATED:" $libName
    } else {
      Write-Host "NO CHANGE:" $libName
    }
  }
  catch {
    # Catch and report any error for this library without stopping the script
    Write-Host "FAILED:" $libName "-" $_.Exception.Message
  }
}

JimmXinu · 03-22-2026, 10:17 AM

It has been stated before, but bears repeating:

You should not be changing the database in your actual Calibre library. You should be making a copy first and modifying that.

Just as the DB schema change broke Calibre Sync, changing your library DB might prevent Calibre itself from using your library in a future version.

My personal script uses rsync to quickly update the copy of my library, then changes the DB file before sync'ing it to my device.

I also want to reiterate: the issue isn't the change to Calibre, it's the fact Calibre Sync is not being maintained now.

03-22-2026, 10:17 AM	#342
JimmXinu Plugin Developer Posts: 7,336 Karma: 5007213 Join Date: Dec 2011 Location: Midwest USA Device: Kobo Clara Colour running KOReader	Make a Copy First! It has been stated before, but bears repeating: You should not be changing the database in your actual Calibre library. You should be making a copy first and modifying that. Just as the DB schema change broke Calibre Sync, changing your library DB might prevent Calibre itself from using your library in a future version. My personal script uses rsync to quickly update the copy of my library, then changes the DB file before sync'ing it to my device. I also want to reiterate: the issue isn't the change to Calibre, it's the fact Calibre Sync is not being maintained now.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
BookFusion eBook Reader & Management Sync Across All Devices Android, iOS & Web	skillachie	Reading and Management	244	01-25-2026 09:06 AM
Android/iOS reading position sync possible?	Freakeao	Android Devices	3	11-08-2012 01:17 AM
PRS-T1 ePub Sync with other Android / iOS	Razva	Sony Reader	10	06-29-2012 05:37 PM
Sync across IOS and Android?	spursbob	General Discussions	0	06-24-2011 12:03 PM
Calibre and iOS 4 Sync	HarryO53	Calibre	25	07-02-2010 03:20 PM

02-12-2026, 11:33 AM	#333
msr Enthusiast Posts: 31 Karma: 59820 Join Date: Jul 2009 Device: Kobo	As PeterT mentioned, one workaround for those who have the option is to switch to a Calibre Content Server. Syncing CalibreSync with that still works. I don't like it as much as syncing with my library in Dropbox, but at least it provides functionality while we wait (perhaps forever) to see if CalibreSync itself will be fixed, and doesn't require posthoc modification of the Calibre DB to add the removed columns back. This means I can only update CalibreSync while the content server is running (something which I do rarely and only when necessary for particular tasks and only while I'm actively at home), rather than allowing me to update the DB on the fly from anywhere I have internet access, but it is better than having no access at all.

03-11-2026, 08:34 AM	#335
MysteryMan2 Member Posts: 21 Karma: 10 Join Date: Oct 2014 Device: ASUS TF101	I have been using Calibre Sync on my Android devices for quite a while and has been working fine. However, just tried to Sync my library, which is on Dropbox and getting an error..... "Metadata: true: Failed to load library info, reason: Database Exception (no such column: isbn (code 1): while compiling: SELECT id, title, timestamp, pubdate, series_index, author_sort, isbn, iccn, path, has_cover, last_modified, uuid FROM books)....." Any ideas of what has changed recently and/or how I can fix this? I have added a user-defined column called isbn to my Calibre library, but this has not made any difference

03-21-2026, 07:40 AM	#340
Knightreader Connoisseur Posts: 77 Karma: 2345678 Join Date: Nov 2018 Device: 1. Kindle PW 6, 2. Kobo Forma	Thanks for this script! Now I can use my library again via OneDrive and without a connection to a running Calibre Content Server on my Laptop. Great work!