DaffyDB.linux-x64 0.8.0

DaffyDB

Query Parquet, CSV, JSON and Excel files with any SQL Server client.

DaffyDB listens on port 1433 and speaks the Tabular Data Stream wire protocol, so anything that already knows how to connect to SQL Server can read a folder of data files without an import step, an ETL job, or a database server. Execution is DuckDB.

dotnet tool install -g DaffyDB

Already installed? Upgrade to the latest version with the command below. Stop any running daffydb first: on Windows a running copy holds its files open, and the update fails.

dotnet tool update -g DaffyDB

Coming from 0.4 or earlier: your tables now live in a database named daffydb instead of in master, which holds only the system catalog, as on SQL Server. A client that names no database lands in daffydb, so most need no change. A connection string or query that names master should name daffydb instead. The folder scan changed too: it reads JSON now, but stops two folders down and passes over build and tooling folders, so a file deeper than that needs an --init script. "Where a table comes from", below, has the details.

Coming from 0.6: @@VERSION, xp_msver and sp_server_info now give DaffyDB's name where they gave SQL Server's. The version numbers beside it are unchanged, so clients that read those see no difference.

Coming from 0.7: a folder of part files, such as part-00000.parquet and on or DuckDB's data_0.parquet and on, is now one table named after the folder, where it was a table per part. And a --data-dir typed with a backslash on the end, as tab completion leaves it, no longer puts the top-level tables in a schema named after the folder.

Use it

Stand in a folder that has data files in it and run:

daffydb

Every .parquet, .csv, .json and .xlsx file underneath becomes a table, and you get a prompt for trying T-SQL without reaching for another client:

daffydb> :tables

  schema  name       type   cols  sample
  ------  ---------  -----  ----  --------------------------------------
  dbo     customers  table     5  SELECT TOP 5 * FROM [dbo].[customers];
  dbo     orders     table     6  SELECT TOP 5 * FROM [dbo].[orders];

  2 row(s)

daffydb> SELECT TOP 3 name, city FROM customers ORDER BY name;

  name            city
  --------------  ----------
  Ada Lovelace    London
  Alan Turing     Manchester
  Barbara Liskov  Boston
  3 row(s)

:help lists the commands, :quit stops the server. Clients stay connected and keep working while you use it. Long results are paged rather than dumped; [space] for the next page, [q] to stop. On Windows, :pbi opens Power BI Desktop already connected; see "Which clients" below.

Meanwhile connect from anywhere else. Encryption needs no setup: Encrypt=Mandatory is what most clients send by default, and it works against the self-signed certificate DaffyDB generates for itself on first run.

sqlcmd:

sqlcmd -S tcp:127.0.0.1,1433 -U any -P any -C -Q "SELECT TOP 5 * FROM customers"

ADO.NET:

using Microsoft.Data.SqlClient;

const string cs = "Server=tcp:127.0.0.1,1433;Encrypt=Mandatory;TrustServerCertificate=True;"
                + "Pooling=False;User Id=any;Password=any";

using var connection = new SqlConnection(cs);
connection.Open();

using var command = new SqlCommand("SELECT TOP 5 name, city FROM customers ORDER BY name", connection);
using var reader = command.ExecuteReader();
while (reader.Read()) Console.WriteLine($"{reader.GetString(0)}  {reader.GetString(1)}");

PowerShell — no module needed; this is the driver that ships with Windows PowerShell, and Encrypt=True is its spelling of Mandatory:

$cs = "Server=tcp:127.0.0.1,1433;Encrypt=True;TrustServerCertificate=True;" +
      "Pooling=False;User Id=any;Password=any"

$conn = New-Object System.Data.SqlClient.SqlConnection $cs
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT TOP 5 name, city FROM customers ORDER BY name"
$reader = $cmd.ExecuteReader()
while ($reader.Read()) { "{0}  {1}" -f $reader[0], $reader[1] }
$conn.Close()

JDBC — confirmed working; this is what DBeaver uses:

jdbc:sqlserver://127.0.0.1:1433;encrypt=true;trustServerCertificate=true;user=any;password=any

Use tcp:127.0.0.1 rather than localhost, or the client may try shared memory and never reach the listener. If you started DaffyDB with --no-cert there is no encryption to negotiate, and the string becomes Server=tcp:127.0.0.1,1433;Encrypt=False;TrustServerCertificate=True;... instead.

Which clients

sqlcmd, ADO.NET and JDBC work straightforwardly — run a query, read the rows.

Management Studio and DBeaver connect and run queries, with limited operation: your tables and their columns appear and are browsable, and SSMS's "Select Top 1000 Rows" works, but deeper Object Explorer nodes — keys, indexes, statistics, security, agent — report errors, and some property dialogs fail. Those errors scroll past in the DaffyDB console; they are non-fatal, and the query window keeps working throughout.

