CMS API | HubDB

​

Rate limits

• Any GET requests made that don’t require authentication (including client-side JavaScript requests) are limited to 10 requests per second. These requests won’t count towards the daily limit.
• All other requests using authentication follow the standard limits.

​

Draft vs live tables

​

Create a HubDB table

// Example request
{
  "name": "test_table",
  "label": "Test Table",
  "useForPages": false,
  "allowPublicApiAccess": false,
  "allowChildTables": true,
  "enableChildTablePages": false,
  "columns": []
}

​

Add table columns

// Example request
{
  "label": "Test Table",
  "name": "test_table",
  "columns": [
    {
      "name": "text_column",
      "label": "Text Column",
      "archived": false,
      "type": "TEXT"
    },
    {
      "name": "number_column",
      "label": "Number Column",
      "archived": false,
      "type": "NUMBER"
    },
    {
      "name": "multiselect",
      "label": "Multi Select Column",
      "archived": false,
      "type": "multiselect",
      "options": [
        {
          "name": "Option 1",
          "type": "option"
        },
        {
          "name": "Option 2",
          "type": "option"
        }
      ]
    }
  ],
  "useForPages": true,
  "allowChildTables": true,
  "enableChildTablePages": false,
  "allowPublicApiAccess": false
}

​

Add table rows

// Example request
{
  "values": {
    "text_column": "sample text value",
    "number_column": 76,
    "rich_text_column": "<strong>This is a styled paragraph.</strong>",
    "date_column": 1591228800000,
    "date_time_column": 1604450520000,
    "boolean_column": 1,
    "select_column": {
      "name": "option 1",
      "type": "option"
    },
    "multiselect_column": [
      {
        "name": "Option 1",
        "type": "option"
      },
      {
        "name": "Option 2",
        "type": "option"
      }
    ],
    "url_column": "https://www.hubspot.com/marketing",
    "video_column": 3392210008,
    "image_column": {
      "url": "https://f.hubspotusercontentqa00.net/hubfs/99992002/image3%20(1).jpg",
      "width": 1600,
      "height": 900,
      "type": "image"
    },
    "foreign_id_column": [
      {
        "id": "4364402239",
        "type": "foreignid"
      },
      {
        "id": "4364402240",
        "type": "foreignid"
      }
    ]
  },
  "path": "test_path",
  "name": "test_title",
  "childTableId": "1902373"
}

​

Import rows from CSV

• config: a set of JSON-formatted options for the import.
• file: the CSV file that you want to import.

// Example config JSON
{
  "skipRows": 1,
  "separator": ",",
  "idSourceColumn": 1,
  "resetTable": false,
  "columnMappings": [
    {
      "target": 1,
      "source": 2
    },
    {
      "target": 2,
      "source": "zip_code"
    }
  ],
  "primaryKeyColumn": "name",
  "encoding": "utf-8",
  "format": "csv"
}

curl -L -X POST 'https://api.hubspotqa.com/hubdb/api/v2/tables/xxxxxx/import?portalId=xxxxxxx' \
-H 'Content-Type: multipart/form-data' \
-F 'config="{\"skipRows\":1,\"format\":\"csv\",\"separator\":\",\",\"encoding\":\"iso-8859-1\",\"columnMappings\":[{\"target\":1,\"source\":7},{\"target\":3,\"source\":8}],\"idSourceColumn\":1,\"pathSourceColumn\":null,\"nameSourceColumn\":null,\"childTableSourceColumn\":null,\"resetTable\":true}"' \
-F 'file=@"/Users/xxxxxxxxxxxxx/Downloads/filename.csv"'

​

Date formatting

• yyyy/mm/dd
• yyyy/mm/dd
• mm/dd/yyyy
• mm/dd/yy

• The 1st of March in the year 2022
• Fri Mar 4 2022
• March 4th '22

• next Thursday
• Today
• tomorrow
• 3 days from now

​

Reset options

