DB Query Webhooks

Introduction

DB Query webhooks enable you to execute raw database queries directly within your Canonic workflows. Supported databases include MongoDB, Microsoft SQL, Redis, MySQL, and PostgreSQL.

Supported Databases

• MongoDB: NoSQL database for modern applications.
• MySQL: Popular open-source relational database.
• PostgreSQL: Advanced open-source relational database.
• Microsoft SQL Server (MS SQL): Comprehensive database server and data platform.
• Redis: In-memory data structure store, used as a database, cache, and message broker.

Creating a DB Query Webhook

1. Click the + button to add a new Webhook to your workflow.
2. Choose the DB Query webhook type and fill in the necessary configuration details in the properties panel.
3. Enter your query in the provided field.

Authentication and Query Details

• Connection String: Enter the full connection string to your database (for databases like MongoDB/Redis/Postgres).
• Host/User/Password: Enter the host, user, and password details required for the connection (for databases like MySQL/MS SQL).
• Query: Input the raw query you wish to run against your database.

Configuration

You can configure your DB Query webhook with the following settings:

• Name: Enter a name for your DB Query webhook.
• Key: A unique identifier for the webhook, automatically generated based on the Name.
• API Integration: Select the database type from the supported list (MongoDB, MS SQL, etc.).

• Authentication: Provide the necessary authentication details, which may vary based on the database:

  • For databases like MongoDB, Redis, Postgres a connection string is often used.
  • For others like MySQL or MS SQL, explicit authentication details such as host, user, and password are required.

Testing the DB Query webhook

Before finalizing your workflow, use the Test feature to ensure that the DB Query webhook is configured correctly and that the response from the DB Query is as expected.

Example

Assuming you have a workflow that needs to fetch user details from a MySQL database:

• You add a DB Query Webhook named "Fetch User Details".
• In the webhook configuration, you select MySQL as the database type.
• You provide the necessary connection details, such as the host, user, and password, to establish a connection with your MySQL database.
• In the query field, you input the SQL command:

  SELECT * FROM users WHERE id = input.userId;

This query is designed to retrieve all columns from the users table where the id matches the userId provided in the workflow's input parameters.

After setting up the webhook, you test it to ensure it retrieves the correct user details and handles errors such as user not found or connection issues.

By using the DB Query webhook, you can seamlessly integrate complex database operations within your Canonic workflows, enhancing their capability to interact with various databases.

Best Practices

• Ensure your connection details are accurate and secure.
• Test your queries to verify they execute as expected before incorporating them into your workflow.

Note: Always review your database's documentation for the correct format and security practices when executing raw queries.