Learn how to use IBM® App Connect on IBM Cloud™ (with a plan that provides enterprise capabilities) to expose a REST API to a PostgreSQL database table.

Scenario:

You want to store customer details in a database and would like to be able to invoke REST operations to create, retrieve, and delete customers. You’ve chosen PostgreSQL as the database and want to construct a REST API to allow controlled access to the customer data.

To achieve this, we’ve provided a BAR file for you to import into App Connect on IBM Cloud, to deploy an integration solution that implements a REST service to create, retrieve, and delete customer data in a PostgreSQL data source. In the integration solution, a LoopBackRequest node is used to issue synchronous requests through a LoopBack connector for PostgreSQL, to perform CRUD operations on a PostgreSQL database table. You’ll need to configure the LoopBackRequest node to add a data-source stanza that defines the PostgreSQL instance to be accessed, and to provide a LoopBack model for the data source. You’ll also need to specify security credentials that the LoopBackRequest node can use to connect to the PostgreSQL data source. You can define the data-source stanza, the model of the LoopBack object, and the security identity by creating policies in App Connect on IBM Cloud.

First, find or create everything you need:

  • A PostgreSQL instance that is accessible from the public internet. This tutorial uses an ElephantSQL instance running on IBM Cloud, which is offered as a free (Lite) tier called Tiny Turtle. The tutorial has been specially designed to keep within the limitations of this free plan, but we recommend that you use a paid instance if possible because limitations such as the number of connections can cause issues. (For reference, we’ve also provided some instructions for setting up a PostgreSQL instance on premises using Docker at the end of this tutorial.)
  • An IBM Cloud account with an IBM App Connect service instance that provides enterprise integration capabilities; for example, the Lite or Custom Enterprise plan.
  • Optionally, you can also install IBM App Connect Enterprise for Developers if you want to look at and change the deployed flow. It is not necessary for running the tutorial, but it is useful for examining the flow to understand how it works.
    (Note: The enterprise integration project for this tutorial was developed with the IBM App Connect Enterprise Toolkit, but can be examined with the Integration Toolkit of IBM Integration Bus V10.)
  • If you want a bit more information before you start, you can read more about the App Connect Enterprise Developer edition on the following pages:

Import the enterprise integration project into App Connect Enterprise or IBM Integration Bus, and examine the message flow:

You do not need to complete this step unless you want to look at or change the flows. A separate BAR file is also provided for deploying the integration into App Connect on IBM Cloud, as described in a later step.

  • Read more

    All the resources required for this tutorial are provided in a project interchange file named appconnect_postgresql_tutorial_pi.zip.

    The steps are the same for App Connect Enterprise V11 Toolkit and IBM Integration Bus V10 Toolkit (the toolkit):

    1. Download the project interchange file, by clicking the link above and saving the file to a local directory.
    2. Open the toolkit.
    3. To import the project into the toolkit, click File > Import, expand IBM Integration, select Project Interchange, and click Next. Then browse to select the downloaded project interchange file and click Finish.

    A new project called CustomerPostgreSQLDBV1 is displayed in the toolkit.

    This project contains one flow called CustomerPostgreSQLDBV1.msgflow, which implements a REST service with the following resources:

    As shown in the last two images, the REST service has two main resource paths:

    • /customers for creating a customer and retrieving all current customers
    • /customers/{customerId} for deleting individual customers

    The resource paths provide three operations that each have their own generated subflow for interacting with PostgreSQL. The subflow constructions are very similar and they need the same configuration to interact with PostgreSQL.

    Let’s look at one of these subflows in detail. The subflow named addCustomer.subflow implements the creation of a customer in the PostgreSQL database and generation of an unique ID.

    This subflow includes the following actions:

    • Generate id: Contains a few lines of ESQL to generate a unique ID to use for the new customer:

    • CreatePostgreSQL Uses the PostgreSQL LoopBack connector to perform the creation:

      • The data source name links the LoopBackRequest node to the definition entry (or data-source stanza) that needs to be created in the datasource.json file in the App Connect Enterprise work directory. This file then contains details for connecting to PostgreSQL; for example, the host name and port. In App Connect on IBM Cloud, the PostgreSQL definition entry will be created for you based on a policy that you define in the web UI. More details on this later…
      • The security identity similarly provides a link to the database user name and password that you use to access your PostgreSQL instance. In App Connect Enterprise, security identities are created using the mqsisetdbparms command and, again, this step will be completed for you in App Connect on IBM Cloud based on a policy.
      • The final thing required is the model for the LoopBack object, which needs to be copied to the data source directory. App Connect on IBM Cloud will do this for you based on any models that you attach to your data source policy.

    When moving the Rest API to the cloud, the important parts of the preceding subflow are the details given on the LoopBackRequest node. This will require policies to be created in App Connect on IBM Cloud to define the data source, the model of the object, and the security identity. Once these are defined, the flow can run in the cloud without any modifications to the flow itself.

Get the connection details for the PostgreSQL instance and create a database table:

To allow App Connect on IBM Cloud to connect to PostgreSQL, you need to obtain the following connection details:

  • Host name
  • Port (normally 5432)
  • User name
  • Password
  • Database name

