Feature: Support :memory: DuckDB connections and direct file querying (e.g. CSV, Parquet)

[daviddwlee84]

Summary

DuckDB's ability to directly query file formats (CSV, Parquet, Feather, etc.) via read_csv(), read_parquet() makes it a powerful tool for ad-hoc data exploration. However, sqlit currently requires a valid .duckdb file path for DuckDB connections, which makes this workflow unnecessarily cumbersome.

Current Workaround

To use DuckDB's file querying capabilities in sqlit today, you have to:

1. Create a valid .duckdb file externally first:

   python3 -c "import duckdb; duckdb.connect('/tmp/scratch.duckdb').close()"

   (Note: touch /tmp/scratch.duckdb does NOT work — DuckDB rejects empty files as invalid)

2. Manually add a connection in ~/.sqlit/connections.json or via the UI pointing to that file

3. Connect to it in sqlit, then run queries like:

   SELECT * FROM read_csv('/path/to/data.csv');
   SELECT * FROM read_parquet('/path/to/data.parquet');

This works, but it's far from the "pick-up-and-go" experience sqlit strives for.

Proposal

1. Support :memory: for DuckDB connections

Currently, entering :memory: in the Database File field shows "File not found." because the UI validates it as a file path. DuckDB's Python API natively supports duckdb.connect(":memory:") for in-memory databases.

Expected behavior: Allow :memory: as a special value in the Database File field that bypasses file existence validation and creates an in-memory DuckDB connection. This is the standard way to use DuckDB for ad-hoc querying without persisting data.

2. Direct file opening via CLI

It would be amazing if sqlit could open data files directly, automatically using DuckDB under the hood:

# Directly open a CSV file
sqlit data.csv
sqlit /path/to/data.parquet
sqlit /path/to/data.feather

# Or explicitly specify the approach
sqlit connect duckdb --file-path /path/to/data.csv

When sqlit detects a non-database file extension (.csv, .parquet, .feather, .json, etc.), it could automatically:

• Create an in-memory DuckDB connection
• Run the appropriate read_*() function
• Display the results

This would make sqlit a universal data file viewer from the terminal, similar to how visidata works but with full SQL querying capabilities.