How to Write a Node.js Code for Communicating with a PostgreSQL Database in Pipedream?

Help
21

To modify the code to include all available columns in table2, you can use a custom query with a JOIN statement. Here’s the updated Node.js code using Pipedream component code: ```
import pg from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, schema: { type: “string”, label: “Schema”, description: “Select the schema you want to use”, // Add options method if needed }, table1: { type: “string”, label: “Table 1”, description: “Select the first table you want to compare”, // Add options method if needed }, table2: { type: “string”, label: “Table 2”, description: “Select the second table you want to compare”, // Add options method if needed }, column: { type: “string”, label: “Column”, description: “Enter the column name you want to compare”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const query = SELECT t2.* FROM "${this.schema}"."${this.table1}" t1 JOIN "${this.schema}"."${this.table2}" t2 ON t1."${this.column}" = t2."${this.column}" ; const result = await client.query(query); await client.end(); return result.rows; }, });

22

Using the code created above, after comparing the tables, check if the “TF” column in table 2 contains the text “240m.”

23

To modify the code to check if the “TF” column in table 2 contains the text “240m” after comparing the tables, you can add a filter to the query results. Here’s the updated code: ```
import pg from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, schema: { type: “string”, label: “Schema”, description: “Select the schema you want to use”, }, table1: { type: “string”, label: “Table 1”, description: “Select the first table you want to compare”, }, table2: { type: “string”, label: “Table 2”, description: “Select the second table you want to compare”, }, column: { type: “string”, label: “Column”, description: “Enter the column name you want to compare”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const query = SELECT t2.* FROM "${this.schema}"."${this.table1}" t1 JOIN "${this.schema}"."${this.table2}" t2 ON t1."${this.column}" = t2."${this.column}" ; const result = await client.query(query); await client.end(); const filteredRows = result.rows.filter(row => row.TF === “240m”); return filteredRows; }, });

24

Using the code created above, after comparing the tables, before displaying the result, check if the column TF in table 2 contains the text “240m.”

25

To modify the code to check if the “TF” column in table 2 contains the text “240m” before displaying the result, you can add a WHERE clause to the query. Here’s the updated code: ```
import pg from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, schema: { type: “string”, label: “Schema”, description: “Select the schema you want to use”, }, table1: { type: “string”, label: “Table 1”, description: “Select the first table you want to compare”, }, table2: { type: “string”, label: “Table 2”, description: “Select the second table you want to compare”, }, column: { type: “string”, label: “Column”, description: “Enter the column name you want to compare”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const query = SELECT t2.* FROM "${this.schema}"."${this.table1}" t1 JOIN "${this.schema}"."${this.table2}" t2 ON t1."${this.column}" = t2."${this.column}" WHERE t2."TF" = '240m' ; const result = await client.query(query); await client.end(); return result.rows; }, });

26

Using the code created above, before comparing the tables, check if the column TF in table 2 contains the text “240m.”

27

To modify the code to check if the “TF” column in table 2 contains the text “240m” before comparing the tables, you can add a WHERE clause to the query. Here’s the updated code: ```
import pg from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, schema: { type: “string”, label: “Schema”, description: “Select the schema you want to use”, }, table1: { type: “string”, label: “Table 1”, description: “Select the first table you want to compare”, }, table2: { type: “string”, label: “Table 2”, description: “Select the second table you want to compare”, }, column: { type: “string”, label: “Column”, description: “Enter the column name you want to compare”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const query = SELECT t2.* FROM "${this.schema}"."${this.table1}" t1 JOIN "${this.schema}"."${this.table2}" t2 ON t1."${this.column}" = t2."${this.column}" WHERE t2."TF" = '240m' ; const result = await client.query(query); await client.end(); return result.rows; }, });

28

Using the code created above, after checking the TF column, I also checked the Symbols column to see if it contains the text “USDCAD.”

29

To modify the code to check if the “Symbols” column contains the text “USDCAD” after checking the “TF” column, you can add an additional condition to the WHERE clause. Here’s the updated code: ```
import pg from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, schema: { type: “string”, label: “Schema”, description: “Select the schema you want to use”, }, table1: { type: “string”, label: “Table 1”, description: “Select the first table you want to compare”, }, table2: { type: “string”, label: “Table 2”, description: “Select the second table you want to compare”, }, column: { type: “string”, label: “Column”, description: “Enter the column name you want to compare”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const query = SELECT t2.* FROM "${this.schema}"."${this.table1}" t1 JOIN "${this.schema}"."${this.table2}" t2 ON t1."${this.column}" = t2."${this.column}" WHERE t2."TF" = '240m' AND t2."Symbols" = 'USDCAD' ; const result = await client.query(query); await client.end(); return result.rows; }, });

30

Using the code created above, add an option for the user to input the “TF” and “Symbols” in the settings.

31

To add options for the user to input the “TF” and “Symbols” values in the settings, you can modify the component’s props to include these inputs. Here’s the updated code: import pg from "pg"; export default defineComponent({ props: { postgresql: { type: "app", app: "postgresql", }, schema: { type: "string", label: "Schema", description: "Select the schema you want to use", }, table1: { type: "string", label: "Table 1", description: "Select the first table you want to compare", }, table2: { type: "string", label: "Table 2", description: "Select the second table you want to compare", }, column: { type: "string", label: "Column", description: "Enter the column name you want to compare", }, tfValue: { type: "string", label: "TF Value", description: "Enter the value for the TF column", }, symbolsValue: { type: "string", label: "Symbols Value", description: "Enter the value for the Symbols column", }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const query = ` SELECT t2.* FROM "${this.schema}"."${this.table1}" t1 JOIN "${this.schema}"."${this.table2}" t2 ON t1."${this.column}" = t2."${this.column}" WHERE t2."TF" = '${this.tfValue}' AND t2."Symbols" = '${this.symbolsValue}' `; const result = await client.query(query); await client.end(); return result.rows; }, }); Now, the user can input the “TF” and “Symbols” values using the tfValue and symbolsValue props in the settings.

32

Create a Node.js code to insert a row in PostgreSQL based on the “Insert Row” action.

33

To insert a row in PostgreSQL using Node.js, you can use the following Pipedream component code based on the “Insert Row” action: ```
import pg from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, schema: { type: “string”, label: “Schema”, description: “Select the schema you want to use”, }, table: { type: “string”, label: “Table”, description: “Select the table you want to insert the row into”, }, rowValues: { type: “object”, label: “Row Values”, description: “Enter the row values as an object”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const keys = Object.keys(this.rowValues).map((key) => "${key}").join(", “); const values = Object.values(this.rowValues) .map((value) => (typeof value === “string” ? '${value}' : value)) .join(”, "); const query = INSERT INTO "${this.schema}"."${this.table}" (${keys}) VALUES (${values}); await client.query(query); await client.end(); }, });

