Connect to DuckDB in FileMaker

Products, Plugins & FM hosting/cloud services MBS Plugins
,
1

Today we like to connect to a DuckDB database from FileMaker using the SQL functions in MBS FileMaker Plugin. For that we need the client libraries for DuckDB, but where to get them?

macOS with homebrew

For macOS you can use homebrew to install the duckdb package. Then you get the duckdb library installed.

You find the library on an Apple Silicon Mac:

/opt/homebrew/Cellar/duckdb/1.2.1/lib/libduckdb.dylib

For an Intel Mac, you find it in the /usr/local folder in a sunfolder.

All platforms

Just got to the DuckDB installation website: Installation

There you can click

  • Version: Stable Release
  • Environment: C/C++
  • Platform: Pick yours
  • Download method: Direct Download
  • Architecture: Pick x86_64 for Intel or arm64 for Apple Silicon

Then you can download the dylib/dll/so file and point the plugin to it.

Connect

To connect, we then define a connection string with host, port and database name. Or for a local file, we just pass the file path.

Set Variable [ $Connection ; Value: MBS("SQL.NewConnection") ]
# Tell plugin where PostgreSQL library is
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $Connection; "DUCKDB.LIBS"; "/opt/homebrew/Cellar/duckdb/1.2.1/lib/libduckdb.dylib") ]
# Connect to database
Set Variable [ $result ; Value: MBS("SQL.Connect"; $Connection; "/Users/cs/Desktop/test.duckdb"; ""; ""; "DuckDB") ]
If [ $result ≠ "OK" ]
    Show Custom Dialog [ "Error: " & $result ]
    Set Variable [ $result ; Value: MBS("SQL.FreeConnection"; $Connection) ]
Else
    # do something here
End If
# Cleanup
Set Variable [ $result2 ; Value: MBS("SQL.FreeConnection"; $Connection) ]

Once connected, you can do whatever queries you like to like the ones below:

// create command
$Command = MBS("SQL.NewCommand"; $Connection; "SELECT * FROM Test")
// run select
$result2 = MBS("SQL.Execute"; $Command)
// go to first row
$result3 = MBS("SQL.FetchNext"; $Command)
// result 3 is 1 if we got a record
// read first name field from result:
$firstname = MBS("SQL.GetFieldAsText"; $command; "FirstName")
// later release command
MBS("SQL.FreeCommand"; $Command)

Let us know if you have questions

2

A question for you not regarding DuckDB.

On macOS, you suggest to use HomeBrew to install DuckDB. I once used HomeBrew to install a package on BigSur for Intel. I had issues and tried to remove HomeBrew. That was painful to say the least., HomeBrew was always in the way, no sure process to remove it from MacOS. I swear I would never install it again. Was I unlucky ?

Thanks

3

I suspect you were unlucky.

There is the uninstall script provided by HomeBrew. I'm not a huge fan of HomeBrew but it does allow me to install and maintain software packages that would be unavailable otherwise. The MacPorts project is an older alternative to HomeBrew.

4

Well, if you use homebrew, you get the dylib installed in

/opt/homebrew/Cellar/duckdb/1.2.1/lib/libduckdb.dylib

or without version:

/opt/homebrew/lib/libduckdb.dylib

It's convenient as it keeps them up to date just like a linux package manager.
Not sure what your pain was with homebrew.

You can also just download the libduckdb.dylib from the Installation page and skip homebrew.