Services

Hal9 allows the users to connect and query a database in PostgreSQL. In this tutorial, we will see how to create a PostgreSQL database on Heroku, how to connect it to services like Notion using Brackets, and how to configure Hal9 to access our database.

Creating a PostgreSQL database on Heroku

Heroku is a platform as a service that enables developers to build, run, and operate applications entirely in the cloud. It also offers a plan for hosting PostgreSQL databases.

In order to create a new database on Heroku, you first need to create an app in your personal dashboard. Then you can attach a Heroku Postgres instance to it as a resource. Let’s see how to do this step by step.

To begin, access the Heroku platform by visiting https://id.heroku.com/login and entering your login credentials. In case you don’t have an account, there is a Sign Up option available beneath the login form.


After accessing your personal app dashboard, proceed to create a new app. If your dashboard is currently vacant, simply click on the “Create new app” button. Alternatively, if your dashboard contains other apps, click on the “New” button located in the top-right corner and select “Create new app”.


To link a PostgreSQL database to the recently created app, go to the Resources tab located in the header of the app dashboard. Proceed to enter “Heroku Postgres” into the search field for Add-ons. From the dropdown list of suggestions, choose the recommended Heroku Postgres add-on.


Congratulations, you now created a PostgreSQL database!

To find the credentials and the connection URL for the PostgreSQL database, you need to navigate to the Resources tab in your app’s dashboard again and select the Heroku Postgres resource. This brings you to the configuration screen of the your PostgreSQL database that should look like this:


Now in the Settings tab in the header of that screen, you can click the View Credentials button to see the credentials of your PostgreSQL database:


These credentials give you access to a database in PostgreSQL, which you build by connecting it to other applications where you will extract data from using differents integration frameworks. In case you do not have a technical background, we recommend using Bracket for this step.

Using Bracket

Bracket is a fast, code-optional way to sync data between databases, data warehouses, and SaaS tools and enables you to set up limitlessly flexible one-way or two-way syncs between any pairing of database or SaaS tool. It support differents integrations, for example Google Sheets, Notion, Snowflake, and Postgres.


Hal9 Configuration

The steps to configure Hal9 to connect to a PostgreSQL database are:

  • Once you are logged in, you must go to settings and you will notice that the Enable database support option is not selected.


  • When you select it, you will need to fill in the connection box, in which you must enter the URI obtained from Heroku, but replacing postgres with postgresql+psycopg2


For example, if our URI is:

postgres://cwzyrhkleuxkbz:c5b6e987f6667c7e810278ff60885c38647735bbba0477e0c72e5dd3d3cdb56a@ec2-34-236-103-63.compute-1.amazonaws.com:5432/dffrnjllhkeh18

we will enter:

postgresql+psycopg2://cwzyrhkleuxkbz:c5b6e987f6667c7e810278ff60885c38647735bbba0477e0c72e5dd3d3cdb56a@ec2-34-236-103-63.compute-1.amazonaws.com:5432/dffrnjllhkeh18

Once this is done, we select Validate dataset, then ok and we will be ready to use Hal9 with the data from our Postgres database.


Example: Notion < Bracket > Postgres <> Hal9

Once we have the Postgres credentials and a database on Notion, it is very easy to connect them both using Bracket as an intermediary. To do this we will use the Bracket legacy app.

In this app, we select Notion as the main data source and then select the database we want to use. In case you do not have any available database, we must select Link new bases and give permission to Bracket to access them.


Then we select Postgres as the secondary data source and introduce the credentials we obtained from Hal9.


Once this information has been introduced, we can check the connection and if it is successful, we can proceed to the next step, in which we will select the synchronization options.


In the next step, we will review our data, the connection and the mapping established between the two databases. We will also have the option of running a test run where we will not synchronize any data, run a single run where we will only synchronize our data once, or set up the connection and run the synchronization every certain time. With the completion of this step, we will have our data ready in Postgres and these will be accessible from Hal9.

As an example dataset, we use the startups-hiring-2023 Kaggle dataset which contains information about startups and the jobs they are offering. If we enter the prompt Which startups are hiring engineer? in Hal9, we will get an app that shows us a table with the required information.


Important concepts

  • Databases are the systems used to store and manage digital information. These systems store data in an organized way and let users query it in order to extract important and relevant information. They are designed not only to store and manage data, but also to enable access and sharing of that information. They are used to store data for a variety of purposes, including recording transactions, managing inventory, supporting customer relationships, and making data-driven decisions.

  • A data warehouse is a centralized repository of organizational data, used to combine databases from multiple sources for reporting and analysis. Data warehouses can be used to store current, historical, and long-term data. They can be built using physical hardware or hosted in a cloud environment. Examples of data warehouses include Amazon Redshift, Google BigQuery, Microsoft Azure SQL Data Warehouse and PostgreSQL. Data warehouses are used to gain insights for improved decision making. Common use cases of data warehouses include sales analysis, marketing analysis, customer feedback analysis, product recommendation engines, fraud detection, resource planning, and forecasting.

The purpose of a database is to collect real-time information for immediate use and for an application’s records. On the other hand, the data warehouse collects, stores, and analyzes large volumes of historical data, for the medium- and long-term. The warehouse is the database of databases, integrating different sources of information and creating a super business intelligence system in the process. This enables much more reliable long-term decision making by having access to more data.

  • An integration framework is a structure designed to make it easier to link up different applications, data sources, APIs, and services within an organization. It simplifies the exchange of data and components between different systems, databases, applications, and services, and allows them to interact with each other automatically. This reduces complexity and costs associated with integration across different systems. Integration frameworks also provide security and automation for the regular exchange of data, and help organizations ensure the accuracy and consistency of data across applications. Furthermore, they give organizations a unified view of their distributed systems, and the ability to quickly build and deploy new applications and services.