Duck DB

Tools and Libraries
1

Hi all, we’re looking into using Duck DB or Motherduck as a database for a large storage for static data, i.e. an logistics dashboard system showing logistical data on a map and such. Duck DB (or Motherduck) is very good at quickly serving static data. As such it needs a datasource connector within Lucee to connect to, and I was wondering if there is such a thing available in the CFML-world or if anyone has done such a thing before and wants to share this? Maybe make it an extension to be installed in the Lucee Admin? I knw Duck DB provides a Java connector of some sorts (Java JDBC Client – DuckDB), but this is not my forte. The step from the Java package to making it a database connector would be a step too far for me technically at this point. Hope to hear from the people on this forum regarding this.

2

Hi Seb

Not seen DuckDB before, but it looks pretty simple to use:

  1. Download the DuckDB java client jar file and put it in your /lucee-server/context/lib/ folder.
  2. In your Application.cfc, create a data source:
this.datasources.duckdb = {
	class: "org.duckdb.DuckDBDriver"
	,connectionString: "jdbc:duckdb:"
}
  1. Query, for example, a CSV file:
csvPath = ExpandPath( "test.csv" )
result = QueryExecute( "SELECT * FROM '#csvPath#'", {}, { datasource: "duckdb" } )
dump( result )

There may be other ways to get it working.

1 Like
3

Hi Julian, I’ll try this with my colleague this or next week, once we have DuckDB setup. Seems pretty easy once you explain it the way you do, I agree :slight_smile: . Sometimes I only see large bears on the road (Dutch expression), instead of the small racoons they really are :smiley: .

4

We have a global file that holds some of our datasources per server, so as not to always have to rely on the Lucee Admin, this could be added there. Hopefully it can be protected by a username and password, the connection string.

And what if the physical database file of DuckDB is situated on another server from the Lucee server? How to go about that?

5

From the docs it looks like DuckDB deliberately doesn’t have built-in security. Instead you would secure the data sources using methods appropriate to that source.

They seem to support a range of local or remote sources:

2 Likes
6

Actually the auto install from Maven does seem to work without OSGi if you’re on Lucee 6.1+. So all you would need to do is define your datasource in Application.cfc as follows:

this.datasources.duckdb = {
	class: "org.duckdb.DuckDBDriver"
	,bundleName: "org.duckdb.duckdb_jdbc"
	,bundleVersion: "1.3.0.0"
	,connectionString: "jdbc:duckdb:"
}

Lucee will then download and install the driver for you.

3 Likes
7

Oh, wow, that is kewl! Will it then appear as an available driver in the Lucee Admin for new datasources utilizing DuckDB? Or just for the webapp in question?

8

Defining the datasource in Application.cfc means it won’t be in the Lucee admin.

According to the docs you should be able to define it in your .CFConfig.json file so it’s available server wide, but I currently can’t get that to work.

9

Looking into DuckDb and DuckLake also this week for some data processing tasks, have also been testing clickhouse for other uses, so seeing where the two intersect also. (as well as playing around with our cfml versions of pandas, splink and others in cfml)

Will it then appear as an available driver in the Lucee Admin for new datasources utilizing DuckDB? Or just for the webapp in question?

you need to create a duckdb lucee extension to have it appear in admin, then define configuration pages.

There are plenty of db extensions you can copy to help get this done on the lucee download page. (it’s almost like you could create a jdbc extension builder for these they are so similar @Zackster #lol #donatenow)

10

You can almost achieve exactly the same in the admin using the Other JDBC option

image
image778×395 13.3 KB

there are the fields at the bottom of the create page, but bundleVersion and bundleName are missing

image
image915×418 18.3 KB

https://luceeserver.atlassian.net/browse/LDEV-5652

1 Like
11

It didn’t work when I tried. Also tried adding directly to CFConfig.json using the bundle name/version but in neither case was the jar downloaded. Adding a this.datasources definition to Application.cfc worked perfectly though.

1 Like
12

perhaps just use AI…

maybe just point github copilot to the current jdbc extension repos and tell it to create a duckdb extension for lucee with all admin pages, and with options for duckdb specifically … then also point it to extension building docs, so that it gets full context.

then test

just an idea :wink:

13

The duck db driver is missing OSGI metadata, I’ve opened an issue

Once that’s done, we’ll have a DuckDB extension!

2 Likes
14

Good news, duckdb have merged in OSGI support, once they make the next release (1.4.0.0) I’ll share a test extension here for initial testing.

Their test builds only produce platform specific jars, not the huge mega jar, as the jdbc driver includes native duckdb runtimes for various platforms

As such, there’s an x64-linux only version artifact at the bottom of this page

For now this is 6.2 and newer only, as there are some specific concurrency issues with duckdb connections which OOTB don’t work with 5.4, due to the older connection pooling in 5.4, Lucee 6 uses apache commons pool2 which seems to just work

3 Likes
15

This is just kewl! And Lucee 6.2.x is no problem for us, as we’ve moved up a notch. Will test this in the coming weeks, just need 2 find some time 4 it!