mysql.adapter Integration Documentation

Overview

The mysql.adapter integration is a component of the Vantage analytics and data platform designed to facilitate the connection and interaction with MySQL databases. It provides a comprehensive interface for executing queries, administering tables, and managing database connections securely and efficiently.

Purpose

The primary purpose of the mysql.adapter is to enable users to perform read and write operations within a MySQL database while ensuring data integrity and security. It allows for executing safe queries by filtering out unsafe SQL operations and supports both user-initiated read and write operations seamlessly.

How It Works

The mysql.adapter works by establishing a connection to a MySQL database using the mysql2/promise library to facilitate asynchronous query execution. The integration defines the following key functionalities:

• Establishing a connection to the MySQL database.
• Performing read operations (like querying data).
• Executing controlled write operations (such as creating tables and inserting data) while preventing unsafe changes to the database.

Data Expectations

The adapter expects the following data from its integration settings:

• Credentials: Host, port, username, password, database name.
• SSL configurations (optional): A boolean value indicating whether to use SSL for the connection.

Settings

The following are the detailed settings for the mysql.adapter:

Use Cases & Examples

Use Case 1: Data Reporting

A marketing team may want to generate reports from various tables in a MySQL database. By using mysql.adapter, they can run complex SELECT queries to retrieve aggregated data for analysis and visualization.

Use Case 2: Dynamic Table Management

An application that allows users to manage their product inventory may require creating tables dynamically. The mysql.adapter enables users to define and create new product tables based on changing business needs.

Use Case 3: Secure Data Manipulation

A finance team needs to insert transaction records while ensuring that no critical DDL commands (e.g., DROP, TRUNCATE) are executed inappropriately. The mysql.adapter enforces safe interactions through its validation processes.

Example Configuration

To illustrate how the mysql.adapter can be configured for a use case, consider a scenario where an application dynamically creates a table to manage user transactions. Here is a sample configuration data:

{
  "host": "192.168.1.1",
  "port": 3306,
  "username": "db_user",
  "password": "secure_password",
  "databaseName": "ecommerce_db",
  "ssl": true
}

In the application, the team could use mysql.adapter to create a new table for storing transactions as follows:

const mysqlAdapter = new MySQLAdapter(configurationData);
await mysqlAdapter.createTable('transactions', [
    { name: 'id', type: 'INT AUTO_INCREMENT', primaryKey: true, nullable: false },
    { name: 'user_id', type: 'INT', nullable: false },
    { name: 'amount', type: 'DECIMAL(10, 2)', nullable: false },
    { name: 'created_at', type: 'DATETIME', nullable: false, default: 'CURRENT_TIMESTAMP' }
]);

This operation creates a transactions table with specified columns and constraints, establishing a foundation for tracking user transactions securely.

Additional Notes

• AI Integrations: This integration can potentially be enhanced with AI capabilities for automated data analysis and reporting options in future versions.
• Billing Impact: Using the adapter may incur charges based on your Vantage service level and usage patterns, particularly if there is high frequency of queries or extensive read/write operations.

This comprehensive documentation provides a thorough understanding of the mysql.adapter integration, guiding users on its functionalities, configurations, and operational scenarios.