Skip to content
Nappai Documentation

Postgres SQL

This component allows you to interact with PostgreSQL databases in Nappai. You can use it to fetch data, insert new records, update existing information, or execute SQL commands. It simplifies database management by providing a user-friendly interface for common tasks, ensuring your data operations are secure and efficient.

How it Works

The component connects to your PostgreSQL server using the credentials you configure. Once connected, it executes the SQL commands or operations you define. You can filter data, limit the number of results, or update specific fields. The component handles the communication with the database securely and returns the results, which you can then use in subsequent steps of your workflow.

Connection & Credentials

This component requires configuring a credential in the Nappai panel before interacting with the external service:

  1. Go to the Credentials section in your Nappai panel.
  2. Create a new credential of the type Postgres SQL and fill in the required fields (such as Host, Port, Database, User, and Password).
  3. In your workflow, select the saved credential in the Credential input field of this node.

Inputs

The following fields are available to configure this component:

  • Number of Results: Determines the maximum number of results the component will return. Useful for performance and limiting data size.
  • Operation: Select the PostgreSQL operation to perform, such as retrieving data, inserting records, or updating information.
  • Return All: Whether to return all results or only up to a given limit.
  • Table Name: Select the table to operate on.
  • Schema: Select Schema to operate on.
  • Custom Query: Write your SQL query here. Examples include SELECT, UPDATE, INSERT, and DELETE. You can write multiple lines for complex queries.
  • Select Filter Type: Choose how to build your search conditions. String Mode is recommended for simple filters using natural SQL-like style, while Build Manually Mode offers a visual form-based builder for complex conditions.
  • Filter String: Define search conditions. Use single quotes for text, no quotes for numbers, and lowercase for booleans. Operators include =, !=, >, <, >=, <=, and connectors AND / OR. Example: city = 'Barcelona' AND age > 30.
  • Limit: Number of results to return.
  • Filters: Number of filters to decide which rows are retrieved.
  • Field to Update: Number of fields to update.
  • Mode: Select how to insert data. Dynamic uses table columns, while Custom uses ingest data.
  • Ingest Data: Insert data into the database table.

Outputs

The component produces the following outputs:

  • Data: Contains the records or results returned by the query. This can be mapped to other components for further processing.
  • Database Info: Provides information about the database connection and status.

Output Data Example (JSON)json

{ “Data”: [ { “id”: 1, “name”: “John Doe”, “email”: “john@example.com”, “age”: 30 }, { “id”: 2, “name”: “Jane Smith”, “email”: “jane@example.com”, “age”: 28 } ] }

Connectivity

Typically, this component connects to a Database source and its output connects to Data Processing, Transformation, or API components. The output provides data that can be used to update records, generate reports, or trigger further actions in your workflow. The Database Info output can be used to verify connection status or log database details.

Usage Example

  1. Connect: Select your Postgres SQL credential in the Credential field.
  2. Retrieve Data: Set Operation to retrieve data, select a Table Name, and use Filter String to specify conditions, e.g., status = 'active'.
  3. Process Results: Connect the Data output to a component that sends an email notification or updates another system with the retrieved records.

Tips and Best Practices

  • Use Filter String to narrow down results and improve performance.
  • When inserting large amounts of data, consider using Custom Query for optimized batch operations.
  • Always review the Limit setting to avoid retrieving excessive amounts of data.
  • Use the Mode field to easily insert data using dynamic table columns or custom ingest data formats.
  • Ensure your credential has the necessary permissions for the operation you perform.

Security Considerations

  • Store database credentials securely using the Credential input. Never hardcode passwords in your workflow.
  • The component supports parameterized queries to help prevent SQL injection. Use filters or custom queries with proper parameters.
  • Regularly audit database access permissions to ensure only authorized workflows can modify or access sensitive data.