REST APIs

Features

Automatic REST APIs for any SQL database
- Generates REST APIs for ANY MySql, Postgres, MSSQL, Sqlite database
- Serves APIs irrespective of naming conventions of primary keys, foreign keys, tables etc
- Support for composite primary keys
- REST APIs :
  - CRUD, List, FindOne, Count, Exists, Distinct (Usual suspects)
    - Pagination
    - Sorting
    - Column filtering - Fields
    - Row filtering - Where
  - Bulk insert, Bulk delete, Bulk read
  - Relations - automatically detected
  - Aggregate functions
- More
  - Upload single file
  - Upload multiple files
  - Download file
- Authentication
- Access Control

Data APIs

Method	Path	Query Params	Description
GET	/api/v1/tableName	where, limit, offset, sort, fields, mm, bt, hm	List rows of the table
POST	/api/v1/tableName		Insert row into table
PUT	/api/v1/tableName/:id		Update existing row in table
GET	/api/v1/tableName/:id		Get row by primary key
GET	/api/v1/tableName/:id/exists		Check row with provided primary key exists or not
DELETE	/api/v1/tableName/:id		Delete existing row in table
GET	/api/v1/tableName/findOne	where, limit, offset, sort, fields	Find first row which matches the conditions in table
GET	/api/v1/tableName/groupby/:columnName		Group by columns
GET	/api/v1/tableName/distribution/:columnName		Distribute data based on column
GET	/api/v1/tableName/distinct/:columnName		Find distinct column values
GET	/api/v1/tableName/aggregate/:columnName		Do aggregation on columns
GET	/api/v1/tableName/count	where	Get total rows count
POST	/api/v1/tableName/bulk		Bulk row insert
PUT	/api/v1/tableName/bulk		Bulk row update
DELETE	/api/v1/tableName/bulk		Bulk row delete

* tableName - Alias of the corresponding table
* columnName - Alias of the column in table

Query params

Name	Alias	Use case	Default value	Example value
where	w	Complicated where conditions		`(colName,eq,colValue)~or(colName2,gt,colValue2)` Usage: Comparison operators Usage: Logical operators
limit	l	Number of rows to get(SQL limit value)	10	20
offset	o	Offset for pagination(SQL offset value)	0	20
sort	s	Sort by column name, Use `-` as prefix for descending sort		column_name
fields	f	Required column names in result	*	column_name1,column_name2
fields1	f1	Required column names in child result	*	column_name1,column_name2
bt		Comma-separated belongs to tables	`All belongs to tables`	click here for example
bfields`<p>`	bf`<p>`	Required belongs to table column names in result. Where `<p>` refers to position of table name in `bt` parameter(starts from `1`)	primary key and primary value	click here for example
hm		Comma-separated has many tables	`All hasmany tables`	click here for example
hfields`<p>`	hf`<p>`	Required has many table column names in result. Where `<p>` refers to position of table name in `hm` parameter(starts from `1`)	primary key and primary value	click here for example
mm		Comma-separated many to many tables	`All many to many tables`	click here for example
mfields`<p>`	mf`<p>`	Required many to many table column names in result. Where `<p>` refers to position of table name in `mm` parameter(starts from `1`)	primary key and primary value	click here for example

HasMany APIs

Method	Path	Query Params	Description
GET	/api/v1/tableName/has/childTableName	where, limit, offset, sort, fields, fields1	List rows of the table with children
GET	/api/v1/tableName/:parentId/childTableName	where, limit, offset, sort, fields, fields1	Get children under a certain parent
POST	/api/v1/tableName/:parentId/childTableName		Insert children under a certain parent
GET	/api/v1/tableName/:parentId/childTableName/findOne	where, limit, offset, sort, fields	Find children under a parent with conditions
GET	/api/v1/tableName/:parentId/childTableName/count		Find children count
GET	/api/v1/tableName/:parentId/childTableName/:id		Find child by id
PUT	/api/v1/tableName/:parentId/childTableName/:id		Update child by id

