SQL

SQL is a Pipe for executing SQL queries against a database. The result is written to $in.sql_query as an object (first row) or an array of objects. It is commonly used in Pipelines of External Objects operations to read, create, update, and delete data in an external database.

Parameters

Field	Type	Required	Default	Description
connector_id	number	Yes	—	SQL connector ID
query	string	Yes	—	SQL query. Supports templating
map	bool	No	false	If true — return the first result as an object. If false — return an array of objects
connection_timeout	number	No	PIPE_SQL_CONNECTION_TIMEOUT	Connection timeout in seconds. Overrides PIPE_SQL_CONNECTION_TIMEOUT in the configuration file
query_timeout	number	No	PIPE_SQL_QUERY_TIMEOUT	Query execution timeout in seconds. Overrides PIPE_SQL_QUERY_TIMEOUT in the configuration file

caution

For the Read operation of external objects, you must use map: true. Without this parameter, the pipe will return an array []map[string]interface{}, even if the database contains only one row. The Result pipe expects an object map[string]interface{}, and a type mismatch will cause an error.

Possible Errors

• Error processing the params.query template
• Error executing the SQL query

Examples

Reading a Single Record (Read)

The Input Field id is used as a filter. The result is passed to Result via $#in.sql_query.

[
  {
    "id": 1,
    "type": "log",
    "params": {
      "message": "[Read] Start",
      "level": "info"
    },
    "outs": {
      "success": 100
    }
  },
  {
    "id": 100,
    "type": "sql_pipe",
    "params": {
      "connector_id": 1,
      "connection_timeout": 15,
      "query_timeout": 15,
      "query": "SELECT * FROM my_table WHERE id = $in.id",
      "map": true
    },
    "outs": {
      "success": 200,
      "error": 150
    }
  },
  {
    "id": 200,
    "type": "result",
    "params": {
      "path": "$#in.sql_query"
    }
  },
  {
    "id": 150,
    "type": "error",
    "params": {
      "code": 500,
      "message": "SQL error: $error.code $error.message"
    }
  }
]

Getting a List (List)

Without map — an array of all records is returned.

[
  {
    "id": 1,
    "type": "log",
    "params": {
      "message": "[List] Start",
      "level": "info"
    },
    "outs": {
      "success": 100
    }
  },
  {
    "id": 100,
    "type": "sql_pipe",
    "params": {
      "connector_id": 1,
      "connection_timeout": 15,
      "query_timeout": 15,
      "query": "SELECT id, name, description FROM my_table"
    },
    "outs": {
      "success": 200,
      "error": 150
    }
  },
  {
    "id": 200,
    "type": "result",
    "params": {
      "path": "$#in.sql_query"
    }
  },
  {
    "id": 150,
    "type": "error",
    "params": {
      "code": 500,
      "message": "SQL error: $error.code $error.message"
    }
  }
]

Creating a Record (Create)

Values from Input Fields are substituted via $in.<shortname>.

[
  {
    "id": 1,
    "type": "log",
    "params": {
      "message": "[Create] Start",
      "level": "info"
    },
    "outs": {
      "success": 100
    }
  },
  {
    "id": 100,
    "type": "sql_pipe",
    "params": {
      "connector_id": 1,
      "connection_timeout": 15,
      "query_timeout": 15,
      "query": "INSERT INTO my_table (name, description) VALUES ('$in.name', '$in.description')"
    },
    "outs": {
      "success": 200,
      "error": 150
    }
  },
  {
    "id": 200,
    "type": "result",
    "params": {
      "path": "$#in.sql_query"
    }
  },
  {
    "id": 150,
    "type": "error",
    "params": {
      "code": 500,
      "message": "SQL error: $error.code $error.message"
    }
  }
]

Updating a Record (Update)

[
  {
    "id": 1,
    "type": "log",
    "params": {
      "message": "[Update] Start",
      "level": "info"
    },
    "outs": {
      "success": 100
    }
  },
  {
    "id": 100,
    "type": "sql_pipe",
    "params": {
      "connector_id": 1,
      "connection_timeout": 15,
      "query_timeout": 15,
      "query": "UPDATE my_table SET name = '$in.name', description = '$in.description' WHERE id = $in.id"
    },
    "outs": {
      "success": 200,
      "error": 150
    }
  },
  {
    "id": 200,
    "type": "result",
    "params": {
      "path": "$#in.sql_query"
    }
  },
  {
    "id": 150,
    "type": "error",
    "params": {
      "code": 500,
      "message": "SQL error: $error.code $error.message"
    }
  }
]

Deleting a Record (Delete)

[
  {
    "id": 1,
    "type": "log",
    "params": {
      "message": "[Delete] Start",
      "level": "info"
    },
    "outs": {
      "success": 100
    }
  },
  {
    "id": 100,
    "type": "sql_pipe",
    "params": {
      "connector_id": 1,
      "connection_timeout": 15,
      "query_timeout": 15,
      "query": "DELETE FROM my_table WHERE id = $in.id"
    },
    "outs": {
      "success": 200,
      "error": 150
    }
  },
  {
    "id": 200,
    "type": "result",
    "params": {
      "path": "$#in.sql_query"
    }
  },
  {
    "id": 150,
    "type": "error",
    "params": {
      "code": 500,
      "message": "SQL error: $error.code $error.message"
    }
  }
]