How to connect n8n to PostgreSQL?

Connecting n8n to PostgreSQL involves setting up the PostgreSQL credentials node in n8n to establish a connection to your database, allowing you to read and write data between n8n workflows and your PostgreSQL database. This integration enables you to automate database operations, run queries, and incorporate database data into your workflow automation.

 

Step 1: Prerequisites

 

Before connecting n8n to PostgreSQL, ensure you have:

• n8n installed and running
• PostgreSQL database set up and running
• Database credentials (host, port, username, password, database name)
• Proper network access between your n8n instance and PostgreSQL server

 

Step 2: Access n8n Workflow Editor

 

First, you need to access the n8n workflow editor to create a new workflow or edit an existing one:

• Open your web browser and navigate to your n8n instance (typically http://localhost:5678 for local installations)
• Log in to your n8n account if prompted
• Click on "Workflows" in the left sidebar
• Click on "Create New Workflow" or open an existing workflow

 

Step 3: Add PostgreSQL Credentials

 

Before using PostgreSQL in your workflow, you need to set up the credentials:

• Click on the gear icon (⚙️) in the top right corner
• Select "Credentials" from the dropdown menu
• Click the "+ Add Credential" button
• Search for "PostgreSQL" in the search box
• Select "PostgreSQL" from the list

 

Step 4: Configure PostgreSQL Credentials

 

Fill in the PostgreSQL connection details:

• Credential Name: Give your credential a descriptive name (e.g., "My PostgreSQL DB")
• Host: Enter your PostgreSQL server address (e.g., localhost, 127.0.0.1, or a remote server address)
• Database: Enter the name of your PostgreSQL database
• User: Enter the PostgreSQL username
• Password: Enter the PostgreSQL password
• Port: Enter the PostgreSQL port (default is 5432)
• SSL: Toggle on if your PostgreSQL connection requires SSL
• Connection Timeout: Set the connection timeout in seconds (optional)
• Max Connection Pool Size: Set the maximum connection pool size (optional)

Click "Create" or "Save" to store your credentials.

 

Step 5: Add PostgreSQL Node to Your Workflow

 

Now you can add a PostgreSQL node to your workflow:

• Return to your workflow canvas
• Click the "+" button to add a new node
• Search for "PostgreSQL" in the search box
• Select the "PostgreSQL" node from the results

 

Step 6: Configure the PostgreSQL Node

 

Configure the PostgreSQL node settings:

• Select the credentials you created in Step 4 from the "Credentials" dropdown
• Choose an operation from the "Operation" dropdown. Options include:
  • Execute Query: Run a custom SQL query
  • Insert: Insert rows into a table
  • Update: Update existing rows in a table
  • Select: Retrieve data from a table
  • Delete: Remove rows from a table

 

Step 7: Configuring Different PostgreSQL Operations

 

Depending on the operation you selected in Step 6, you'll need to configure different settings:

For "Execute Query" operation:

• Enter your SQL query in the "Query" field. For example:

SELECT \* FROM users WHERE age > 18

• If your query has parameters, you can use $1, $2, etc., and provide values in the "Query Parameters" section

For "Insert" operation:

• Enter the table name in the "Table" field
• In the "Columns" section, specify the column names and their values
• You can use "Add Column" to add multiple columns

For "Update" operation:

• Enter the table name in the "Table" field
• In the "Columns" section, specify the columns to update and their new values
• In the "Where" section, specify the conditions for the update

For "Select" operation:

• Enter the table name in the "Table" field
• Optionally, specify columns to return in the "Columns" field (comma-separated, leave empty for all columns)
• In the "Where" section, specify conditions for your SELECT query
• Optionally, add "Additional Options" like ORDER BY, LIMIT, etc.

For "Delete" operation:

• Enter the table name in the "Table" field
• In the "Where" section, specify conditions for the deletion

 

Step 8: Using Expressions and Dynamic Values

 

n8n allows you to use expressions and dynamic values from previous nodes in your PostgreSQL node:

• Click on the settings icon (gear) next to any field
• Select "Add Expression"
• Use the expression editor to reference data from previous nodes. For example:

SELECT \* FROM users WHERE email = '{{$json["email"]}}'

This will use the email value from the previous node's output.

 

Step 9: Advanced PostgreSQL Query Examples

 

Here are some examples of more complex PostgreSQL operations you might use:

Joining Tables:

SELECT u.id, u.name, o.order\_date, o.total 
FROM users u
JOIN orders o ON u.id = o.user\_id
WHERE o.total > 100
ORDER BY o.order\_date DESC

Inserting with Returning Values:

INSERT INTO users (name, email, created\_at)
VALUES ('John Doe', '[email protected]', NOW())
RETURNING id, name, created\_at

Updating Multiple Records:

UPDATE products
SET in\_stock = false
WHERE quantity = 0 AND category = 'electronics'

Using Transactions:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

 

Step 10: Handling PostgreSQL Query Results

 

After configuring your PostgreSQL node, you need to understand how to handle the results:

• The PostgreSQL node outputs the query results as an array of objects
• Each object represents a row from the result set
• Column names become property names in the output objects

To process these results, you can add subsequent nodes such as:

• "Function" node to transform the data
• "IF" node to create conditional logic based on database results
• "Set" node to set variables based on database values
• "HTTP Request" node to send the data to another system

 

Step 11: Error Handling

 

To handle potential errors in your PostgreSQL connection or queries:

• Add an "Error Trigger" node to your workflow
• Connect it to nodes that should execute when the PostgreSQL node encounters an error
• Use a "Function" node to log the error details:

// Example function to log PostgreSQL errors
return [
  {
    error: true,
    errorType: $input.all()[0].error.type,
    errorMessage: $input.all()[0].error.message,
    timestamp: new Date().toISOString()
  }
];

 

Step 12: Testing the PostgreSQL Connection

 

Before fully integrating your PostgreSQL node into a complex workflow, it's good practice to test the connection:

• Create a simple workflow with just a "Manual Trigger" node connected to your PostgreSQL node
• Configure the PostgreSQL node with a simple query like:

SELECT 1 as connection\_test

• Execute the workflow by clicking the "Execute Workflow" button
• Check the output of the PostgreSQL node to verify the connection is working

 

Step 13: Optimizing PostgreSQL Performance

 

To optimize the performance of your PostgreSQL connection in n8n:

• Limit the amount of data returned by using WHERE clauses and LIMIT statements
• Use indexes on columns you frequently query
• Consider using connection pooling by setting appropriate Max Connection Pool Size
• For large result sets, consider using pagination to process data in chunks

Example of paginated query:

SELECT \* FROM large\_table
ORDER BY id
LIMIT 1000 OFFSET {{$json["offset"] || 0}}

Then process this in batches using a loop.

 

Step 14: Securing Your PostgreSQL Connection

 

Ensure your PostgreSQL connection is secure:

• Use SSL for connections to remote PostgreSQL servers
• Create a dedicated database user for n8n with minimal required permissions
• Consider using environment variables for sensitive connection information
• Regularly rotate database credentials
• Ensure your PostgreSQL server has proper firewall rules in place

 

Step 15: Troubleshooting Common Issues

 

If you encounter issues connecting n8n to PostgreSQL, try these troubleshooting steps:

Connection Refused:

• Verify the PostgreSQL server is running
• Check the host and port settings
• Ensure firewall rules allow the connection
• Verify PostgreSQL is configured to accept remote connections in pg\_hba.conf

Authentication Failed:

• Double-check username and password
• Verify the user has access to the specified database

Database Does Not Exist:

• Confirm the database name is correct
• Create the database if it doesn't exist

SSL Connection Issues:

• Toggle the SSL setting in the credentials
• Verify SSL is properly configured on the PostgreSQL server

 

Step 16: Real-World Workflow Example

 

Here's an example of a complete workflow that uses PostgreSQL:

1. HTTP Trigger Node: Receives webhook data from a form submission
2. Function Node: Validates and formats the incoming data
3. PostgreSQL Node: Inserts the validated data into a "customers" table
4. IF Node: Checks if the insertion was successful
5. Email Node (Success Path): Sends a confirmation email
6. Email Node (Failure Path): Sends a notification about the failure

PostgreSQL node configuration for this example:

• Operation: Insert
• Table: customers
• Columns:
  • name: {{$node["Function"].json["name"]}}
  • email: {{$node["Function"].json["email"]}}
  • phone: {{$node["Function"].json["phone"]}}
  • signup\_date: NOW()

 

Step 17: Working with PostgreSQL-Specific Data Types

 

PostgreSQL supports several specific data types that you might need to handle in n8n:

JSON and JSONB:

-- Inserting JSON data
INSERT INTO config (settings) 
VALUES ('{"theme":"dark","notifications":true}')

-- Querying JSON data
SELECT id, settings->>'theme' as theme 
FROM config 
WHERE settings->>'notifications' = 'true'

Arrays:

-- Working with array data
SELECT \* FROM products 
WHERE 'red' = ANY(available\_colors)

-- Expanding arrays
SELECT unnest(available\_colors) as color 
FROM products

Time and Date Functions:

-- Using PostgreSQL date functions
SELECT id, created\_at, 
       AGE(NOW(), created_at) as account_age
FROM users
ORDER BY created\_at

 

Step 18: Setting Up Database Monitoring with n8n

 

You can use n8n with PostgreSQL to create database monitoring workflows:

1. Schedule Trigger Node: Set to run at regular intervals (e.g., every hour)
2. PostgreSQL Node: Run diagnostic queries

Example monitoring queries:

-- Check for long-running queries
SELECT pid, now() - query\_start as duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query\_start > interval '5 minutes'

-- Check database size
SELECT pg_size_pretty(pg_database_size(current_database())) as db_size

-- Check table sizes
SELECT relname as table\_name,
       pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation\_size(relid) DESC

3. IF Node: Check if any issues were detected
4. Slack/Email Node: Send alerts if needed

 

Step 19: Creating a Workflow for Database Backups

 

You can use n8n to automate PostgreSQL backups:

1. Schedule Trigger Node: Configure to run at your desired backup frequency
2. Execute Command Node: Run pg\_dump to create a backup

// Example command for Execute Command node
pg_dump -h {{$node["Credentials"].json["host"]}} -p {{$node["Credentials"].json["port"]}} -U {{$node["Credentials"].json["user"]}} -F c -b -v -f "/backups/backup_{{$today}}.dump" {{$node["Credentials"].json["database"]}}

3. IF Node: Check if the backup was successful
4. S3/Google Drive/Dropbox Node: Upload the backup file to cloud storage
5. Email Node: Send notification about backup status

 

Step 20: Finalizing and Managing Your PostgreSQL Integration

 

After setting up your PostgreSQL connection in n8n, remember these best practices:

• Document your database schema and the queries used in your workflows
• Regularly test your workflows to ensure they continue working with database changes
• Consider implementing version control for your database schemas and n8n workflows
• Monitor the performance of your database queries and optimize as needed
• Set up alerts for workflow failures, especially for critical database operations
• Periodically review database user permissions to maintain security

Remember that complex database operations might be better handled with stored procedures in PostgreSQL rather than complex queries in n8n. You can call stored procedures from n8n using the "Execute Query" operation.

With these steps, you should have a robust and functional connection between n8n and your PostgreSQL database, enabling powerful automation workflows that leverage your database data.