Using DuckDB in Windows PowerShell, PowerShell Core (pwsh) - independent from OS

readme.md

Use it via Browser, the DuckDB Web Shell

This is pretty easy to have a play... and it also supports data that is in S3 or other data storages.

Just go to https://shell.DuckDB.org/ and enter a query like

Select 10+15 as result;

It is important to always have a ; at the end of the query.

When you are going into the live demo through the DuckDB website, you get an example queries like

-- Create table from Parquet file
CREATE TABLE train_services AS
    FROM 'https://blobs.DuckDB.org/train_services.parquet';

-- Get the top-3 busiest train stations
SELECT
    station_name,
    count(*) AS num_services
FROM train_services
GROUP BY ALL
ORDER BY num_services DESC
LIMIT 3;

which basically loads a remote parquet file into your browser cache and lets you work on it.

Use it via DuckDB.exe and PowerShell

If you want to execute DuckDB from CommandLine like PowerShell (Core), you can download it here: https://duckdb.org/install/?platform=windows&environment=cli Please make sure you download the right version for your processor architecture, which is either x64 or arm64. Check if you have installed vcredist 64bit, which is recommended to have it installed, when using Windows. You can find and download it here https://learn.microsoft.com/en-us/cpp/windows/latest-supported-vc-redist?view=msvc-170#latest-supported-redistributable-version

You just need to unzip it and execute the DuckDB.exe file. This can be done by double-clicking on it or start it from PowerShell

& .\duckdb.exe

Then you can use the following commands to get a result

.open :memory:
Select 10+15 as result;

It is easy than that!

Use duckdb.exe in a browser with -ui extension

Yes! DuckDB supports extensions, which it will install automatically when using them. One very helpful one is ui. You can read more about that here: More information can be found here: https://duckdb.org/docs/stable/core_extensions/ui

But that extension is firing up a small webserver and serves a small local website to use DuckDB through you favorite browser. This is currently only possible for x64 processor architecture, arm64 is not supported yet.

Use duckdb.exe directly from PowerShell

Below you see a more integrated way with .NET to use DuckDB in PowerShell, but here is another easy approach. You can directly ask the exe file to do some things. I will show you a few examples:

# Create an temporary alias to work easier with the exe
Set-Alias duck .\duckdb.exe

# Get the result direclty through your in-memory database
duck -c 'Select 10+15 as result;'

# Execute that command with a persistent database
duck .\duck.db -c 'Select 10+15 as result;'

# You should now see a duck.db file in your current directory

# If you want to start the ui from here, execute
duck -ui

# To return the generated data in a readable format, return it as json (csv is also possible) and parse it directly
# This should not be used to output a large amount of data, but useful for some small command or to return some values
duck -json -c 'Select 10+15 as result, 23*3 as column2;' | ConvertFrom-Json

# View other options
duck -help

Use it via DuckDB.NET in PowerShell

This is the best approach if you permanently working with the database as this is the tightest way of integration. This is also tested on Ubuntu 24.04 LTS

So navigate whereever you want to work with DuckDB.NET We will only download, unzip and load a few files.

# Create a new directory for the packages
New-Item -Path lib -ItemType Directory

# Download the two DuckDB packages from Nuget without Install-Package or Save-Package
Invoke-WebRequest -UseBasicParsing -Uri https://www.nuget.org/api/v2/package/DuckDB.NET.Bindings.Full -OutFile ./lib
Invoke-WebRequest -UseBasicParsing -Uri https://www.nuget.org/api/v2/package/DuckDB.NET.Data.Full -OutFile ./lib

# Expand and delete the nupkg files
Set-Location ./lib
Get-ChildItem -Path ./lib/ -Filter *.nupkg | % { Expand-Archive -Path $_; Remove-Item -Path $_ }
Set-Location ..

# Install and import modules from Apteco to easily load the dependencies independent from os
Install-Module WriteLog, ImportDependency
Import-Module ImportDependency

# Import the nuget packages
Import-Dependency -LoadWholePackageFolder -LocalPackageFolder .\lib -verbose

# Check if you have installed vcredist 64bit, which is recommended to have it installed, when using Windows
# You can find and download it here https://learn.microsoft.com/en-us/cpp/windows/latest-supported-vc-redist?view=msvc-170#latest-supported-redistributable-version
$psEnv = Get-PSEnvironment
$psEnv.VcRedist

# Create an in-memory database connection and open it
$conn = [duckDB.NET.Data.DuckDBConnection]::new("DataSource=:memory:;")
$conn.Open()

# Create a new command and fill the query
$cmd = $conn.CreateCommand()
$cmd.CommandText = "Select 10+15 as result;"

# To just execute and get that single result number, you could use this command
$cmd.ExecuteScalar()

# To gather data back, use this approach, but there are more possibilites (e.g. streaming) available
$cmd = $conn.CreateCommand()
$cmd.CommandText = "Select 10+15 as result, 23*3 as column2;"
$datatable = [System.Data.DataTable]::new()
$sqlResult = $cmd.ExecuteReader()
$datatable.Load($sqlResult, [System.Data.Loadoption]::Upsert)
$datatable # to output your data

# There is also something like $cmd.ExecuteNonQuery() available which
# can be used for inserting or updating data

# Close all results/command/connections
$sqlResult.close()
$cmd.Dispose()
$conn.Close()

More information can be found here: https://duckdb.net/docs/basic-usage.html