mysql-rest

v0.6.27

Published

8 months ago

One command to generate REST APIs for any MySql database, support multi databases

Downloads

1,563

0High
0Medium
0Low

serveryang

mysql-rest-api-generator node-mysql-rest-api rest restful rest-apis

MySQL REST API Server

支持MySQL、TiDB、达梦数据库的REST API服务器，提供完整的数据库操作接口。

数据库配置

本项目支持多种数据库，并提供丰富的命令行参数配置：

支持的数据库

MySQL: 原生MySQL数据库
TiDB: 分布式NewSQL数据库
达梦: 国产数据库

配置文档

MySQL配置参数 - MySQL/TiDB数据库连接和连接池配置
达梦配置参数 - 达梦数据库连接和连接池配置
生产环境配置 - 生产环境优化配置指南

快速开始

MySQL数据库

# 基本连接
xmysql --dialect mysql -h localhost -u root -p password

# 高并发配置
xmysql --dialect mysql -h localhost -u root -p password \
  --mysqlConnectionLimit 100 --mysqlMaxIdle 50 \
  --mysqlQueueLimit 500 --mysqlIdleTimeout 30000

达梦数据库

# 基本连接
xmysql --dialect dameng -h localhost -o 5236 -u SYSDBA -p SYSDBA

# 高并发配置(5000+并发)
xmysql --dialect dameng -h localhost -o 5236 -u SYSDBA -p SYSDBA \
  --dmPoolMax 250 --dmPoolMin 50 --dmPoolTimeout 90 \
  --dmQueueMax 15000 --dmQueueTimeout 90000 --dmTestOnBorrow

# 生产环境配置(关闭HTTP访问日志和SQL查询日志)
xmysql --dialect dameng -h localhost -o 5236 -u SYSDBA -p SYSDBA \
  --dmPoolMax 250 --dmPoolMin 50 --disableAccessLog

构建和打包

本项目提供完整的构建工具链，支持生成自包含的单文件可执行程序，无需在目标系统安装Node.js环境。

构建自包含可执行文件

使用 pkg.sh 脚本可以构建自包含的可执行文件：

# 自动检测当前架构并构建
./pkg.sh

# 交叉编译：在x86_64上构建ARM64版本
TARGET_ARCH=aarch64 ./pkg.sh

# 交叉编译：在ARM64上构建x86_64版本  
TARGET_ARCH=x86_64 ./pkg.sh

# 指定Node.js版本（默认为v22.17.0）
NODE_VERSION=v20.11.1 ./pkg.sh

# 组合使用：交叉编译并指定Node.js版本
TARGET_ARCH=aarch64 NODE_VERSION=v20.11.1 ./pkg.sh

构建结果

构建完成后，在 bin/ 目录下会生成对应架构的可执行文件：

bin/mysql-rest-x86_64 - x86_64架构可执行文件
bin/mysql-rest-aarch64 - ARM64架构可执行文件

特性说明

真正的自包含：包含完整的Node.js运行时，无需目标系统安装Node.js
原生模块支持：完全支持dmdb、mysql2、snappy等原生模块
交叉编译：支持在x86_64和ARM64之间交叉编译
智能缓存：自动缓存Node.js二进制文件，加速后续构建
自动清理：运行时自动清理临时文件，防止磁盘空间浪费
构建后清理：每次构建完成后自动清理所有遗留的临时目录

使用构建的可执行文件

生成的可执行文件与原Node.js版本具有相同的命令行接口：

# MySQL数据库
./bin/mysql-rest-x86_64 --dialect mysql -h localhost -u root -p password

# 达梦数据库
./bin/mysql-rest-aarch64 --dialect dameng -h localhost -o 5236 -u SYSDBA -p SYSDBA

新增功能

_refby说明：表table_a中的id字段是否被table_b中的a_id字段所引用，ref_by_model为输出结果key字段名称，结果为true/false。
示例：
GET http://localhost:15000/api/dbname.table_a?_refby=id,dbname.table_b,a_id,ref_by_model
_include说明：表table_a中的b_id、c_id为其它表的外键，将其展开为具体表内容。
示例：
GET http://localhost:15000/api/dbname.table_a?_include=b_id,dbname.table_b,id;c_id,dbname.table_c,id
_refcheck说明：批量删除某个表的记录时，检查表中哪些列是否被其它表某列使用，如果该id被使用时，则不删除该id对应的记录。
示例：
DELETE http://localhost:15000/api/dbname.table_a/bulk?_ids=11,22,33,44&_refcheck=class_col_a,db.talbe_b,class;category_col_in_a,db.table_c.category
_sort说明：排序时，支持字段为字符串的列内容转成数字之后来排序。字符串默认按ASCII排序，当内容为中文时排序会有问题，因此需要将中文转成数字再排序。
示例（升序与降序）：
GET http://localhost:15000/api/dbname.table_a?_sort=score(高|5|中|4|低|3|信息|2)
GET http://localhost:15000/api/dbname.table_a?_sort=-score(高|5|中|4|低|3|信息|2)