ODBC works through ODBC Driver 17 or 18 for SQL Server. Set the DSN's encryption to Mandatory and tick "Trust server certificate".

OLE DB works through MSOLEDBSQL, the Microsoft OLE DB Driver 19 for SQL Server:

Provider=MSOLEDBSQL19;Data Source=127.0.0.1;Use Encryption for Data=Mandatory;Trust Server Certificate=True

The drivers Windows ships with — the ODBC driver named just "SQL Server", and the SQLOLEDB provider — are not supported: both speak the protocol version of SQL Server 2000.

Power BI connects three ways:

• Get Data → SQL Server, its built-in connector. Enter 127.0.0.1 as the server — 127.0.0.1,PORT if DaffyDB is not on 1433 — and leave the database empty, or enter daffydb. Not localhost, which does not reach it. This connector checks DaffyDB's certificate and has no setting to skip that, so Windows has to trust the certificate first; see below.
• Get Data → ODBC, with a DSN on ODBC Driver 17 or 18 set up as described above.
• Get Data → OLE DB, with the MSOLEDBSQL19 connection string above.

The navigator lists your tables, and they import.

Or let DaffyDB open it. Type :pbi at the prompt and Power BI Desktop opens with the SQL Server connection filled in, in Import mode, straight to the navigator. It opens a .pbids file, Power BI's own connection shortcut, which DaffyDB writes at startup to %LOCALAPPDATA%\DaffyDB\daffydb.pbids (daffydb-PORT.pbids on another port) and prints in its banner, so double-clicking that file works too. The certificate has to be trusted first, and :pbi offers to do that if it is not. The file holds no credentials: the first time, Power BI asks for them. Choose Database, then any username and password, or the ones you gave --user and --password.

Trusting the certificate, for Power BI's SQL Server connector

The first time you run daffydb on Windows, its prompt offers to do this for you before your first command. You can also run daffydb --trust-cert at any time. Either way, Windows then shows a security warning about installing the certificate: answer Yes.

To do it by hand instead:

1. Find daffydb.cer. DaffyDB prints its path when it creates it; it lives in %LOCALAPPDATA%\DaffyDB\certs.
2. Right-click it and choose Install Certificate.
3. Choose Current User, then Next. Local Machine works too, but it needs administrator rights and makes every account on the PC trust the certificate.
4. Choose Place all certificates in the following store. Do not leave it on "Automatically select", which does not put the certificate among the trusted roots.
5. Click Browse, select Trusted Root Certification Authorities, and click OK. Then click Next and Finish.
6. If Windows shows a security warning, click Yes to approve.

This only affects DaffyDB. Windows will accept DaffyDB's own server under the names on its certificate: localhost, 127.0.0.1 and this machine's name. The certificate is not a certificate authority, so it cannot vouch for any other site or program. daffydb --untrust-cert removes it again. If the certificate is ever replaced — --regen-cert, or when it expires after five years — trust the new one.

No data is copied. Each file is read in place, on demand — except Excel workbooks, which are loaded into memory when DaffyDB starts (see below).

Where a table comes from

The file name decides where it lands:

Every table is in the daffydb database, so orders, dbo.orders and daffydb.dbo.orders all name the same one.

A folder of parts is one table. A table too big for one file is usually written as a folder of pieces, and DaffyDB reads that folder as one table named after it. That covers part-00000.parquet and on, as sqlcarbon, Spark, Hadoop and pyarrow write them, data_0.parquet and on, as DuckDB writes them, and any folder of parquet files holding a _SUCCESS marker. A folder that mixes parts with other files is read file by file. A re-export with more parts or fewer shows up at the next query, without a restart. If a table was exported both ways, orders.parquet beside an orders folder, the newer of the two is served and the other is named in a warning.

Getting tables out of SQL Server. sqlcarbon, also from TroBeeOne, copies SQL Server tables to another SQL Server or to parquet, as a YAML file sets out (pip install sqlcarbon). It writes a small table as one file and a large one as a folder of parts, so its output folder is ready for daffydb --data-dir as it stands.

JSON can be one array of objects, or one object per line, and .jsonl and .ndjson are read too. Gzipped .csv.gz and .json.gz files (and .jsonl.gz, .ndjson.gz) are read without unpacking them first. All of this is built into DuckDB, so no extension is ever downloaded.

CSV and JSON column types are sniffed by DuckDB. A nested JSON object or array comes back as text. Where a folder holds both orders.parquet and orders.csv, the parquet wins — it carries a real schema instead of a guess at one — and a CSV wins over a JSON file in the same way. A file that cannot be read, JSON that does not parse included, is named in a warning and skipped, so one bad file costs its own table and nothing else.