You also need to create a table in the database being used to store the customer records.

To obtain the connection details and create the database table:

  1. Log in to your ElephantSQL instance and click Manage in the left pane.
  2. If necessary, click DETAILS to open the Details view, which lists the required information. In a later step, you’ll need to copy and paste these connection details into App Connect.

  3. Create the database table as follows:
    1. Download the following table definition: create_customer_table.ddl.
    2. Execute this DDL script against the database being used for the tutorial. In the ElephantSQL instance, you can use the browser tool to run this script:

Configure the integration in App Connect on IBM Cloud:

  1. Download the CustomerPostgreSQLDBV1.bar file.
  2. Sign in to App Connect on IBM Cloud.
  3. From the App Connect on IBM Cloud dashboard, click New > Import a BAR file and select the BAR file that you downloaded. Then click Import.

    The integration server is displayed as a tile in the dashboard.

  4. From the App Connect menu App Connect menu icon, click Manage > Policies to open the Policies view.
  5. Click Create a policy (if shown) or click New.
  6. Create a PostgreSQL (Loopback) policy with the following details (to configure the data source and model):
    • Policy name: Enter a name; for example, ElephantSQL datasource.
    • Datasource name: Enter postgresqlds. (Must be set to postgresqlds to match the value given on the LoopBack node.)
    • Host: Enter the host name for the PostgreSQL instance; for example, stampy.db.elephantsql.com. This is the Server value in the ElephantSQL Details view.
    • Port: Accept the PostgreSQL default value of 5432 or specify your configured port.
    • Database name: Enter the name of your database. If using the ElephantSQL Lite plan, the same name is provided for the database and user by default; for example, kovszfpa.
    • Add model: Use this boxed area to attach a model for the customers object. Download and add this definition of the model: customers.json.
    • Connect to PostgreSQL via the public internet: Select this check box.
    • Use SSL: Select this check box.

      Note: The Use SSL check box is displayed when you select the Connect to PostgreSQL via the public internet check box. If you do not select Connect to PostgreSQL via the public internet, App Connect will instead attempt to connect to an on-premises PostgreSQL system.

      Create a PostgreSQL policy

  7. Click Create and then return to the App Connect dashboard.
  8. Create a second policy of type Generic (Security Identity). This is needed because the LoopBackRequest node also requires a security identity.

    The policy name is unimportant (in our example, we’ve used ElephantSQL security identity), but the security identity name must be postgresqlsi to match the value given on the LoopBackRequest node. Specify the security identity type as loopback, and then specify the database user name and password that you use to access your PostgreSQL instance.

    Create a Security Identity policy for PostgreSQL

  9. Attach the two polices to the integration server.
    1. From the App Connect dashboard, click the CustomerPostgreSQLDBV1 tile to open the integration.
    2. Click the Attached policies tab, and then click Manage.
    3. Select the ElephantSQL datasource and ElephantSQL security identity policies and then save.

  10. Return to the dashboard. Then start the integration by opening the options menu [⋮] for the integration server, and then clicking Start. When the tile shows Running, the integration is running and ready to use.

Finally, test your integration:

Now try creating a customer and retrieving the details:

  1. From the dashboard, open the integration.
  2. Click Show API Explorer.
  3. Choose addCustomer from the three operations shown. Then click Try it.

  4. Click Generate to produce test data for a new customer.

  5. Click Call operation and success should get returned.

  6. Next, choose the getAllCustomers operation and then click Try it.
  7. Click Call operation and success should get returned with details of the customer you added.

If all of these steps work, then you have the tutorial up and running. If it fails, then an error message with the reason will be returned. Check the integration log for any errors – you can access the log from the integration in the dashboard.

Using a Docker container running on premises rather than an ElephantSQL instance:

To run PostgreSQL on premises, you need to have Docker running on a local machine that has App Connect Enterprise installed. Then run the following command, substituting the user name and password with your own values:

docker run -p 5432:5432 --name postgresql -e POSTGRES_USER=postgresadmin -e POSTGRES_PASS=secret -d postgres:latest

To create the required database table, download this file: create_customer_table.ddl. Then start a PSQL session in the Docker container:

docker exec -ti postgresql psql postgres -U postgresadmin

Paste the contents of the create_customer_table.ddl file into the postgres command line. It should successfully create the table in the postgres database.

You now have PostgreSQL running locally and you just need to configure App Connect on IBM Cloud to have access to your local machine.

  1. Change the ElephantSQL security identity policy to use the user name and password for the Docker container.
  2. Change the ElephantSQL datasource policy to use a host name of localhost and to not connect to the public internet. Change the database name to postgres, which is the default PostgreSQL database.
  3. Save the policy and refresh the policy page. You should now see that the policy has a warning against it asking you to update the agent.
  4. Click the policy warning and follow the instructions given. After this is done, a green tick should appear against the policy.
  5. Stop and start the integration to pick up the new configuration.
  6. Now repeat the test from the previous section. It should work the same as before, but using your local PostgreSQL system rather than your ElephantSQL instance.

Join The Discussion

Your email address will not be published. Required fields are marked *