通过NodeJS自动生成的MySQL的REST风格API
程序员文章站
2024-02-23 09:21:46
...
通过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 |