The scan looks two folders down, and leaves out:

• bin, obj, node_modules, and folders that are hidden or start with a dot (.git, .vscode)
• appsettings.json (and appsettings.*.json), launchSettings.json, package.json and package-lock.json, which are configuration rather than data
• folders it is not allowed to open

A folder with more than 500 data files is not mounted at all, and the log says so; a folder of parts counts as one, however many parts it has. It is almost always the wrong folder, and half of it would be worse than none. Point --data-dir somewhere narrower, or mount what you need with an --init script, where one read_parquet glob can cover a whole partitioned tree.

Excel workbooks

An .xlsx or .xlsm workbook becomes a schema, and each sheet in it a table. That holds for a workbook with one sheet too, so adding a sheet later never renames the others. The workbook's folder and any dots in its name make no difference: Finance/Plan.2024.xlsx is the schema Plan.2024.

Sheets are loaded into DaffyDB's in-memory DuckDB database when it starts. The workbook itself is never changed, and the loaded copy goes away when DaffyDB stops. Changes saved to a workbook while DaffyDB is running show up after a restart. A workbook that is open in Excel still loads.

Each column's type is worked out from every row, not a sample, so a value that appears only far down a sheet can neither fail the load nor be rounded to fit:

• whole numbers are bigint, and one fraction anywhere makes the column float
• dates are date, or datetime2 if any of them has a time; times of day are time
• TRUE/FALSE is bit
• a column that mixes these with text, or with each other, is nvarchar and keeps every value as it reads in Excel
• errors such as #N/A are NULL, and formulas give the value Excel last saved

A sheet becomes a table when its first row is a header. If that first row is not all text, it is data instead, and the columns are named column_A, column_B and so on. A blank header cell gets the same kind of name, and a repeated one gets its column added: amount_column_E.

Left out: hidden sheets, chart sheets, empty sheets, and sheets laid out as a report rather than a table, which DaffyDB recognises by values to the right of the header row, such as a title above the table. Those are named in the log. So is a workbook that cannot be read, such as one protected with a password; it costs itself and nothing else. Legacy .xls files are not read.

Workbooks are read by Sylvan.Data.Excel, an MIT-licensed .NET library that ships inside DaffyDB, so no DuckDB extension is involved and nothing is downloaded. If you would rather use DuckDB's own read_xlsx, an --init script can still INSTALL excel and create views with it.

Flags

--init is the interesting one: it is DuckDB's own dialect, so anything DuckDB can reach becomes a table here — S3 and Azure through httpfs, Postgres and SQLite through ATTACH, a JSON or Excel file through an extension.

Some SQL that works

All of it against the sample files daffydb --seed writes. None of this is DuckDB syntax — watch the console to see what each statement was rewritten into.

Join and aggregate.

SELECT c.name,
       COUNT(*)                       AS order_count,
       SUM(o.quantity * o.unit_price) AS total
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total DESC;

Comparison ignores case, the way SQL Server does by default.

SELECT name, city FROM customers WHERE name = 'ada lovelace';

TOP inside a subquery — each one becomes a LIMIT on the query it belongs to, not on the statement.

SELECT TOP 2 c.name,
       (SELECT TOP 1 o.product
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY o.order_ts DESC) AS most_recent_order
FROM customers c
ORDER BY c.name;

Paging, and a table hint that is silently dropped.

SELECT order_id, product
FROM orders WITH (NOLOCK)
ORDER BY order_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

CROSS APPLY, which becomes a lateral join.

SELECT c.name, x.units, x.spend
FROM customers c
CROSS APPLY (
    SELECT SUM(o.quantity) AS units, SUM(o.quantity * o.unit_price) AS spend
    FROM orders o WHERE o.customer_id = c.customer_id
) x
ORDER BY c.name;

Window functions and CTEs.

WITH ranked AS (
    SELECT c.name,
           SUM(o.quantity * o.unit_price) AS total,
           RANK() OVER (ORDER BY SUM(o.quantity * o.unit_price) DESC) AS position
    FROM orders o JOIN customers c ON c.customer_id = o.customer_id
    GROUP BY c.name
)
SELECT name, total, position FROM ranked WHERE position <= 3 ORDER BY position;

Variables, control flow, and two result sets from one batch.

DECLARE @best nvarchar(50);

SELECT TOP 1 @best = c.name
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY SUM(o.quantity * o.unit_price) DESC;

