PostgreSQL

The PostgreSQL integration in Locale allows users to seamlessly connect their PostgreSQL databases to Locale, enabling the creation of dynamic and data-driven workflows.

Connecting PostgreSQL to Locale

To integrate your PostgreSQL database with Locale, follow these steps:

  1. Provide Connection Details: Enter the following information to establish the connection:
    • Display Name: A name for the connection in Locale.
    • Host Address: The address of your PostgreSQL server.
    • Port: The port number of your PostgreSQL server.
    • Database: The name of the database you want to connect.
    • Username: The username for accessing the database.
    • Password: The password for the specified username.
  2. Additional Configurations (optional):
    • Enable SSL: Toggle this option if your database requires SSL.
    • Bastion Host Configurations: Provide details if your database connection requires a bastion host.
  3. Firewall Configurations: Ensure to whitelist Locale’s IP addresses if your database is behind a firewall.

Triggers

New Row is Added

The "New Row is Added" trigger allows you to execute a workflow when a new row is added to your PostgreSQL database. This trigger runs an SQL query at a specified frequency and initiates the workflow for each new row found.

How to Use

  1. Set Up the Trigger: Add the "New Row is Added" trigger to your workflow.
  2. Configure SQL Query: Provide an SQL query that returns the rows to be monitored. Ensure the query includes all necessary information and at least one column with a unique value.
    • If no single column is unique, configure the query to create a unique key column using a combination of two columns.
  3. Primary Key Column: Specify the unique or primary key column.
  4. Frequency of Execution: Choose the frequency at which the query should run to check for new rows.
    • Fixed Frequency: Select from predefined intervals (e.g., every 1 minute to every 4 hours).
    • Cron Schedule: Define custom schedules using cron syntax. Examples:
      • * * * * * (Every minute)
      • 0 0 * * * (At midnight every day)
      • 0 */2 * * * (Every 2 hours)

Example Configuration

  • SQL Query: 
    SELECT id, name, email, created_at FROM users WHERE created_at > NOW() - INTERVAL '1 day'
  • Primary Key Column: id
  • Frequency:
    • Fixed Frequency: Every 5 minutes
    • Cron Schedule: */5 * * * * (Every 5 minutes)

Once the trigger is set, you can select a row from the query response to use it for further workflow configurations.

Actions

Run SQL Query

The "Run SQL Query" action allows you to execute SQL queries on your PostgreSQL database. This action can be used to insert, update, delete, or fetch data.

How to Use

  1. Set Up the Action: Add the "Run SQL Query" action to your workflow.
  2. Write SQL Query: Enter the SQL query you want to execute. You can inject data variables from previous nodes to customize the query.

Example

  1. Add Action: Drag and drop the "Run SQL Query" action into your workflow canvas.
  2. Write SQL Query: Use data variables to dynamically insert values. For example: 
    INSERT INTO orders (user_id, product_id, quantity) VALUES ({{ previousNode.data.user_id }}, {{ previousNode.data.product_id }}, {{ previousNode.data.quantity }})

Need Help?

If you have any questions or need assistance with the PostgreSQL integration in Locale, our support team is here to help. Visit our Help Center or contact us directly at support@locale.ai.

The PostgreSQL integration in Locale enables powerful data-driven workflows by allowing you to connect your database, monitor new rows, and execute SQL queries. With the "New Row is Added" trigger and the "Run SQL Query" action, you can automate complex processes and keep your workflows in sync with your PostgreSQL data.

Table of Contents