Operations (sample payloads)

Main operations

Count rows

Counts the number of rows that meet a given set of conditions. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "sales_db",
        "user": "sales_user",
        "password": "securePassword123"
    \},
    "table": "customers",
    "conditions": [
        \{
            "field": "country",
            "operator": "equal to",
            "value": "USA"
        \},
        \{
            "field": "age",
            "operator": "greater than",
            "value": 18
        \}
    ]
}

Sample Output

{
    "count": 1250
}

Create or update table from csv

Create or update a table from a CSV file export. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "sales_db",
        "user": "admin",
        "password": "securePassword123"
    \},
    "file": \{
        "name": "sales_data.csv",
        "content": "base64encodedcontent..."
    \},
    "table_name": "monthly_sales",
    "has_header": true,
    "delimiter": ",",
    "primary_key": "sale_id",
    "quote": "\"",
    "escape": "\""
}

Sample Output

\{
    "tableName": "monthly_sales",
    "affectedRows": 1000,
    "inserted": true,
    "insertId": 1001,
    "tableCreated": true,
    "tableUpdated": false
\}

Create or update table with load data

Create or update a table using MySQl LOAD DATA. local_infile global variable must be set to true in your MySQL database. Max file size allowed is 300MB. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "sales_db",
        "user": "admin_user",
        "password": "secure_password123"
    \},
    "file": \{
        "name": "sales_data.csv",
        "size": 1024000,
        "type": "text/csv"
    \},
    "table_name": "monthly_sales",
    "has_header": true,
    "delimiter": ",",
    "end_line": "\n",
    "primary_key": "sale_id",
    "quote": "\"",
    "escape": "\""
}

Sample Output

\{
    "tableName": "monthly_sales",
    "affectedRows": 1000,
    "inserted": true,
    "tableCreated": true,
    "tableUpdated": false
\}

Delete rows

Deletes rows in your database based on criteria of your choosing. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "customers_db",
        "user": "admin",
        "password": "securepassword123"
    \},
    "table": "customers",
    "conditions": [
        \{
            "field": "last_login",
            "operator": "less than",
            "value": "2023-01-01"
        \},
        \{
            "field": "status",
            "operator": "equal to",
            "value": "inactive"
        \}
    ]
}

Sample Output

\{
    "rowCount": 15,
    "deleted": true
\}

Export table as csv

Creates a CSV from a table. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "customer_db",
        "user": "db_user",
        "password": "securePassword123"
    \},
    "table_name": "customers",
    "filename": "customer_export.csv",
    "connection_timeout": 20000
}

Sample Output

{
    "file": \{
        "name": "customer_export.csv",
        "url": "https://storage.example.com/exports/customer_export.csv",
        "mime_type": "text/csv",
        "expires": 1623456789
    \}
}

Find rows

Find rows in your database based on criteria of your choosing. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "customers_db",
        "user": "db_user",
        "password": "securePassword123"
    \},
    "table": "customers",
    "fields": [
        "id",
        "first_name",
        "last_name",
        "email",
        "age"
    ],
    "conditions": [
        \{
            "field": "age",
            "operator": "greater than",
            "value": 30
        \},
        \{
            "field": "last_name",
            "operator": "starts with",
            "value": "S"
        \}
    ],
    "limit": 5,
    "offset": 0
}

Sample Output

{
    "count": 3,
    "rows": [
        \{
            "id": 102,
            "first_name": "John",
            "last_name": "Smith",
            "email": "john.smith@example.com",
            "age": 35
        \},
        \{
            "id": 157,
            "first_name": "Emily",
            "last_name": "Sanders",
            "email": "emily.sanders@example.com",
            "age": 42
        \},
        \{
            "id": 203,
            "first_name": "Michael",
            "last_name": "Scott",
            "email": "michael.scott@example.com",
            "age": 45
        \}
    ]
}

Insert new rows

Insert one or more rows into your MySQL database. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "customer_db",
        "user": "admin",
        "password": "securepassword123"
    \},
    "table": "customers",
    "row_data": [
        \{
            "first_name": "John",
            "last_name": "Doe",
            "email": "john.doe@example.com",
            "age": 35,
            "city": "New York"
        \},
        \{
            "first_name": "Jane",
            "last_name": "Smith",
            "email": "jane.smith@example.com",
            "age": 28,
            "city": "Los Angeles"
        \}
    ]
}

Sample Output

\{
    "id": 1234,
    "affectedRows": 2,
    "inserted": true
\}

