REST APIs

If you are using NocoDB with version 0.90 onwards, please check out the latest documentation here instead. The documentation for previous versions is no longer maintained and will be completely removed on 30 Jun 2022. See v0.90 Important Announcement for more.

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,not,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

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