dbClient
dbClient
component: perform CRUD operations on the database through the standard sql interface. Built-in support for mysql
and postgres
databases.
It also supports third-party database drivers that implement the database/sql
interface, but you need to import them yourself, such as TDengine (opens new window) driver:
import (
_ "github.com/taosdata/driver-go/v3/taosRestful"
)
//Configuration use
//driverName:taosRestful
1
2
3
4
5
6
2
3
4
5
6
# GO third-party database driver packages
- github.com/taosdata/driver-go: TDengine (opens new window) driver.
- github.com/denisenkom/go-mssqldb: Microsoft SQL Server driver.
- github.com/godror/godror: Oracle Database driver.
- github.com/snowflakedb/gosnowflake: Snowflake driver.
- github.com/ClickHouse/clickhouse-go: ClickHouse driver.
- github.com/vertica/vertica-sql-go: Vertica driver.
# Configuration
This component allows the reuse of shared connection clients through the dsn
field. See Component Connection Reuse for reference.
Field | Type | Description | Default value |
---|---|---|---|
sql | string | SQL statement | None |
params | array | SQL statement parameter list, can using Component Configuration Variables. | None |
getOne | bool | Whether to return only one record, true: the return structure is not an array structure, false: the return data is an array structure | None |
poolSize | int | Connection pool size | None |
driverName | string | Database driver name, mysql/postgres, or other database driver types | mysql |
dsn | string | Database connection configuration, refer to sql.Open parameters | None |
# Relation Type
- Success: Execution successful, send the message to the
Success
chain - Failure: Execution failed, send the message to the
Failure
chain
# Execution result
- Select: Query result, replace to msg.Data, and pass to the next node.
- UPDATE, DELETE: The result is stored in msg.Metadata:
- msg.Metadata.rowsAffected: How many rows are affected
- msg.Data: Content unchanged
- INSERT: The result is stored in msg.Metadata:
- msg.Metadata.rowsAffected: How many rows are affected
- msg.Metadata.lastInsertId: Insert ID (if any)
- msg.Data: Content unchanged
# Configuration example
{
"id": "s1",
"type": "dbClient",
"name": "Insert 1 record",
"configuration": {
"driverName":"mysql",
"dsn":"root:root@tcp(127.0.0.1:3306)/test",
"poolSize":5,
"sql":"insert into users (id,name, age) values (?,?,?)",
"params":["${id}", "${name}", "${age}"]
}
}
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# Application example
Application example reference: dbClient (opens new window)
{
"ruleChain": {
"id":"rule01",
"name": "Test rule chain",
"root": true
},
"metadata": {
"nodes": [
{
"id": "s1",
"type": "dbClient",
"name": "Insert 1 record",
"configuration": {
"driverName":"mysql",
"dsn":"root:root@tcp(127.0.0.1:3306)/test",
"poolSize":5,
"sql":"insert into users (id,name, age) values (?,?,?)",
"params":["${metadata.id}", "${metadata.name}", "${metadata.age}"]
}
},
{
"id": "s2",
"type": "dbClient",
"name": "Query 1 record",
"configuration": {
"driverName":"mysql",
"dsn":"root:root@tcp(127.0.0.1:3306)/test",
"sql":"select * from users where id = ?",
"params":["${metadata.id}"],
"getOne":true
}
},
{
"id": "s3",
"type": "dbClient",
"name": "Query multiple records, parameters do not use placeholders",
"configuration": {
"driverName":"mysql",
"dsn":"root:root@tcp(127.0.0.1:3306)/test",
"sql":"select * from users where age >= 18"
}
},
{
"id": "s4",
"type": "dbClient",
"name": "Update record, parameters use placeholders",
"configuration": {
"driverName":"mysql",
"dsn":"root:root@tcp(127.0.0.1:3306)/test",
"sql":"update users set age = ? where id = ?",
"params":["${metadata.updateAge}","${metadata.id}"]
}
},
{
"id": "s5",
"type": "dbClient",
"name": "Delete record",
"configuration": {
"driverName":"mysql",
"dsn":"root:root@tcp(127.0.0.1:3306)/test",
"sql":"delete from users"
}
}
],
"connections": [
{
"fromId": "s1",
"toId": "s2",
"type": "Success"
},
{
"fromId": "s2",
"toId": "s3",
"type": "Success"
},
{
"fromId": "s3",
"toId": "s4",
"type": "Success"
},
{
"fromId": "s4",
"toId": "s5",
"type": "Success"
}
]
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Edit this page on GitHub (opens new window)
Last Updated: 2024/10/23, 10:13:01