Representation State Transfer is the new industry standard of building technology neutral API’s. REST API are essentially web services to read or transfer data. REST API’s can be build on top of most relational databases. Mysql being the most popular database around. If you have nodejs and mysql already installed, you can build a REST API for your mysql db in less than 2 minutes 🙂
Before we begin we must have nodejs and mysql running on the server. If you don’t have nodejs, you can check my other article on how to install node.js on Cent OS Linux : https://easyoradba.com/2018/05/08/install-nodejs-on-centos-6-7/
And to quickly install mysql refer to my another of my blog posts 🙂 https://easyoradba.com/2018/05/18/install-mysql-database-along-with-phpadmin-on-centos-6-7/
Once you have mysql and nodejs setup. You can install and spin up REST API’s doing the below steps
1.Install xmysql using node package manager
$ npm install -g xmysql
2. Create a new user, a new test database and a new table with few records
mysql -u root -p mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY 'test123'; mysql> CREATE DATABASE easyoradba; mysql> USE easyoradba; mysql> CREATE TABLE emp ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) ); mysql> INSERT INTO emp ( id, name ) VALUES ( null, 'SHADAB' ); mysql> select * from emp; +----+--------+ | id | name | +----+--------+ | 1 | SHADAB | +----+--------+ 1 row in set (0.00 sec) mysql> exit;
3. Generate the REST API for db ‘easyoradba’ using xmysql
$ xmysql -h localhost -u test -p test123 -d easyoradba -n 8899 -r 192.168.1.46 Generating REST APIs at the speed of your thought.. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Database : easyoradba Number of Tables : 1 REST APIs Generated : 19 Xmysql took : 0.3 seconds - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -h hostname of your mysql DB server -u username of mysql -p password for mysql user -d DB name for which API needs to be generated -n port number to create the REST service -r your server IP, if left blank it will create with default value localhost
4. Check the REST API service and Call it from Postman
Go to browser and check the ip with defined port number : http://192.168.1.46:8899/
[{"resource":"emp","routes":[{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/describe"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/count"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/groupby"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/distinct"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/ugroupby"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/chart"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/aggregate"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/findOne"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/autoChart"},{"httpType":"post","routeUrl":"http://192.168.1.46:8899/api/emp"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp"},{"httpType":"post","routeUrl":"http://192.168.1.46:8899/api/emp/bulk"},{"httpType":"delete","routeUrl":"http://192.168.1.46:8899/api/emp/bulk"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/bulk"},{"httpType":"put","routeUrl":"http://192.168.1.46:8899/api/emp"},{"httpType":"patch","routeUrl":"http://192.168.1.46:8899/api/emp/:id"},{"httpType":"delete","routeUrl":"http://192.168.1.46:8899/api/emp/:id"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/:id"},{"httpType":"get","routeUrl":"http://192.168.1.46:8899/api/emp/:id/exists"}]},"http://192.168.1.46:8899/api/tables","http://192.168.1.46:8899/api/xjoin","http://192.168.1.46:8899/api/dynamic","/upload","/uploads","/download"]
Open Postman and call the URL with GET method to fetch the data
And voila ! There you have it an REST API running on top of a mysql database in less than 5 minutes :)))
