Backend API, backbone to the Internet and world.

Generating REST APIs for a MySQL database which does not follow conventions of frameworks such as rails, Django, Laravel etc. is a small adventure.

xmysql rest api illustration for web applications

$ npm install -g xmysql

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

http://localhost:3000/

Generates API for any MySQL database and Serves APIs irrespective of naming conventions of primary keys, foreign keys, tables etc.

Support for composite primary keys

REST API Usual suspects

• CRUD — Get, Post, Put, Delete, List -Bulk insert, Bulk delete, Bulk read
• Exist Record
• findOne
• Count
• Distinct
• Table Description

• GET /api/tableName — Lists rows of table

• GET /api/tableName/:id — Retrieves a row by primary key

• /api/tableName/bulk — Lists multiple rows

• Example - /api/tableName/bulk?_ids=1,2,3

• /api/tableName/:id/exists — True or false whether a row exists or not

• Example - /api/tableName/1/exists

• /api/tableName/count — Count number of rows in a table

• /api/tableName/findOne — Works as list but gets single record matching criteria

• /api/tableName/distinct?_fields=col1 — Distinct row(s) in table

• /api/tableName/distinct

• /api/tableName/describe — describe each table for its columns

• /api/tables — get all tables in database

Relations

xMySQL identifies foreign key relations automatically and provides GET api

• /api/parentTable/:id/childTable

Example - /api/blogs/103/comments

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

Pagination

_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

Sorting

/api/payments?_sort=column1 (asc)

/api/payments?_sort=-column1 (desc)

/api/payments?_sort=column1,-column2 (multiple columns column1 asc and column2 desc)

Column filtering — Fields

Similar to projects, selected columns will be available in response

/api/payments?_fields=customerNumber,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 %

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

Logical operators

• ~or - ‘or’
• ~and - ‘and’
• ~xor - ‘xor’
• /api/payments?_where=(checkNumber,eq,JM555205)~or(checkNumber,eq,OM314933)

Example -

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

Aggregate functions

Retrieves all numeric aggregate of a column or multiple columns in a table.

• /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 } ]

• /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 } ]

Group By, Having (as query params)

Group by results of column(s) uisng query parameters

• GET /api/tableName/groupby

Example - SELECT country,count(*) FROM offices GROUP BY country

• /api/offices?_groupby=country

Example - SELECT country,count(1) as _count FROM offices GROUP BY country having _count > 1

• /api/offices?_groupby=country&_having=(_count,gt,1)

• GET /api/tableName/ugroupby

Group By, Having (as a separate API)

Group by results of column(s) using API field exposures, and

Example - SELECT country,count(*) FROM offices GROUP BY country

• /api/offices/groupby?_fields=country

SELECT country,city,count(*) FROM offices GROUP BY country,city

• /api/offices/groupby?_fields=country,city

SELECT country,city,count(*) as _count FROM offices GROUP BY country,city having _count > 1

• /api/offices/groupby?_fields=country,city&_having=(_count,gt,1)

Multiple group by in one API

Group by multiple columns in one API call using _fields query params — comes really handy

• /api/tableName/ugroupby

Example -

• http://localhost:3000/api/employees/ugroupby?_fields=jobTitle,reportsTo

Chart API for numeric column

Numeric column distribution based on (min, max, step) or(step array) or (automagic)

• /api/tableName/chart

Auto Chart API — (a gift for lazy while prototyping)

Same as Chart but identifies which are numeric column automatically — gift for lazy while prototyping

• /api/tableName/autochart

XJoins

XJOIN — (Supports any number of JOINS)

Xjoin query params and values:

_join : List of tableNames alternated by type of join to be made (_j, ij, lj, _rj)

alias.tableName : TableName as alias

_j : Join [ _j => join, _ij => ij, _lj => left join , _rj => right join)

_onNumber : Number ’n’ indicates condition to be applied for ‘n’th join between (n-1) and ‘n’th table in list Si

Example - Join 2 tables

• /api/xjoin?_join=pl.productlines,_j,pr.products&_on1=(pl.productline,eq,pr.productline)&_fields=pl.field1,pr.field2

Equivalent SQL SELECT pl.field1, pr.field2 FROM productlines as pl JOIN products as pr ON pl.productline = pr.productline

Example Join multiple tables

• /api/xjoin?_join=pl.productlines,_j,pr.products,_j,ord.orderDetails&_on1=(pl.productline,eq,pr.productline)&_on2=(pr.productcode,eq,ord.productcode)&_fields=pl.field1,pr.field2,ord.field3

Equivalent SQL SELECT pl.field1, pr.field2, ord.field3 FROM productlines as pl JOIN products as pr ON pl.productline = pr.productline JOIN orderdetails as ord ON pr.productcode = ord.productcode Xjoin response has aliases for fields like below aliasTableName + ‘_’ + columnName.

Supports views

Run dynamic queries

Execute dynamic MySQL statements with params namely query and params in POST request body.

• POST /dynamic

  { “query”: “select * from ?? limit 1,20”, “params”: [“customers”] }

Also, we can suffix dynamic queries for prototyping —

• POST /dynamic/weeklyReport

• POST /dynamic/user/update

Upload single file

• /upload url with multiform ‘field’ assigned to local file to be uploaded, returns uploaded file name else ‘upload failed’

• POST /upload curl —form file=@/Users/me/Desktop/a.png http://localhost:3000/upload

Upload multiple files

Do POST operation on /uploads url with multiform ‘fields’ assigned to local files to be uploaded and returns uploaded file names as string

• POST /uploads curl —form files=@/Users/me/Desktop/a.png —form files=@/Users/me/Desktop/b.png http://localhost:3000/uploads

Download file

Download a file, as per param

• 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

Health and version APIs

Gives health report and stats of MySQL server, and more details on process.

• /_health

response body {“process_uptime”:3.858,“mysql_uptime”:“2595”} /_version

response body {“Xmysql”:“0.4.1”,“mysql”:“5.7.15”,“node”:“8.2.1”}

Use more than one CPU Cores

Docker support and Nginx reverse proxy config

docker run -p 3000:80 -d markuman/xmysql:0.4.2

docker run -d
—network local_dev
—name xmysql
-p 3000:80
-e DATABASE_HOST=mysql_host
-e DATABASE_USER=root
-e DATABASE_PASSWORD=password
-e DATABASE_NAME=sys
markuman/xmysql:0.4.2 Supports nginx reverse proxy to expose to world.

Closure

You need just REST APIs for (ANY) MySQL database at blink of an eye. You are learning new frontend frameworks and need REST APIs for your MySQL database. Consider limitations, suitable for demo, hacks etc.

Early stage of project when API is not ready by backend team and frontend team is blocked, so they can leverage this hack to continue development.

And not suitable — If you are in need of a full blown MVC framework, ACL, Validations, Authorization etc. Happy No-Coding 😊 for Rest-API!

References —

• https://github.com/o1lab/xmysql