smartbotic/nodes/mysql/mysql-query.js

/**
 * @node mysql-query
 * @name MySQL Query
 * @category database
 * @version 1.0.0
 * @description Execute SQL queries against a MySQL database with schema introspection support
 * @icon database
 */

const configSchema = {
    type: 'object',
    properties: {
        credentialId: {
            type: 'string',
            title: 'MySQL Credential',
            description: 'Select the MySQL credential to use for connection',
            dynamicOptions: {
                source: 'credentials',
                filter: { type: 'mysql' }
            }
        },
        database: {
            type: 'string',
            title: 'Database',
            description: 'Database name (overrides credential default if set)'
        },
        table: {
            type: 'string',
            title: 'Table',
            description: 'Table name for query operations'
        },
        queryType: {
            type: 'string',
            title: 'Query Type',
            description: 'Type of query to execute',
            enum: ['select', 'insert', 'update', 'delete', 'custom'],
            enumLabels: ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'Custom SQL'],
            default: 'select'
        },
        columns: {
            type: 'array',
            title: 'Columns',
            description: 'Columns to select (empty for all). Enter column names manually.',
            items: {
                type: 'string'
            },
            showWhen: { field: 'queryType', value: 'select' }
        },
        whereClause: {
            type: 'string',
            title: 'WHERE Clause',
            description: 'Optional WHERE conditions (without WHERE keyword). Supports {{variable}} interpolation.',
            showWhen: { field: 'queryType', valueIn: ['select', 'update', 'delete'] }
        },
        orderBy: {
            type: 'string',
            title: 'ORDER BY',
            description: 'Order by clause (without ORDER BY keyword)',
            showWhen: { field: 'queryType', value: 'select' }
        },
        limit: {
            type: 'number',
            title: 'Limit',
            description: 'Maximum number of rows to return',
            default: 100,
            showWhen: { field: 'queryType', value: 'select' }
        },
        insertData: {
            type: 'string',
            title: 'Data to Insert (JSON)',
            description: 'JSON object with column:value pairs. Supports {{variable}} interpolation.',
            showWhen: { field: 'queryType', value: 'insert' }
        },
        updateData: {
            type: 'string',
            title: 'Data to Update (JSON)',
            description: 'JSON object with column:value pairs to set. Supports {{variable}} interpolation.',
            showWhen: { field: 'queryType', value: 'update' }
        },
        customQuery: {
            type: 'string',
            title: 'Custom SQL',
            description: 'Custom SQL query. Supports {{variable}} interpolation. Use ? for parameters.',
            showWhen: { field: 'queryType', value: 'custom' }
        },
        parameters: {
            type: 'array',
            title: 'Query Parameters',
            description: 'Parameters for the query (for ? placeholders)',
            items: {
                type: 'string'
            },
            showWhen: { field: 'queryType', value: 'custom' }
        },
        returnInsertId: {
            type: 'boolean',
            title: 'Return Insert ID',
            description: 'Return the auto-generated ID for INSERT queries',
            default: true,
            showWhen: { field: 'queryType', value: 'insert' }
        }
    },
    required: ['credentialId', 'queryType']
};

const inputSchema = {
    type: 'object',
    properties: {
        data: {
            type: 'any',
            description: 'Input data available for interpolation in queries'
        },
        parameters: {
            type: 'array',
            description: 'Override query parameters from input'
        }
    }
};

const outputSchema = {
    type: 'object',
    properties: {
        rows: { type: 'array', description: 'Query result rows (for SELECT)' },
        affectedRows: { type: 'number', description: 'Number of affected rows (for INSERT/UPDATE/DELETE)' },
        insertId: { type: 'number', description: 'Auto-generated insert ID (for INSERT)' },
        columns: { type: 'array', description: 'Column names from the result' },
        query: { type: 'string', description: 'The executed SQL query' },
        error: { type: 'string', description: 'Error message if query failed' }
    }
};

const outputs = [
    { name: 'main', displayName: 'Query Result', type: 'object', color: '#06b6d4' }
];

function buildSelectQuery(config, data) {
    const table = config.table;
    if (!table) {
        throw new Error('Table is required for SELECT queries');
    }

    const columns = config.columns && config.columns.length > 0
        ? config.columns.join(', ')
        : '*';

    let query = `SELECT ${columns} FROM \`${table}\``;

    if (config.whereClause) {
        const where = smartbotic.utils.interpolate(config.whereClause, data);
        query += ` WHERE ${where}`;
    }

    if (config.orderBy) {
        query += ` ORDER BY ${config.orderBy}`;
    }

    if (config.limit && config.limit > 0) {
        query += ` LIMIT ${config.limit}`;
    }

    return { query, params: [] };
}

