HubDB: Storing and Retrieving Data

  • Post category:General
  • Post author:

The HubSpot CMS along with it’s simple and powerful templating language HubL, provides a database to store some public information. Information such as content of a webpage, Author bio’s, employee details, testimonials can be stored in HubDB, HubSpot’s very own database. This way, whenever we want to display the same data at multiple places on a webpage, we need not write the same content again and clutter our HTML. Using the combination of HubL and HubDB we can make our HTML pages more lighter and faster to load.
Hi! I am Sumeet Hande, a front-end engineer at Addend Analytics and in this blog, we are going to see how to store data in HubDB and how to retrieve it with the help of HTML and HubL in our code.

Creating a HubDB table:
HubDB can be found under the Marketing Tab > Files and Templates > HubDB.
This is what a HubDB dashboard looks like,

Each entry in the Dashboard represents a HubDB table. Every table in the HubDB has a unique ID which is used to reference that particular table and retrieve data from it. The dashboard gives information about the status of a particular table (whether it is published or unpublished), it’s last updates and the size of the table (in number of rows). The Actions tab present on the right of each entry provides options like Edit, Clone, Delete, Publish/Unpublish.
Here, we will create a new ‘Employee Database’ by clicking on the ‘Create Table’ button on the top right of the dashboard interface.

The ‘Table label’ is the name which will be displayed on the HubDB dashboard. The ‘Table name’ is the reference variable which is used to refer to the table in the code during data retrieval. To avoid confusion it is advised to keep Labels and Table names the same. Thus, as we start typing in the ‘Table Label’ input field, the Table name field gets automatically filled with the same name with spaces replaced with underscores and capitalized letters with lowercase letters.

We can change the label whenever we want, however once we set the ‘Table name’, we cannot change it. Once we click create, an empty table gets created as shown below,

By Default, we get the ID and NAME columns already added to our Employee Database table. We can add more columns and rows by clicking on the Actions tab on the top right corner of the page and choosing ‘Add Row’ or ‘Add Column’
When we click on ‘Add Column’ we get a similar dialog box as we had seen when we create a new table.

We have to specify the column name, the column label and the ‘type’ of the column. This type field tells us what kind of data we want that column to store. HubDB provides us with a multitude of options ranging from Text, Images, URLs, Videos, Checkboxes. Once we fill in these details, we click on Add Column and we can see the changes reflected on the table dashboard. An example of a filled Employee database is shown below,

It consists of Columns like ID, Employee Name, Position, Contact and City. Each row has a unique ID which we can reference to in the code.
After we store all the required data, we need to publish the database by clicking the publish button on the top right corner. Publishing a database in an important step as then only it can be accessible via code in any template or module.

Accessing HubDB data:

Now, let’s head over to the Design Tools again by assessing the Marketing Tab, then hovering over ‘Files and Templates’ and then selecting Design Tools.
For simplicity let’s create a simple HTML coded template. For that, we can click the ‘File’ tab on the Finder and select ‘New File’. From there, we can head over to select the ‘HTML + HubL’ option under the Templates heading.
This is what the coded template will look like with some standard markup code already added to it.

On the top right corner, we have the preview option available. All the content will go in the body section of the HTML page, i.e. between these two tags <body> </body>.
We first need to remove the demo code which is already present in the body section. Make sure you do not remove the {{ standard_footer_includes }} snippet as that might introduce an error. This code is needed as HubSpot requires it for Analytics.
Now, we will use HubL to access the Employee Database which we created and stored in HubDB. We write a HubL for loop to access the database as shown below,

The number which we see passed as a parameter is the unique ID for the Employee Database Table. Let’s start by accessing and displaying the names of all the employees in the database table. For that, we write the following code in our for loop

Here, employee_name is the label which was set while the Employee Name column was made. <h3></h3> is just an HTML header tag. We can provide header tags all the way from H1 to H6 where H1 is the largest size text and H6 is the smallest size text. This is how it would look like when we preview this current code,

The names of all the five employees which are presently stored in the database are displayed. If we add more employees in the database and publish it, we need not change anything in our code as it will automatically display all the employees from the table.
In a similar manner, we can also access other columns of the table as contact, city, position by using their respective labels.
With the hubdb_table_rows() function, other than the unique table ID parameter, we can also specify an optional query for filtering our retrieval. For instance, If we want to display only those employees who are ‘Web Developers’, we can write it like this,

The syntax for the filter query is as follows:
‘columnlabel__filter=’column value’’
Considering the above example, the column which we are looking for is the ‘position’ column and the filter which we have used here is the ‘Equals’ filter (eq) which is set to a value ‘Web Developer’. In short, this piece of code means that only those rows which have the position value as ‘Web Developer’ will be fetched from the table and displayed. This is the output:

In this way, we can also use some standard filters which HubL provides like the Select Attribute filter: selectAttr() or the Reject Attribute filter: rejectAttr().
There are a lot of filters available which can be found on the HubDB Documentation page. Detailed technical definitions and usage have been provided.
Just keep in mind that databases created in HubDB can be publicly accessed using API as well. So sensitive data such as passwords, account credentials must be strictly refrained from being stored in the HubDB database.
So, this is how one can get started with learning how to create, access and retrieve data using HubDB which can provide a more beneficial, simple and intuitive experience for developers.

Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like financial services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Australia, and India. Companies partnering with us save their valuable time and efforts of searching and managing resources while saving hugely on the development costs and hence, most small and medium enterprises in North America prefer Addend to be their Power BI implementation partner. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com