Query postgres database and send message to SMS, email and Slack

Deploy to configure a custom schedule

This workflow runs on Pipedream's servers and is triggered on a custom schedule.

steps.

query_postgres

auth

to use OAuth tokens and API keys in code via theauths object

params

Host

Enter your Postgres database host name.

1

my.database.host.com

string ·params.host

Database

Enter your database name.

1

my_database_name

string ·params.database

User

Enter your database username.

1

my_database_username

string ·params.user

Password

Enter your database password or use an environment variable (configure at https://pipedream.com/settings)

1

string ·params.password

Port

Enter the port for your database.

1

5432

string ·params.port

Sql query

Enter the SQL query you want to run. The results will be exported from this step as an array. You can access the data in any subsequent steps by referencing steps.query_postgres.results

1

select * from my_table_name

string ·params.sql_query

code

Write any Node.jscodeand use anynpm package. You can alsoexport datafor use in later steps via return or this.key = 'value', pass input data to your code viaparams, and maintain state across executions with$checkpoint.

async (event, steps, params) => {

1

2

3

4

5

6

7

8

9

10

11

12

13

}

14

const { Client } = require('pg')

const client = new Client({
  host: params.host,
  database: params.database,
  user: params.user,
  password: params.password,
  port: params.port,
})
await client.connect()
this.results = (await client.query(params.sql_query)).rows
await client.end()

steps.

generate_message

auth

to use OAuth tokens and API keys in code via theauths object

code

Write any Node.jscodeand use anynpm package. You can alsoexport datafor use in later steps via return or this.key = 'value', pass input data to your code viaparams, and maintain state across executions with$checkpoint.

async (event, steps) => {

1

2

3

4

5

6

}

7

// write node.js glue code here to transform your query results
// the message you generate here is sent via sms using Twilio in the next step
// you can also optionally send to email and slack. just activate the steps using the toggle and configure them.

return `Your query returned ${steps.query_postgres.results.length} rows.`

steps.

send_sms

To send a new outgoing message, make an HTTP POST to your Messages list resource URI

auth

(auths.twilio)

params

To

The destination phone number. Format with a '+' and country code e.g., +16175551212 (E.164 format).

1

string ·params.To

From

A Twilio phone number (in E.164 format) or alphanumeric sender ID enabled for the type of message you wish to send. Phone numbers or short codes purchased from Twilio work here. You cannot (for example) spoof messages from your own cell phone number.

1

string ·params.From

Body

The text of the message you want to send, limited to 1600 characters.

1

{{steps.generate_message.$return_value}}

string ·params.Body

Optional

code

async (params, auths) => {

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

}

22

const data = {
  To: params.To,
  From: params.From,
  MessagingServiceSid: params.MessagingServiceSid,
  Body: params.Body,
  MediaUrl: params.MediaUrl,
}
const config = {
  method: "post",
  url: `https://api.twilio.com/2010-04-01/Accounts/${auths.twilio.AccountSid}/Messages.json`,
  headers: {
    "Content-Type": "application/x-www-form-urlencoded",
  },
  auth: {
    username: auths.twilio.Sid,
    password: auths.twilio.Secret,
  },
  data: require("qs").stringify(data),
}
return await require("@pipedreamhq/platform").axios(this, config)

steps.

email_me

Customize and send an email to the email address you registered with Pipedream. The email will be sent by notifications@pipedream.com.

params

Subject

1

Postgres Query Results

string ·params.subject

Text

1

{{steps.generate_message.$return_value}}

string ·params.text

Optional

code

async params => {

1

2

3

4

5

6

7

8

9

}

10

const options = {
  subject: params.subject,
  text: params.text,
}
if (params.html) {
  options.html = params.html
}
$send.email(options)

steps.

send_to_slack

Sends a message to a channel.

auth

(auths.slack)

params

Channel

Channel, private group, or IM channel to send message to. Can be an encoded ID, or a name. See below for more details.

1

C1234567890

string ·params.channel

Text

Text of the message to send. See below for an explanation of formatting. This field is usually required, unless you're providing only attachments instead. Provide no more than 40,000 characters or risk truncation.

1

{{steps.generate_message.$return_value}}

string ·params.text

Optional

code

async (params, auths) => {

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

}

29

const data = {
  channel: params.channel,
  text: params.text,
  as_user: params.as_user,
  attachments: params.attachments,
  blocks: params.blocks,
  icon_emoji: params.icon_emoji,
  icon_url: params.icon_url,
  link_names: params.link_names,
  mrkdwn: params.mrkdwn,
  parse: params.parse,
  reply_broadcast: params.reply_broadcast,
  thread_ts: params.thread_ts,
  unfurl_links: params.unfurl_links,
  unfurl_media: params.unfurl_media,
  username: params.username,
}
const config = {
  method: "post",
  url: `https://slack.com/api/chat.postMessage`,
  headers: {
    Authorization: `Bearer ${auths.slack.oauth_access_token}`,
  },
  data,
}
return await require("@pipedreamhq/platform").axios(this, config)