PostGres

PostgreSQL, often referred to as Postgres, is a robust, open-source relational database management system (RDBMS). It has gained prominence for its extensibility, reliability, and support for advanced data types and features. PostgreSQL is widely used in various applications, from small-scale projects to enterprise-level solutions.

The documentation will cover the configuration process, usage of the PostgreSQL integration, querying the database via datasets, and troubleshooting common issues. By the end of this guide, you will have a comprehensive understanding of integrating PostgreSQL with Nected and leveraging its capabilities for your applications.

Setting Up PostgreSQL Integration

To connect Nected to a PostgreSQL database and enable seamless data interaction, you need to set up a PostgreSQL integration. This section outlines the step-by-step process for configuring the integration effectively.

1. Adding Integration

Select Postgres from the integrations page.. This choice initiates the process of setting up an integration for PostgreSQL.

2. Configuring Connection Settings

The configuration of connection settings is a critical step in establishing the connection between Nected and your PostgreSQL database. The following parameters need to be defined:

• Environment Type: Choose whether you are configuring the integration for a "Staging" or "Production" environment. The selection of the environment type determines the context in which the integration will operate. By default, the environment type is “Staging”.

• Integration Name: Assign a unique and meaningful name for the integration. It is essential that this name is distinct throughout the Nected platform and does not contain any spaces.

• Host (Database IP Address): Specify the IP address or hostname of the PostgreSQL database to which you want to connect.

• Port Number: Enter the port number on which Nected should communicate with the PostgreSQL database. The default port for PostgreSQL is 5432.

• Username: Provide the username of the authorized user who has the necessary privileges to connect to the database and perform read and write operations.

• Password (optional, encrypted): You may enter the password for the authorized user. Passwords are encrypted to ensure maximum security for your database. If the database user does not have a password, you can leave this field blank.

• Database Name: Specify the name of the PostgreSQL database from which Nected will access and manipulate data.

• IP Addresses for Allow-list: To enhance security, it's recommended to configure an allow-list of IP addresses that can access your PostgreSQL database. Ensure that Nected service IP addresses, such as "43.205.43.45," are included in this allow-list to secure the connection.

3. Testing the Connection

After configuring the connection settings, it's crucial to test the connection to ensure that Nected can successfully communicate with the PostgreSQL database. This test verifies that the provided information is accurate and the connection is established without issues.

4. Publishing in Staging

If the connection test is successful, you can proceed to publish the integration in the "Staging" environment. In this context, "Staging" refers to a development or testing environment. For a "Production" database, the option will be "Publish in Production." Publishing the connector in a specific environment enables you to set up rules and operations within that environment.

5. Integration Status

After configuring and publishing the integration, it's essential to check the integration status. Close the connection information form and check the integration status. For a "Staging" environment, the status will be "staging," and for a "Production" environment, it will be "Production." Monitoring the status ensures that the connector is active and properly integrated into the selected environment.

Note: If using Nected cloud, Nected IP address (43.205.43.45) must be added to the allow-list on your database server to secure your connections. Not required in on-premise setup

You need to publish staging and production integrations in their respective environments. If a integration is published only in staging, then it cannot be used in the production environment, and any call to rule in production env will fail giving "connector not published in production" error

Querying Postgres Database via DataSet

Nected provides a powerful feature that allows you to query and interact with your PostgreSQL database through datasets. This section will guide you through the process of adding a new dataset, writing SQL code for querying the PostgreSQL database, and performing various operations, including creating rows and executing join operations.

Adding a New DataSet:

Before you can query the PostgreSQL database, you need to add a new dataset to your Nected environment. Follow these steps:

1. Navigate to Datasets: Click on "Datasets" in the left navigation panel to access the Datasets page.

2. Create a Dataset: Click the "+ Create Dataset" button on the Datasets page. Choose the database integration associated with the dataset you wish to create. Note that you can create multiple datasets for one integration.

3. Dataset Information: Complete the dataset information form, including details such as the dataset name, dataset type (staging or production), the source for dataset parameters.

4. Finally you need to write the specific database query to retrieve data from your connected database. For example, to connect the complete databse within your dataset, write:

SELECT * FROM table_name; //Change the table_name with your database table_name

Query Operations:

In addition to basic querying, you can perform various operations on the data retrieved from the PostgreSQL database. Here are some common tasks you can carry out:

1. Aggregations:

You can use aggregations to summarize and analyze your financial data. For instance, to calculate the total deposit amount for a specific day:

SELECT
    transaction_date,
    SUM(amount) AS total_deposit
FROM
    financial_transactions
WHERE
    transaction_type = 'Transfer'
GROUP BY
    transaction_date;

This query aggregates the total deposit amount for each distinct transaction date.

2. Filtering:

Filtering is essential for retrieving specific subsets of data. To find all transactions above a certain amount, you can use:

