通过NodeJS自动生成的MySQL的REST风格API

最新推荐文章于 2022-10-08 16:11:17 发布

星河_赵梓宇

最新推荐文章于 2022-10-08 16:11:17 发布

阅读量1.9k

点赞数 1

文章标签： mysql rest api

本文链接：https://blog.csdn.net/Aria_Miazzy/article/details/102859005

版权

通过NodeJS自动生成的MySQL的REST风格API

前置条件

安装NodeJS，requires node >= 7.6.0，目前使用的稳定版NodeJS v10.x

安装XMYSQL

xmysql requires node >= 7.6.0

npm install -g xmysql
xmysql -h localhost -u mysqlUsername -p mysqlPassword -d databaseName
http://localhost:3000

验证查询

Docker方式安装XMYSQL

1. 配置docker-compose.yml文件

version: '3'
services:
    xmysql:
        image: markuman/xmysql:0.4.2
        ports:
            - "80"
        environment:
            - DATABASE_HOST=172.18.231.224
            - DATABASE_USER=root
            - DATABASE_PASSWORD=ziyequma
            - DATABASE_NAME=jeecg
        restart: always

2. 启动容器

docker-compose up -d

3. 设置集群模式

docker-compse scale xmysql=10

4. 查看IP端口

5. 配置nginx的负载均衡

配置Nginx反向代理

events {
   worker_connections 1024;
   
}
http {
    server {
        server_name 127.0.0.1;
        listen 80 ;
        location / {
            rewrite ^/(.*) /$1 break;
            proxy_redirect off;
            proxy_set_header Host $host;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_pass http://127.0.0.1:3000;
        }
    }
}

完整nginx.conf文件

user root;
worker_processes auto;
error_log /var/log/nginx/error.log;
pid /usr/local/nginx/logs/nginx.pid;