Run SQL query

Execute the specified SQL code on the chosen database Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "customers_db",
        "user": "db_user",
        "password": "securePassword123"
    \},
    "sql": "SELECT * FROM public.customers WHERE city = 'New York' LIMIT 5;"
}

Sample Output

{
    "result": [
        \{
            "id": 1,
            "first_name": "John",
            "last_name": "Doe",
            "email": "john.doe@example.com",
            "city": "New York",
            "state": "NY",
            "created_at": "2023-05-01T10:30:00Z"
        \},
        \{
            "id": 2,
            "first_name": "Jane",
            "last_name": "Smith",
            "email": "jane.smith@example.com",
            "city": "New York",
            "state": "NY",
            "created_at": "2023-05-02T14:45:00Z"
        \},
        \{
            "id": 3,
            "first_name": "Michael",
            "last_name": "Johnson",
            "email": "michael.johnson@example.com",
            "city": "New York",
            "state": "NY",
            "created_at": "2023-05-03T09:15:00Z"
        \},
        \{
            "id": 4,
            "first_name": "Emily",
            "last_name": "Brown",
            "email": "emily.brown@example.com",
            "city": "New York",
            "state": "NY",
            "created_at": "2023-05-04T11:20:00Z"
        \},
        \{
            "id": 5,
            "first_name": "David",
            "last_name": "Wilson",
            "email": "david.wilson@example.com",
            "city": "New York",
            "state": "NY",
            "created_at": "2023-05-05T16:00:00Z"
        \}
    ]
}

Update rows

Updates rows in your database based on criteria of your choosing. Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "customers_db",
        "user": "admin",
        "password": "securepassword123"
    \},
    "table": "customers",
    "data": \{
        "email": "newemail@example.com",
        "last_updated": "2023-06-15 14:30:00"
    \},
    "conditions": [
        \{
            "field": "customer_id",
            "operator": "equal to",
            "value": 12345
        \},
        \{
            "field": "status",
            "operator": "not equal to",
            "value": "inactive"
        \}
    ]
}

Sample Output

\{
    "count": 1,
    "updated": true
\}

DDL operations

List table fields (DDL)

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "my_database",
        "user": "db_user",
        "password": "secure_password123"
    \},
    "connection_timeout": 20000
}

Sample Output

{
    "fields": [
        \{
            "field_name": "id",
            "data_type": "INT",
            "is_nullable": false,
            "key": "PRI",
            "default": null,
            "extra": "auto_increment"
        \},
        \{
            "field_name": "name",
            "data_type": "VARCHAR(255)",
            "is_nullable": false,
            "key": "",
            "default": null,
            "extra": ""
        \},
        \{
            "field_name": "email",
            "data_type": "VARCHAR(255)",
            "is_nullable": true,
            "key": "UNI",
            "default": null,
            "extra": ""
        \},
        \{
            "field_name": "created_at",
            "data_type": "TIMESTAMP",
            "is_nullable": false,
            "key": "",
            "default": "CURRENT_TIMESTAMP",
            "extra": ""
        \}
    ]
}

List tables (DDL)

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

{
    "authentication": \{
        "host": "mysql.example.com",
        "port": 3306,
        "database": "my_database",
        "user": "db_user",
        "password": "securePassword123"
    \},
    "connection_timeout": 20000
}

Sample Output

{
    "tables": [
        \{
            "table_name": "users",
            "ddl": "CREATE TABLE users (\n  id int(11) NOT NULL AUTO_INCREMENT,\n  username varchar(50) NOT NULL,\n  email varchar(100) NOT NULL,\n  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  PRIMARY KEY (id),\n  UNIQUE KEY username (username),\n  UNIQUE KEY email (email)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        \},
        \{
            "table_name": "products",
            "ddl": "CREATE TABLE products (\n  id int(11) NOT NULL AUTO_INCREMENT,\n  name varchar(100) NOT NULL,\n  description text,\n  price decimal(10,2) NOT NULL,\n  stock int(11) NOT NULL DEFAULT '0',\n  PRIMARY KEY (id)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        \},
        \{
            "table_name": "orders",
            "ddl": "CREATE TABLE orders (\n  id int(11) NOT NULL AUTO_INCREMENT,\n  user_id int(11) NOT NULL,\n  total_amount decimal(10,2) NOT NULL,\n  order_date datetime NOT NULL,\n  PRIMARY KEY (id),\n  KEY user_id (user_id),\n  CONSTRAINT orders_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        \}
    ]
}