SELECT
    transaction_date,
    amount,
    description
FROM
    financial_transactions
WHERE
    amount > 1000;

This query filters and selects transactions with amounts greater than $1000.

3. Sorting:

Sorting data can be useful for visualizing trends or identifying the most significant transactions. To sort transactions by amount in descending order:

SELECT
    transaction_date,
    amount,
    description
FROM
    financial_transactions
ORDER BY
    amount DESC;

This query returns transactions sorted by the amount in descending order.

4. Grouping and Aggregating:

Combining grouping and aggregation helps summarize data based on certain criteria. To find the total transaction amount by transaction type:

SELECT
    transaction_type,
    SUM(amount) AS total_amount
FROM
    financial_transactions
GROUP BY
    transaction_type;

This query groups transactions by type and calculates the total amount for each type.

5. Joins:

If your database has multiple related tables, joins can be used to combine data from different sources. For instance, to link transactions with account details:

SELECT
    t.transaction_id,
    t.transaction_date,
    t.amount,
    a.account_name
FROM
    financial_transactions t
JOIN
    accounts a
ON
    t.account_id = a.account_id;

This query joins the financial_transactions table with an accounts table to retrieve transactions along with their corresponding account names.

These SQL operations and queries can help you extract valuable insights and generate reports from your financial data stored in the financial_transactions table. The principles and techniques demonstrated here can be adapted and expanded upon to suit specific analysis and reporting needs in the fintech industry or any database scenario.

Tip: Always ensure that your SQL code is well-tested in the "Staging" environment before deploying it to the "Production" environment to prevent potential data integrity issues and unexpected consequences.

Supported Queries for Creating a Dataset:

When creating a dataset in Nected, you have the flexibility to retrieve and filter data using a variety of PostgreSQL queries. These queries help you shape your dataset to meet your analytical needs. Below are the supported queries for creating a dataset:

Query Type	Description	Example
Select Queries	Retrieve data from one or more tables, with various filtering and sorting options.	SELECT * FROM products WHERE price > 50;
		SELECT customer_name, order_date FROM customers JOIN orders ON customers.customer_id = orders.customer_id;
Aggregation Queries	Perform data analysis with aggregation functions like SUM, COUNT, GROUP BY, etc.	SELECT product_id, SUM(sale_amount) AS total_sales FROM sales GROUP BY product_id;
Join Queries	Combine data from multiple tables using JOIN operations for more complex queries.	SELECT customers.customer_name, orders.order_date FROM customers JOIN orders ON customers.customer_id = orders.customer_id;
Subqueries	Use subqueries to nest one query inside another, often in the WHERE or FROM clauses.	SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM sales WHERE sale_amount > 1000);
Union Queries	Merge the results of two or more SELECT queries into a single result set.	SELECT customer_name FROM customers UNION SELECT supplier_name FROM suppliers;
Intersection Queries	Retrieve common records from two or more SELECT queries.	SELECT product_id FROM products WHERE price > 50 INTERSECT SELECT product_id FROM sales;
Difference Queries	Find records that exist in one SELECT query but not in another.	SELECT product_id FROM products WHERE price > 50 EXCEPT SELECT product_id FROM sales;
Conditional Queries	Use conditional expressions (CASE WHEN) to perform conditional logic within queries.	SELECT product_name, CASE WHEN price > 100 THEN 'Expensive' ELSE 'Affordable' END AS price_category FROM products;
Window Functions	Analyze data across a set of table rows related to the current row with functions like ROW_NUMBER(), RANK(), LEAD(), and LAG().	SELECT product_name, price, ROW_NUMBER() OVER (ORDER BY price) AS row_num FROM products;
Recursive Queries	Create queries that refer to themselves, commonly used for hierarchical data.	WITH RECURSIVE employee_hierarchy AS ( SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM employee_hierarchy;
Dynamic SQL	Construct and execute SQL queries dynamically based on runtime conditions or user inputs.	Example of constructing a dynamic SQL query based on user input.

Non-Supported Queries for Creating a Dataset

While Nected offers extensive support for data retrieval and shaping, some certain queries and operations are not supported when creating a dataset:

Query Type	Description	Example
Delete Queries	Removing records from a table is not supported when creating a dataset.	DELETE FROM products WHERE price < 10;
Truncate Table	Truncating a table to remove all records is not supported.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported.	CREATE INDEX idx_customer_name ON customers (customer_name);
Stored Procedures	Creating stored procedures and functions within the database is not supported.	CREATE PROCEDURE calculate_total_sales() BEGIN SELECT SUM(sale_amount) FROM sales; END;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported.	GRANT SELECT ON products TO user1;

Trigger Postgres operations as Rule Actions

In Nected, rule actions provide a way to automate PostgreSQL operations when specific conditions are met. While datasets are typically used for read-only queries, rule actions allow you to modify the dataset and trigger specific operations within your PostgreSQL database. Below, we'll explore some SQL queries that can be used as rule actions to write or edit data within the financial_transactions table:

1. Inserting New Financial Transactions:

1. Static Query

   To insert new financial transactions into the PostgreSQL database as a rule action, you can use the INSERT INTO statement with static values. For example, if a rule is triggered to log a new deposit:

   INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status)
   VALUES ('2023-10-21', '09:30:00', 106, 'Deposit', 1500.00, 'USD', 'Client Payment', 'Completed');
   

   This query adds a new deposit transaction to the database with the specified details.

2. Tokenized Query Using Output Data, Custom Input and/or Data from Dataset

   To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to insert a new transaction with attributes from either of those:

   INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status)
   VALUES ('{{.dataSet.transaction_date}}', '{{.dataSet.transaction_time}}', {{.customInput.account_id}}, '{{.dataSet.transaction_type}}', {{.outputData.amount}}, '{{.outputData.currency_code}}', '{{.outputData.description}}', 'Completed');
   

   This query fetches the transaction details from the rule's output data, custom input and details from dataset attributes.

2. Updating Transaction Status:

1. Static Query

   You can use the UPDATE statement with static values as a rule action to modify the status of specific transactions. For instance, if a rule action is set to change the status of a transaction:

   UPDATE financial_transactions
   SET status = 'Completed'
   WHERE transaction_id = 21;
   

   This query updates the status of a transaction with a specific transaction_id to 'Completed'.

2. Tokenized Query Using Output Data, Custom Input and/or Data from Dataset

   To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to update the status of a transaction based on either of these:

   UPDATE financial_transactions
   SET status = '{{.outputData.new_status}}'
   WHERE transaction_id = {{.customInput.transaction_id}};
   

   This query adjusts the status of a transaction using data from the rule's output, custom inputs. If needed, you can also use token to extract data from dataset attributes.

These SQL queries demonstrate how you can use rule actions with both static and tokenized queries to perform data modification operations within the PostgreSQL database in response to specific triggers or conditions. Rule actions offer flexibility in automating data updates, ensuring data accuracy, and enhancing the functionality of your fintech application or database.

Supported Queries for Writing an Action

Nected allows you to perform various actions on your data, such as inserting, updating, and merging records, all while ensuring data integrity. Here are the supported queries for writing an action:

Query Type	Description	Example
Insert Queries	Add new records to a table.	INSERT INTO products (product_name, price) VALUES ('New Product', 50);
Update Queries	Modify existing records in a table.	UPDATE customers SET email = '[e-mail]' WHERE customer_id = 123;
Merge Queries	Combine data from source table(s) into a target table, typically used for data synchronization.	MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.value = source_table.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source_table.id, source_table.value);
Transactional Queries	Execute a sequence of queries within a transaction to ensure consistency.	BEGIN; INSERT INTO orders (order_id, customer_id, order_date) VALUES (789, 123, '2023-11-15'); UPDATE customers SET total_orders = total_orders + 1 WHERE customer_id = 123; COMMIT;

Non-Supported Queries for Writing an Action

When it comes to writing actions in Nected, there are limitations on the types of queries and operations that can be performed to ensure data consistency and security. Here are the queries that are not supported for writing an action:

Query Type	Description	Example
Truncate Table	Truncating a table to remove all records is not supported as actions.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported as actions.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported as actions.	CREATE INDEX idx_customer_name ON customers (customer_name);
Stored Procedures	Creating stored procedures and functions within the database is not supported as actions.	CREATE PROCEDURE calculate_total_sales() BEGIN SELECT SUM(sale_amount) FROM sales; END;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported as actions.	GRANT SELECT ON products TO user1;

Add PostgreSQL as Workflow Node

Adding nodes in the workflow editor of Nected is essential because they represent the individual tasks, decisions, or actions that comprise the overall automated process. By adding and configuring nodes, users can tailor workflows to fit their specific business processes, data handling requirements, and decision logic, ensuring that tasks are executed efficiently and consistently.

To add PostgreSQL as a workflow node, follow the following steps:

1. Within the workflow canvas, you would typically click the '+' or 'Add Node' button to reveal the list of nodes.

2. Choose PostgreSQL from the node list.

These are the most basic steps, for a more detailed overview of how to configure it, read the DB Node page.

Supported & Non-supported Queries as Workflow Node:

1. While used as a Workflow Node, all types of SELECT and INSERT or UPDATE queries are supported.

2. The list of Non-Supported query list is just the same as 'Non-Supported Queries for Writing an Action'.

Next Steps

After you successfully publish the database connector, you can use it via Dataset to unify data & attach it as input to the Rules as well as to the Workflows to invoke any database operations as part of Rule Actions or Workflow Nodes.