34

In rowValues, add the following lines as defaults:
DATE: {{steps.data_processing_discord.Data}}
TIME: {{steps.data_processing_discord.Time}}

35

To add the default values for “DATE” and “TIME” in the rowValues prop, you can use the default property. Here’s the updated code: import pg from "pg"; export default defineComponent({ props: { postgresql: { type: "app", app: "postgresql", }, schema: { type: "string", label: "Schema", description: "Select the schema you want to use", }, table: { type: "string", label: "Table", description: "Select the table you want to insert the row into", }, rowValues: { type: "object", label: "Row Values", description: "Enter the row values as an object", default: { DATE: "{{steps.data_processing_discord.Data}}", TIME: "{{steps.data_processing_discord.Time}}", }, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const keys = Object.keys(this.rowValues).map((key) => `"${key}"`).join(", "); const values = Object.values(this.rowValues) .map((value) => (typeof value === "string" ? `'${value}'` : value)) .join(", "); const query = `INSERT INTO "${this.schema}"."${this.table}" (${keys}) VALUES (${values})`; await client.query(query); await client.end(); }, }); Now, the “DATE” and “TIME” values in the rowValues prop will have default values based on the steps.data_processing_discord.Data and steps.data_processing_discord.Time variables.

36

In rowValues, add the following lines as defaults:
DATE: {{steps.data_processing_discord.Data}}
TIME: {{steps.data_processing_discord.Time}}
TIME CLOSE CANDLE:{{steps.data_processing_discord[“Time Close Candle”]}}

37

To add the default values for “DATE”, “TIME”, and “TIME CLOSE CANDLE” in the rowValues prop, you can use the default property. Here’s the updated code: import pg from "pg"; export default defineComponent({ props: { postgresql: { type: "app", app: "postgresql", }, schema: { type: "string", label: "Schema", description: "Select the schema you want to use", }, table: { type: "string", label: "Table", description: "Select the table you want to insert the row into", }, rowValues: { type: "object", label: "Row Values", description: "Enter the row values as an object", default: { DATE: "{{steps.data_processing_discord.Data}}", TIME: "{{steps.data_processing_discord.Time}}", "TIME CLOSE CANDLE": "{{steps.data_processing_discord['Time Close Candle']}}", }, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql.$auth; const client = new pg.Client({ host, database, user, password, port, }); await client.connect(); const keys = Object.keys(this.rowValues).map((key) => `"${key}"`).join(", "); const values = Object.values(this.rowValues) .map((value) => (typeof value === "string" ? `'${value}'` : value)) .join(", "); const query = `INSERT INTO "${this.schema}"."${this.table}" (${keys}) VALUES (${values})`; await client.query(query); await client.end(); }, }); Now, the “DATE”, “TIME”, and “TIME CLOSE CANDLE” values in the rowValues prop will have default values based on the steps.data_processing_discord.Data, steps.data_processing_discord.Time, and steps.data_processing_discord['Time Close Candle'] variables.

38

Create a Node.js code based on the ‘add_single_row’ action of the Google Sheets app.

39

To create a Node.js code based on the ‘add_single_row’ action of the Google Sheets app, you can use the following Pipedream component: import { GoogleSheets } from "@pipedream/platform"; export default defineComponent({ props: { googleSheets: { type: "app", app: "google_sheets", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", description: "The ID of the Google Sheet you want to add the row to", }, sheetName: { type: "string", label: "Sheet Name", description: "The name of the sheet where you want to add the row", }, rowValues: { type: "string[]", label: "Row Values", description: "Enter the values for each cell in the row", }, }, async run({ steps, $ }) { const sheets = new GoogleSheets(this.googleSheets.$auth); const range = `${this.sheetName}!A:${String.fromCharCode(64 + this.rowValues.length)}`; const response = await sheets.addSingleRow(this.spreadsheetId, range, this.rowValues); return response; }, }); This component will add a single row to the specified Google Sheet using the ‘add_single_row’ action. You’ll need to provide the spreadsheetId, sheetName, and rowValues as props.

40

The previous code reports the following error: “The requested module ‘@pipedream/platform’ does not provide an export named ‘GoogleSheets’.”