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:
|
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.