Connecting IBM App Connect to PostgreSQL

You want to connect your integration to a PostgreSQL database.

1. Download the set of sample files: cp4i-ace-postgresql.zip.

   This ZIP archive contains a BAR file with a basic REST integration and a set of configuration files for setting up the system.

2. Extract the contents of the ZIP archive to a directory that you can use for working through the tutorial. The ZIP archive contains the following contents:
   • CustomerPostgreSQLDBV1.bar: A BAR file that contains a simple REST-based integration that runs CRUD operations on a PostgreSQL database
   • postgresqlloopbackdatasource: A subdirectory that provides the LoopBack connector data source, which contains the following files:
     • datasources.json: A file that contains the connection details to a PostgreSQL database
     • postgresqlds/customers.json: A file that contains the database model for a PostgreSQL database
   • postgresqlsetdbparms.txt: A file that contains the credentials to use for connecting to a PostgreSQL database
   • create_customer_table.ddl: A DDL script for creating the PostgreSQL table definition that is used in this tutorial
   • prepare_and_upload_configurations.sh: A script that shows how to create or update configurations by using the Red Hat® OpenShift® CLI (oc)
3. Obtain access to a PostgreSQL system:
   • You will need an instance of PostgreSQL that is accessible from the public internet. This tutorial uses an ElephantSQL instance, which is offered as a free tier for trying out PostgreSQL.
   • Obtain the connection details for the PostgreSQL instance, which will allow the integration server to connect to PostgreSQL:
     • Host name
     • Port (normally 5432)
     • Username
     • Password
     • Database name
   • Create a table in the database being used to store the customer records. To do so, run the DDL script named create_customer_table.ddl (in the extracted contents of the cp4i-ace-postgresql.zip file) against your PostgreSQL database.

   For further details and other options (such as running a PostgreSQL database locally in Docker), see the full tutorial.

You will now use the App Connect Dashboard to configure an integration server to expose an HTTP endpoint. The example flow is very simple and exposes the following REST API operations:

• GET on /customers: Gets customer records from a PostgreSQL customers database.
• POST on /customers: Creates a customer record in a PostgreSQL customers database.
• DELETE on /customers/{customerId}: Deletes a customer record {customerId} from a PostgreSQL customers database.

The same configuration could be used for far more complex integrations that include interactions with other external systems.

Before you begin

Update the extracted contents of the cp4i-ace-postgresql.zip file as follows:

1. From the postgresqlloopbackdatasource subdirectory that provides the LoopBack connector data source, edit the datasources.json file, which contains the following content for connecting:

   {
     "postgresqlds": {
       "host": "[HOSTNAME]",
       "protocol": "postgres",
       "port": 5432,
       "database": "[DATABASE]",
       "name": "postgresqlds",
       "connector": "postgresql"
     }
   }

   1. Replace [HOSTNAME] with the host name for your PostgreSQL instance, which you obtained earlier.
   2. Replace [DATABASE] with the database name of your PostgreSQL database, which you obtained earlier.
   3. If the specified standard port of 5432 is different for your PostgreSQL instance, update the port number.
2. Create a ZIP file of the postgresqlloopbackdatasource subdirectory (and its updated contents). You will use this ZIP file to create a LoopBack data source configuration later.
3. Edit the postgresqlsetdbparms.txt file, which contains placeholder credentials for connecting to your PostgreSQL database:

   loopback::postgresqlsi DBUSER DBPASS

   Update the content with your own credentials:

   • Replace DBUSER with the username for PostgreSQL database, which you obtained earlier.
   • Replace DBPASS with the password for your PostgreSQL database, which you obtained earlier.

Procedure

To create the integration server and configure it to connect to PostgreSQL, complete the following steps:

1. From your App Connect Dashboard instance, click the Dashboard icon in the navigation pane.
2. On the Servers page, click Create server.
3. Choose Quick start integration as the type of integration and click Next.
4. Provide CustomerPostgreSQLDBV1.bar as the BAR file that you want to deploy and click Next.
5. From the Configuration view, use the Create configuration button to create each of the following configurations in turn:
   1. Select LoopBack data source as the type and upload the ZIP file that you created earlier for the postgresqlloopbackdatasource subdirectory. Give the configuration a meaningful name; for example, postgresqlloopbackdatasource.
   2. Select setdbparms.txt as the type and upload the postgresqlsetdbparms.txt file. Give the configuration a meaningful name; for example, postgresqlsetdbparms.
6. Ensure that all of the new configurations that you created are selected and then click Next.
7. Define the following details for the integration server:
   1. Give the integration server a meaningful name; for example, postgresqltest.
   2. Ensure that the Designer flows mode is set to disabled, which is required when deploying a Toolkit integration.
   3. Set the application transport for the integration endpoint to http.
   4. Click Create. The integration server should start and expose the HTTP input that drives the integration's REST operations. (You might need to refresh the page to see the Started state on the integration server's tile.)

To test your flow, complete the following steps:

1. Locate the URLs of the REST API that is exposed by the integration server:
   1. Click the tile for the integration server in the dashboard.
   2. Click the tile for the application within the server.
   3. Click POST /customers and note the displayed curl command example. This operation will be used to add a record to the customers table in your PostgreSQL database.
   4. Click GET /customers and note the displayed curl command example. This operation will be used to retrieve all customer records from the customers table in your PostgreSQL database.
2. Run the curl commands to first add records to the table, and then to display the records from the table.

   Also explore the other DELETE operation for a record in the table.

This tutorial has shown how to set up an integration server to securely connect to a PostgreSQL database.

If preferred, you can use the Red Hat OpenShift CLI to create or update the configurations in the system. The supplied prepare_and_upload_configurations.sh script creates or updates the required configurations by using the oc apply -f command and then causes the integration server to do a rolling restart to pick up the changes. You can modify this script to create any number of different configurations that you require.

To use the script, run oc login to log in to the cluster and project where the Dashboard instance is installed and then execute the script. It will compress the LoopBack connector data source into a ZIP archive and then create or update each configuration object in the cluster.