PostgreSQL to Honeycode Pipeline
Tags: postgres, devops, aws
Published: 2020-08-12 14:29:59

Recently Amazon released their "no code" platform, [Honeycode](https://honeycode.aws). The aim is to help non-developers pull together a working application with a [WYSIWYG](https://en.wikipedia.org/wiki/WYSIWYG) UI and logic that is closer to spreadsheet formulas! 

There is a lot of discussion online about the impact "no code", "low code", and "full code" will have, and we will have to see how it plays out as even Gartner predicts that by 2024, [65% of development will be performed on low-code platforms](https://b2bsalescafe.files.wordpress.com/2019/09/gartner-magic-quadrant-for-enterprise-low-code-application-platforms-august-2019.pdf).

Honeycode itself comes with several [templates](https://www.honeycode.aws/templates) and seems aimed at creating standalone applications, MVPs etc.. 

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Templates.png)

Yet, I really want to tie this into our existing systems, allow our non developers to create applications that meet their needs with the data they require. Over the last few bog posts, we looked into [exporting data from a database to store in S3](https://gizmo.codes/postgresql-trigger-to-s3/), and also how to [access the Honeycode API](https://gizmo.codes/get-aws-honeycode-table-uuids/). 

Now we will bring this all together into one pipeline!

## POC

The aim of this POC is to get users who are created in our existing system, to have them imported into the Honeycode App where a Honeycode user will review them, and we will set some additional attribute data within Honeycode. A simple approve/deny review system.

## Architecture

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-End-To-End-Architecture.png)

### API 

We will start off with our existing application in the form of API Gateway, a Lambda, and a Database. This will simply take some JSON input and map it to our database table, won't really go into it, but wanted to provide a more end to end flow.

### Database

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/DB-Table-Design.png)
Sticking with a very simple table here, but it really doesn't matter. The magic is all centered around our database trigger which will kick off our query. We would actually want to add our trigger to all the tables we want to capture an event on. For this test, I'm going to use the following SQL to populate my CSV:

```
SELECT 
  st.id, 
  st.name, 
  st.email, 
  CASE 
    WHEN st.email = 'boss@gizmo.codes' THEN 'Boss' 
    ELSE 'Pleb' 
  END AS "status"
FROM 
  sample_table st
```

### Pipeline

Our database trigger will generate the CSV file and upload it to S3. We will utilize the [S3 Event Notification](https://docs.aws.amazon.com/AmazonS3/latest/dev/NotificationHowTo.html) feature to call our honeycode lambda. This lambda will be responsible for reading the CSV file off S3, formatting the data, and sending it to our Honeycode table... all the heavy lifting!

### Honeycode

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Dashboard.png)
The end goal! Within Honeycode we will create an application, build several tables, link those tables to screens.. and see the data sync in real time! The only real design consideration here is to treat the table you are syncing with as if external - don't build any filters etc on to the table, don't update it via the UI. Use it for look ups and save data into other Honeycode tables. There are other projects out there to [export Workbook data](https://github.com/iann0036/honeycode-export) though if you want that type of functionality!

## Step One

Ugh, since I can't provide a way to create this workbook, the next LARGE chunk of this post will be on building up the majority of it! 

### Honeycode

Lets start at the end and get our Honeycode application up and running! This isn't meant as a tutorial for building a Honeycode application as I've been pretty rough in shoehorning this application into the POC :)

Lets create our Workbook to start off:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Create-Workbook-From-Scratch.png)

This will drop you into an Untitled workbook, and present you with your first table called Table1! Honeycode's UI is pretty straightforward, the navigation consists of the three menu items on the left horizontal bar: 

* Tables
* Builder
* Automations

We won't even touch Automations for this pipeline! 

### Honeycode Tables

We will build the following table structure:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Table-Design.png)

Lets dive into each table quickly so!

#### Users

The `Users` table is what we will be writing to from the pipeline, so we wont have it linking out to anything. We don't want Honeycode writing to it etc, as the data is going to be changing.

Lets take `Table1` and rename it `Users` and provide the following columns to it:

* ID
* Name
* Email
* Role

We want to keep this in-sync with the query we will later trigger.

Lets put in some dummy data right now to help us with the Honeycode App, and delete the other rows:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Users-Table.png)

### Review Status

We will now track the status of the reviews, and use the status to filter the results in the App so we can choose to see only the non-reviewed rows.

Create a new blank table:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Create-Table.png)

and lets name this `Review_Status` and provide the following columns:

* Status
* Reviews

Lets enter the following data