* tableName - Name of the parent table
* parentId - Id in parent table
* childTableName - Name of the child table * id - Id in child table

BelongsTo APIs

Method	Path	Query Params	Description
GET	/api/v1/childTableName/belongs/parentTablename	where, limit, offset, sort, fields	List rows of the table with parent

* tableName - Name of the parent table
* childTableName - Name of the child table

Comparison operators

eq      -   '='         -  (colName,eq,colValue)
not     -   '!='        -  (colName,ne,colValue)
gt      -   '>'         -  (colName,gt,colValue)
ge      -   '>='        -  (colName,ge,colValue)
lt      -   '<'         -  (colName,lt,colValue)
le      -   '<='        -  (colName,le,colValue)
is      -   'is'        -  (colName,is,true/false/null)
isnot   -   'is not'    -  (colName,isnot,true/false/null)
in      -   'in'        -  (colName,in,val1,val2,val3,val4)
btw     -   'between'   -  (colName,btw,val1,val2) 
nbtw    -  'not between'-  (colName,nbtw,val1,val2) 
like    -   'like'      -  (colName,like,%name)

Example use of comparison operators - complex example

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

Logical operators

~or     -   'or'
~and    -   'and'
~not    -   'not'

Examples

List

GET /api/v1/country

[
  {
      "country_id": 1,
      "country": "Afghanistan",
      "last_update": "2006-02-14T23:14:00.000Z"
  }
]

List + where

GET /api/v1/country?where=(country,like,United%)

[
  {
      "country_id": 101,
      "country": "United Arab Emirates",
      "last_update": "2006-02-15T04:44:00.000Z"
  },
  {
      "country_id": 102,
      "country": "United Kingdom",
      "last_update": "2006-02-15T04:44:00.000Z"
  },
  {
      "country_id": 103,
      "country": "United States",
      "last_update": "2006-02-15T04:44:00.000Z"
  }
]

Usage : comparison operators

List + where + sort

GET  /api/v1/country?where=(country,like,United%)&sort=-country

[
    {
        country_id: 103,
        country: "United States",
        last_update: "2006-02-15T04:44:00.000Z"
    },
    {
        country_id: 102,
        country: "United Kingdom",
        last_update: "2006-02-15T04:44:00.000Z"
    },
    {
        country_id: 101,
        country: "United Arab Emirates",
        last_update: "2006-02-15T04:44:00.000Z"
    }
]

List + where + sort + offset

GET  /api/v1/country?where=(country,like,United%)&sort=-country&offset=1

[
    {
        country_id: 102,
        country: "United Kingdom",
        last_update: "2006-02-15T04:44:00.000Z"
    },
    {
        country_id: 101,
        country: "United Arab Emirates",
        last_update: "2006-02-15T04:44:00.000Z"
    }
]

List + limit

GET  /api/v1/country?limit=6

[
    {
        "country_id": 1,
        "country": "Afghanistan",
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "country_id": 2,
        "country": "Algeria",
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "country_id": 3,
        "country": "American Samoa",
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "country_id": 4,
        "country": "Angola",
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "country_id": 5,
        "country": "Anguilla",
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "country_id": 6,
        "country": "Argentina",
        "last_update": "2006-02-14T23:14:00.000Z"
    }
]

⤴️

Get By Primary Key

GET  /api/v1/country/1

{
    "country_id": 1,
    "country": "Afghanistan",
    "last_update": "2006-02-14T23:14:00.000Z"
}

⤴️

Create

POST  /api/v1/country
{
    "country": "Afghanistan"
}

{
    "country_id": 1,
    "country": "Afghanistan",
    "last_update": "2006-02-14T23:14:00.000Z"
}

⤴️

Update

PUT  /api/v1/country/1
{
    "country": "Afghanistan1"
}

{
    "country_id": 1,
    "country": "Afghanistan1",
    "last_update": "20020-02-14T23:14:00.000Z"
}