function buildInsertQuery(config, data) {
    const table = config.table;
    if (!table) {
        throw new Error('Table is required for INSERT queries');
    }

    let insertData = config.insertData;
    if (typeof insertData === 'string') {
        insertData = smartbotic.utils.interpolate(insertData, data);
        try {
            insertData = JSON.parse(insertData);
        } catch (e) {
            throw new Error('Insert data must be valid JSON: ' + e.message);
        }
    }

    if (!insertData || typeof insertData !== 'object') {
        throw new Error('Insert data is required');
    }

    const columns = Object.keys(insertData);
    const values = Object.values(insertData);
    const placeholders = columns.map(() => '?').join(', ');

    const query = `INSERT INTO \`${table}\` (\`${columns.join('`, `')}\`) VALUES (${placeholders})`;

    return { query, params: values };
}

function buildUpdateQuery(config, data) {
    const table = config.table;
    if (!table) {
        throw new Error('Table is required for UPDATE queries');
    }

    let updateData = config.updateData;
    if (typeof updateData === 'string') {
        updateData = smartbotic.utils.interpolate(updateData, data);
        try {
            updateData = JSON.parse(updateData);
        } catch (e) {
            throw new Error('Update data must be valid JSON: ' + e.message);
        }
    }

    if (!updateData || typeof updateData !== 'object') {
        throw new Error('Update data is required');
    }

    const columns = Object.keys(updateData);
    const values = Object.values(updateData);
    const setClause = columns.map(col => `\`${col}\` = ?`).join(', ');

    let query = `UPDATE \`${table}\` SET ${setClause}`;

    if (config.whereClause) {
        const where = smartbotic.utils.interpolate(config.whereClause, data);
        query += ` WHERE ${where}`;
    }

    return { query, params: values };
}

function buildDeleteQuery(config, data) {
    const table = config.table;
    if (!table) {
        throw new Error('Table is required for DELETE queries');
    }

    let query = `DELETE FROM \`${table}\``;

    if (config.whereClause) {
        const where = smartbotic.utils.interpolate(config.whereClause, data);
        query += ` WHERE ${where}`;
    } else {
        throw new Error('WHERE clause is required for DELETE queries to prevent accidental data loss');
    }

    return { query, params: [] };
}

function buildCustomQuery(config, data, input) {
    if (!config.customQuery) {
        throw new Error('Custom query is required');
    }

    const query = smartbotic.utils.interpolate(config.customQuery, data);

    let params = input.parameters || config.parameters || [];
    if (params.length > 0) {
        params = params.map(p => {
            if (typeof p === 'string') {
                return smartbotic.utils.interpolate(p, data);
            }
            return p;
        });
    }

    return { query, params };
}

module.exports = {
    configSchema,
    inputSchema,
    outputSchema,
    outputs,

    async execute(config, input, context) {
        const credentialId = config.credentialId;
        const data = input.data || input;

        if (!credentialId) {
            throw new Error('MySQL credential is required');
        }

        let queryInfo;
        const queryType = config.queryType || 'select';

        switch (queryType) {
            case 'select':
                queryInfo = buildSelectQuery(config, data);
                break;
            case 'insert':
                queryInfo = buildInsertQuery(config, data);
                break;
            case 'update':
                queryInfo = buildUpdateQuery(config, data);
                break;
            case 'delete':
                queryInfo = buildDeleteQuery(config, data);
                break;
            case 'custom':
                queryInfo = buildCustomQuery(config, data, input);
                break;
            default:
                throw new Error('Invalid query type: ' + queryType);
        }

        const { query, params } = queryInfo;

        smartbotic.log.info(`MySQL ${queryType.toUpperCase()}: ${query.substring(0, 100)}...`);

        const options = {
            credentialId,
            database: config.database,
            query,
            params
        };

        const result = smartbotic.mysql.query(options);

        if (!result.success) {
            smartbotic.log.error(`MySQL query failed: ${result.error}`);
            throw new Error(`MySQL query failed: ${result.error}`);
        }

        smartbotic.log.info(`MySQL query completed: ${result.affectedRows || 0} affected, ${(result.rows || []).length} rows returned`);

        return {
            rows: result.rows || [],
            affectedRows: result.affectedRows || 0,
            insertId: result.insertId || null,
            columns: result.columns || [],
            query: query
        };
    }
};