| Status | Reviews|
|---|---|
|Pending|`=FilterDiff(Users,Filter(Users,"Users[ID]=Reviewed[User ID] AND (Reviewed[Status]=""Approved"" OR Reviewed[Status]=""Denied"")"))`|
|Reviewed|`=Filter(Users,"Users[ID]=Reviewed[User ID] AND (Reviewed[Status]=""Approved"" OR Reviewed[Status]=""Denied"")")`|

Delete the extra column and row :)

These formulas will initial throw an exception, but we will fix that in the next step.

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Review-Status-Broken.png)

#### Reviewed

Now to start connecting to the `Users` table, and save meta-data about the users to a Honeycode controlled table.

Lets create another new table and name it `Reviewed` and provide the following two columns:

* User ID
* Status

We won't put any data in this table, simply delete the additional column and the empty rows:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Reviewed.png)

And now that we have this table, you can jump back to `Review_Status` to see that it is working and showing our test `Users` data as pending!

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Review-Status-Working.png)

#### Reviewer Comments

Our final table, lets add some additional meta data like, who approved the user and maybe allow them to set an optional note!

Create a table called `Reviewer_Comments` and set the following columns:

* Review Date
* Reviewer
* User ID
* Comment

Delete the empty rows, and finally we wil want to format both `Review Date` and `Reviewer`. 

##### Review Date

If you select the Column by `A` and select `Format...`:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Review-Date-Format-Select.png)

We want to set the `Column Format` to be `Date & Time` and the `Column Formula` to be `=NOW()`, click Apply:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Review-Date-Format-Config.png)

##### Reviewer

Within Honeycode, the users who will use the web app are known as [Contacts](https://honeycodecommunity.aws/t/formats-dates-numbers-contacts-more/648#contacts). We will configure the column to be a type `Contact` so that we will get the logged in users details.

Similar to `Review Date`, select the column to format and this time set `Column Format` to be `Contact` and leave the `Column Formula` blank:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Reviewer-Format.png)

### Honeycode App

Lets now build out our UI! Keeping this straight forward, we will only have two screens - a main dashboard screen, and a details sceen!

Select the Builder option from the left menu and click `Build your own`:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-Build-Your-Own.png)

#### Dashboard

Lets start from the `+ Add objects` in the bottom left:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Add-Objects.png)

First add a `Button` and then also select `Column list`. For the list, lets take the defaults, while selecting `Add a detail screen`:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-List-Users-Default.png)

##### Buttons, all the Buttons

Lets just focus in on our one lonely button... Lets shring it a bit and duplicate it so that we have FOUR buttons, name two of them `Pending` and two `Reviewed`:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Four-Buttons.png)

These buttons will control our list, allowing us to filter to Pending users or Reviewed users driven mainly thanks to our `Review_Status` table.

We want two buttons for each action - while the table is set to pending, we do not want the pending button to do anything. So one button will simply sit there, looking all nice. When the table is showing already reviewed though, we then want the "action" pending button to appear so that it switches up the list when clicked.

To achieve this, we will need some variables...

##### Variables

To allow us to control the list, we will need two variables. 

One variable, the `CurrentFilter`, will determine WHICH button is showing - the one that performs an action, or just sits there? Another variable, `UsersFilter`, will actual contain the rows we want displayed.

###### Current Filter

From `+ Add objects` click `Data Cell` - change the name to `CurrentFilter`, switch the type to `Variable`, and set the inital value to `Pending`. Under the Display tab, change Visibility to `=FALSE`:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Current-Filter.png) 

###### Users Filter

We will default our list to show the pending users on load.

Again we want a data cell so from `+ Add objects` click `Data Cell` - change the name to `UsesFilter`, switch the type to `Variable`, and set the initial value to a formula. This will be: `=FindRow(Review_Status,"Review_Status[Status]=%","Pending")`. Lets also set the visibility to False under the Display tab:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-User-Filter.png)

##### Connecting Buttons to Variables

If we select View App in the top right, we will see a pretty ugly UI, with our four buttons:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Ugly-UI.png)

So lets reduce the clutter and implement the button logic. For each of the buttons, we will update the Visibility formula on the Display Tab.

Update the top `Pending` button to: `=IF($[CurrentFilter]="Pending", TRUE, FALSE)`
Update the bottom `Pending` button to: `=IF($[CurrentFilter]<>"Pending", TRUE, FALSE)`
Update the top `Reviewed` button to: `=IF($[CurrentFilter]="Reviewed", TRUE, FALSE)`
Update the bottom `Reviewed` button to: `=IF($[CurrentFilter]<>"Reviewed", TRUE, FALSE)`

i.e.
![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Button-Visibility.png)

And our App should now only show two buttons:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Ugly-UI-Two-Buttons.png)

##### Button Actions

Lets give our buttons something to do now! You'll have noticed that the button dialog has three options:

* Data
* Display
* Actions