• If resetTable is set to true:
  • If the rows in the CSV file does not have a row ID column (hs_id or row ID is specified as 0, those rows will be inserted with the new row IDs generated.
  • If the row IDs in the CSV file already exists in the target table, the existing rows in the table will be updated with the new values from the input file.
  • If the table has rows but the input CSV file does not have those row IDs, those rows will be deleted from the target table.
  • If the row IDs in the input CSV file do not exist in the target table, those rows will be inserted with the new row IDs generated and the row IDs given in the input file will be ignored.
  • If the input CSV file does not contain the row ID column at all, all the rows will be deleted from the target table and the rows from the input file will be inserted with the new row IDs generated.
• If resetTable is set to false (default):
  • If the row IDs in the CSV file already exists in the target table, the existing rows in the table will be updated with the new values from the input file.
  • If the table has rows but the input CSV file does not have those row IDs, those rows will not be deleted from the target table and those rows will remain unchanged.
  • If the row IDs in the input CSV file do not exist in the target table, those rows will be inserted with the new row IDs generated and the row IDs given in the input file will be ignored.
  • If the rows in the CSV file does not have a row ID column or row ID is specified as 0, those rows will be inserted with the new row IDs generated.

​

Retrieve HubDB data

• To retrieve table details from all published tables, make a GET request to /cms/v3/hubdb/tables.
• To retrieve table details from a specific published table, make a GET request to /cms/v3/hubdb/tables/{tableIdOrName}.
• To retrieve all rows from a specific table, make a GET request to /cms/v3/hubdb/tables/{tableIdOrName}/rows.
• To retrieve a specific row from a table, make a GET request to /cms/v3/hubdb/tables/{tableIdOrName}/rows/{rowId}.

​

Filter returned rows

• When passing values for multiselect columns, the values should be comma-separated (e.g. multiselect_column__contains=1,2).
• For datetime filters, you can use relative dates in place of timestamps in order to specify a value relative to the current time. For example, -3h would correspond to the timestamp 3 hours before now, whereas 10s would correspond to 10 seconds in the future. Supported time units are ms (milliseconds), s (seconds), m (minutes), h (hours), d (days). Current time can be used by specifying a zero value: 0s
• For the purposes of these filters, the built in column hs_id is a number column, the hs_created_at column is a datetime, and the hs_path and hs_name columns are text columns.

​

Sort returned rows

• geo_distance(location_column_name, latitude, longitude): takes the name of a location column and coordinates, returns the rows ordered by how far away the values of the specified location column are from the provided coordinates.
• length(column_name): takes the name of a column, returns the rows ordered by the length of the column value (calculated as a string)
• random(): returns the rows in random order.

​

Configuring HubDB tables for dynamic pages

// Example POST request to create table
{
  "name": "dynamic_page_table",
  "label": "Dynamic page table",
  "useForPages": true,
  "columns": [
    {
      "name": "meta_description",
      "label": "Meta description",
      "archived": false,
      "type": "TEXT"
    },
    {
      "name": "featured_image",
      "label": "Featured image",
      "archived": false,
      "type": "IMAGE"
    },
    {
      "name": "canonical_url",
      "label": "Canonical URL",
      "archived": false,
      "type": "URL"
    }
  ],
  "dynamicMetaTags": {
    "DESCRIPTION": 1,
    "FEATURED_IMAGE_URL": 2,
    "LINK_REL_CANONICAL_URL": 3
  }
}

​

Changes in v3

• Tables should have both name and label. This name cannot be changed once the table is created. Names can only include lowercase letters, digits, and underscores and cannot begin with a number. Both name and label should be unique in the account.
• The API supports both table id and name in the URL paths.
• The GET row endpoints return column name instead of id in values field. Also, POST / PUT / PATCH row endpoints require column name instead of id in values field.
• The row update PATCH endpoints now accept sparse updates, which means you can specify only the column values that you need to update (whereas you had to specify all the column values in the previous versions). When you update a column with a list of values such as multiselect, you need to specify the list of all the values. In order to delete the value for a column, you need to specify the column with the value as null in the request.
• Removed the endpoints to get / update / delete a row cell in favor of the row update PATCH endpoints.
• The import endpoint now supports an optional field idSourceColumn along with existing fields in the JSON-formatted options. You can use this field to specify the column in the CSV file which contains row IDs. To import new rows along with the new values for existing rows, specify 0 as the row ID for the new rows and the valid row IDs for the existing columns. Learn more about importing rows from CSV. Also you can use column names or IDs in the target field of the column mappings in the JSON-formatted options.
• The clone endpoint requires a new name and new label to be set for the cloned table.