Connect to DuckDB in FileMaker

[MonkeybreadSoftware]

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