We will give our buttons some quick actions!

###### Pending Action Button

Select the bottom `Pending` button and go to the Action tab. Under Quick Actions, you want to select `Update current screen`. You'll be prompted to `Set a variable`, and choose the `CurrentFilter` First. We want to populate the `Take data from` formula with simply: `="Pending"`. 

Hit the `Set another (1 variable)` and choose `UsersFilter`. This time we want to populate the formula with `=FindRow(Review_Status,"Review_Status[Status]=%","Pending")` - identical to the default value we set the variable to initially!

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Pending-Action.png)

###### Reviewed Action Button

Select the bottom `Reviewed` button and go to the Action tab. Under Quick Actions, you want to select `Update current screen`. You'll be prompted to `Set a variable`, and choose
the `CurrentFilter` First. We want to populate the `Take data from` formula with simply: `="Reviewed"`.

Hit the `Set another (1 variable)` and choose `UsersFilter`. This time we want to populate the formula with `=FindRow(Review_Status,"Review_Status[Status]=%","Reviewed")`

##### Connecting Buttons To The Table

We now have the buttons performing actions, but we don't see anything changing in our app! This is because the table is still sourcing data directly from the `Users` table. We want to update it to pull actually from our variable!

This is the nice and easy part, select the List from the UI and on the Data tab, lets change the source from `=Users` to be `=$[UsersFilter][Reviews]`

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-User-Table.png)

Switching back to our App view, we can see the buttons working correctly:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Filtering-Working.gif)


#### Detail Screen

So our detail screen will save some data into our Honeycode tables, set the comment and the audit data - who did it and when.

##### Action Section

Ok, so we currently have our pending test user, so lets set it up to review them. From the Builder option, lets switch to the automatically created `Users detail` screen. Our actions will need buttons, so hopefully you know routine now :) Select `+ Add objects` and pick `Button` twice. Lets resize them, and change the text so that one says "Approve", and the other "Deny":

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Action-Buttons.png)

###### Approve Automation

Lets take the Approve button first, select the `Action` tab and this time we want to `Create an automation` as we will be saving data to the tables!

We want three actions in total:

* Add a row to `Reviewed`
* Add a row to `Reviewer_Comments`
* Redirect back to the Dashboard

####### Adding a Row to Reviewed

Click `+ Add actions` and select `Add a row`. Choose our `Reviewed` table from the drop down. Remember, we only have two columns on this table: User ID and Status. The screen currently is showing the User ID, and we can pull that into our "Take data from" formula with `=$[InputRow][ID]` and then set the column to be `=[User ID]`.

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Approve-Reviewed-User-ID.png)

Since we have two columns to populate, lets select `+ Add another` and popuate the options with:

|Take Date From|Write To|
|--|--|
|="Approved"|=[Status]|

####### Adding a Row to Reviewer Comments

Once again, click `+ Add actions` and select `Add a row`. Choose our `Reviewer_Comments` table from the drop down. Populate the following columns:

|Take Date From|Write To|
|--|--|
|=NOW()|=[Review Date]|
|=$[SYS_USER]|=[Reviewer]|
|=$[InputRow][ID]|=[User ID]|

####### Redirect

Our final action, click `+ Add actions` and select `Navigate`. Select `Screen` from the drop-down. And that's our action button complete!

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Honeycode-App-Approve-Working.gif)

## Step Two

Yes.. finally on step 2 - thankfully the rest is more or less automatically deploy-able!

We will want to setup our "existing" infrastructure - an API hitting a database. We then will have a lambda that keeps out Honeycode `Users` table in sync with a CSV file in S3.

### Terrform

