SQLer：无需编程语言即可将SQL查询转换为RESTful API的工具

SQLer是一个微型http服务器，用Go语言编写，将旧的CGI概念应用于SQL查询。SQLer允许编写端点并分配一个SQL查询，以便任何人点击它时能执行查询。此外SQLer还允许自定义验证规则，可验证请求正文或查询参数。SQLer使用nginx样式配置语言（HCL）。

\n

SQLer功能

\n

• \n
• 无需依赖，可独立使用；\n
• 支持多种数据可类型，包括：SQL Server, MYSQL, SQLITE, PostgreSQL, Cockroachdb等；\n
• 内置RESTful服务器；\n
• 内置RESP Redis协议，可以使用任何redis客户端连接到SQLer；\n
• 内置Javascript解释器，可轻松转换结果；\n
• 内置验证器；\n
• 自动使用预备语句；\n
• 使用（HCL）配置语言；\n
• 可基于unix glob模式加载多个配置文件；\n
• 每条SQL查询可被命名为宏；\n
• 在每个宏内可使用 Go text/template；\n
• 每个宏都有自己的Context（查询参数+正文参数）作为.Input(map [string] interface{})，而.Utils是辅助函数列表，目前它只包含SQLEscape;\n
• 可自定义授权程序，授权程序只是一个简单的webhook，sqler使用这个webhook验证是否应该完成某请求。\n

\n

下载

\n

• \n
• 源代码：github.com/alash3al/sqler\n
• 二进制版本：https://github.com/alash3al/sqler/releases\n

\n

配置概况

\n

// create a macro/endpoint called \u0026quot;_boot\u0026quot;,\n// this macro is private \u0026quot;used within other macros\u0026quot; \n// because it starts with \u0026quot;_\u0026quot;.\n// this rule only used within `RESTful` context.\n_boot {\n    // the query we want to execute\n    exec = \u0026lt;\u0026lt;SQL\n        CREATE TABLE IF NOT EXISTS `users` (\n            `ID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n            `name` VARCHAR(30) DEFAULT \u0026quot;@anonymous\u0026quot;,\n            `email` VARCHAR(30) DEFAULT \u0026quot;@anonymous\u0026quot;,\n            `password` VARCHAR(200) DEFAULT \u0026quot;\u0026quot;,\n            `time` INT UNSIGNED\n        );\n    SQL\n}\n\n// adduser macro/endpoint, just hit `/adduser` with\n// a `?user_name=\u0026amp;user_email=` or json `POST` request\n// with the same fields.\nadduser {\n    // what request method will this macro be called\n    // default: [\u0026quot;ANY\u0026quot;]\n    // this only used within `RESTful` context.\n    methods = [\u0026quot;POST\u0026quot;]\n\n    // authorizers,\n    // sqler will attempt to send the incoming authorization header\n    // to the provided endpoint(s) as `Authorization`,\n    // each endpoint MUST return `200 OK` so sqler can continue, other wise,\n    // sqler will break the request and return back the client with the error occurred.\n    // each authorizer has a method and a url.\n    // this only used within `RESTful` context.\n    authorizers = [\u0026quot;GET http://web.hook/api/authorize\u0026quot;, \u0026quot;GET http://web.hook/api/allowed?roles=admin,root,super_admin\u0026quot;]\n\n    // the validation rules\n    // you can specify separated rules for each request method!\n    rules {\n        user_name = [\u0026quot;required\u0026quot;]\n        user_email =  [\u0026quot;required\u0026quot;, \u0026quot;email\u0026quot;]\n        user_password = [\u0026quot;required\u0026quot;, \u0026quot;stringlength: 5,50\u0026quot;]\n    }\n\n    // the query to be executed\n    exec = \u0026lt;\u0026lt;SQL\n       {{ template \u0026quot;_boot\u0026quot; }}\n\n        /* let's bind a vars to be used within our internal prepared statement */\n        {{ .BindVar \u0026quot;name\u0026quot; .Input.user_name }}\n        {{ .BindVar \u0026quot;email\u0026quot; .Input.user_email }}\n        {{ .BindVar \u0026quot;emailx\u0026quot; .Input.user_email }}\n\n        INSERT INTO users(name, email, password, time) VALUES(\n            /* we added it above */\n            :name,\n\n            /* we added it above */\n            :email,\n\n            /* it will be secured anyway because it is encoded */\n            '{{ .Input.user_password | .Hash \u0026quot;bcrypt\u0026quot; }}',\n\n            /* generate a unix timestamp \u0026quot;seconds\u0026quot; */\n            {{ .UnixTime }}\n        );\n\n        SELECT * FROM users WHERE id = LAST_INSERT_ID();\n    SQL\n}\n\n// list all databases, and run a transformer function\ndatabases {\n    exec = \u0026quot;SHOW DATABASES\u0026quot;\n\n    transformer = \u0026lt;\u0026lt;JS\n        // there is a global variable called `$result`,\n        // `$result` holds the result of the sql execution.\n        (function(){\n            newResult = []\n\n            for ( i in $result ) {\n                newResult.push($result[i].Database)\n            }\n\n            return newResult\n        })()\n    JS\n}\n

\n

支持的SQL引擎

\n

• \n
• sqlite3\n
• mysql\n
• postgresql\n
• cockroachdb\n
• sqlserver\n

\n

支持的 Util

\n

• \n
• .Hash \u0026lt;method\u0026gt; - 使用指定的方法[md5，sha1，sha256，sha512，bcrypt]散列指定的输入, {{ \u0026quot;data\u0026quot; | .Hash \u0026quot;md5\u0026quot; }}；\n
• ·.UnixTime - 以秒为单位返回unit时间, {{ .UnixTime }}；\n
• .UnixNanoTime - 以纳秒为单位返回unix时间，{{ .UnixNanoTime }}；\n
• .Uniqid - 返回唯一ID，{{ .Uniqid }}。\n

\n

协议

\n

SQLer遵循 Apache 2.0协议。

\n