sqler 从开源很快就获取了 1k 的 star, 使用起来很方便, 而且也很灵活, 支持的数据库也比较多.
支持的功能
无需依赖, 可独立使用;
支持多种数据可类型, 包括: SQL Server, MySQL, SQLite, PostgreSQL, Cockroachdb 等;
内置 RESTful 服务器;
内置 RESP Redis 协议, 可以使用任何 Redis 客户端连接到 SQLer;
内置 JavaScript 解释器, 可轻松转换结果;
内置验证器;
自动使用预备语句;
使用 (HCL) 配置语言;
可基于 unix glob 模式加载多个配置文件;
每条 SQL 查询可被命名为宏;
在每个宏内可使用 Go text/template;
每个宏都有自己的 Context(查询参数 + 正文参数)作为. Input(map [string] interface{}), 而. Utils 是辅助函数列表, 目前它只包含 SQLEscape;
可自定义授权程序, 授权程序只是一个简单的 webhook,sqler 使用这个 webhook 验证是否应该完成某请求
测试环境准备
为了方便测试, 我制作了一个比较简单的 docker 镜像 dalongrong/sqler:1.5, 对于运行的参数可以通过环境
变量指定
docker-compose 文件
- version: "3"
- services:
- sqler:
- image: dalongrong/sqler:1.5
- 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
说明
镜像 dalongrong/sqler:1.5 包含一个默认的配置, 也可以通过环境指定, 配置内容来自官方
内容如下:
- // create a macro/endpoint called "_boot",
- // this macro is private "used within other macros"
- // because it starts with "_".
- _boot {
- // the query we want to execute
- 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
- }
- ?
- // adduser macro/endpoint, just hit `/adduser` with
- // a `?user_name=&user_email=` or JSON `POST` request
- // with the same fields.
- adduser {
- // what request method will this macro be called
- // default: ["ANY"]
- methods = ["POST"]
- ?
- // authorizers,
- // sqler will attempt to send the incoming authorization header
- // to the provided endpoint(s) as `Authorization`,
- // each endpoint MUST return `200 OK` so sqler can continue, other wise,
- // sqler will break the request and return back the client with the error occured.
- // each authorizer has a method and a url, if you ignored the method
- // it will be automatically set to `GET`.
- // authorizers = ["GET http://web.hook/api/authorize", "GET http://web.hook/api/allowed?roles=admin,root,super_admin"]
- ?
- // the validation rules
- // you can specifiy seprated rules for each request method!
- rules {
- user_name = ["required"]
- user_email = ["required", "email"]
- user_password = ["required", "stringlength: 5,50"]
- }
- ?
- // the query to be executed
- 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
- }
- ?
- // list all databases, and run a transformer function
- 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
- }
运行 && 测试
运行
docker-compose up -d
添加数据
REST 接口地址为 8025
添加数据 curl 命令
- 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"
- }'
返回结果
- {
- "data": [
- {
- "ID": 1,
- "email": "1141591465@qq.com",
- "name": "dalong",
- "password": "$2a$10$nfPllaq3AqYDwu4SQTskWeN0BphHCoBzwmb4rj6Q0OB21voBHCZke",
- "time": 1547127497
- }
- ],
- "success": true
- }
数据库数据
说明
sqler 的设计很方便, 我们通过简单的配置就可以创建灵活的 REST API 了, 很强大, 而且内置的认证处理, 数据校验...
参考资料
- https://www.infoq.cn/article/LZxqJd-ZcNiUKcG1APDz
- https://github.com/alash3al/sqler
来源: http://www.bubuko.com/infodetail-2915718.html