Connect to Postgres in FileMaker

[MonkeybreadSoftware]

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

macOS

For macOS you can download the Postgres App to get a local test server for Postgres on your Mac. You can directly point to the libpq.5.dylib within the application.

We adjusted the install names to make them work into the Frameworks folder of your application. The libpq library needs crypto and ssl libraries, so these must be in the same folder. We got the libraries here for you: PostgreSQL Libraries. You can store these for example in the Extensions folder next to our plugin.

Set Variable [ $Connection ; Value: MBS("SQL.NewConnection") ]

# Tell plugin where PostgreSQL library is
Set Variable [ $path ; Value: "/Users/cs/Library/Application Support/FileMaker/Extensions/libpq.5.dylib" ]
Set Variable [ $r ; Value: MBS("SQL.SetConnectionOption"; $Connection; "LIBPQ.LIBS"; $path) ]

# you can include an app name to show in server administration views
Set Variable [ $r ; Value: MBS("SQL.SetConnectionOption"; $Connection; "APPNAME"; "FileMaker") ]

# Connection string in the following format:
# "" or "@" - empty string or '@' character, connects to a local server
<database_name> or @<database_name> - connects to a database with the specified name on local server
<serve_name>@ - connects to the specified server
<server_name>@<database_name> - connects to a database with the specified name on the specified server
Set Variable [ $result ; Value: MBS("SQL.Connect"; $Connection; "server@database"; "user"; "password"; "PostgreSQL") ]

If [ $result ≠ "OK" ]
    Show Custom Dialog [ "Error: " & $result ]
    Set Variable [ $result ; Value: MBS("SQL.FreeConnection"; $Connection) ]
    Halt Script
End If

Linux

For Ubuntu we can just install the postgres client package and get libpq.so file installed. Once installed, the plugin should find it automatically. e.g. for Ubuntu 24 on ARM, we find the library in /usr/lib/aarch64-linux-gnu/libpq.so.5

You can look for libpq5 package in the package manager or just install it via Terminal:

apt-get install libpq5

You do not need to specify the library name or path. We automatically look for libpq on Linux for PostgreSQL.

Windows

For Windows we download the Windows installer for PostgreSQL Server. We look into the C:\Program Files\PostgreSQL\17\bin folder here and find all the DLLs we need.

The libpq.dll doesn't do it alone. As you may guess from above, that it needs at least the ssl and crypto libraries, too.

Here are the dependency:

• libpq.dll
  • libintl-9.dll
    • libiconv-2.dll
    • libwinpthread-1.dll
  • libssl-3-x64.dll
    • libcrypto-3-x64.dll
  • libcrypto-3-x64.dll

Plus of course DLLs like VCRuntime140.dll coming with Visual Studio 2015 runtime libraries.

You can find the download from us here with all six DLLs: PostgreSQL Libraries.

On Windows you can put all these DLLs into the application folder for FileMaker Pro or Server. Alternatively you can put them in a different folder, but use Process.SetDllDirectory function. Then Windows knows where to find the DLLs and then you don't need to tell the plugin the path as we automatically look for libpq.dll file.

Connect

To connect, we then define a connection string with host, port and database name. Then we call SQL.Connectfunction. See the script above.

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.