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:

1 Like
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.

2 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.