Create an API to Read/Write Data to a PostgreSQL Database

By:
Last updated:
10:16am May 02, 2018

The Flogo Connector in TCI allows you to build APIs to perform read and write operations on data in a PostgreSQL database.  This tutorial walks you through a simple SQL example that builds an API with a query parameter passing a State Code and returning all Cities which match the query parameter.  To begin, you need to provision a PostgreSQL database and run the following DDL statement to create a table called city with three fields.  

CREATE TABLE public.city
(
    id integer NOT NULL DEFAULT nextval('city_id_seq'::regclass),
    city_name character varying COLLATE pg_catalog."default",
    state_code character varying(2) COLLATE pg_catalog."default",
    CONSTRAINT city_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;  

I am going to build my Flogo integration app from an API Spec.  Therefore, first click on API Specs and model the exposed API.  Call the resource /cities with a query parameter of state_code.  This will be the parameter that is passed in to filter which cities are returned in the response.  

Click the Response button to generate a schema from sample data.  In this case, an array of cities and state codes.

With the API Spec created, we now want to Create a Flogo integration app from this API.  Select the 3 vertical buttons to the right of the API to bring up the window menu and choose Create Flogo app.  

Before we start editing our Flogo app that was just created, let's first enter a PostgreSQL Connector that points to your PostgreSQL database.  From the Connections menu at the top of the screen, select PostgreSQL, and enter your database connection details.

Now we are ready to configure our flow.  Click into the flow within the integration app created from your API Spec.  You will see that it automatically creates a two activities.  Shift the ReplyToHTTPMessage to the right one spot so you have an empty position to add a PostgreSQLQuery activity.  

Select the connection name that you defined when setting up your PostgreSQL Connection.  

In the Input Settings form, type the SQL query.  As you type the SQL, TCI will connect to the database and attempt to match the fields from the table in your SQL.  It is case sensitive so if you were to type '...from CITY..' it would display an error 'No metadata found for CITY'.  If it does find metadata for the table, the list of fields will automatically be populated in the Fields table at the bottom.  In the example below, if I use 'select * from city..', all fields on the city table would display on the Fields grid.  However, since I am only retrieving city_name and state_code, only those fields are shown.  Don't forget to end your SQL with a semicolon (;), otherwise, the SQL will hang during execution.

Notice how in the Where clause of my SQL, I entered '.. = ?state_code'.  This variable will be substituted with its mapped value at runtime.  Select Input and expand the input hash until you see your substitution input parameter value entered in the SQL in the middle window.  We want to map this to our query parameter from the HTTP Request.  Expand the $flow element until you find the query parameter defined in the ReceiveHTTPMessage activity.

Now that the PostgreSQL activity is set, let's configure the ReplyToHTTPMessage activity.  We need to map the Input data array fields as defined by the HTTP Response schema we generated in the API Specs to the output fields from the PostgreSQLQuery activity.  Because our response is an array, we first have to map the []data array to the []records array returned by the PostgreSQLQuery activity.  With []data selected in the middle window, expand the {}PostgreSQLQuery element to select the []record array.

We can now map the individual data fields to its corresponding output field.  The image below shows the city_name mapped but you need to repeat this step for state_code.

After pushing the app, select View API to open a Swagger UI page where we can execute the API and get data back.

All deployed apps generate a Swagger UI to view and test the API.