Operations (sample payloads)

Main operations

Count rows

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

{
    "host": "db.example.com",
    "port": 5432,
    "database": "sales_db",
    "schema": "public",
    "user": "db_user",
    "password": "securePassword123",
    "ssl": true,
    "table": "customers",
    "conditions": [
        \{
            "field": "country",
            "operator": "equal to",
            "value": "USA"
        \},
        \{
            "field": "age",
            "operator": "greater than",
            "value": 18
        \}
    ]
}

Sample Output

{
    "count": 1250
}

Delete rows

Sample Input

{
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "schema": "public",
    "user": "dbuser",
    "password": "secretpassword",
    "ssl": true,
    "table": "customers",
    "conditions": [
        \{
            "field": "status",
            "operator": "equal to",
            "value": "inactive"
        \},
        \{
            "field": "last_login",
            "operator": "less than",
            "value": "2023-01-01"
        \}
    ]
}

Sample Output

{
    "deleted": true
}

Find rows

Sample Input

{
    "host": "db.example.com",
    "port": 5432,
    "database": "customers_db",
    "schema": "public",
    "user": "db_user",
    "password": "password123",
    "ssl": true,
    "table": "customers",
    "fields": [
        "id",
        "name",
        "email",
        "created_at"
    ],
    "conditions": [
        \{
            "field": "created_at",
            "operator": "greater than",
            "value": "2023-01-01"
        \},
        \{
            "field": "name",
            "operator": "contains",
            "value": "Smith"
        \}
    ],
    "limit": 10,
    "offset": 0
}

Sample Output

{
    "count": 3,
    "rows": [
        \{
            "id": 1001,
            "name": "John Smith",
            "email": "john.smith@example.com",
            "created_at": "2023-03-15T10:30:00Z"
        \},
        \{
            "id": 1052,
            "name": "Sarah Smithson",
            "email": "sarah.smithson@example.com",
            "created_at": "2023-02-28T14:45:00Z"
        \},
        \{
            "id": 1078,
            "name": "Michael Smith-Johnson",
            "email": "michael.sj@example.com",
            "created_at": "2023-04-02T09:15:00Z"
        \}
    ]
}

Insert new rows

Sample Input

{
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "schema": "public",
    "user": "dbuser",
    "password": "securepassword",
    "ssl": true,
    "ssl_config": {},
    "table": "customers",
    "data": [
        \{
            "id": 1,
            "name": "John Doe",
            "email": "john.doe@example.com",
            "age": 30
        \},
        \{
            "id": 2,
            "name": "Jane Smith",
            "email": "jane.smith@example.com",
            "age": 28
        \}
    ]
}

Sample Output

\{
    "count": 2,
    "inserted": true
\}

Run SQL query

Execute the specified SQL code on the chosen database Sample Input

{
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "schema": "public",
    "user": "dbuser",
    "password": "securepassword",
    "ssl": true,
    "ssl_config": {},
    "sql": "SELECT * FROM public.users WHERE age > $1 AND city = $2",
    "sql_parameters": [
        30,
        "New York"
    ]
}

Sample Output

{
    "count": 3,
    "result": [
        \{
            "id": 1,
            "name": "John Doe",
            "age": 35,
            "city": "New York"
        \},
        \{
            "id": 2,
            "name": "Jane Smith",
            "age": 42,
            "city": "New York"
        \},
        \{
            "id": 3,
            "name": "Mike Johnson",
            "age": 38,
            "city": "New York"
        \}
    ]
}

Update rows

Sample Input

{
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "schema": "public",
    "user": "dbuser",
    "password": "secretpassword",
    "ssl": true,
    "ssl_config": {},
    "table": "employees",
    "data": \{
        "salary": 55000,
        "department": "Marketing"
    \},
    "conditions": [
        \{
            "field": "employee_id",
            "operator": "equal to",
            "value": 1001
        \},
        \{
            "field": "hire_date",
            "operator": "less than",
            "value": "2023-01-01"
        \}
    ]
}

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

{
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "schema": "public",
    "user": "dbuser",
    "password": "securepassword123",
    "ssl": true,
    "ssl_config": {}
}

Sample Output

{}

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

{
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "schema": "public",
    "user": "dbuser",
    "password": "secretpassword",
    "ssl": true,
    "ssl_config": {}
}

Sample Output

{
    "tables": [
        \{
            "table_name": "users",
            "ddl": "CREATE TABLE users (\n  id SERIAL PRIMARY KEY,\n  username VARCHAR(50) NOT NULL,\n  email VARCHAR(100) NOT NULL,\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);"
        \},
        \{
            "table_name": "products",
            "ddl": "CREATE TABLE products (\n  id SERIAL PRIMARY KEY,\n  name VARCHAR(100) NOT NULL,\n  description TEXT,\n  price DECIMAL(10, 2) NOT NULL,\n  stock INTEGER NOT NULL DEFAULT 0\n);"
        \},
        \{
            "table_name": "orders",
            "ddl": "CREATE TABLE orders (\n  id SERIAL PRIMARY KEY,\n  user_id INTEGER REFERENCES users(id),\n  total_amount DECIMAL(10, 2) NOT NULL,\n  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);"
        \}
    ]
}