logo_smallAxellero.io

PostgreSQL

PostgreSQL database integration with named parameter binding and schema discovery.

Execute SQL queries in PostgreSQL databases with Axellero's workflow integration features.

Connector Configuration

ParameterTypeRequiredDescription
hostTEXTYesDatabase host address
portINTNoDatabase port (default: 5432)
userTEXTYesDatabase username
passwordTEXTYesDatabase password
schemaTEXTNoDatabase schema (default: public)
dbTEXTYesDatabase name

SQL Query Execution

Named Parameter Binding

PostgreSQL nodes use @parameter_name syntax for safe parameter binding:

SELECT * FROM users 
WHERE status = @user_status 
  AND created_at > @start_date
ORDER BY created_at DESC;

Variables:

{
  "user_status": "active",
  "start_date": "2024-01-01T00:00:00Z"
}

Workflow Context Integration

Access data from other workflow nodes in your queries:

-- Use outputs from other nodes
SELECT * FROM orders 
WHERE customer_id = @selected_customer
  AND total > @minimum_amount;

Variables:

{
  "selected_customer": "{{ctx.nodes.userInput.outputs.customerId}}",
  "minimum_amount": "{{ctx.nodes.config.outputs.orderThreshold}}"
}

Editor Features

SQL Editor Capabilities:

  • Auto-completion: Table and column names from your PostgreSQL schema
  • Parameter Completion: Auto-complete for @parameter_name from workflow variables
  • Schema Discovery: Automatic detection of tables, columns, and data types
  • Syntax Validation: Real-time SQL syntax checking

For complete editor details, see Code Editing & Schema Reference.

Schema Discovery

Axellero automatically discovers your PostgreSQL database structure:

  • Tables: All tables in the specified schema
  • Columns: Column names, data types, and constraints
  • Auto-completion: Intelligent suggestions based on your actual database

Connection Features

Read-Only Mode

PostgreSQL connections use read-only transactions by default for data safety.

Connection Testing

Test database connectivity and credentials before executing queries.

Result Processing

Query Results

{
  "rows": [
    {
      "id": 1,
      "name": "John Doe",
      "created_at": "2024-01-15T10:30:00Z"
    }
  ],
  "rowsAffected": 1
}

Data Type Mapping

  • Integers: PostgreSQL numbers → JavaScript numbers
  • Text/VARCHAR: PostgreSQL strings → JavaScript strings
  • Timestamps: PostgreSQL dates → ISO string format
  • JSON/JSONB: PostgreSQL JSON → JavaScript objects
  • Arrays: PostgreSQL arrays → JavaScript arrays

Workflow Integration Patterns

Using Query Results in Other Nodes

// Access PostgreSQL results in subsequent JavaScript nodes
const users = ctx.nodes.postgresQuery.outputs.rows;
const activeUsers = users.filter(user => user.status === 'active');

callback({ processedUsers: activeUsers }, null);

Conditional Logic with Database Results

// Use in Branch node conditions
{{ctx.nodes.postgresQuery.outputs.rows && ctx.nodes.postgresQuery.outputs.rows != ""}}

// Check for errors
{{!ctx.nodes.postgresQuery.error}}

// Check if query succeeded
{{ctx.nodes.postgresQuery.outputs.rowsAffected > 0}}

Loop Processing Database Results

// Loop over query results
// Loop Value: {{ctx.nodes.postgresQuery.outputs.rows}}

// Access current row in loop body
{{ctx.nodes.loopNode.outputs.item.user_id}}
{{ctx.nodes.loopNode.outputs.item.email}}

Error Handling

Connection Errors

  • Connection refused: Check host, port, and network connectivity
  • Authentication failed: Verify username and password
  • Database not found: Confirm database name exists
  • Schema access: Ensure user has permissions for specified schema

Query Errors

  • Syntax errors: Use editor validation to check SQL syntax
  • Missing parameters: Ensure all @parameter_name values are provided
  • Permission denied: Verify user has SELECT permissions on tables

Configuration Examples

Basic Connection

{
  "host": "localhost",
  "port": 5432,
  "user": "app_user",
  "password": "secure_password",
  "db": "production_db",
  "schema": "public"
}

Environment Variables

{
  "host": "{{ctx.consts.DB_HOST}}",
  "user": "{{ctx.vars.db_username}}",
  "password": "{{ctx.vars.db_password}}",
  "db": "{{ctx.consts.DB_NAME}}"
}

Sample Query with Parameters

{
  "query": "SELECT id, name, email FROM users WHERE department = @dept AND active = @status ORDER BY name",
  "variables": {
    "dept": "{{ctx.nodes.input.outputs.selectedDepartment}}",
    "status": true
  }
}

Methods Reference

MethodDescription
executeQueryExecute SQL query with named parameters
testConnectionValidate database connection
fetchSchemaDiscover database schema information

Code Editing & Schema Reference

SQL editor features and auto-completion guide

Workflow Context

Understanding context expressions and variable access

Oracle Node

Oracle database integration with positional parameters