⤴️

Exists

DELETE  /api/v1/country/1/exists

true

⤴️

Delete

DELETE  /api/v1/country/1

⤴️

Find One

GET  /api/v1/country/findOne?where=(country_id,eq,1)

{
    "country_id": 1,
    "country": "Afghanistan",
    "last_update": "2006-02-14T23:14:00.000Z"
}

⤴️

Group By

GET  /api/v1/country/groupby/last_update

[
    {
        "count": 109,
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "count": 1,
        "last_update": "2020-01-06T15:18:13.000Z"
    },
    {
        "count": 1,
        "last_update": "2020-01-06T14:33:21.000Z"
    }
]

⤴️

Distribution

GET  /api/v1/payment/distribution/amount

[
    {
        "count": 8302,
        "range": "0-4"
    },
    {
        "count": 3100,
        "range": "5-8"
    },
    {
        "count": 371,
        "range": "9-11.99"
    }
]

⤴️

Distinct

GET  /api/v1/country/distinct/last_update

[
    {
        "last_update": "2006-02-14T23:14:00.000Z"
    },
    {
        "last_update": "2020-01-06T15:18:13.000Z"
    },
    {
        "last_update": "2020-01-06T14:33:21.000Z"
    },
    {
        "last_update": "2020-01-07T13:42:01.000Z"
    }
]

⤴️

Aggregate

GET  /api/v1/payment/aggregate/amount?func=min,max,avg,sum,count

[
    {
        "min": 0,
        "max": 11.99,
        "avg": 4.200743,
        "sum": 67413.52,
        "count": 16048
    }
]

⤴️

Count

GET  /api/v1/country/count

{
    "count": 161
}

⤴️

Nested Parent(Belongs To)

GET  /api/v1/City?bt=country&bfields1=Country,CountryId

[
  {
    "CityId": 1,
    "City": "A Corua (La Corua)",
    "CountryId": 87,
    "LastUpdate": "2006-02-14T23:15:25.000Z",
    "CountryRead": {
      "CountryId": 87,
      "Country": "Spain"
    }
  },
  {
    "CityId": 2,
    "City": "Abha",
    "CountryId": 82,
    "LastUpdate": "2006-02-14T23:15:25.000Z",
    "CountryRead": {
      "CountryId": 82,
      "Country": "Saudi Arabia"
    }
  },
  {
    "CityId": 3,
    "City": "Abu Dhabi",
    "CountryId": 101,
    "LastUpdate": "2006-02-14T23:15:25.000Z",
    "CountryRead": {
      "CountryId": 101,
      "Country": "United Arab Emirates"
    }
  }
]

⤴️

Nested Children(Has Many)

GET  /api/v1/Country?hm=city&hfields1=City,CityId

[
  {
    "CountryId": 1,
    "Country": "Afghanistan",
    "LastUpdate": "2021-11-15T14:11:37.000Z",
    "CityList": [
      {
        "CityId": 251,
        "City": "Kabul",
        "CountryId": 1
      }
    ]
  },
  {
    "CountryId": 2,
    "Country": "Algeria",
    "LastUpdate": "2021-11-15T14:11:42.000Z",
    "CityList": [
      {
        "CityId": 59,
        "City": "Batna",
        "CountryId": 2
      },
      {
        "CityId": 63,
        "City": "Bchar",
        "CountryId": 2
      },
      {
        "CityId": 483,
        "City": "Skikda",
        "CountryId": 2
      }
    ]
  },
  {
    "CountryId": 3,
    "Country": "American Samoa",
    "LastUpdate": "2006-02-14T23:14:00.000Z",
    "CityList": [
      {
        "CityId": 516,
        "City": "Tafuna",
        "CountryId": 3
      }
    ]
  }
]

⤴️

Nested Children(Many To Many)

GET  /api/v1/Actor?l=3&mm=film&mfields1=ReleaseYear

