Upgrade DuckDB Storage Versions
No more juggling CLI paths and versions.
Currently, data engineers and data scientists are engaged in discussions about DuckDB, which is one of the most popular topics in the field. This database management system provides a unique and refreshing alternative approach with its file-based system. However, it may take some time to adapt to its unique functionality. DuckDB frequently releases new versions, which come with additional features and deprecate older ones. When this happens, the storage version of the database may also be upgraded, requiring users to use a previous version to work with their existing database. Nonetheless, DuckDB provides a clear explanation and solution for this behavior.
If you know the problem, scroll to the bottom to check the solution!
The DuckDB internal storage format is currently in flux, and is expected to change with each release until we reach v1.0.0.
The Problem
For most users, this is nothing more than a minor inconvenience. Understanding the complexities of storage versions is beyond the scope of most people, and a simple upgrade usually solves the problem. The real challenge lies in the fact that most users need to keep multiple versions of DuckDB installed on their devices to facilitate the upgrading process. In the past, I would manually install pinned versions, export data, reinstall the desired version, and then import the data.
I frequently encountered this problem attempting to work with demos that had already materialized DuckDB databases. I wanted to make the upgrade process much more accessible to prevent repeating steps.
The Solution
DuckDB provides helpful information related to compatible versions and how to check the storage version of a particular file. With this in mind, we can scaffold a few boilerplate solutions to check the version and download the appropriate CLI version of DuckDB for a particular platform.
With this code, we define a StorageVersion
class to represent different storage versions, each associated with a list of DuckDB versions that support it. It also includes a list of predefined STORAGE_VERSIONS
, detailing these associations.
The script provides two main functions. First, it has a check_version
function that checks the version of a DuckDB storage file by reading its binary data. Second, it has a download_version
function to download and extract the DuckDB binary for a specific version. The binary choice is determined based on the operating system (Darwin, Windows, Linux) and processor architecture (i386, arm). The downloaded binary is then given executable permissions using the subprocess
module's chmod
command.
Please note that the code assumes a specific directory structure and relies on the availability of DuckDB binaries on the provided download URL.
We can now chain these functions together to perform the upgrade.
The upgrade_database
function is designed to facilitate upgrading a DuckDB database from an old version to a new version. It takes three parameters:
old_name
: The name of the existing DuckDB database that you want to upgrade.new_name
: The name for the upgraded database.new_version
: The target DuckDB version (default is "v0.9.1").
Here’s a step-by-step explanation of what the function does:
- It starts by calling the
download_version
function with the specifiednew_version
to ensure that the desired DuckDB version is downloaded and available for use. - It determines the DuckDB version of the existing database (
old_name
) by searching through the predefinedSTORAGE_VERSIONS
list and find the most recent DuckDB version that corresponds to the storage version of the old database. This version is stored in theduckdb_version
variable. - It then calls the
download_version
function again, this time to ensure that the DuckDB binary for the determinedduckdb_version
is downloaded and available. - It uses the
subprocess.run
function to execute two DuckDB command-line operations to export the data from the old database (old_name
) to a temporary location named 'tmp' and import the data from the ‘tmp’ location into a new database with the specifiednew_name
using thenew_version
of DuckDB. - After the database upgrade is completed, the script removes the temporary ‘tmp’ directory using
shutil.rmtree("tmp")
.
In summary, this function automates the process of upgrading a DuckDB database. It takes care of downloading necessary DuckDB versions, exporting and importing data, and cleaning up temporary files.
Conclusion
I may consider turning this into a CLI in the future, but for now, it serves as a quick upgrade tool. It could also use a few improvements by checking better to determine if downloads are necessary and optionally using previously installed locations to avoid duplication.
I hope this is helpful if you’re running into challenges maintaining different versions of DuckDB databases.
Full Source Code
import platform
import shutil
import struct
import subprocess
import zipfile
from io import BytesIO
from pathlib import Path
import requests
BASE_DOWNLOAD_URL = "https://github.com/duckdb/duckdb/releases/download"
TMP_PATH = "../tmp"
DARWIN_FILE = "duckdb_cli-osx-universal.zip"
WINDOWS_FILE = "duckdb_cli-windows-amd64.zip"
LINUX_I386_FILE = "duckdb_cli-linux-amd64.zip"
LINUX_ARM_FILE = "duckdb_cli-linux-aarch64.zip"
class StorageVersion:
def __init__(self, version, duckdb_versions):
self.version = version
self.duckdb_versions = duckdb_versions
def __str__(self):
return f"Storage Version {self.version} supported by DuckDB versions: {', '.join(self.duckdb_versions)}"
STORAGE_VERSIONS = [
StorageVersion(64, ["v0.9.0", "v0.9.1"]),
StorageVersion(51, ["v0.8.0", "v0.8.1"]),
StorageVersion(43, ["v0.7.0", "v0.7.1"]),
StorageVersion(39, ["v0.6.0", "v0.6.1"]),
StorageVersion(38, ["v0.5.0", "v0.5.1"]),
StorageVersion(33, ["v0.3.3", "v0.3.4", "v0.4.0"]),
StorageVersion(31, ["v0.3.2"]),
StorageVersion(27, ["v0.3.1"]),
StorageVersion(25, ["v0.3.0"]),
StorageVersion(21, ["v0.2.9"]),
StorageVersion(18, ["v0.2.8"]),
StorageVersion(17, ["v0.2.7"]),
StorageVersion(15, ["v0.2.6"]),
StorageVersion(13, ["v0.2.5"]),
StorageVersion(11, ["v0.2.4"]),
StorageVersion(6, ["v0.2.3"]),
StorageVersion(4, ["v0.2.2"]),
StorageVersion(1, ["v0.2.1 and prior"]),
]
def check_version(path: str):
"""Check the version of a DuckDB storage file."""
pattern = struct.Struct("<8x4sQ")
return pattern.unpack(open(path, "rb").read(pattern.size))[-1]
def download_version(version: str):
"""Download and extract the DuckDB binary for a specific version."""
system = platform.system()
processor = platform.processor()
if system == "Darwin":
FILE_NAME = DARWIN_FILE
elif system == "Windows":
FILE_NAME = WINDOWS_FILE
elif system == "Linux":
if processor == "i386":
FILE_NAME = LINUX_I386_FILE
elif processor == "arm":
FILE_NAME = LINUX_ARM_FILE
LOCAL_VERSION_PATH = f"{TMP_PATH}/{version}"
DOWNLOAD_VERSION_PATH = f"{BASE_DOWNLOAD_URL}/{version}/{FILE_NAME}"
if not Path(LOCAL_VERSION_PATH).exists():
Path(LOCAL_VERSION_PATH).mkdir(parents=True, exist_ok=True)
zipfile.ZipFile(
BytesIO(requests.get(DOWNLOAD_VERSION_PATH).content)
).extractall(LOCAL_VERSION_PATH)
subprocess.run(["chmod", "755", f"{LOCAL_VERSION_PATH}/duckdb"])
return f"{LOCAL_VERSION_PATH}/duckdb"
def upgrade_database(old_name, new_name, new_version: str = "v0.9.1"):
"""Upgrade a DuckDB database from an old version to a new version."""
download_version(new_version)
duckdb_version = [
x.duckdb_versions[-1]
for x in STORAGE_VERSIONS
if x.version == check_version(old_name)
][0]
download_version(duckdb_version)
subprocess.run(
[f"{TMP_PATH}/{duckdb_version}/duckdb", old_name, "-c", "EXPORT DATABASE 'tmp'"]
)
subprocess.run(
[f"{TMP_PATH}/{new_version}/duckdb", new_name, "-c", "IMPORT DATABASE 'tmp'"]
)
shutil.rmtree("tmp")