Nushell and DuckDB

2025-11-30 230 words 2 minutes

Contents

Nushell¹ is an amazing shell that brings structure to terminal. Nevertheless, sometime it would be usefull to have the raw power of SQL directly into the terminal. To acheive this, the below scripts allows use duckdb² as query engine on nushell tables.

# Query Nushell data using DuckDB SQL
#
# Parameters:
#   query: string - A DuckDB SQL query that operates on the "data" table
#
# Input: Any Nushell structured data (tables, records, lists)
# Output: The query results as Nushell structured data
def nu-duck [query] {
    let full_query = $"
    CREATE TABLE data as
        SELECT *
        FROM read_json\('/dev/stdin', timestampformat='%Y-%m-%d %H:%M:%S.%n %z'\);

     COPY \( ( $query ) \) TO '/dev/stdout' \(FORMAT json, array true\)"
    $in | to json | duckdb -c $full_query | from json
}

Example:


 ~#@❯ ls -l /nix/var/nix/profiles/ | nu-duck 'SELECT target, COUNT(*) as count FROM data GROUP BY target HAVING count > 1'
╭───┬────────────────────────────────────────────────────────────────────────────────────────────┬───────╮
│ # │                                           target                                           │ count │
├───┼────────────────────────────────────────────────────────────────────────────────────────────┼───────┤
│ 0 │ /nix/store/k2daxdk9202cphkfm7adrp3gc6l85r0k-nixos-system-la-machine-25.11.20251122.050e09e │     2 │
│ 1 │ /nix/store/694k8m59j59j8ng241ms8v74sc1sdj06-nixos-system-la-machine-25.11.20251122.050e09e │     2 │
│ 2 │ /nix/store/dprqzsni9hivn55x2v6qy714ddr8n0f7-nixos-system-la-machine-25.11.20251122.050e09e │     2 │
│ 3 │ /nix/store/38gs7dxxq573bjhigs1a4797n5l97a2x-nixos-system-la-machine-25.11.20251122.050e09e │     2 │
╰───┴────────────────────────────────────────────────────────────────────────────────────────────┴───────╯

Cool Part

The cool part is that everything is processed in-memory. Duckdb reads and writes directly to the stdin and stdout. Moreover, duckdb has been tuned to read timestamp in nushell format.

https://www.nushell.sh/ ↩︎
https://duckdb.org/ ↩︎
documentation has partly been written by claude code. ↩︎