MySQL Query Task¶

Overview¶

The MySQL Query Task executes SQL queries against your MySQL database. Use it to read data, create records, update information, or perform complex database operations directly from your workflows.

When to use this task:

• Fetch data from custom database tables
• Create records in external systems
• Update database fields programmatically
• Generate reports from database queries
• Sync data between BaseCloud and other systems
• Perform complex joins and aggregations
• Execute stored procedures

Key Features:

• Full SQL support (SELECT, INSERT, UPDATE, DELETE)
• Parameterized queries for security
• Multiple database connection support
• Result parsing and output fields
• Transaction support
• Prepared statements
• Error handling and rollback

[SCREENSHOT NEEDED: MySQL Query task configuration showing connection and query fields]

Quick Start¶

1. Add MySQL Query task to workflow
2. Select database connection
3. Write SQL query
4. Add parameters (if using variables)
5. Test query
6. Save

Simple Example:

SELECT * FROM customers WHERE email = ?

Database Connection¶

Setting Up Connection¶

Configure database credentials in BaseCloud Admin:

1. Go to Admin → Database Connections
2. Add New Connection
3. Enter details:
4. Host: localhost or db.example.com
5. Port: 3306 (default MySQL)
6. Database: your_database_name
7. Username: db_user
8. Password: secure_password
9. Test Connection
10. Save

Connection in Task¶

Database Connection: Production Database

Select from your configured connections.

Multiple Databases¶

You can query different databases in same workflow:

Task 1: MySQL Query → Database: CRM Database
Task 2: MySQL Query → Database: Accounting Database
Task 3: MySQL Query → Database: Analytics Database

Query Types¶

SELECT - Read Data¶

Fetch records from database.

Single record:

SELECT * FROM customers 
WHERE email = ?
LIMIT 1

Multiple records:

SELECT * FROM orders 
WHERE customer_id = ?
ORDER BY created_at DESC

With JOIN:

SELECT c.name, c.email, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.id = ?

Aggregation:

SELECT 
  COUNT(*) as total_orders,
  SUM(total) as total_revenue,
  AVG(total) as avg_order_value
FROM orders
WHERE customer_id = ?

INSERT - Create Records¶

Add new records to database.

Single insert:

INSERT INTO leads (
  email, 
  first_name, 
  last_name, 
  phone, 
  source, 
  created_at
) VALUES (?, ?, ?, ?, ?, NOW())

{{task_15001_email}},
{{task_15001_first_name}},
{{task_15001_last_name}},
{{task_15001_phone}},
Website

Insert with return ID:

INSERT INTO contacts (email, name) 
VALUES (?, ?)

UPDATE - Modify Records¶

Update existing records.

Simple update:

UPDATE customers 
SET phone = ?, updated_at = NOW()
WHERE email = ?

Conditional update:

UPDATE orders 
SET status = 'processed', processed_at = NOW()
WHERE order_id = ? AND status = 'pending'

Increment values:

UPDATE customers 
SET total_purchases = total_purchases + 1,
    lifetime_value = lifetime_value + ?
WHERE id = ?

DELETE - Remove Records¶

Delete records from database.

With condition:

DELETE FROM temporary_data 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)

Specific record:

DELETE FROM unsubscribes 
WHERE email = ?

Note: Use DELETE carefully, consider soft deletes instead.

Parameterized Queries¶

Why Use Parameters?¶

Security: Prevents SQL injection attacks Safety: Handles special characters automatically Performance: Query optimization by database

❌ NEVER do this (SQL Injection risk):

SELECT * FROM users WHERE email = '{{task_15001_email}}'

✅ ALWAYS do this:

SELECT * FROM users WHERE email = ?

Parameter Syntax¶

Use ? as placeholder in query, then provide values in parameters field.

Single parameter:

SELECT * FROM contacts WHERE id = ?

Multiple parameters:

INSERT INTO logs (user_id, action, timestamp) 
VALUES (?, ?, ?)

Order matters: First ? = first parameter, second ? = second parameter, etc.

Parameter Types¶

BaseCloud automatically handles type conversion:

Strings:

Parameters: {{task_15001_email}}
Becomes: 'john@example.com'

Numbers:

Parameters: {{task_15001_contact_id}}
Becomes: 12345

Dates:

