maxi-k/duckdb-saneql

Use saneql in duckdb

DuckDB SaneQL Plugin

Allows you to type saneql directly into a duckdb shell.

Building

Build steps

Now to build the extension, run:

make

alternatively, pass extra cmake flags like this:

CMAKE_FLAGS="-DCMAKE_CXX_COMPILER=g++" make

The main binaries that will be built are:

./build/release/duckdb
./build/release/test/unittest
./build/release/extension/saneql/saneql.duckdb_extension

• duckdb is the binary for the duckdb shell with the extension code automatically loaded.
• unittest is the test runner of duckdb. Again, the extension is already linked into the binary.
• saneql.duckdb_extension is the loadable binary as it would be distributed.

Running the extension

To run the extension code, simply start the shell with ./build/release/duckdb.

Now we can use saneql directly in DuckDB:

D create table foo(a int);              -- regular sql
D insert into foo values (1), (2), (3); -- regular sql
D foo.filter(a >= 2)                    -- saneql
┌───────┐
│   a   │
│ int32 │
├───────┤
│     2 │
│     3 │
└───────┘

For a full list of saneql operations, see the saneql repo, or checkout the saneql TPC-H queries.

Running the tests

Different tests can be created for DuckDB extensions. The primary way of testing DuckDB extensions should be the SQL tests in ./test/sql. These SQL tests can be run using:

make test

Loading in an existing duckdb binary

To install load the extension into a duckdb instance that doesn't have it, you will need to do two things. Firstly, DuckDB should be launched with the allow_unsigned_extensions option set to true. How to set this will depend on the client you're using. Some examples:

CLI:

duckdb -unsigned

Python:

con = duckdb.connect(':memory:', config={'allow_unsigned_extensions' : 'true'})

NodeJS:

db = new duckdb.Database(':memory:', {"allow_unsigned_extensions": "true"});

After running these steps, you can install and load your extension using the regular LOAD command in DuckDB:

LOAD '/path/to/saneql.duckdb_extension'

Always load on the command line

To always load this plugin into a duckdb instance other than the one built by this repo, create a shell alias:

alias duckdb='duckdb -unsigned -cmd "LOAD '\''/path/to/saneql.duckdb_extension\''"'

Links

You should also check out PRQL as an SQL alternative. Thanks to the duckdb prql repo, which served as an example of how to put another language into duckdb.