Note : More cool things to be committed this week - 👂 so stay tuned 👀 😇 🎃 🎃
Generating REST APIs quickly for a MySql database which does not follow conventions of frameworks such as rails, django etc is a small adventure that one rather like to avoid ..
Hence this.
Powered by node packages : (express, mysql) => { xmysql }
npm install -g xmysql
xmysql -h localhost -u mysqlUsername -p mysqlPassword -d databaseName
http://localhost:3000
That's it!
- Generates API for ANY MySql database
- Serves APIs irrespective of naming conventions of primary keys, foreign keys, tables etc
- CRUD : Usual suspects
- Support for composite primary keys
- Pagination
- Sorting
- Fields
- Group By
- Group By, Order By
- Aggregate functions 🎉
- Relations
- Run dynamic queries
- Upload single file
- Upload multiple files
- Download file
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.
Root URL (localhost:3000/) returns all REST API urls for each table in schema.
- GET /api/tableName
- POST /api/tableName
- GET /api/tableName/:id
- PUT /api/tableName/:id
- GET /api/tableName/count
- GET /api/tableName/exists
- GET /api/tableName/groupby
- GET /api/tableName/aggregate
- GET /api/parentTable/:id/childTable
- DELETE /api/tableName/:id
- POST /dynamic
- GET /api/tableName/describe
- GET /api/tables
/api/payments/103___JM555205
___ : If there are multiple primary keys - seperate them by three underscores as shown
_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
/api/payments?_sort=column1
eg: sorts ascending by column1
/api/payments?_sort=-column1
eg: sorts descending by column1
/api/payments?_sort=column1,-column2
eg: sorts ascending by column1 and descending by column2
/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
/api/offices/groupby?_fields=country
eg: SELECT country,count(*) FROM offices GROUP BY country
/api/offices/groupby?_fields=country,city
eg: SELECT country,city,count(*) FROM offices GROUP BY country,city
/api/offices/groupby?_fields=country,city&sort=city
eg: SELECT country,city,count(*) FROM offices GROUP BY country,city ORDER BY city ASC
/api/offices/groupby?_fields=country,city&sort=city,country
eg: SELECT country,city,count(*) FROM offices GROUP BY country,city ORDER BY city ASC, country ASC
/api/offices/groupby?_fields=country,city&sort=city,-country
eg: SELECT country,city,count(*) FROM offices GROUP BY country,city ORDER BY city ASC, country DESC
http://localhost:3000/api/payments/aggregate?_fields=amount
response body
[
{
"min_of_amount": 615.45,
"max_of_amount": 120166.58,
"avg_of_amount": 32431.645531,
"sum_of_amount": 8853839.23,
"stddev_of_amount": 20958.625377426568,
"variance_of_amount": 439263977.71130896
}
]
eg: retrieves all numeric aggregate of a column in a table
http://localhost:3000/api/orderDetails/aggregate?_fields=priceEach,quantityOrdered
response body
[
{
"min_of_priceEach": 26.55,
"max_of_priceEach": 214.3,
"avg_of_priceEach": 90.769499,
"sum_of_priceEach": 271945.42,
"stddev_of_priceEach": 36.576811252187795,
"variance_of_priceEach": 1337.8631213781719,
"min_of_quantityOrdered": 6,
"max_of_quantityOrdered": 97,
"avg_of_quantityOrdered": 35.219,
"sum_of_quantityOrdered": 105516,
"stddev_of_quantityOrdered": 9.832243813502942,
"variance_of_quantityOrdered": 96.67301840816688
}
]
eg: retrieves numeric aggregate can be done for multiple columns too
xmysql identifies foreign key relations automatically and provides GET api.
/api/customers/103/payments
eg: Customers is parent table and payments is child table. API invocation will result in all payments with customer 103.
Dynamic queries on a database can be run by POST method to URL localhost:3000/dynamic
This is enabled only when using local mysql server i.e -h localhost or -h 127.0.0.1 option.
Post body takes two fields : query and params.
query: SQL query or SQL prepared query (ones with ?? and ?)
params : parameters for SQL prepared query
POST /dynamic
{
"query": "select * from ?? limit 1,20",
"params": ["customers"]
}
POST /upload
Do POST operation on /upload url with multiform 'field' assigned to local file to be uploaded
eg: curl --form file=@/Users/me/Desktop/a.png http://localhost:3000/upload
returns uploaded file name else 'upload failed'
(Note: POSTMAN has issues with file uploading hence examples with curl)
POST /uploads
Do POST operation on /uploads url with multiform 'fields' assigned to local files to be uploaded
Notice 's' near /api/uploads and files in below example
eg: curl --form files=@/Users/me/Desktop/a.png --form files=@/Users/me/Desktop/b.png http://localhost:3000/uploads
returns uploaded file names as string
http://localhost:3000/download?name=fileName
For upload and download of files -> you can specify storage folder using -s option Upload and download apis are available only with local mysql server
- You need just REST APIs without much hassle for (ANY) MySql database.
- You are learning new frontend frameworks and need REST APIs for your MySql database.
- You are working on a demo, hacks etc
- If you are in need of a full blown MVC framework, ACL, Authorisation etc - Not this.
- Other times not mentioned in when to use section
Options:
-V, --version output the version number
-h, --host <n> hostname of mysql
-d, --database <n> database schema name
-u, --user <n> username of database / root by default
-p, --password <n> password of database / empty by default
-n, --portNumber <n> port number / 3000 by default
-s, --storageFolder <n> storage folder / current working dir by default
-h, --help output usage information
Examples:
$ xmysql -u username -p password -d databaseSchema