Parameters: {{task_48001_current_date}}
Becomes: '2024-02-08'

NULL values:

Parameters: {{task_15001_phone}}
If empty: NULL

Output Fields¶

SELECT Queries¶

Access first row:

{{task_43001_row_0_email}}
{{task_43001_row_0_name}}
{{task_43001_row_0_total}}

Access all rows (use with Loop task):

{{task_43001_results_JSON}}

Check if results exist:

{{task_43001_rows}} > 0

INSERT Queries¶

UPDATE/DELETE Queries¶

Error Handling¶

Real-World Examples¶

Example 1: Create Lead from Form¶

Workflow: 1. Website Form 2. MySQL Query - Insert lead

Query:

INSERT INTO marketing_leads (
  email,
  first_name,
  last_name,
  phone,
  company,
  message,
  source,
  utm_source,
  utm_campaign,
  created_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())

Parameters:

{{task_55001_email}},
{{task_55001_first_name}},
{{task_55001_last_name}},
{{task_55001_phone}},
{{task_55001_company}},
{{task_55001_message}},
Website Form,
{{task_55001_utm_source}},
{{task_55001_utm_campaign}}

Next steps: 3. Match to Client - Find existing contact in CRM too 4. Email - Send confirmation

Example 2: Daily Sales Report¶

Workflow: 1. Schedule Trigger - Every day at 8 AM 2. MySQL Query - Get yesterday's sales

Query:

SELECT 
  COUNT(DISTINCT order_id) as total_orders,
  COUNT(DISTINCT customer_id) as unique_customers,
  SUM(total) as total_revenue,
  AVG(total) as avg_order_value,
  MAX(total) as largest_order
FROM orders
WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND status = 'completed'

No parameters needed (static query)

Next steps: 3. Email - Send report

Total Orders: {{task_43001_row_0_total_orders}}
Revenue: R{{task_43001_row_0_total_revenue}}
Average: R{{task_43001_row_0_avg_order_value}}

Example 3: Update Customer Lifetime Value¶

Workflow: 1. Webhook In - Order completed 2. MySQL Query - Calculate total spent 3. Edit Client - Update CRM

Query:

SELECT 
  customer_id,
  COUNT(*) as total_orders,
  SUM(total) as lifetime_value,
  MAX(created_at) as last_order_date
FROM orders
WHERE customer_email = ?
  AND status = 'completed'
GROUP BY customer_id

Parameters: {{task_46001_customer_email}}

Next steps: 3. Edit Client: Update custom_lifetime_value with {{task_43001_row_0_lifetime_value}}

Example 4: Sync External System¶

Workflow: 1. Schedule Trigger - Every hour 2. MySQL Query - Get updated records 3. Loop - Process each record 4. Webhook Out - Sync to external API

Query:

SELECT 
  id,
  email,
  first_name,
  last_name,
  phone,
  last_updated
FROM customers
WHERE last_updated > ?
  AND synced = 0
ORDER BY last_updated ASC
LIMIT 100

Parameters: {{task_41001_last_sync_time}}

Loop through results:

Loop: {{task_43001_results_JSON}}
For each item:
  - Webhook Out: Sync {{task_29001_email}}
  - MySQL Query: Mark as synced

Example 5: Check Duplicate Before Insert¶

Workflow: 1. Webhook In - New contact 2. MySQL Query - Check if exists 3. If Task - Check result 4. MySQL Query - Insert or update

First Query (Check):

SELECT id, email 
FROM contacts 
WHERE email = ?
LIMIT 1

Condition:

If {{task_43001_rows}} > 0:
  → Update existing record
Else:
  → Insert new record

Update Query:

UPDATE contacts 
SET phone = ?, updated_at = NOW()
WHERE email = ?

Insert Query:

INSERT INTO contacts (email, phone, created_at)
VALUES (?, ?, NOW())

Advanced Techniques¶

Stored Procedures¶

Call stored procedures:

CALL update_customer_stats(?, ?)

Transactions¶

For multiple related operations:

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (?, ?);
UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?;

COMMIT;

Note: Manual transaction control limited. Consider stored procedures for complex transactions.

Subqueries¶

SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'VIP'
)
AND created_at > ?

JSON Columns¶

Query JSON data (MySQL 5.7+):

SELECT 
  id,
  metadata->>'$.source' as source,
  metadata->>'$.campaign' as campaign
