Operations (sample payloads)

Main operations
Copy

Count rows
Copy

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

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "sales_db",
6        "user": "sales_user",
7        "password": "securePassword123"
8    },
9    "table": "customers",
10    "conditions": [
11        {
12            "field": "country",
13            "operator": "equal to",
14            "value": "USA"
15        },
16        {
17            "field": "age",
18            "operator": "greater than",
19            "value": 18
20        }
21    ]
22}

Sample Output

1{
2    "count": 1250
3}

Create or update table from csv
Copy

Create or update a table from a CSV file export.

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "sales_db",
6        "user": "admin",
7        "password": "securePassword123"
8    },
9    "file": {
10        "name": "sales_data.csv",
11        "content": "base64encodedcontent..."
12    },
13    "table_name": "monthly_sales",
14    "has_header": true,
15    "delimiter": ",",
16    "primary_key": "sale_id",
17    "quote": "\"",
18    "escape": "\""
19}

Sample Output

1{
2    "tableName": "monthly_sales",
3    "affectedRows": 1000,
4    "inserted": true,
5    "insertId": 1001,
6    "tableCreated": true,
7    "tableUpdated": false
8}

Create or update table with load data
Copy

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

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "sales_db",
6        "user": "admin_user",
7        "password": "secure_password123"
8    },
9    "file": {
10        "name": "sales_data.csv",
11        "size": 1024000,
12        "type": "text/csv"
13    },
14    "table_name": "monthly_sales",
15    "has_header": true,
16    "delimiter": ",",
17    "end_line": "\n",
18    "primary_key": "sale_id",
19    "quote": "\"",
20    "escape": "\""
21}

Sample Output

1{
2    "tableName": "monthly_sales",
3    "affectedRows": 1000,
4    "inserted": true,
5    "tableCreated": true,
6    "tableUpdated": false
7}

Delete rows
Copy

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

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "customers_db",
6        "user": "admin",
7        "password": "securepassword123"
8    },
9    "table": "customers",
10    "conditions": [
11        {
12            "field": "last_login",
13            "operator": "less than",
14            "value": "2023-01-01"
15        },
16        {
17            "field": "status",
18            "operator": "equal to",
19            "value": "inactive"
20        }
21    ]
22}

Sample Output

1{
2    "rowCount": 15,
3    "deleted": true
4}

Export table as csv
Copy

Creates a CSV from a table.

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "customer_db",
6        "user": "db_user",
7        "password": "securePassword123"
8    },
9    "table_name": "customers",
10    "filename": "customer_export.csv",
11    "connection_timeout": 20000
12}

Sample Output

1{
2    "file": {
3        "name": "customer_export.csv",
4        "url": "https://storage.example.com/exports/customer_export.csv",
5        "mime_type": "text/csv",
6        "expires": 1623456789
7    }
8}

Find rows
Copy

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

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "customers_db",
6        "user": "db_user",
7        "password": "securePassword123"
8    },
9    "table": "customers",
10    "fields": [
11        "id",
12        "first_name",
13        "last_name",
14        "email",
15        "age"
16    ],
17    "conditions": [
18        {
19            "field": "age",
20            "operator": "greater than",
21            "value": 30
22        },
23        {
24            "field": "last_name",
25            "operator": "starts with",
26            "value": "S"
27        }
28    ],
29    "limit": 5,
30    "offset": 0
31}

Sample Output

1{
2    "count": 3,
3    "rows": [
4        {
5            "id": 102,
6            "first_name": "John",
7            "last_name": "Smith",
8            "email": "john.smith@example.com",
9            "age": 35
10        },
11        {
12            "id": 157,
13            "first_name": "Emily",
14            "last_name": "Sanders",
15            "email": "emily.sanders@example.com",
16            "age": 42
17        },
18        {
19            "id": 203,
20            "first_name": "Michael",
21            "last_name": "Scott",
22            "email": "michael.scott@example.com",
23            "age": 45
24        }
25    ]
26}

Insert new rows
Copy

Insert one or more rows into your MySQL database.

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "customer_db",
6        "user": "admin",
7        "password": "securepassword123"
8    },
9    "table": "customers",
10    "row_data": [
11        {
12            "first_name": "John",
13            "last_name": "Doe",
14            "email": "john.doe@example.com",
15            "age": 35,
16            "city": "New York"
17        },
18        {
19            "first_name": "Jane",
20            "last_name": "Smith",
21            "email": "jane.smith@example.com",
22            "age": 28,
23            "city": "Los Angeles"
24        }
25    ]
26}

Sample Output

1{
2    "id": 1234,
3    "affectedRows": 2,
4    "inserted": true
5}

