Introduction
Connecting to a database is slightly different from selecting a file for a file/folder-based format. The operation relies much more on format-specific parameters. In this article, we'll learn how to create a database connection, then view and inspect data stored in PostGIS or PostgreSQL using FME Workbench Visual Preview.
Note: This example will use PostGIS in the text and screenshots, but it is also applicable to the PostgreSQL format.
Video
This video was recorded using FME 2016.1; the data and interface differ from the current article, but the concepts remain the same.
Connecting to a Database
Connecting to a database with FME is a bit different from reading from one. To retrieve data from a database, a connection must be established, whether the database is on-premises or in the cloud. Database connections provide a convenient and secure way to store and reuse connection parameters.
Database connections are, by default, private and are only available to the user. If a public connection is desired, please see Making Database and Web Connections Public for more information.
Before creating a connection, obtain the following for your PostgreSQL or PostGIS database:
- Host (Server) Name
- Network Port Number
- Database (Service) Name
- Username
- Password
Safe Software provides a READ-ONLY PostgreSQL/PostGIS database to use while working through training or articles. Since this database is read-only, instructions have been provided for creating and setting up your own training database that allows write access as well. For instructions on how to create that database, see Creating a PostgreSQL/PostGIS Training Database with FME.
Safe Software read-only PostgreSQL training credentials:
- Host: postgis.train.safe.com
- Port: 5432
- Database: fmedata
- Username: fmedata
- Password: fmedata
Connection Methods
There are two main ways to set up a database connection in FME. The first way is through the FME Options, which allows you to set up the database connection before creating a workspace. The second, more common way is to create the database connection while adding a reader or writer.
FME Options
In FME Workbench or FME Data Inspector, on the top menu bar, go to Utilities > FME Options (Windows), FME Workbench > Preferences (macOS and Linux).
For FME 2025.0 and older, please go to Tools > FME Options
When the FME Options dialog opens, select Database Connections from the side menu. Database Connections shows all your connections. This is where you can add, edit, delete, or test connections.
To create a new database connection, click on the plus (+) sign at the bottom. Then proceed to Add Credentials and Testing.
Reader or Writer
The other option for creating a Database Connection is from within the Add Reader/Writer dialog in FME Workbench or FME Data Inspector. After selecting either PostgreSQL or PostGIS as the Format, the dialog will change from Dataset to Connection. Click the drop-down in the Connection dialog, then select Add Database Connection. This drop-down will also show other database connections for this format that you may have already created. Proceed to Add Credentials and Testing.
Add Credentials and Testing
In the Add Database Connection dialog, select PostgreSQL as the Database Connection, then give the connection a meaningful name. If you are connecting to the Safe Software provided training database, fill in the credentials as follows; otherwise, use your own credentials:
- Host: postgis.train.safe.com
- Port: 5432
- Database: fmedata
- Username: fmedata
- Password: fmedata
Default credentials from Creating a PostgreSQL/PostGIS Training Database with FME:
- Host: localhost
- Port: 5432
- Database: FMETraining
- Username: postgres
- Password: <user created>
Click Test. If the test is successful, click Save, and the database connection is ready to use.
If the Test fails, confirm that the credentials were filled in correctly. If the Test still fails, ensure that the database is turned on, or, if you are using a cloud-based database, that your computer or network IP address has been set as an Authorized Network (see Google Cloud example documentation). If you are still having trouble, see the Troubleshooting section for PostgreSQL or PostGIS in our documentation.
Viewing Data
In FME Workbench, data can be viewed directly in the FME Workbench window using Visual Preview. Data can also be viewed using FME Data Inspector; for more information, see Getting Started with FME Form: Introduction to FME Data Inspector.
1. Open FME Workbench
In a blank FME Workbench canvas, add a PostGIS reader. If you haven’t already set up your database connection, click on the drop-down next to Connection and then follow the steps listed in Connecting to a Database. Once you have selected or connected to the database, click the Parameters button.
In the Parameters dialog, click on the ellipsis next to Table List to open up the PostGIS/PostgreSQL database. Select which tables you would like to read in. Once the table has been selected, click OK twice to add the reader to the canvas. For this example, read in public.AddressPoints
2. Open Visual Preview
Once the reader feature type has been added to the canvas, click on it to open the mini toolbar. On the mini toolbar, click on the View Source Data button to open Data Preview (formerly Visual Preview).
Now you can view the data in Data Preview. Continue your workflow as desired.
Data Attribution
The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.