Database Action (Database Query Executor)

The Database Action executes SQL queries on databases and stores the results for further automation steps.

What does this integration do?

The Database Action establishes a connection to a database and executes configurable SQL statements. Query results are stored in workflow variables and can be used in subsequent steps. Both SELECT queries and INSERT, UPDATE, and DELETE operations are supported.

Typical Use Cases:

Data Querying: Retrieving information for reports and analyses
Data Updates: Updating records based on workflow results
Data Insertion: Storing processed data in the database
Data Validation: Checking data integrity and consistency
Reporting: Generation of database-based reports
ETL Processes: Extract, Transform, Load operations

User Configuration

Database Connection

Client (Database Client Type)

Purpose: Type of database connection
Supported Types: SQLite, MySQL, PostgreSQL, other LibSQL-compatible databases
Examples: libsql, mysql, postgresql, sqlite

Host (Database Host)

Purpose: Server address of the database
Format: IP address, domain, or localhost
Examples: localhost, db.company.com, 192.168.1.100, production-db.internal

User (Database User)

Purpose: Username for database connection
Permissions: Depends on SQL statement (SELECT, INSERT, UPDATE, DELETE)
Examples: workflow_user, analytics_reader, data_processor

Password (Database Password)

Purpose: Password for database connection
Security: Stored encrypted
Type: Secured Field

Database (Database Name)

Purpose: Name of the database to use
Examples: production, analytics, crm_data, workflow_db

SQL Execution

SQL Statement (SQL Statement)

Purpose: SQL command to be executed
Supported: SELECT, INSERT, UPDATE, DELETE, and other SQL commands
Variable Interpolation: Yes, with {{variableName}} syntax

Beispiele:

SELECT * FROM orders WHERE status = 'pending';
INSERT INTO customers (name, email) VALUES ('{{customerName}}', '{{customerEmail}}');
UPDATE orders SET status = 'processed' WHERE id = {{orderId}};

Output Configuration

Result (Rows) - Variable for Query Result (Optional)

Purpose: Variable that stores query results
Content: Array of rows for SELECT, execution info for other statements
Example Variable: queryResults

Result Error - Variable for Errors (Optional)

Purpose: Variable that stores error messages for SQL errors
Usage: For robust error handling in workflows
Example Variable: sqlError

How it Works

Connection Management

Database Client:

Verwendet LibSQL Client für breite Datenbankkompatibilität
Automatische Verbindungsherstellung mit konfigurierten Credentials
Sichere Verbindung mit verschlüsselten Passwörtern

Statement-Ausführung:

Unterstützung für alle gängigen SQL-Befehle
Variable-Substitution aus Workflow-Memory
Robuste Fehlerbehandlung und Logging

Data Processing

Result-Handling:

SELECT-Abfragen: Array von Zeilen-Objekten
INSERT/UPDATE/DELETE: Execution-Informationen (affected rows, etc.)
Automatische JSON-Serialisierung für komplexe Datentypen

Workflow Integration

SQL Statements with Variables

SELECT with Variables:

SELECT
  o.id,
  o.total_amount,
  c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_date >= '{{startDate}}'
  AND o.status = '{{orderStatus}}';

INSERT with Workflow Data:

INSERT INTO reports (
  name,
  content,
  created_by,
  created_at
) VALUES (
  '{{reportName}}',
  '{{reportContent}}',
  '{{currentUser}}',
  NOW()
);

UPDATE based on Workflow:

UPDATE customers
SET
  last_contact = NOW(),
  status = '{{newStatus}}',
  notes = '{{contactNotes}}'
WHERE email = '{{customerEmail}}';

Query Results

SELECT Result:

[
  {
    "id": 12345,
    "customer_email": "max@example.com",
    "total_amount": 99.99,
    "order_date": "2024-03-15T10:30:00Z"
  },
  {
    "id": 12346,
    "customer_email": "anna@example.com",
    "total_amount": 149.5,
    "order_date": "2024-03-15T11:15:00Z"
  }
]

INSERT/UPDATE Result:

{
  "lastInsertRowid": 12347,
  "changes": 1,
  "totalChanges": 1
}

Usage in Subsequent Steps

Processing Query Results:

Variable: queryResults

In LLM Action:
"Analyze the following sales data: {{queryResults}}"

In Script Action:
const orders = memory.load('queryResults');
const totalRevenue = orders.reduce((sum, order) => sum + order.total_amount, 0);

Practical Examples

Customer Analysis

Konfiguration:

Client: mysql
Host: analytics-db.company.com
User: analytics_reader
Database: crm

SQL Statement:

SELECT
  c.id,
  c.name,
  c.email,
  COUNT(o.id) as order_count,
  SUM(o.total) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_date >= '{{analysisStartDate}}'
GROUP BY c.id
ORDER BY lifetime_value DESC
LIMIT 50;

Result: topCustomers

Usage: Generation of customer segmentation, VIP customer lists, marketing target groups

Order Status Update

