Do More with Compose PostgreSQL using Zapier

Zapier is a service which allows you to create custom integrations among a variety of applications, including PostgreSQL. Below we'll look at a couple of examples for how you can do more with Compose PostgreSQL by integrating it with other tools via Zapier.

We first introduced our readers to Zapier when we showed how to Zapier your data to MongoDB and later we followed that up with an article about how to send alerts from our platform to your application of choice. Since then, Zapier has added PostgreSQL to the long list of integrations they offer.

Notify me when something changes

One of the most common business requirements with databases is to receive notifications when something important has changed. DBAs may want to receive a notification when a new table is created or when a new column is added to a table. Business users may want to know when there's a new row added to a favorite table or when the data from a custom query changes.

Among other things, at Compose we like to keep track of how many people are trying out our service with our free 30-day trial so we've written a "zap" (a Zapier integration widget) to notify us in our main Slack channel when the number of trial customers changes. Here's how:

Once you sign up for a Zapier account, you'll see a button to "Make a Zap":

Triggers

The first step in making a zap is setting the trigger. You'll be asked which application you want to start from. For this scenario, that's PostgreSQL:

Next comes the trigger type we want to use. For us, that's a custom query, but you can see the other options we mentioned (table, column and row):

In a previous article we explained how to setup a Segment warehouse using Compose PostgreSQL. We're going to run our custom query against our Segment warehouse where we're tracking trial events.

If you haven't already created a connection in Zapier to your Compose PostgreSQL, you'll be asked to do so:

If you already have a connection, you'll be asked if you want to use an existing one or create a new one.

For this scenario, because we are using the custom query trigger, we're asked to provide our query:

After that, we're asked to test the query by fetching a row. Once we've run the test, we can view the row, re-test, or choose to just continue:

Actions

Now that we have our trigger setup from PostgreSQL, we'll set the resulting action - the notification.

For the action, we're asked what application to use. At Compose, we're using Slack:

We're going to send a channel message, but there are several other options to choose from:

Then, if you already have a Slack account setup in Zapier, you'll be asked if you want to use it or create a new one. For our example, we'll create a new account:

The next step is to fill out the Slack template. As you can see we're sending a message to our "general" channel. In the message text, we're using the trials count from our query with some additional text:

There are several other options in the template including bot, image, link, and mention settings.

At the end, similar to the trigger section, we'll then get to test our Slack message action. We can also re-test or just finish.

Finally, we'll give our "zap" a name (in this case we are using Effie, the fictional tributes' escort from The Hunger Games) and we'll turn it "on":

Hey! There's a Slack notification!

Now that the "zap" is turned on, it will run every 5-15 minutes (depending on the billing plan you select) and will only perform the action (called a "task" in the billing plan) when there has been a change in the data. Zapier lets you try this out with a 14 day free trial so you can determine what billing plan best fits your situation.

Filters and intermediary actions

While we didn't use any filters or intermediary actions between our "zap" trigger and action, you can add them to enhance the precision or functionality of your own zaps. A filter might check if the data met a certain criteria. For example, we could apply a filter to check if our trials are greater than 300 before moving on to our final action that sends a channel message in Slack. An intermediary action might be posting the data to another application, such as setting the data as a metric in a dashboard tool like Leftronic, before moving on the the final action. In this way, you can hit multiple apps or take multiple steps in the same app, with your trigger data. That's pretty powerful stuff!

Now, that we've seen how to create notifications from changes in our Compose PostgreSQL database, let's look at a couple other use cases.

Copy the data somewhere else

In our example above where we're using a custom query to generate a data row or when a new row is added to our table, rather than sending a notification, we may want to copy that data to another app. For example, we may want to copy that data to Google sheets for our marketing team to have easy access to it for creating reports. We could also use this option in a polyglot persistence scenario where we need the same data in a different database. In that case, we could copy data from our Compose PostgreSQL database to our Compose MongoDB or RethinkDB databases (or vice versa!), or even to your corporate SQL Server instance.

The steps for this use case are similar to the ones we demonstrated above, though each application will have its own specifics, of course. The great thing with Zapier is that it's built to be intuitive and to guide you along for each integration type. Since we've run through one case with you here and a couple others in our previous articles, we know you've already got the hang of how to get data from PostgreSQL to other apps.

Now let's look at our final use case for this article... having another application trigger the insertion of a data row into PostgreSQL.

Add data from another app

At Compose, we use Help Scout for keeping in contact with our customers and helping them resolve support issues. Let's say that we want to tally the customer support conversations from Help Scout so that we can tie them directly to our accounts database in PostgreSQL.

So, we'll make a new "zap", choose Help Scout as the trigger application, and then choose "New conversation" as the trigger:

You'll be asked to setup the connection to Help Scout app via API key (which you can generate in the settings for your Help Scout profile) if you don't have one created already.

Next, we'll select the Help Scout mailbox we want and a status if that's applicable:

We'll then test the request and continue on.

Next, we'll move on to the action... Add data to PostgreSQL.

We'll choose to add a new row for this example:

Since our PostgreSQL connection already exists in Zapier, we'll choose to use it, though as we mentioned, you could add a new one if you need to.

Next, we'll select the table in PostgreSQL and set how the fields from Help Scout map to the fields in our table. For this example, we just have a simple table that will collect the timestamp at which the conversation was created and the customer email:

We then test our data row insertion into PostgreSQL and finish our "zap" by giving it a name and turning it on.

Now, what we can do is create a report from data in PostgreSQL that aggregates a customer's conversations from Help Scout and joins that to the account record that already exists in our PostgreSQL database. Or we can create a query to tell us the most frequent days and times that conversations are created to make sure we have good coverage in support.

This is just a simple example, but think of how powerful this use case can be. With Zapier, you can add data to your PostgreSQL database from other applications so that you can easily create reports and run analyses from multiple sources in one convenient location - Compose PostgreSQL!

Wrapping up

Zapier is a powerful tool that will help you get more from your Compose PostgreSQL database, either by using it to trigger data or notifications to other apps or by using it to generate new data rows in the database based on data or events from other apps. Compose PostgreSQL, MongoDB and RethinkDB are all currently supported by Zapier as well as more than 500 other applications available for integration. If you don't already have a Compose account, signup to get started with PostgreSQL today.