Connect to Postgres in Xojo

Today we like to connect to Postgres Database server from Xojo using MBS Xojo SQL 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. You can find these with a test project here: PostgreSQL Libraries. You can add a these using a CopyFiles build step into the Frameworks folder of your application.

Var con As New SQLConnectionMBS Var cmd As New SQLCommandMBS Var ResourceFolder As FolderItem = SpecialFolder.Resources Var ContentsFolder As FolderItem = ResourceFolder.Parent Var FrameworksFolder As FolderItem = ContentsFolder.Child("Frameworks") Var LibFile As FolderItem = FrameworksFolder.child("libpq.5.dylib") If LibFile.Exists = False Then MessageBox "libpq not found?" End If con.SetFileOption con.kOptionLibraryPostgreSQL, LibFile

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

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.

Connect

To connect, we then define a connection string with host, port and database name. Then we call Connect method (or function) on the SQLDatabaseMBS class or SQLConnectionMBS class.

// put in your IP or DNS name, port and the database name Var databaseName As String = "cs" Var host As String = "localhost" Var port As Integer = 5432 Var ConnectionString As String = host + "," + port.ToString + "@" + databaseName con.Connect(ConnectionString, "username","password", SQLConnectionMBS.kPostgreSQLClient)

Once connected, you can do whatever queries you like to do.

Let us know if you have questions