CMS Developer Tools

Table of contents
Close

HubDB - HubSpot CMS

HubDB is a relational data store represented as rows, columns, and cells in a table, much like a spreadsheet.  The data within a table can be retrieved in several ways depending on your needs. You can query the data externally via the HubDB API or you can use HubSpot’s HubL markup tags to pull data into the CMS.

Please note: HubDB is available to all HubSpot customers with the CMS add-on and currently cannot be used to personalize HubSpot marketing emails.

Getting access to HubDB

There are two types of access you can assign to a user in order for them to be able to make use of HubDB. Both of these settings are located inside of a user's permissions area. To assign access, go to Settings > Users & Teams > Hover over a user and choose Edit from the Actions Menu

settings-users-and-teams-edit

Once in the edit permissions pane, the settings are in:

  • Marketing Tab > HubDB [Publish, Read, Write]:
    • Publish: Allows user to publish/update an existing HubDB tables
    • Read: Allows the user to view the contents of HubDB tables
    • Write: Allows the user to make edits to the HubDB tables

  • Admin Tab > Edit HubDB Tables [Toggle]:
    • When toggled on, this will allow users to also create, clone, unpublish, and delete tables.

HubDB Technical Limits

Creating your first table

Open up the HubDB editor app and click the Create Table button (or use the APIs create table endpoint).
Note: All new tables created are set with a status of draft. They cannot be used to output data via HubL or API until you publish the table.

Create a new HubDB table by clicking the Create Table button.

Set your table permission settings

You can manage your HubDB tables settings by clicking on the settings button above the table information. The following settings are available:

  • Allow public API access: [On by default] When this option is toggled off, your table contents can only be viewed in the app or with authenticated APIs. When toggled on, your table can be queried by unauthenticated APIs.
  • Enable creation of dynamic pages using row data: With dynamic pages, HubDB allows you to create a page for every row in your table. Learn more about dynamic pages here. Upon toggling this option on, you will then be able to select columns of you HubDB table that will serve as the source for your Meta Description, Featured Image, and Canonical URL.

How to access the settings area for your table.

Do I need to enable dynamic pages if I am only fetching data via the API?

No, you do not need to enable this setting if you only plan to fetch your table data via API.

HubDB Architecture

Tables

A table is a 2-dimensional arrangement of rows and columns. When a table is created, it is given a globally unique id which can be used to identify it. 

Rows

Rows are horizontal slices of a table. All the values in a row are related, usually to a single primary identifier. In a spreadsheet application, rows are represented by numbers, starting with 1. Each table row is given a globally unique id on creation. 

Each HubDB table can have a maximum of 10,000 rows.

Columns

Columns are vertical slices of a table. Each column has a type. In a spreadsheet application, they're represented by alphabetic columns like A, B, C, etc. There are two types of table columns:

Built-in Columns

Each row in a table has a couple of built-in columns:

    • hs_id: an automatically assigned, globally unique, numeric id for this row
    • hs_created_at: a timestamp of when this row was created
    • hs_path: when used with dynamic pages, this string is the last segment of the URL's path for the page
    • hs_name: when used with dynamic pages, this is the title of the page

Custom Columns

Tables can have as many custom columns as you'd like. They can be text, rich text, numbers, currency, dates, times, images, videos, selects or locations (latitude and longitude). 

When a column is created, it is given a numeric id unique to the table, starting at value 1. Column ids are increasing, but not necessarily sequential. Column ids cannot be reused, so if a table has 2 columns, 1 and 2, and the 2nd column is deleted, the next column created will have id 3.

Cells

Cells store the values where a row and column intersect. Cells can be read or updated individually or as part of a row. Setting the value of a cell to null is equivalent to deleting the cell's value.

Access HubDB data using HubL

Getting Rows

You can query tables from HubL and iterate over the results. 

To list rows of a table, use hubdb_table_rows().


{% for row in hubdb_table_rows(<tableId>, <filterQuery>) %}
the value for row {{ row.hs_id }} is {{ row.<column name> }}
{% endfor %}

<filterQuery> uses the same syntax as the HTTP api. For example,  hubdb_table_rows(123, "employees__gt=10&orderBy=count") would return a list of rows where the "employees" column is greater than 10, ordered by the "count" column. A complete list of optional <filterQuery> parameters can be found here.

NOTE: Instead of using multiple row queries with different <filterQuery>  parameters, you should make one query and use the selectattr()  or rejectattr()  filters to filter your rows:


{% set all_cars = hubdb_table_rows(<tableId>) %}

{% set cars_with_windows = all_cars|selectattr('windows') %}

{% set teslas = all_cars|selectattr('make','equalto','tesla') %}

 

To get a single row, use hubdb_table_row().


{% set row = hubdb_table_row(<tableId>, <rowId>) %}
the value for {{ row.hs_id }} is {{ row.<column name> }}

Built-in and custom column names are case insensitive. HS_ID will work the same as hs_id.

Row attributes

  • {{ row.hs_id }} - The globally unique id for this row.
  • {{ row.hs_path }} When using dynamic pages, this string is the Page Path column value and the last segment of the url's path.
  • {{ row.hs_name }} - When using dynamic pages, this string is the Page Title column value for the row.
  • {{ row.hs_created_at }} - Unixtimestamp for when the row was created.
  • {{ row.hs_child_table_id }} - When using dynamic pages, this is the ID of the other table that is populating data for the row.
  • {{ row.column_name}} or {{ row["column_name"] }} - Get the value if the custom column by the name of the column.

Please note: There is a limit of 10,000 rows per HubDB table.

Getting Table Info

To get information on a table including its name, columns, last updated, etc, use the hubdb_table() function.


{% set table_info = hubdb_table(<tableId>) %}

Table attributes

The attributes listed below are in reference to the variable that hubdb_table() was assigned to in the above code. Your variable may differ.
Note: It is recommended assigning this to a variable for easier use. If you don't want to do that, you can use
{{ hubdb_table(<tableId>).attribute }}

  • {{ table_info.id }} - The id of the table.
  • {{ table_info.name }} - The name of the table.
  • {{ table_info.columns }} - List of column information. 
    • You can use a for loop to iterate through the information available in this attribute.
  • {{ table_info.created_at }} - Timestamp of when the table was first created.
  • {{ table_info.published_at }} - Timestamp of when this table was published.
  • {{ table_info.updated_at }} - Timestamp of when this table was last updated.
  • {{ table_info.row_count }} - Number of rows in the table. 

Getting Column Info

To get information on a column in table such as its label, type and options, use the hubdb_table_column() function


{% set table_info = hubdb_table_column(<tableId>, <columnId or column name>) %}

Column attributes

The attributes listed below are in reference to the variable that hubdb_table_column() was assigned to in the above code. Your variable may differ.
Note: It is recommended assigning this to a variable for easier use. If you don't want to do that, you can use
{{ hubdb_table_column(<tableId>,<columnId or column name>).attribute }}

  • {{ table_info.id }} - The id of the column.
  • {{ table_info.name }} - The name of the column.
  • {{ table_info.label }} - The label to be used for the column.
  • {{ table_info.type }} - Type of this column.
  • {{ table_info.options }} - For select column type, this is a map of optionId to option information.
  • {{ table_info.foreignIds }} - For foreignId column types, a list of foreignIds (with id and name properties)

Column methods

  • getOptionByName("<option name") - For select column types, get option information by the options name. 

Using the HubDB REST APIs

If you would like to manage your HubDB data externally, you can use our REST APIs found here.