sqler sql 转rest api javascript 试用

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

posted on 2019-01-11 11:39  荣锋亮  阅读(487)  评论(0)  编辑  收藏  举报

导航