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 cannot currently be used to personalize HubSpot marketing emails.

Getting access to HubDB

HubDB is available to all HubSpot customers with the CMS add-on.

You must be an administrator in the account with the “Edit HubDB tables” setting enabled to create, publish, unpublish, and delete HubDB tables.

HubDB Technical Limits

10,000 rows per HubDB table

10 table scans per CMS page defined as a single call to hubdb_table_rows()

HubSpot’s general API limits

Creating your first table

Open up the HubDB editor app and select the Create table option or use the APIs create table endpoint.

Set your table permission settings

A table can be private or public. When private, the table's contents can only be viewed inside the app or by using authenticated APIs. When a table is published, it becomes public and it can be queried from unauthenticated APIs such as HubL tags and functions.

Dynamic pages or API only?

A unique element of HubDB is its ability to automatically generate CMS pages using table data and you can enable this feature from within the settings options in the UI.  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 has 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 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 an 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
id

the globally unique id for this row

created_at

a timestamp representing when this row was created

path

when used to define a page, this string is the last segment of the url's path for the page

<column name> or ["<column name>"]

Get the value of the column for this row 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
id

id of the table

name

name of the table

columns

list of column information

created_at

timestamp of when this table was first created

published_at

timestamp of when this table was published

updated_at

timestamp of when this table was last updated

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
id

id of the column

name

name of the column

label

label to be used for the column

type

type of this column

options

for columns of type select, a map of optionId to option information

foreignIds

for columns of type foreignId, a list of foreignIds (with id and name properties)

Column methods
getOptionByName("<option name>")

For columns of type select, get option info by the option's name

Using the HubDB REST APIs

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