# Load dynamic modules. See /usr/share/doc/nginx/README.dynamic.
include /usr/share/nginx/modules/*.conf;

events {
    worker_connections 1024;
}

http {
    
    include       mime.types;
    default_type  application/octet-stream;

    #access_log  logs/access.log  main;
    client_max_body_size 20m;

    sendfile        on;
    tcp_nopush     on;
    keepalive_timeout  65;
    
    gzip  on;

    # 设置缓存的路径和其他参数
    # proxy_cache_path path [levels=levels] keys_zone=name:size [inactive=time] [max_size=size] [loader_files=number] [loader_sleep=time] [loader_threshold=time];
    # 缓存路径 /data/nginx/cache 缓存结构为 2 层，即该路径下会有 2 层子目录，缓存文件会保存在最下层子目录
    # 缓存的 key 会保存在名为 web_cache 的内存区域，该内存区域大小为 50 m
    # 10 分钟内缓存没有被访问就会过期
    # 缓存文件最多占用 1g 空间
    proxy_cache_path ./web_cache levels=1:2 keys_zone=web_cache:1024m inactive=1000m max_size=1g;

    upstream manage-web_name-live {
        server 100.100.100.100:8000 weight=10  max_fails=3 fail_timeout=30s;
    }
   
    upstream cwbase {
        server 127.0.0.1:32777 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32778 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32779 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32780 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32781 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32782 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32783 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32784 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32785 weight=10 max_fails=3 fail_timeout=30s;
        server 127.0.0.1:32786 weight=10 max_fails=3 fail_timeout=30s;
    }

    server {
        listen       443 ssl;
        server_name  web_name.domain_name.com;

        ssl_certificate /root/.caddy/acme/acme-v02.api.letsencrypt.org/sites/web_name.domain_name.com/web_name.domain_name.com.crt;
        ssl_certificate_key /root/.caddy/acme/acme-v02.api.letsencrypt.org/sites/web_name.domain_name.com/web_name.domain_name.com.key;
        ssl_session_timeout 5m;
        ssl_ciphers ECDHE-RSA-AES128-GCM-SHA256:ECDHE:ECDH:AES:HIGH:!NULL:!aNULL:!MD5:!ADH:!RC4;
        ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
        ssl_prefer_server_ciphers on;

        root         /usr/share/nginx/html;

        # 开启gzip
        gzip on;

        # 启用gzip压缩的最小文件，小于设置值的文件将不会压缩
        gzip_min_length 1k;

        # gzip 压缩级别，1-9，数字越大压缩的越好，也越占用CPU时间，后面会有详细说明
        gzip_comp_level 3;

        # 进行压缩的文件类型。javascript有多种形式。其中的值可以在 mime.types 文件中找到。
        gzip_types text/plain application/javascript application/x-javascript text/css application/xml text/javascript application/x-httpd-php image/jpeg image/gif image/png application/vnd.ms-fontobject font/ttf font/opentype font/x-woff image/svg+xml;

        # 是否在http header中添加Vary: Accept-Encoding，建议开启
        gzip_vary on;

        # 禁用IE 6 gzip
        gzip_disable "MSIE [1-6]\.";

        # 设置压缩所需要的缓冲区大小
        gzip_buffers 32 4k;

        # 设置gzip压缩针对的HTTP协议版本
        gzip_http_version 1.0;

        #后台服务配置，配置了这个location便可以通过http://域名/jeecg-boot/xxxx 访问
        location ^~ /jeecg-boot {
            proxy_pass              http://172.18.100.100:8080/jeecg-boot/;
            proxy_set_header        Host 172.18.100.100;
            proxy_set_header        X-Real-IP $remote_addr;
            proxy_set_header        X-Forwarded-For $proxy_add_x_forwarded_for;
        }

        #设置Rest风格的API
        location ^~ /api {
            rewrite ^/(.*) /$1 break;
            proxy_redirect off;
            proxy_set_header Host $host;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_pass http://cwbase/;
        }

        #解决Router(mode: 'history')模式下，刷新路由地址不能找到页面的问题
        location / {

            root   /usr/share/nginx/html;
            index  index.html index.htm;

            if (!-e $request_filename) {
                rewrite ^(.*)$ /index.html?s=$1 last;
                break;
            }
            # 缓存使用前面定义的内存区域
            proxy_cache web_cache;
            # 对于 200 和 304 的响应码进行缓存，过期时间为 1 分钟，这会覆盖前面定义的 10 分钟过期时间
            proxy_cache_valid 200 206 304 301 302 10d;
            # 设置缓存的 key
            proxy_cache_key  $scheme$host$request_uri;

            #设置超时时间
            expires 30d;
        }

        location ^~ /jeecg-boot/sys/common/view {
            alias   /root/jeecg/upFiles/;
            index  index.html index.htm;
        }

    }

}

API参考

HTTP Type	API URL	Comments
GET	/	Gets all REST APIs
GET	/api/tableName	Lists rows of table
POST	/api/tableName	Create a new row
PUT	/api/tableName	Replaces existing row with new row
POST	/api/tableName/bulk	Create multiple rows - send object array in request body
GET	/api/tableName/bulk	Lists multiple rows - /api/tableName/bulk?_ids=1,2,3
DELETE	/api/tableName/bulk	Deletes multiple rows - /api/tableName/bulk?_ids=1,2,3
GET	/api/tableName/:id	Retrieves a row by primary key
PATCH	/api/tableName/:id	Updates row element by primary key
DELETE	/api/tableName/:id	Delete a row by primary key
GET	/api/tableName/findOne	Works as list but gets single record matching criteria
GET	/api/tableName/count	Count number of rows in a table
GET	/api/tableName/distinct	Distinct row(s) in table - /api/tableName/distinct?_fields=col1
GET	/api/tableName/:id/exists	True or false whether a row exists or not
GET	/api/parentTable/:id/childTable	Get list of child table rows with parent table foreign key
GET	/api/tableName/aggregate	Aggregate results of numeric column(s)
GET	/api/tableName/groupby	Group by results of column(s)
GET	/api/tableName/ugroupby	Multiple group by results using one call
GET	/api/tableName/chart	Numeric column distribution based on (min,max,step) or(step array) or (automagic)
GET	/api/tableName/autochart	Same as Chart but identifies which are numeric column automatically - gift for lazy while prototyping
GET	/api/xjoin	handles join
GET	/dynamic	execute dynamic mysql statements with params
GET	/upload	upload single file
GET	/uploads	upload multiple files
GET	/download	download a file
GET	/api/tableName/describe	describe each table for its columns
GET	/api/tables	get all tables in database
GET	/_health	gets health of process and mysql -- details query params for more details
GET	/_version	gets version of Xmysql, mysql, node

星河_赵梓宇

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
通过NodeJS自动生成的MySQL的REST风格API

通过NodeJS自动生成的MySQL的REST风格API前置条件安装NodeJS，requires node >= 7.6.0，目前使用的稳定版NodeJS v10.x安装XMYSQLxmysql requires node >= 7.6.0npm install -g xmysqlxmysql -h localhost -u mysqlUserna...
复制链接

扫一扫