Open Source Database (PostgreSQL, MySQL/MariaDB, or SQLite) Connection Configuration Using ODBC on a Mac

[#1]

This JMPer blog provides step-by-step instructions for connecting JMP to an open-source database — specifically PostgreSQL — from a Mac. The process involves two main parts:

1. Installing the ODBC driver and manager

2. Establishing the connection from within JMP

Follow these steps to ensure a seamless connection.

Step 1: Install ODBC Driver and Manager

1.1 Download the Driver:

• Download the Actual Technologies ODBC Driver from https://www.actualtech.com/download.php
• Important: This driver is licensed software. Without a license, only the first 3 rows of your database will be accessible.
• Tip: Download the driver using Safari, not Chrome, to avoid installation errors. 

1.2 Run the Installer

• Double-click on the “Actual ODBC Pack.pkg” icon to run the installer.

1.3 Open the ODBC Manager

• Go to /Applications/Utilities folder and double-click ODBC Manager.

1.4 Add a System DSN

• Click on the System DSN tab and press Add.

1.5 Configure the Driver 

• Select your driver (Actual Open Source Databases ) and click OK.
• Click on Licenses to add your license keys (first-time setup). Without a license, only three rows of data will be accessible.
• Give the Data Source a name and select PostgreSQL for type.

• Enter the Server IP address, Port number, Login ID, and Password.

• Choose the database you want to connect to from the "Database" dropdown menu.
• Click Continue, then Test Connection to confirm everything works. 

Here’s a reference table to know which driver one should choose based on the type of your database:

Driver	Supported Database(s)	Best For
Actual Access	Microsoft Access (.mdb, .accdb)	Legacy Access files, cross-platform sharing.
Actual Open Source	PostgreSQL, MySQL/MariaDB, SQLite	Modern, open-source database use cases
Actual Oracle	Oracle Database	Enterprise-grade Oracle database environments.
Actual SQL Server	Microsoft SQL Server, Azure SQL	Enterprise environments using SQL Server.

Step 2: Establishing the Connection in JMP

2.1 Create a New Database Connection

• In JMP, go to File --> New --> New Database Query --> New Connection.
• Select the Data Source Name (DSN) you created earlier.
• Enter the username and password, then click Choose DSN.

2.2 Select Schemas and Tables

• Browse the available schemas and tables and select those you want to query.

2.3 Build and Run Your Query

• Construct your query by choosing the columns you want to add, and any columns you want to Filter or Order By.
• That's it! — your database is now connected and ready for analysis.

Happy JMPing!