COS Tools

Table of contents
Close

HubDB

HubDB is relational data store represented as rows, columns and cells in a table, much like a spreadsheet.

Please note: HubDB cannot currently be used to personalize HubSpot marketing emails.

Concepts

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. 

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: (for future use) when used to define a page, this string is the last segment of the url's path for the page
Custom Columns

Tables can have as many custom columns as you'd like. They can be text, numbers, dates, times, images, 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.

Creating and Editing Tables

Tables can created and edited at https://app.hubspot.com/hubdb/<portalid> 

The HubDB REST APIs can also be used to edit tables. 

Table Permissions

A table can be private or public. When private, the table's contents can only be viewed inside the app or with authenticated APIs. When a table is published, it becomes public and it can be queried from unauthenticated APIs like from HubL.

Using the HubL Functions

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.

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

map of column information, by id

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

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.