sqler 内嵌了一个js 引擎的实现（基于goja,当我们配置了exec的配置之后
调用宏（redis 接口）或者rest api 的时候会有一个全局变量$result ，保存了执行的结果，我们可以
通过js 操作数据对象，比如进行数据改写，进行一些额外的数据控制。
同时默认的demo 配置的databases 宏就包含了js 调用的demo
环境准备
docker-compose 运行
- docker-compose 文件
version: "3"
services:
sqler:
image: dalongrong/sqler:1.6
volumes:
- "./config/config.example.hcl:/app/config.example.hcl"
environment:
- "DSN=root:dalongrong@tcp(mysqldb:3306)/test?multiStatements=true"
ports:
- "3678:3678"
- "8025:8025"
mysqldb:
image: mysql:5.7.16
ports:
- 3306:3306
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
environment:
MYSQL_ROOT_PASSWORD: dalongrong
MYSQL_DATABASE: test
MYSQL_USER: test
MYSQL_PASSWORD: test
TZ: Asia/Shanghai
- 配置说明
_boot {
exec = <<SQL
CREATE TABLE IF NOT EXISTS `users` (
`ID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(30) DEFAULT "@anonymous",
`email` VARCHAR(30) DEFAULT "@anonymous",
`password` VARCHAR(200) DEFAULT "",
`time` INT UNSIGNED
);
SQL
}
allusers {
methods = ["GET"]
exec = <<SQL
SELECT * FROM users;
SQL
}
adduser {
methods = ["POST"]
rules {
user_name = ["required"]
user_email = ["required", "email"]
user_password = ["required", "stringlength: 5,50"]
}
exec = <<SQL
{{ template "_boot" }}
/* let's bind a vars to be used within our internal prepared statment */
{{ .BindVar "name" .Input.user_name }}
{{ .BindVar "email" .Input.user_email }}
{{ .BindVar "emailx" .Input.user_email }}
INSERT INTO users(name, email, password, time) VALUES(
/* we added it above */
:name,
/* we added it above */
:email,
/* it will be secured anyway because it is encoded */
'{{ .Input.user_password | .Hash "bcrypt" }}',
/* generate a unix timestamp "seconds" */
{{ .UnixTime }}
);
SELECT * FROM users WHERE id = LAST_INSERT_ID();
SQL
}
databases {
exec = "SHOW DATABASES"
transformer = <<JS
// there is a global variable called `$result`,
// `$result` holds the result of the sql execution.
(function(){
newResult = []
for ( i in $result ) {
newResult.push($result[i].Database)
}
return newResult
})()
JS
}
usersinfo {
exec = "select * from users"
transformer = <<JS
// do some convert only print name && email
(function(){
var newResult=[];
for (var item in $result) {
var user = {
user_name:$result[item].name,
user_email:$result[item].email
}
newResult.push(user)
}
return newResult;
})()
JS
}
- 说明
官方demo 的databases 是一个，usersinfo 是我自己写的一个简单demo，进行查询数据的处理
启动&&测试
- 启动
docker-compose up -d
- 添加数据
curl -X POST
http://localhost:8025/adduser
-H 'Content-Type: application/json'
-H 'Postman-Token: a7784ea1-9f50-46ee-92ac-1d850334f3f1'
-H 'cache-control: no-cache'
-d '{
"user_name":"dalong",
"user_email":"1141591465@qq.com",
"user_password":"dalongdemo"
}'
- 查询效果
curl -i http://localhost:8025/usersinfo
HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json; charset=UTF-8
Vary: Origin
Vary: Accept-Encoding
Date: Fri, 11 Jan 2019 03:35:44 GMT
Content-Length: 137
{"data":[{"user_name":"dalong","user_email":"1141591465@qq.com"},{"user_name":"dalong","user_email":"1141591465@qq.com"}],"success":true}%
说明
$result 是全局定义的，从源码也可以看出来
if transformer == "" {
return data, nil
}
vm := goja.New()
vm.Set("$result", data)
v, err := vm.RunString(transformer)
if err != nil {
return nil, err
}
return v, nil
参考资料
https://github.com/alash3al/sqler/blob/master/macro.go
https://github.com/rongfengliang/sqler-docker-compose