[
  {
    "ActorId": 1,
    "FirstName": "PENELOPE",
    "LastName": "GUINESS",
    "LastUpdate": "2021-11-24T14:43:23.000Z",
    "FilmMMList": [
      {
        "actor_actor_id": 1,
        "FilmId": 1,
        "Title": "Test Movie 1",
        "ReleaseYear": 2001
      }
    ]
  },
  {
    "ActorId": 2,
    "FirstName": "NICK",
    "LastName": "WAHLBERG",
    "LastUpdate": "2006-02-14T23:04:33.000Z",
    "FilmMMList": [
      {
        "actor_actor_id": 2,
        "FilmId": 1,
        "Title": "Test Movie 2",
        "ReleaseYear": 2002
      }
    ]
  },
  {
    "ActorId": 3,
    "FirstName": "ED",
    "LastName": "CHASE",
    "LastUpdate": "2006-02-14T23:04:33.000Z",
    "FilmMMList": [
      {
        "actor_actor_id": 3,
        "FilmId": 1,
        "Title": "Test Movie 3",
        "ReleaseYear": 2000
      }
    ]
  }
]

⤴️

Bulk Insert

POST  /api/v1/country/bulk
[
    {
        "country": "test 1"
    },
    {
        "country": "test 2"
    }
]

[
    10262
]

⤴️

Bulk Update

PUT  /api/v1/country/bulk
[
    {
        "country_id" : 10261,
        "country": "test 3"
    },
    {
        "country_id" : 10262,
        "country": "test 4"
    }
]

⤴️

Bulk Delete

DELETE  /api/v1/country/bulk
[
    {
        "country_id" : 10261
    },
    {
        "country_id" : 10262
    }
]

⤴️

With Children

GET  /api/v1/country/has/city

[
    {
        "country_id": 1,
        "country": "Afghanistan",
        "last_update": "2006-02-14T23:14:00.000Z",
        "city": [
            {
                "city_id": 251,
                "city": "Kabul",
                "country_id": 1,
                "last_update": "2006-02-14T23:15:25.000Z"
            },
            ...
        ]
    }
]

⤴️

Children of parent

GET  /api/v1/country/1/city

[
    {
        "city_id": 251,
        "city": "Kabul",
        "country_id": 1,
        "last_update": "2006-02-14T23:15:25.000Z"
    }
]

⤴️

Insert to child table

POST  /api/v1/country/1/city
    {
        "city": "test"
    }

{
    "city": "test",
    "country_id": "1",
    "city_id": 10000
}

⤴️

Findone under parent

GET  /api/v1/country/1/city/findOne?where=(city,like,ka%)

{
    "city": "test",
    "country_id": "1",
    "city_id": 10000
}

⤴️

Child count

GET  /api/v1/country/1/city/count

{
    "count": 37
}

⤴️

Get Child By Primary key

GET  /api/v1/country/1/city/251

[
    {
        "city_id": 251,
        "city": "Kabul",
        "country_id": 1,
        "last_update": "2006-02-14T23:15:25.000Z"
    }
]

⤴️

Update Child By Primary key

POST  /api/v1/country/1/city/251
{
    "city": "Kabul-1"
}

⤴️

Get parent and chlidren within

GET  /api/v1/country/has/city

[
    {
        "city_id": 1,
        "city": "sdsdsdsd",
        "country_id": 87,
        "last_update": "2020-01-02T14:50:49.000Z",
        "country": {
            "country_id": 87,
            "country": "Spain",
            "last_update": "2006-02-14T23:14:00.000Z"
        }
    },
    ...
]

⤴️

Get table and parent class within

GET  /api/v1/city/belongs/country

[
    {
        city_id: 1,
        city: "A Corua (La Corua)",
        country_id: 87,
        last_update: "2006-02-15T04:45:25.000Z",
        country: {
        country_id: 87,
        country: "Spain",
        last_update: "2006-02-15T04:44:00.000Z"
    }
]