You can clone the [github repo](https://github.com/johncolmdoyle/aws-rds-to-honeycode). There are several variables that we will want to set before executing the terraform script, namely, we need:

* Our Honeycode login
* Our Honeycode password
* The Honeycode Workbook UUID
* The Honeycode Table UUID for the `Users` table

Lets retrieve the Table UUID!

#### Users UUID

I wrote a little script to help us, and go into more details on it in [another blog post](https://gizmo.codes/get-aws-honeycode-table-uuids/) if you're interested in it.

Lets kick it off though: 

```
$ cd aws-rds-to-honeycode/query-honeycode
$ npm install
$ node index.js workbooks -u USERNAME -p "PASSWORD"
Workbook Name	Workbook UUID
-------------	-------------
Untitled => arn:aws:sheets:us-west-2:122162422134:workbook:28e53ee7-adcd-4c40-b243-ea289eb954e2
```

Now taking the Workbook UUID, we can get the Tables UUID:

```
$ node index.js tables -u  USERNAME -p "PASSWORD" -w "arn:aws:sheets:us-west-2:122162422134:workbook:28e53ee7-adcd-4c40-b243-ea289eb954e2"
Table NameTable UUID
-------------	-------------
Users => b2cdc524-4085-352f-86b9-c7c956eb9da2
Review_Status => 9c83ce89-6172-4968-a4e6-971c1cf121bf
Reviewed => 8d65ae15-1d35-4633-a339-171fa6592ea0
Reviewer_Comments => 9859cdd7-d556-4ad6-9d60-b64dd1f06275
```

We now have the `Users` UUID: b2cdc524-4085-352f-86b9-c7c956eb9da2

#### Workbook UUID

Very similar to before, just run the script to retrieve our Workbook UUID:

```
$ node index.js workbooks-uuid -u USERNAME -p "PASSWORD"
Workbook Name	Workbook UUID
-------------	-------------
Untitled => 28e53ee7-adcd-4c40-b243-ea289eb954e2
```

#### Terraform Variable File

We will want to create a new file in the `terraform` folder called `terraform.tfvars` which you want to populate with the following:

```
honeycode_login    = "USERNAME"
honeycode_password = "PASSWORD"
honeycode_workbook = "WORKBOOK UUID"
honeycode_sheet    = "TABLE UUID"
```

Last two steps before we deploy, we need to prep our application dependencies: 

```
$ cd aws-rds-to-honeycode/app
$ npm install
$ cd ../lambda
$ npm install
```

Finally, lets spin up our environment with: 

```
cd ../terraform
terraform init
terraform apply -auto-approve
```

This will take a few minutes to complete as we wait for the database cluster to stand up.

We should get the following output:

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Run-Terraform-Apply.png)

Great, then we are ready to move on :D

### Database

We need to grant the database permission to connect to our S3 bucket, we can do this with the following script:

```
cd aws-rds-to-honeycode/script
./add_role_to_db_cluster.sh
```

This will take a few minutes to apply, but we can press on! Connect to the database using the URL and credentials you received from the terraform output. See (PostgreSQL Trigger To S3)[https://gizmo.codes/postgresql-trigger-to-s3/] if you are not sure how to connect to the Database.

Once you're connected, we will want to execute the following SQL statements.

```
CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;

CREATE TABLE sample_table (id SERIAL PRIMARY KEY, name varchar, email varchar);
```

#### Database Trigger

Now for the black magic, refer back to the terraform output, particularly `database_bucket_id`. We want to update the trigger code to match this:

```

CREATE OR REPLACE FUNCTION export_to_s3() 
	RETURNS TRIGGER 
AS $export_to_s3$
BEGIN
	PERFORM aws_s3.query_export_to_s3(
		'SELECT st.id, st.name, st.email, CASE WHEN st.email = ''boss@gizmo.codes'' THEN ''Boss'' ELSE ''Pleb'' END manager_check FROM sample_table st', 
	   	aws_commons.create_s3_uri(
	   		'S3_BUCKET_NAME', 
	   		'test.csv', 
	   		'us-east-1'),
	   	options :='format csv, delimiter $$,$$'
	);
	RETURN NEW;
END;
$export_to_s3$ LANGUAGE plpgsql;

CREATE TRIGGER sample_table_trg
	AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE
	ON sample_table
	FOR EACH STATEMENT
		EXECUTE PROCEDURE export_to_s3();
```

Excellent! We are done!

## Step Three

Lets test this bad boy out!

Refer back to the terraform output again, and find the `api_gateway_url`. We can make a JSON post call to this and watch it propagate through the system.

I'll first return to Honeycode and delete the test data we had in `Users`, `Reviewed`, and `Reviewer_Comments`.

Then we can call: 

```
$ curl --header "Content-Type: application/json" --request POST --data '{"name": "JD", "email": "pleb@gizmo.codes"}' API_URL/honeycode-sync
```

We should see our user appear - we can add a few more.

```
$ curl --header "Content-Type: application/json" --request POST --data '{"name": "Gizmo", "email": "boss@gizmo.codes"}' API_URL/honeycode-sync
$ curl --header "Content-Type: application/json" --request POST --data '{"name": "Swayze", "email": "boss@gizmo.codes"}' API_URL/honeycode-sync
```

And we are done! Our pipeline is complete with updates taking around 13 seconds from calling the Application, saving to the database, updating S3, pushing the data from S3 to Honeycode, and finally displaying in the Honeycode App.

![](https://gizmo.codes/images/PostgreSQL-To-Honeycode-Pipeline/Pipeline-End-To-End.gif)

## Step Four

Finally, make sure to tear this all down when you're finished. You don't want public databases left out there and AWS charges on your bill...

```
$ cd aws-rds-to-honeycode/terraform
$ terraform destroy -auto-accept
```