IF @best IS NOT NULL
BEGIN
    SELECT @best AS best_customer;
    SELECT order_id, product, quantity
    FROM orders o JOIN customers c ON c.customer_id = o.customer_id
    WHERE c.name = @best ORDER BY order_id;
END;

TRY / CATCH, with the error readable from the CATCH.

BEGIN TRY
    SELECT * FROM no_such_table;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS number, ERROR_MESSAGE() AS message;
END CATCH

A temp table, filled and read back.

CREATE TABLE #spend (name nvarchar(50), total decimal(18,2));

INSERT INTO #spend
SELECT c.name, SUM(o.quantity * o.unit_price)
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name;

SELECT name, total, CASE WHEN total > 1000 THEN 'high' ELSE 'normal' END AS band
FROM #spend ORDER BY total DESC;

DROP TABLE #spend;

DuckDB itself, with --allow-passthrough. What you create this way is an ordinary table to every client afterwards, which is how you reach S3, Postgres, SQLite, JSON or Excel:

EXEC sp_duckdb N'CREATE SCHEMA IF NOT EXISTS live;
                 CREATE OR REPLACE VIEW live.top_products AS
                 SELECT product, SUM(quantity) AS units
                 FROM daffydb.dbo.orders GROUP BY product';

SELECT TOP 3 product, units FROM live.top_products ORDER BY units DESC;

What works, and what does not

T-SQL is translated to DuckDB, not executed by SQL Server, so the surface is real but finite. Batches, parameters, local variables, IF/ELSE, TRY/CATCH, temp tables, multiple result sets, TOP (including inside subqueries, where it becomes a LIMIT on that subquery rather than on the statement), USE, and a sys catalog and INFORMATION_SCHEMA complete enough for Object Explorer, DBeaver and Power BI to draw your tables and columns all work.

String comparison is case-insensitive, as it is in SQL Server by default, so WHERE first_name = 'paTricia' finds Patricia. That covers =, IN, JOIN, DISTINCT, GROUP BY and ORDER BY. It does not cover LIKE, which stays case-sensitive because DuckDB's LIKE ignores collation, and the fold is ASCII only, so accented characters still compare exactly.

Deliberately unimplemented, each reported as a proper SqlException rather than a hang: integrated authentication, MARS, bulk copy, WHILE loops, user stored procedures and output parameters. Transactions are accepted and ignored — everything is autocommit. Tables mounted from files are views over read-only files, so writes to them fail. Sheets loaded from a workbook are in-memory tables: a write to one succeeds but only changes DaffyDB's copy, which is gone at restart. The workbook itself is never written. Table row counts are not available, because there is no sys.partitions.

DuckDB runs behind a single connection, so concurrent sessions take turns. Fine for one person with a query tool and a script open at once; not yet a multi-user server.

This is an early release. It is useful today for exploring data files with tools you already have, and it is not a SQL Server replacement.

Security

DaffyDB binds to loopback only, and with no --user/--password it accepts any login. --allow-passthrough is off by default because it is arbitrary DuckDB execution, including file and network reads, available to anyone who can connect.

Encrypted and plaintext clients share the port, and encryption is on by default: a self-signed certificate is generated on first run and kept per-user. Encrypt=Mandatory needs nothing but TrustServerCertificate=True; Encrypt=Strict is stronger but ignores that setting and needs ServerCertificate= pointed at the .cer written beside the key. --no-cert turns all of it off, which also leaves the login password readable on the wire — the protocol obfuscates it rather than encrypting it, so --password and --no-cert are a poor pairing.

Trusting the certificate (--trust-cert, or the question on first run) is only ever done when you say yes, only in your own Windows account, and only with the public half. The private key stays beside it in your profile. Anyone who can read your profile could use that key to pose as localhost to your own programs, so keep the folder as private as the rest of your profile.

Protocol and trademarks

DaffyDB implements the Tabular Data Stream protocol as published by Microsoft in the open [MS-TDS] specification. It contains no Microsoft code, and is not a derivative of, replacement for, or emulator of Microsoft SQL Server. It is not affiliated with, endorsed by, or sponsored by Microsoft Corporation. Microsoft, SQL Server and Azure are trademarks of Microsoft Corporation.

During login DaffyDB reports SQL Server's product name and a SQL Server version number, because clients require them: Microsoft's OLE DB driver refuses a server that gives any other name. That is a protocol requirement, not a claim of equivalence. Everywhere else a name is shown, such as @@VERSION, DaffyDB names itself.

License

MIT. Copyright (c) 2026 TroBeeOne LLC.

Bundled dependencies and their licenses are listed in THIRD-PARTY-NOTICES.txt: DuckDB (MIT), DuckDB.NET (MIT), Sylvan.Data.Excel (MIT) and Apache Arrow (Apache-2.0).