Run SQL query
Copy

Execute the specified SQL code on the chosen database

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "customers_db",
6        "user": "db_user",
7        "password": "securePassword123"
8    },
9    "sql": "SELECT * FROM public.customers WHERE city = 'New York' LIMIT 5;"
10}

Sample Output

1{
2    "result": [
3        {
4            "id": 1,
5            "first_name": "John",
6            "last_name": "Doe",
7            "email": "john.doe@example.com",
8            "city": "New York",
9            "state": "NY",
10            "created_at": "2023-05-01T10:30:00Z"
11        },
12        {
13            "id": 2,
14            "first_name": "Jane",
15            "last_name": "Smith",
16            "email": "jane.smith@example.com",
17            "city": "New York",
18            "state": "NY",
19            "created_at": "2023-05-02T14:45:00Z"
20        },
21        {
22            "id": 3,
23            "first_name": "Michael",
24            "last_name": "Johnson",
25            "email": "michael.johnson@example.com",
26            "city": "New York",
27            "state": "NY",
28            "created_at": "2023-05-03T09:15:00Z"
29        },
30        {
31            "id": 4,
32            "first_name": "Emily",
33            "last_name": "Brown",
34            "email": "emily.brown@example.com",
35            "city": "New York",
36            "state": "NY",
37            "created_at": "2023-05-04T11:20:00Z"
38        },
39        {
40            "id": 5,
41            "first_name": "David",
42            "last_name": "Wilson",
43            "email": "david.wilson@example.com",
44            "city": "New York",
45            "state": "NY",
46            "created_at": "2023-05-05T16:00:00Z"
47        }
48    ]
49}

Update rows
Copy

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

Sample Input

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "customers_db",
6        "user": "admin",
7        "password": "securepassword123"
8    },
9    "table": "customers",
10    "data": {
11        "email": "newemail@example.com",
12        "last_updated": "2023-06-15 14:30:00"
13    },
14    "conditions": [
15        {
16            "field": "customer_id",
17            "operator": "equal to",
18            "value": 12345
19        },
20        {
21            "field": "status",
22            "operator": "not equal to",
23            "value": "inactive"
24        }
25    ]
26}

Sample Output

1{
2    "count": 1,
3    "updated": true
4}

DDL operations
Copy

List table fields (DDL)
Copy

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

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "my_database",
6        "user": "db_user",
7        "password": "secure_password123"
8    },
9    "connection_timeout": 20000
10}

Sample Output

1{
2    "fields": [
3        {
4            "field_name": "id",
5            "data_type": "INT",
6            "is_nullable": false,
7            "key": "PRI",
8            "default": null,
9            "extra": "auto_increment"
10        },
11        {
12            "field_name": "name",
13            "data_type": "VARCHAR(255)",
14            "is_nullable": false,
15            "key": "",
16            "default": null,
17            "extra": ""
18        },
19        {
20            "field_name": "email",
21            "data_type": "VARCHAR(255)",
22            "is_nullable": true,
23            "key": "UNI",
24            "default": null,
25            "extra": ""
26        },
27        {
28            "field_name": "created_at",
29            "data_type": "TIMESTAMP",
30            "is_nullable": false,
31            "key": "",
32            "default": "CURRENT_TIMESTAMP",
33            "extra": ""
34        }
35    ]
36}

List tables (DDL)
Copy

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

1{
2    "authentication": {
3        "host": "mysql.example.com",
4        "port": 3306,
5        "database": "my_database",
6        "user": "db_user",
7        "password": "securePassword123"
8    },
9    "connection_timeout": 20000
10}

Sample Output

1{
2    "tables": [
3        {
4            "table_name": "users",
5            "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"
6        },
7        {
8            "table_name": "products",
9            "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"
10        },
11        {
12            "table_name": "orders",
13            "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"
14        }
15    ]
16}

Operations (sample payloads)

Main operationsCopy

Count rowsCopy

Create or update table from csvCopy

Create or update table with load dataCopy

Delete rowsCopy

Export table as csvCopy

Find rowsCopy

Insert new rowsCopy

Run SQL queryCopy

Update rowsCopy

DDL operationsCopy

List table fields (DDL)Copy

List tables (DDL)Copy

Overview

Operations

Main operations
Copy

Count rows
Copy

Create or update table from csv
Copy

Create or update table with load data
Copy

Delete rows
Copy

Export table as csv
Copy

Find rows
Copy

Insert new rows
Copy

Run SQL query
Copy

Update rows
Copy

DDL operations
Copy

List table fields (DDL)
Copy

List tables (DDL)
Copy