新增raw接口：扩展dynamic接口，用于支持单次多条SQL语句提交。说明：

  POST /api/raw 
  [
    {
      "query": "lock table dbname.table_a write",
      "params": ""
    },
    {
      "query": "select * from dbname.table_a where id='6711131423589335041'",
      "params": ""
    },
    {
      "query": "UNLOCK TABLES",
      "params": ""
    }
  ]

返回结果:

[
    {
        "fieldCount": 0,
        "affectedRows": 0,
        "insertId": 0,
        "info": "",
        "serverStatus": 2,
        "warningStatus": 0,
        "changedRows": 0
    },
    [
        {
            "id": "6711131423589335041",
            "other_fields": ""
        }
    ],
    {
        "fieldCount": 0,
        "affectedRows": 0,
        "insertId": 0,
        "info": "",
        "serverStatus": 2,
        "warningStatus": 0,
        "changedRows": 0
    }
]

添加启动项参数--dialect：方言参数用于支持国产 TiDB、达梦8 数据库类型。示例：
${workspaceFolder}/bin/index.js -r 0.0.0.0 --dialect dameng -n 15236 -h 127.0.0.1 -o 5236 -u SYSDBA -p PASSWORD
${workspaceFolder}/bin/index.js -r 0.0.0.0 -n 13306 -h 127.0.0.1 -o 3306 -u root -p PASSWORD
使用pkg打包成二进制文件，无node依赖，同时支持x86及arm环境。 示例：
bin/mysql-rest-$(uname -m) -r 0.0.0.0 --dialect dameng -n 15236 -h 127.0.0.1 -o 5236 -u SYSDBA -p PASSWORD
bin/mysql-rest-$(uname -m) -r 0.0.0.0 -n 15000 -h 127.0.0.1 -o 33306 -u root -p PASSWORD

npm version

https://github.com/nocodb/nocodb

Xmysql : One command to generate REST APIs for any MySql database, support multi databases

Why this ?

Generating REST APIs for a MySql database which does not follow conventions of frameworks such as rails, django, laravel etc is a small adventure that one like to avoid ..

Hence this.

Setup and Usage

xmysql requires node >= 7.6.0

npm install -g xmysql

xmysql -h localhost -u mysqlUsername -p mysqlPassword -d databaseName

http://localhost:3000

That is it! Simple and minimalistic!

Happy hackery!

Example : Generate REST APIs for Magento

Features

Generates API for ANY MySql database :fire::fire:
Serves APIs irrespective of naming conventions of primary keys, foreign keys, tables etc :fire::fire:
Support for composite primary keys :fire::fire:
REST API Usual suspects : CRUD, List, FindOne, Count, Exists, Distinct
Bulk insert, Bulk delete, Bulk read :fire:
Relations
Pagination
Sorting
Column filtering - Fields :fire:
Row filtering - Where :fire:
Aggregate functions
Group By, Having (as query params) :fire::fire:
Group By, Having (as a separate API) :fire::fire:
Multiple group by in one API :fire::fire::fire::fire:
Chart API for numeric column :fire::fire::fire::fire::fire::fire:
Auto Chart API - (a gift for lazy while prototyping) :fire::fire::fire::fire::fire::fire:
XJOIN - (Supports any number of JOINS) :fire::fire::fire::fire::fire::fire::fire::fire::fire:
Supports views
Prototyping (features available when using local MySql server only)
- Run dynamic queries :fire::fire::fire:
- Upload single file
- Upload multiple files
- Download file
Health and version apis
Use more than one CPU Cores
Docker support and Nginx reverse proxy config :fire::fire::fire: - Thanks to @markuman
AWS Lambda Example - Thanks to @bertyhell :fire::fire::fire:

Use HTTP clients like Postman or similar tools to invoke REST API calls

Download node, mysql (setup mysql), sample database - if you haven't on your system.

API Overview

| 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 :fire:| /api/tableName/bulk | Create multiple rows - send object array in request body| | GET :fire:| /api/tableName/bulk | Lists multiple rows - /api/tableName/bulk?_ids=1,2,3 | | DELETE :fire:| /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 :fire:| /api/tableName/aggregate | Aggregate results of numeric column(s) | | GET :fire:| /api/tableName/groupby | Group by results of column(s) | | GET :fire:| /api/tableName/ugroupby | Multiple group by results using one call | | GET :fire:| /api/tableName/chart | Numeric column distribution based on (min,max,step) or(step array) or (automagic)| | GET :fire:| /api/tableName/autochart | Same as Chart but identifies which are numeric column automatically - gift for lazy while prototyping| | GET :fire:| /api/xjoin | handles join | | GET :fire:| /dynamic | execute dynamic mysql statements with params | | GET :fire:| /upload | upload single file | | GET :fire:| /uploads | upload multiple files | | GET :fire:| /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|

