Default extension_directory not respecting XDG Base Directory Specification

[WillC33]

Currently, when extension_directory is nil (the default), DuckDB falls back to ~/.duckdb/extensions/, which pollutes the user's home directory.

This violates the [XDG Base Directory Specification](https://specifications.freedesktop.org/basedir-spec/basedir-spec-latest.html), which is the standard on Linux for where applications should store cache/data/config files.

Extensions are cached downloads, so they belong in $XDG_CACHE_HOME/duckdb/extensions (defaulting to ~/.cache/duckdb/extensions when XDG_CACHE_HOME is unset).

Users of duckdbex often have no direct control over the config if they're using libraries like phoenix_analytics which call Duckdbex.open() without passing a custom config. This means there's no way to fix the path without forking upstream dependencies.

Proposed fix

Change the default in lib/config.ex from:

extension_directory: nil,

To:

extension_directory: System.get_env("XDG_CACHE_HOME", Path.expand("~/.cache")) <> "/duckdb/extensions",

This is a one-line change that makes duckdbex XDG-compliant by default, while still allowing users to override it explicitly.

I've been running this locally with an override in mix to remove .duckdb from my home and I am happy to raise a PR with a fix and a test.

I'm aware this could be argued as "should be fixed in DuckDB core." However, [that issue has been open since April 2024](duckdb/duckdb#11827) with a contributor ready to implement, but no approval from DuckDB Labs.

In the meantime, downstream users like myself pull in duckdbex transitively (via phoenix_analytics) and have no way to pass a custom config. This fix uses DuckDB's existing extension_directory option with an XDG-compliant default. Thanks :)

[AlexR2D2]

Hi! Thanks for the proposition!
I released the DuckDB bug fix update and:

• Added the DUCKDBEX_EXTENSION_DIRECTORY env var to set default value of extensions directory in %DBConfig{}.
• Added the DUCKDBEX_TEMPORARY_DIRECTORY env var to set default value of temporary directory in %DBConfig{}.
  so, I think should help because phoenix_analytics creates the DB with the default %DBConfig{} and in the default config there are this thing now:

extension_directory: System.get_env("DUCKDBEX_EXTENSION_DIRECTORY"),

and this is just independent Duckdbex thing

[WillC33]

That's awesome thanks very much!