Skip to content
Nappai Documentation

SQL Server

This component helps you connect your Nappai automation to a Microsoft SQL Server database. It acts as a secure bridge, allowing Nappai to read, write, and manage data in your database automatically. You can use it to fetch records, update information, or perform database tasks within your workflows.

How it Works

Think of this component as a gateway to your SQL Server database. When you add this component to your workflow, Nappai uses the credentials you saved to open a secure line to your database. This connection allows Nappai to fetch records, run updates, or manage your database structure automatically. The component handles the technical details of establishing the link, checking permissions, and returning the results to the next steps in your automation.

Connection & Credentials

This component requires a SQL Server credential to connect. You must configure this credential before using the component in your workflows.

  1. Go to the Credentials section in your Nappai panel.
  2. Create a new credential of the SQL Server type and fill in the required fields:
    • Connection Method: Choose how you want to connect.
    • SQL Server Connection URL: The full address to your database (e.g., mssql+pymssql://user:pass@host:port/db).
    • User: Your database username.
    • Password: Your database password.
    • Host: The server address.
    • Port: The port number.
    • Database: The name of the database.
    • Trust Server Certificate: Set to “Yes” only if necessary for development or self-signed certificates.
  3. Return to your workflow editor and select the saved credential in the Credential input field of this component.

Important: Never enter passwords directly into component fields. Always use the Credential input to select your saved credentials for security.

Inputs

The following fields are available to configure this component. You can use these inputs to define how the component interacts with your database.

  • sqlserver_driver: The underlying engine used to communicate with SQL Server. Usually left default unless using legacy systems.
  • Number of Results: Sets a limit on how many records are returned.
  • Operation: Select the type of action you want the component to perform (e.g., Read, Write, Manage).
  • Return All: If enabled, returns all matching records; otherwise, respects the result limit.
  • Use Filters: Turns on options to filter records based on specific criteria.
  • Table Name: The name of the database table you want to work with.
  • Schema: The database container that groups your tables.
  • Custom Query: Write your own SQL commands here for advanced control. You can use this for complex SELECT, UPDATE, INSERT, or DELETE operations.
  • Selection Mode: Choose how to select records, such as by Primary Key or by Filters.
  • Primary Key Value: The unique ID of a specific record to update or delete.
  • Select Filter Type: Choose between Simple Text mode (recommended) or Manual Builder mode for creating filters.
  • Filter String: Type search conditions here, such as age > 25 or name = 'Anna'. If left empty, all records are returned.
  • Limit: The maximum number of rows to retrieve.
  • Number of Filters to use: Specifies how many filter conditions to apply when retrieving data.
  • Detail Level: Choose to return simple table names or full column details for a specific table.
  • Table: Select a table to view its detailed column information.

Outputs

This component produces data that can be used by subsequent nodes in your workflow.

  • Data: The actual records or results returned by the database operation. You can use this output to populate tables, send reports, or trigger further actions.
  • Database Info: Metadata or structure details about the database or table, such as table names and column definitions.

Output Data Example (JSON)json

{ “Data”: [ { “id”: 1, “name”: “Alice”, “status”: “Active” }, { “id”: 2, “name”: “Bob”, “status”: “Pending” } ], “Database Info”: { “tables”: [“users”, “orders”], “connection_status”: “ok” } }

Connectivity

This component is typically used at the beginning of a data workflow to establish a connection to your SQL Server database. Its outputs, such as Data and Database Info, are designed to be connected to downstream components like data transformers, reporting tools, or notification services.

Usage Example

Scenario: Fetching Customer Data for a Report

  1. Setup Credential: Create a SQL Server credential in Nappai with your database details.
  2. Add Component: Drag the SQL Server component into your workflow.
  3. Connect: Select your SQL Server credential in the Credential field.
  4. Configure:
    • Set Table Name to Customers.
    • Set Operation to Read.
    • Enable Use Filters and set Filter String to status = 'Active'.
  5. Connect Output: Link the Data output to a component that displays a table or sends an email report.

Tips and Best Practices

  • Always use the Credential input for authentication to keep passwords secure.
  • Test your connection early in the workflow to ensure your database details are correct.
  • Use Filter String to retrieve only the data you need, which improves performance.
  • When working with large datasets, use Limit or Number of Results to avoid performance issues.
  • For complex database tasks, consider using the Custom Query input for full control.

Security Considerations

  • Credential Safety: Never share credentials. Ensure your Nappai panel is secured.
  • Input Validation: Be cautious when using Custom Query inputs. Ensure that any user-provided data is sanitized to prevent security risks.
  • Trust Certificates: Only set Trust Server Certificate to “Yes” if necessary. In production environments, use valid certificates.
  • Least Privilege: Configure your SQL Server credentials with the minimum permissions required for your automation tasks.