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

MethodPathQuery ParamsDescription
GET/api/v1/tableNamewhere, limit, offset, sort, fields, mm, bt, hmList rows of the table
POST/api/v1/tableNameInsert row into table
PUT/api/v1/tableName/:idUpdate existing row in table
GET/api/v1/tableName/:idGet row by primary key
GET/api/v1/tableName/:id/existsCheck row with provided primary key exists or not
DELETE/api/v1/tableName/:idDelete existing row in table
GET/api/v1/tableName/findOnewhere, limit, offset, sort, fieldsFind first row which matches the conditions in table
GET/api/v1/tableName/groupby/:columnNameGroup by columns
GET/api/v1/tableName/distribution/:columnNameDistribute data based on column
GET/api/v1/tableName/distinct/:columnNameFind distinct column values
GET/api/v1/tableName/aggregate/:columnNameDo aggregation on columns
GET/api/v1/tableName/countwhereGet total rows count
POST/api/v1/tableName/bulkBulk row insert
PUT/api/v1/tableName/bulkBulk row update
DELETE/api/v1/tableName/bulkBulk row delete
* tableName - Alias of the corresponding table
* columnName - Alias of the column in table

Query params

NameAliasUse caseDefault valueExample value
wherewComplicated where conditions(colName,eq,colValue)~or(colName2,gt,colValue2)
Usage: Comparison operators
Usage: Logical operators
limitlNumber of rows to get(SQL limit value)1020
offsetoOffset for pagination(SQL offset value)020
sortsSort by column name, Use - as prefix for descending sortcolumn_name
fieldsfRequired column names in result*column_name1,column_name2
fields1f1Required column names in child result*column_name1,column_name2
btComma-separated belongs to tablesAll belongs to tablesclick 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 valueclick here for example
hmComma-separated has many tablesAll hasmany tablesclick 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 valueclick here for example
mmComma-separated many to many tablesAll many to many tablesclick 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 valueclick here for example

HasMany APIs

MethodPathQuery ParamsDescription
GET/api/v1/tableName/has/childTableNamewhere, limit, offset, sort, fields, fields1List rows of the table with children
GET/api/v1/tableName/:parentId/childTableNamewhere, limit, offset, sort, fields, fields1Get children under a certain parent
POST/api/v1/tableName/:parentId/childTableNameInsert children under a certain parent
GET/api/v1/tableName/:parentId/childTableName/findOnewhere, limit, offset, sort, fieldsFind children under a parent with conditions
GET/api/v1/tableName/:parentId/childTableName/countFind children count
GET/api/v1/tableName/:parentId/childTableName/:idFind child by id
PUT/api/v1/tableName/:parentId/childTableName/:idUpdate 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

MethodPathQuery ParamsDescription
GET/api/v1/childTableName/belongs/parentTablenamewhere, limit, offset, sort, fieldsList 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
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"
    }
]
[
    1,
    1
]

猡达笍

Bulk Delete

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

猡达笍

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"
}
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"
    }
]