Konfiguration:

Client: postgresql
Host: order-system.internal
User: workflow_updater
Database: ecommerce

SQL Statement:

UPDATE orders
SET
  status = '{{newStatus}}',
  tracking_number = '{{trackingNumber}}',
  updated_at = NOW()
WHERE id = {{orderId}};

Result: updateResult

Usage: Automatic order status changes, tracking updates, workflow synchronization

Inventory Check

Konfiguration:

Database: warehouse

SQL Statement:

SELECT
  p.sku,
  p.name,
  i.quantity_available,
  i.quantity_reserved,
  i.reorder_level
FROM products p
JOIN inventory i ON p.id = i.product_id
WHERE i.quantity_available < i.reorder_level;

Result: lowStockItems

Usage: Automatic reorders, stock warnings, inventory management

Collecting Report Data

Konfiguration:

SQL Statement:

SELECT
  DATE(created_at) as date,
  COUNT(*) as order_count,
  SUM(total_amount) as daily_revenue,
  AVG(total_amount) as avg_order_value
FROM orders
WHERE created_at >= '{{reportStartDate}}'
  AND created_at < '{{reportEndDate}}'
GROUP BY DATE(created_at)
ORDER BY date;

Result: dailyStats

Usage: Daily reports, KPI dashboards, business intelligence

Technical Details

Schema Configuration

configSchema: {
  client: {
    name: 'Client',
    description: 'Database client type',
    schema: z.string(),
  },
  host: {
    name: 'Host',
    description: 'Database host',
    schema: z.string()
  },
  user: {
    name: 'User',
    description: 'Database user',
    schema: z.string()
  },
  password: {
    name: 'Password',
    description: 'Database password',
    reference: 'secured',
    schema: z.string(),
  },
  database: {
    name: 'Database',
    description: 'Database name',
    schema: z.string(),
  },
  statement: {
    name: 'SQL Statement',
    description: 'Statement to execute',
    parser: true,
    schema: z.string(),
  },
  out_result: {
    name: 'Result (Rows)',
    reference: 'memory-out',
    schema: z.string().optional(),
  },
  out_error: {
    name: 'Result Error',
    reference: 'memory-out',
    schema: z.string().optional(),
  },
}

Internal Implementation

Database Connectivity:

Verwendet LibSQL Client für universelle Datenbankkompatibilität
Automatische Connection-String-Generierung
Sichere Credential-Verarbeitung

Statement-Processing:

Variable-Interpolation mit Template-Engine
SQL-Injection-Schutz durch Prepared Statements
Robuste Fehlerbehandlung mit detailliertem Logging

Result-Processing:

Automatische Serialisierung von Abfrageergebnissen
Strukturierte Fehlerbehandlung
Memory-Integration für Workflow-Kontinuität

Best Practices

Security

SQL Injection Protection:

Use parameterized queries where possible
Validate all input variables
Avoid direct string concatenation in SQL

Permissions:

Use minimally necessary database permissions
Create dedicated workflow users
Implement regular credential rotation

Performance

Query-Optimierung:

Verwenden Sie Indizes für häufig abgefragte Spalten
Begrenzen Sie Ergebnismengen mit LIMIT-Klauseln
Optimieren Sie JOINs und WHERE-Bedingungen

Verbindungsmanagement:

Implementieren Sie Connection-Pooling für häufige Abfragen
Verwenden Sie Read-Replicas für analytische Abfragen
Überwachen Sie Verbindungsanzahl und -stabilität

Robustheit

Fehlerbehandlung:

Implementieren Sie umfassende Fehlerbehandlung
Verwenden Sie Retry-Logic für transiente Fehler
Loggen Sie SQL-Fehler für Debugging

Workflow-Integration:

Validieren Sie Abfrageergebnisse in nachfolgenden Schritten
Implementieren Sie Fallback-Strategien für leere Ergebnisse
Berücksichtigen Sie verschiedene Datentypen in Ergebnissen

The Database Action enables seamless integration of databases into Vectense Platform workflows and provides robust, secure database operations for automation processes.

What does this integration do?​

User Configuration​

Database Connection​

SQL Execution​

Output Configuration​

How it Works​

Connection Management​

Data Processing​

Workflow Integration​

SQL Statements with Variables​

Query Results​

Usage in Subsequent Steps​

Practical Examples​

Customer Analysis​

Order Status Update​

Inventory Check​

Collecting Report Data​

Technical Details​

Schema Configuration​

Internal Implementation​

Best Practices​

Security​

Performance​

Robustheit​

What does this integration do?

User Configuration

Database Connection

SQL Execution

Output Configuration

How it Works

Connection Management

Data Processing

Workflow Integration

SQL Statements with Variables

Query Results

Usage in Subsequent Steps

Practical Examples

Customer Analysis

Order Status Update

Inventory Check

Collecting Report Data

Technical Details

Schema Configuration

Internal Implementation

Best Practices

Security

Performance

Robustheit