Database Blocks

  • Published on Sep 20, 2024
  • 1 minute(s) read

Overview

Database Blocks allow you to make queries from Hyperscience to databases, which allows for additional data extraction and validation from your systems.

Database Blocks support three types of databases - Oracle, PostgreSQL, and MSSQL. To learn more about the supported database versions for each of the three database types, see Database Specifications in Infrastructure Requirements (Production).

Sample use cases

  • I want to call out to an external database to validate data I have extracted from my submission.

  • I want to call out to an external database to pull in additional data to enhance the submission data I have in my flow.

Block settings table

You can configure and customize your Database Block by editing the settings described below.

Name

Required?

Description

Database type

Yes

The type of database you want to connect to. The currently supported types are MSSQL, Oracle, and Postgres.

Timeout

No

Query timeout in seconds. Timeout only takes effect for MSSQL and Postgres.

Host/Server

Yes

URL/IP address of the server the database is hosted on.

Port

Yes

The value you enter for the port depends on the type of database you’re using. Below you can find the default ports for each supported database type:

  • MSSQL - 1433

  • Oracle - 1521

  • Postgres - 5432

Database/Service

Yes

Database or Service name

Username

Yes

Database username

Password

Yes

Database password

Additional options

No

Dictionary of additional connection string options

Query

Yes

A query that could be parameterized

Parameters

No

Key-value pairs for parameters included in the query

Building a database query

Database blocks can validate and pull additional data from a database using queries. These queries are defined with Query and Parameters. The Query could be parameterized, and query parameterization is database specific as explained below:

  • MSSQL: Parameters should be specified in the query with @paramName notation

  • Oracle: Parameters should be specified in the query with :paramName notation

  • Postgres: Parameters should be specified in the query with %(paramName)s notation

Here is an example of how Query and Parameters should be defined for an MSSQL database:

  • Query: SELECT Id, Name, Age, Location FROM Employees WHERE Name = @name AND Age = @age

  • Parameters: {"name": "Tom", "age": 32}

The query will pull additional data from the database, and the output from the Database Block will look like the following:

{"result": [{"Id": 2,"Name": "Tom","Age": 32,"Location": "Texas"}]}

Reserved words and keywords in a database (e.g., “DESC” in Oracle) can’t be used as parameter names. Also, values used to constrain the number of rows returned by the query (e.g., 10 in Limit 10) can’t be parameterized.