CMS API | HubDB

HubDB is a relational data store that presents data as rows, columns, and cells in a table, much like a spreadsheet. HubDB tables can be added or modified within your HubSpot account, but you can also use the API endpoints documented here. For information on using data from HubDB tables on your website or in programmable emails, check out HubSpot's CMS developer documentation.

Similar to HubSpot website pages, HubDB tables support draft and published versions. This allows you to update data in the table, either for testing or to allow for a manual approval process, without affecting any live pages. Learn more about drafted versus live tables.

If a table is set to be allowed for public access, you can access the published version of the table and rows without any authentication by specifying your HubSpot account ID via the query parameter portalId.

If you're migrating from v2 of the HubDB API, learn more about the changes in the current (v3) API.

HubDB API requests have different rate limits, depending on the type of request:

• 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.

HubDB tables have both draft and live versions and live versions can be published or unpublished. This will allow you to update data in the table, either for page previews or testing or to allow for a manual approval process, without affecting any live pages.

In this API, separate endpoints are designated for the draft and published versions of a table. For example, you can retrieve the published version of a table by making a GET request to the following endpoint:

/cms/v3/hubdb/tables/{tableIdOrName}

And to retrieve any content that has been drafted but not yet published, you would add /draft to the end of the URL:

/cms/v3/hubdb/tables/{tableIdOrName}/draft

Draft data can be reviewed and then pushed in HubSpot, or with the /push-live endpoint. The draft data can also be discarded via /reset endpoint, allowing you to revert to the current live version of the data without disruption.

To create a HubDB table, make a POST request to /cms/v3/hubdb/tables.

In the request body, specify the following required fields:

In addition, you can specify the following optional fields:

Without any columns added yet, your create request might look like the following:

Each column in a HubDB table can be defined with the following properties:

Using the above fields, your request to create a new HubDB table might look like the following:

After creating a table, columns will be assigned IDs in ascending order. When updating existing columns, include the column's id field in the input object.

You can add rows either manually through the API, or you can import rows from a CSV file.

To add rows to a HubDB table, make a POST request to /cms/v3/hubdb/tables/{tableIdOrName}/rows.

For each table row, you can include the following fields:

Using the above fields, your request might look similar to the following:

To import data into a HubDB table from a CSV file, make a POST request to /cms/v3/hubdb/tables/{tableIdOrName}/draft/import.

The import endpoint takes a multipart/form-data POST request:

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

In config, include the following fields as a JSON string:

Using the above table, your config JSON might look like the following:

If using cURL, your command might look like the following:

There are several formats you can use when importing data into a date-type column.

Integers

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

These formats require the month to precede the day (i.e., dd/mm/yy is not accepted). Integers can be separated by hyphens (-) or slashes (/).

Relaxed dates

You can also import date formats that are less standardized than integer-based dates. For example:

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

Relative dates

HubSpot will parse the following date formats relative to the current day:

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

When importing data from a CSV file into a HubDB table, you can set the  resetTable field to true or false (default) to manage whether HubDB row data is overwritten.

• 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.

There are multiple ways to retrieve HubDB data, depending on whether you're looking for table details or the rows of a table:

• 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}.

When retrieving row data, you can further filter and sort the results.

If a table is set to be allowed for public access, you can access the published version of the table and rows without any authentication by specifying your HubSpot account ID via the query parameter portalId.

When retrieving HubDB table data, you can apply filters as query parameters to receive specific data. Filter query parameters are constructed as follows: columnName__operator. 

For example, if you have a number column named bar, you can filter results to only include rows where bar is greater than 10: &bar__gt=10.

All filters are ANDed together (OR filters are not currently supported).

When filtering, keep the following in mind:

• 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.

Below, learn which operators can be applied to which column types:

When retrieving HubDB data, you can apply sorting as a query parameter to determine the order of the returned data. To sort data, add a sort query parameter and specify the column name:

&sort=columnName

By default, data will be returned in the natural order of the specified column. You can reverse the sort by adding a - to the column name: 

&sort=-columnName

You can include this parameter multiple times to sort by multiple columns.

In addition to sorting by a column, there are three functions that can be used:

• 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.

These functions also support reverse ordering. For example, the following geo_distance sort returns items that are the farthest away first:

sort=-geo_distance(location_column,42.37,-71.07) 

Using HubSpot's CMS, you can use a HubDB table as a data source to generate dynamic pages. For example, you can create a table that contains a row for each member of your executive team, with columns containing information that you want to display on a page. After selecting that table as the dynamic data source for a page, that page will generate a listing page that displays all rows as summary items, along with separate pages for each row, similar to a blog listing page and blog post pages.

To enable a table to be selected as a data source in the content editor, you'll need to set useForPage to true. You can optionally include dynamicMetaTags to specify which columns to use for each page's metadata.

For example, the code below would create a table that can be used for dynamic pages, and specifies the three columns to use for page metadata.

• 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.
• API supports both table id and name in the URL paths.
• 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.
• 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.
• 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, you can simply specify 0 as the row id for the new rows and the valid row ids for the existing columns.  See more details in the Import section below. Also you can use column names or ids in the target field of the column mappings in the JSON-formatted options.
• Clone endpoint requires a new name and new label.