Relational Tables

xmysql identifies foreign key relations automatically and provides GET api.

/api/blogs/103/comments

eg: blogs is parent table and comments is child table. API invocation will result in all comments for blog primary key 103. :arrow_heading_up:

Support for composite primary keys

___ (three underscores)

/api/payments/103___JM555205

___ : If there are multiple primary keys - separate them by three underscores as shown

Pagination

_p & _size

_p indicates page and _size indicates size of response rows

By default 20 records and max of 100 are returned per GET request on a table.

/api/payments?_size=50

/api/payments?_p=2

/api/payments?_p=2&_size=50

When _size is greater than 100 - number of records defaults to 100 (i.e maximum)

When _size is less than or equal to 0 - number of records defaults to 20 (i.e minimum)

Order by / Sorting

ASC

/api/payments?_sort=column1

eg: sorts ascending by column1

DESC

/api/payments?_sort=-column1

eg: sorts descending by column1

Multiple fields in sort

/api/payments?_sort=column1,-column2

eg: sorts ascending by column1 and descending by column2

Column filtering / Fields

/api/payments?_fields=customerNumber,checkNumber

eg: gets only customerNumber and checkNumber in response of each record

/api/payments?_fields=-checkNumber

eg: gets all fields in table row but not checkNumber

Row filtering / Where

Comparison operators

eq      -   '='         -  (colName,eq,colValue)
ne      -   '!='        -  (colName,ne,colValue)
gt      -   '>'         -  (colName,gt,colValue)
gte     -   '>='        -  (colName,gte,colValue)
lt      -   '<'         -  (colName,lt,colValue)
lte     -   '<='        -  (colName,lte,colValue)
is      -   'is'        -  (colName,is,true/false/null)
in      -   'in'        -  (colName,in,val1,val2,val3,val4)
bw      -   'between'   -  (colName,bw,val1,val2) 
like    -   'like'      -  (colName,like,~name)   note: use ~ in place of % 
nlike   -   'not like'  -  (colName,nlike,~name)  note: use ~ in place of %

Use of comparison operators

/api/payments?_where=(checkNumber,eq,JM555205)~or((amount,gt,200)~and(amount,lt,2000))

Logical operators

~or     -   'or'
~and    -   'and'
~xor    -   'xor'

Use of logical operators

eg: simple logical expression

/api/payments?_where=(checkNumber,eq,JM555205)~or(checkNumber,eq,OM314933)

eg: complex logical expression

/api/payments?_where=((checkNumber,eq,JM555205)~or(checkNumber,eq,OM314933))~and(amount,gt,100)

eg: logical expression with sorting(_sort), pagination(_p), column filtering (_fields)

/api/payments?_where=(amount,gte,1000)&_sort=-amount&p=2&_fields=customerNumber

eg: filter of rows using _where is available for relational route URLs too.

/api/offices/1/employees?_where=(jobTitle,eq,Sales%20Rep)

FindOne

/api/tableName/findOne?_where=(id,eq,1)

Works similar to list but only returns top/one result. Used in conjunction with _where :arrow_heading_up:

Count

/api/tableName/count

Returns number of rows in table :arrow_heading_up:

Exists

/api/tableName/1/exists

Returns true or false depending on whether record exists :arrow_heading_up:

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

MySQL REST API Server

数据库配置

支持的数据库

配置文档

快速开始

MySQL数据库

达梦数据库

构建和打包

构建自包含可执行文件

构建结果

特性说明

使用构建的可执行文件

新增功能

Xmysql : One command to generate REST APIs for any MySql database, support multi databases

Why this ?

Setup and Usage

Example : Generate REST APIs for Magento

Features

API Overview

Relational Tables

Support for composite primary keys

___ (three underscores)

Pagination

_p & _size

Order by / Sorting

ASC

DESC

Multiple fields in sort

Column filtering / Fields

Row filtering / Where

Comparison operators

Use of comparison operators

Logical operators

Use of logical operators

FindOne

Count

Exists

Group By Having as query params

Group By Having as API

Group By, Order By

Aggregate functions

Union of multiple group by statements

Chart

Autochart

XJOIN

Xjoin query params and values:

Simple example of two table join:

Multiple tables join

Run dynamic queries

Upload single file

Upload multiple files

Download file

Health

Version

When to use ?

When NOT to use ?

Command line options

Docker

Debugging xmysql in docker.

Nginx Reverse Proxy Config with Docker

Tests : setup on local machine