FROM leads
WHERE metadata->>'$.source' = ?

Dynamic Table Names¶

Not supported directly for security.

Workaround: Use If task to choose different queries.

Best Practices¶

Security¶

1. Always use parameters - Never concatenate user input
2. Limit permissions - Database user should have minimum required access
3. Read-only when possible - Use SELECT-only accounts for reporting
4. Sanitize output - Don't expose sensitive data in logs
5. Encrypt connections - Use SSL for database connections

Performance¶

1. Use indexes - Index columns used in WHERE clauses
2. Limit results - Add LIMIT clause to large queries
3. **Avoid SELECT *** - Specify needed columns
4. Cache results - Store in Variable task if reused
5. Optimize joins - Use EXPLAIN to analyze query performance

Reliability¶

1. Check for errors - Always verify {{task_x_success}}
2. Handle empty results - Check {{task_x_rows}} > 0
3. Set timeouts - Don't let queries run forever
4. Log operations - Track important database operations
5. Test with production data - Use realistic test datasets

Maintainability¶

1. Comment complex queries - Explain business logic
2. Use consistent naming - Follow SQL naming conventions
3. Version schema - Track database schema changes
4. Document assumptions - Note expected data formats
5. Keep queries simple - Break complex operations into steps

Troubleshooting¶

Query Returns No Results¶

Check: 1. WHERE conditions too restrictive? 2. Data actually exists in database? 3. Parameter values correct? 4. Date format matches database format?

Debug:

View execution history:
- Check parameters sent
- Run query manually in database tool
- Verify {{task_43001_rows}} value

Syntax Error¶

Common causes: 1. Missing comma in column list 2. Unmatched parentheses 3. Reserved keywords not quoted 4. Wrong number of parameters

Error: You have an error in your SQL syntax

Solution: Test query in MySQL Workbench first.

Parameter Count Mismatch¶

Error: Parameter count doesn't match placeholder count

Check: - Number of ? in query - Number of comma-separated parameters - Extra commas in parameters field

Example:

Query: VALUES (?, ?, ?)
Parameters: value1, value2, value3  ✓
Parameters: value1, value2, value3, ✗ (extra comma)

Connection Failed¶

Error: Can't connect to MySQL server

Check: 1. Database server running? 2. Host/port correct? 3. Firewall blocking connection? 4. Database user has access from this IP? 5. Password correct?

Timeout¶

Error: Query timeout after 30 seconds

Causes: - Query too slow (missing indexes) - Database overloaded - Locking issues

Solutions: - Optimize query - Add indexes - Reduce result set size - Increase timeout setting

Insert ID Not Returning¶

Check: - Table has AUTO_INCREMENT primary key? - INSERT actually succeeded? - Correct field name: {{task_43001_insert_id}}

Frequently Asked Questions¶

Can I query multiple tables?¶

Yes, use JOIN:

SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_id = ?

How do I loop through results?¶

Use Loop task:

1. MySQL Query - Get records
2. Loop - Process {{task_43001_results_JSON}}
3. Inside loop: Access {{task_29001_column_name}}

Can I use multiple databases?¶

Yes, configure multiple connections in Admin, then select appropriate connection per query.

How do I handle NULL values?¶

Parameters automatically handle NULL. In SQL:

WHERE column IS NULL
WHERE column IS NOT NULL
WHERE COALESCE(column, 'default') = ?

What's the query timeout?¶

Default: 30 seconds Maximum: 120 seconds (configurable)

Can I execute multiple statements?¶

Limited support. Use stored procedures or separate tasks.

How do I format dates?¶

DATE_FORMAT(created_at, '%Y-%m-%d')
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')

Or use Date Formatter task after query.

Can I connect to PostgreSQL or other databases?¶

Currently MySQL/MariaDB only. Other databases: use Webhook Out to call API.

How do I debug failed queries?¶

1. View execution history
2. Check error message
3. Copy query and parameters
4. Test in MySQL Workbench
5. Check database logs

What happens if query fails mid-workflow?¶

Workflow stops at failed task unless error handling added with If task.

Related Tasks¶

• Loop Task - Process multiple query results
• If Task - Handle query results conditionally
• Variable Task - Store query results
• Code Task - Process complex query results
• Edit Client - Update CRM with query results