Newlisp bindings for DuckDB

Started by fdb, August 05, 2020, 03:30:02 PM

Previous topic - Next topic



I've added newLISP bindings for DuckDB at">//

So what is DuckDB? Have a look at their website:">//

They state that :DuckDB is an embeddable SQL OLAP database management system

In practice it is almost like SQLITE (only much faster!) so the  API I made is almost the same as the sqlite3.lsp standard module. Only exception is that BLOB's are not supported (yet) (They are in DuckDB but IMHO of little use for the expected things you would do with it).

What also doesn't work (yet) is prepared SQL statements/parameter bindings so no protection against SQL injection attacks however since this will mostly be used as a single person DB this is probably not a big issue but maybe I'll add it later, the DuckDB dynamic library supports it.

What is different from the  SQLITE module is that there is a  *very* fast CSV  import & export command, on my laptop it creates a table and imports 1.5M records within a second!

Furthermore DuckDB is a columnar database (as opposed to SQLITE which is a row based db), which means that is is *much* faster for select statements with lots of aggregations, windows functions etc. What I was also looking for is a PIVOT statement, (think about MS Excel pivot tables) but this isn't supported as it needs dynamic column creation which is only possible (AFAIK) with some dynamic SQL creation so that is what I've also created on top of this API. I haven't included this in this API but will make a separate module for that.



I've added a pivot command to the bindings, which allows for generating pivot tables (like in excel) over tables with millions of records, which is not possible in excel. Have a look at">//, which shows several examples.