当前位置: 首页 > 工具软件 > sqler > 使用案例 >

mysql redis rest_sqler sql 转rest api redis 接口使用

慕弘深
2023-12-01

sqler 支持redis 协议,我们可以用过redis client 连接sqler,他会将宏住转换为redis command

实现上看源码我们发现是基于一个开源的redis 协议的golang 实现,同时sqler 内置了一些方便

的command : list、 info、 echo、select、ping。

目前关于redis 的使用文档基本没有,但是我们通过阅读源码可以看出,就是解析参数,第一个为

command, 第二个为数据(json 序列化之后的,下边会有使用的说明

环境准备

docker 镜像,可以参考我的文章,里面有镜像的制作同时我也上传dockerhub 了

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 = <

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 = <

SELECT * FROM users;

SQL

}

adduser {

methods = ["POST"]

rules {

user_name = ["required"]

user_email = ["required", "email"]

user_password = ["required", "stringlength: 5,50"]

}

exec = <

{{ 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 = <

// 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

添加数据

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$9gB.kOC8sn5f3jGPo4qzl.tULFkDRXj0i3EMyAr7VvkvBxBijbqKu","time":1547169926}],"success":true}%

查询数据

curl -i http://localhost:8025/allusers

返回结果

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 01:26:20 GMT

Content-Length: 170

{"data":[{"ID":1,"email":"1141591465@qq.com","name":"dalong","password":"$2a$10$9gB.kOC8sn5f3jGPo4qzl.tULFkDRXj0i3EMyAr7VvkvBxBijbqKu","time":1547169926}],"success":true}%

redis 集成使用

为了测试,我使用的是cli,没有编写代码

连接

默认端口是3678,可以通过环境变量修改

redis-cli -p 3678

查看宏信息

list

输出如下:

1) "adduser"

2) "databases"

3) "_boot"

4) "allusers"

通过redis-cli 添加数据

调用adduser 宏

adduser "{\"user_name\":\"dalong\",\"user_email\":\"1141591465@qq.com\",\"user_password\":\"dalongdemo\"}"

效果

实际上从这个也可以看出,会有一些bug,具体待确定

adduser "{\"user_name\":\"dalong\",\"user_email\":\"1141591465@qq.com\",\"user_password\":\"dalongdemo\"}"

(error) not found

127.0.0.1:3678> adduser "{\"user_name\":\"dalong\",\"user_email\":\"1141591465@qq.com\",\"user_password\":\"dalongdemo\"}"

、1) (integer) 1

2) "[{\"ID\":2,\"email\":\"1141591465@qq.com\",\"name\":\"dalong\",\"password\":\"$2a$10$995GTjNXCNAElXHafDoxkOsum5juPBjpu5dlIt0GJi.TsJfQ3uRIK\",\"time\":1547170172}]"

通过redis-cli 查询数据

调用allusers 宏

allusers

1) (integer) 1

2) "[{\"ID\":1,\"email\":\"1141591465@qq.com\",\"name\":\"dalong\",\"password\":\"$2a$10$9gB.kOC8sn5f3jGPo4qzl.tULFkDRXj0i3EMyAr7VvkvBxBijbqKu\",\"time\":1547169926},{\"ID\":2,\"email\":\"1141591465@qq.com\",\"name\":\"dalong\",\"password\":\"$2a$10$995GTjNXCNAElXHafDoxkOsum5juPBjpu5dlIt0GJi.TsJfQ3uRIK\",\"time\":1547170172}]"

列出数据

调用databases 宏

databases

1) (integer) 1

2) "[\"information_schema\",\"mysql\",\"performance_schema\",\"sys\",\"test\"]"

说明

不太确定是sqler redis 协议的兼容问题还是,目前使用mac 的cli,会有数据时有时无的问题,待确定原因,同时对于添加了安全

认证与redis 怎么通信还有待研究。

参考资料

 类似资料: