Exporting Notebooks from DuckDB UI

DuckDB added a very cool UI last week and I’ve been using it as my primary interface to DuckDB since.

One thing that bothered me was that the SQL I was writing in the notebooks wasn’t exportable. Since DuckDB uses DuckDB in the background for storing notebooks, getting the SQL out is easy enough.

Here’s a rough-and-readyscript that will dump it out to a series of .sql files, one per notebook. It only exports the SQL; any other data like useDatabase and isActive is not included.

#!/bin/bash
#
# @rmoff / 2025-03-19

# Create a temporary file for the JSON output
temp_file=$(mktemp)

# Run the DuckDB query and save the output to the temp file
duckdb -json ~/.duckdb/extension_data/ui/ui.db \
        -c 'select title,json from ui.main.notebook_versions where expires is null' \
        | grep -v "^Run Time" > "$temp_file"

# Create a directory with timestamp for the output
timestamp=$(date +"%Y%m%d_%H%M%S")
output_dir="DuckDB_notebooks_${timestamp}"
mkdir -p "$output_dir"

# Process the JSON output
jq -c '.[]' "$temp_file" | while read -r item; do

    # Extract title
    title=$(echo "$item" | jq -r '.title')

    # Create a sanitized filename from the title
    # Replace spaces with underscores and remove special characters
    filename=$(echo "$title" | tr ' ' '_' | tr -cd 'a-zA-Z0-9_-').sql

    # Extract all queries from the notebook JSON and save them to the file
    # Credit to Hayley Jane Wakenshaw for the Duck ASCII art :)
    echo "$item" | jq -r '.json' | jq -r '.cells[] | "\n--           _      _      _\n--         >(.)__ <(.)__ =(.)__\n--          (___/  (___/  (___/ \n-- °º¤ø,¸¸,ø¤º°`°º¤ø,¸,ø¤°º¤ø,¸¸,ø¤º°`°º¤ø,¸\n\n" + .query' > "$output_dir/${filename}"

    echo "Created file: $filename"
done

# Clean up the temporary file
rm "$temp_file"

echo "All notebook queries have been saved to individual files in folder $output_dir."
echo ""
echo "To create a new gist run: gh gist create --desc \"DuckDB UI Notebook export $timestamp\" $output_dir/*.sql"

In use it looks like this:

Created file: Load_historical_data.sql
Created file: Load_latest_data.sql
Created file: Buildadd_to_main_tables.sql
Created file: Join_and_Analyse.sql
Created file: Untitled_Notebook.sql
All notebook queries have been saved to individual files in folder DuckDB_notebooks_20250319_170611.

To create a new gist run: gh gist create DuckDB_notebooks_20250319_170611/*.sql

I use it to then upload the SQL to gist (the GitHub CLI tool gh is awesome, install it if you haven’t already!) with:

❯ gh gist create --desc "DuckDB UI Notebook export 20250319_170851" DuckDB_notebooks_20250319_170851/*.sql
- Creating gist with multiple files
✓ Created secret gist Buildadd_to_main_tables.sql
https://gist.github.com/rmoff/7c681529754a74d7e8f6bff31e069d5b

For other DuckDB UI notebook export ideas, see here and here