Internal PostgreSQL

Each Workspace is equipped with a dedicated PostgreSQL database for storing and managing relational data, forming the foundation for various software applications. The database is called internal-postgres. All apps within a Workspace share this database, enabling them to create, read, update, and delete data.

Access to all database-related functions can be found in the functions panel under the path: Data Connectors -> Relational Database -> internal-postgres. If the documentation in this article is not sufficient, you can also find more detailed information on PostgreSQL specific topics in its documentation.

To build apps based on an already existing database, please refer to the relational database connector.

Working with tables

Tables play a crucial role in managing relational data. You can create multiple tables and associate them. Actions like creating, modifying, and deleting tables are typically performed during app development (build time) but not during application runtime.

Functions related to tables may be deleted after a single manual execution during build time. However, it's advisable to allocate a dedicated section on the logic board for managing tables, preserving these functions for future reference.

Creating tables

You can create tables with the createTable function. It expects two inputs:

• Name: A unique name of the table as string.

• Fields: The fields of the table as objects with field name and data type separated by a colon.

Being a best practice in database design and software development, use English and camel case notation for naming tables and fields in your database. Examples include firstName and dateOfBirth.

To create the table:

1. Enter a table name.

2. Enter table fields and data types.

3. Trigger the function once.

When creating a table, additional fields will automatically be added to the table:

• id: Serves as the table's primary key, a unique number incremented for each new row.

• created at: This timestamp records the time of row creation as date, remaining unaltered.

• updated at: This timestamp tracks the last time a row is altered or updated as date.

The following example shows the creation of a table named machineswith three fields. The function output shows the created table.

Create table function after execution.

Data types

The following table shows the data types that can be used for fields:

Data type	Represenation
boolean	Represents true or false values, often used for logical decisions.
bool	Same as boolean, an abbreviation.
integer	Represents whole numbers without decimal points.
uniqueInteger	Whole numbers that must be unique and cannot be empty.
bigint	Represents very large whole numbers.
float	Represents decimal numbers with single precision.
double	Represents decimal numbers with double precision.
number	Represents numeric values, including decimal numbers.
string	Represents text or character data.
uniqueString	Text data that must be unique and cannot be empty.
text	Represents long text or character data.
date	Represents dates and times.
uuid	Represents a universally unique identifier (UUID), a 128-bit number used to identify information in computer systems globally.
json	Represents structured data in JavaScript Object Notation (JSON).
file	Represents files or binary data stored as JSON.
jsonb	Represents binary JSON data, a binary-encoded serialization of JSON.

$USER Variable

The $USER variable enables referencing the current logged-in user in your app, particularly useful for apps requiring user authentication. A recommended practice involves defining the username as a unique key using the data type uniqueString when creating the table. This configuration enables utilizing the upsertRow function, capable of both updating and inserting rows based on the existence of the unique key.

$USER in combination with upsertRow function

Creating tables automatically with forms

The autoUpsertRow allows you to automatically populate tables from a configured form widget. Additionally, like upsertRow, it provides the capability to update or insert a row based on whether a value exists in a field defined as a unique key.

To create a table with this fuction:

1. Add the autoUpsertRow function to the logic board.

2. Add a table name as input, connect a configured form with the function.

3. Define a unique key for the function.

4. Fill the form and trigger the function to create the table and a first entry.

The image below shows the described process including a getTable function to visualize the result.

Creating a table and populating fields from a form with autoUpsertRow

The autoUpsertRow function is more suitable as a development tool than as a runtime function. However, you can use it during runtime, too.

Assigning data relationships

In database design, relationships between tables are pivotal for defining database structure and integrity. These relationships are typically categorized as one-to-one, one-to-many, and many-to-many. All these relationships can also be created in Heisenware.

One-to-one relationship

For the sake of simplicity, we recommend consolidating all fields into one table instead of creating two tables and associating them.

One-to-many relationship

One-to-many relationships can be optional or mandatory. The functions to use are optionallyHasOne and mandatorilyHasOne. Both functions have identical input parameters:

• sourceTable: The table where the relationship originates. That is the "many" parameter.

• targetTable: The table to which the relationship points. That is the "one" parameter.

• sourceKey: (Optional) The column in the source table referencing the primary key of the target table.

The optionallyHasOne function establishes a one-to-many relationship where entries in the source and target tables can exist independently. In the example below, each machine can manufacture multiple products, but each product is manufactured by only one machine. That is why the machineId is automatically added as a foreign key in the products table.

Optional one-to-many relationship

The mandatorilyHasOne function establishes a one-to-many relationship where entries in the source table require a corresponding entry in the target table. In the example below, each machine must have a supplier assigned, but multiple machines can have the same supplier. In this case, the supplierId is added automatically as a foreign key in the machines table.

Mandatory one-to-many relationship

Technically, the distinction between optionallyHasOne and mandatorilyHasOne lies in the handling of null values in the foreign key. optionallyHasOne permits null values, while mandatorilyHasOne does not as shown in the image below.

Null is not allowed

Associating one-to-many data records

To associate data in a one-to-many relationship, the foreign key in the sourceTable must be set or changed programmatically by using changeRow or upsertRow. The image below shows how the machineId is set in the products table. The output of the getTable function illustrates the linked data records.

Associated one-to-many data

Many-to-many relationship

The optionallyHasMany function creates a so-called auxiliary table, which manages the linking of individual entries. Students and courses are a typical example of a many-to-many relationship. The optionallyHasMany function expects a sourceTable and targetTable as inputs. Each student can have several courses and each course is attended by several students.

Associating many-to-many data records

With the setAssociation function, you can associate data in a many-to-many relationship. The function requires the source table, the source tables's record id, the target table and the target table's record id as inputs. Execution of this function leads to an update in the auxiliary table managing the linked records.

Creating temporal tables

Temporal tables in PostgreSQL are designed to store historical data, enabling users to track changes over time. These tables maintain a history of all changes made to the data, including inserts, updates, and deletions, along with timestamps indicating when each change occurred. This feature is particularly useful for scenarios where you need to maintain a record of data modifications for compliance, auditing, or analysis purposes. By leveraging temporal tables, you can easily query historical data and analyze trends or patterns over time, providing valuable insights into your dataset's evolution.

Using the createTemporalTable function, you can establish a temporal table, following the same creation process as standard tables. However, opting for a temporal table in Heisenware triggers the automatic generation of associated history tables, denoted as [tableName]History, allowing comprehensive historical data management. In the history table, data is stored as an array of objects, with each alteration to the original table resulting in a new entry.

Creating a temporal table

Altering tables

To alter the columns of a table, respectively its fields, you can also use the createTable function. Simply add the additional fields as key-value pairs and trigger the function again. It doesn't make a difference if you include the already existing fields or if you only input the new fields.

Deleting fields and changing data types is not supported. To accomplish this, you need to delete the table and create a new one. It's important to note that this action will result in the deletion of existing data in the table. Unused data fields are not a problem, however. Consider leaving them as part of the table.

Retrieving table data

There are functions to retrieve information about the internal database and its tables. Fore some apps, these might be interesting runtime functions, however, their main purpose is to provide information during build time.

Retrieving all tables

The getAllTables function returns all tables in a Workspace's database.

Get all tables function

Retrieving table schema

A table's schema refers to its structure or blueprint, which includes its properties. getTableSchema expects a table name as input and returns the schema of that table.

Get table schema function

All table schema information can be retrieved by unfolding the table fields. The PostgreSQL documentation contains further information on schemas.

Retrieving table attributes

Attributes refer to the columns or fields of a table, defining the schema of the data stored in that table. getTableAttributes expects a table name as input and returns the various attributes of that table. Unfold a field to see all of them. In the example below, you can see that the field's productNumber attribute unique is true because it was defined as uniqueInteger during table creation.

Unfolded table attributes

Clearing tables

The clearTable function removes all data from a table, keeping the table structure (columns and data types). Imagine erasing all information in a spreadsheet but keeping the grid layout. To clear a table, enter its name into the input box and trigger the function.

Clear table function

Deleting tables

The deleteTablefunction permanently removes the entire table, including its structure and all data within. Think of it like deleting a file from your computer - it's gone forever. To delete a table, enter its name into the input box and trigger the function.

Delete table function

Remember: Both clearing and deleting tables are permanent actions. Double-check the table name and proceed with caution.

Manipulating table data

Manipulating table data means inserting, updating, and deleting rows within tables. There are different functions available for each of the operations.

All data manipulation functions work seamlessly with the data grid widget. By combining them, app users are empowerd to interact with table data by inserting, updating, and deleting rows directly in the data grid. This integration is particularly useful for functions requiring a row id and enable the development of interactive applications.

Inserting rows

There are several methods to create rows, with "create," "add," and "insert" being synonymous terms.

Inserting a single row

Use the addRow function to insert a single row into a database table. This function requires the table name and a data object (key-value pairs) as inputs. For instance, the data can come from a form widget and inserting triggered by a button click like in the example below.

Insert one row with addRow

Inserting multiple rows

The addRows function allows the insertion of one or more rows into a database table simultaneously. It expects the table name and an array of data objects (key-value pairs) as inputs. In the example below, form data is used to create a second version of the data, put both into an array and add the array as two rows into the products table. The function returns the number of added rows which are 2 in this case.

The addRows function enables the simultaneous insertion of one or more rows into a database table. It requires the table name and an array of data objects (key-value pairs) as inputs. In the example below, an array of two objects coming from a modifier is added as two rows into the products table. Upon execution, the function returns the number of added rows, which is 2 in this instance.

Inserting multiple rows with addRows

Inserting or updating a row

For either inserting or updating a row, utilize the upsertRow function. This function verifies if a certain value of a unique key is present in a table. If the value does not exist, it inserts a new row; if it does, it updates the existing row with the new data. Alongside the table name and a data object (key-value pairs), upsertRow requires an additional uniqueKey as input.

The field used as theuniqueKey needs to already be a unique data type in the table creation (uniqueString, uniqueNumber, uuid). Otherwise upsertRow will always create new rows.

In the example below, the upsertRow function was triggered twice on the products table. In the first image, the row with the id: 4 and productNumber: 1285 is updated as it was already existing, while in the second image, a new row with the id: 9 is added for productNumber: 1290.

Image 1: Updating a row with upsertRow

Image 2: Inserting a row with upsertRow

Inserting or updating row and creating table

As explained above, the autoUpsertRow function combines createTable and upsertRow functionality using a form widget.

Updating rows

Updating rows in the database can be achieved using the changeRow, upsertRow, or autoUpsertRow functions. While upsertRow and autoUpsertRow are capable of both updating existing rows and inserting new ones, their documentation is covered in the section on inserting rows.

For specific updates to existing rows, the changeRow function is recommended. It requires specifying the table's name, the row's id, and a data object containing the updated information for that row. The data object can include one or multiple key-value pairs representing the fields to be updated.

Updating a row with changeRow

As with addRow and deleteRow, combining updateRow with a data grid is recommended as the row id is automatically captured in such scenarios.

Deleting rows

The deleteRow function allows you to remove a specific row from a table by providing the table's name and the id of the row to be deleted.

Deleting a row with deleteRow

Retrieving table data

The following functions help to retrieve data from existing tables that contain data.

Retrieving table data

The getTable function retrieves rows from a specified table. It requires only the table name as a mandatory input and returns an array of data objects.

Retrieving table data with getTable

To narrow down or modify the results, the optional parameters filter, fields and options are available.

Filter: Apply expressions to narrow down the selection of rows based on specific criteria. Refer to the following table for available expressions and their functionalities.

Expression	Functionality	Example
-	Filters values that match a specified value.	status: new
isnot	Filters values that do not match a specified value.	status: {isnot: new}
contains	Filters values that are contained in a set.	city: {contains: [Berlin, Hamburg]}
notcontains	Filters values that are not contained in a set.	city: {notcontains: [Berlin, Hamburg]}
startswith	Filters values that start with a specified string.	name: {startswith: John}
endswith	Filters values that end with a specified string.	email: {endswith: @example.com}
eq or ['=']	Filters values that are equal to a specified value.	age: {eq: 30}
ne or ['<>']	Filters values that are not equal to a specified value.	city: {ne: Berlin}
lt or ['<']	Filters values that are less than a specified value.	age: {lt: 30}
gt or ['>']	Filters values that are greater than a specified value.	age: {gt: 30}
lte or ['<=']	Filters values that are less than or equal to a specified value.	age: {lte: 30}
gte or ['>=']	Filters values that are greater than or equal to a specified value.	age: {gte: 30}
between	Filters values that are within a specified range.	price: {between: [10, 50]}
isblank	Filters values that are null or undefined.
isnotblank	Filters values that are not null or undefined.

Fields: Allows to specify an array containing all the fields you want included in the output. Essentially, it serves as a filter for selecting specific table columns to be retrieved.

Options: Enables you to customize how dates and times are displayed in the output. You can utilize the following options:

• locale: Specifies the local representation and timezone for expressing dates and times. It comprises two tags combined with a hyphen: the first tag represents the language code, and the second denotes the region. Examples include locale: EN-de and locale: fr-CA.

• dateStyle: Determines the style of the date representation, with options including full, long, medium, short, or hidden. The default style is medium.

• timeStyle: Specifies the style of the time representation, offering options such as full, long, medium, short, or hidden. The default style is medium.

Retrieving first matching row

The findOne function retrieves the first table entry that matches the specified query options. If no query options are provided, it returns the first row of the table. The query options mirror those available in the filter options of the getTable function. Simply add "where:" before the expression. The example below constitues a valid queryy:

where: price: {between: [10, 50]}

Using the $USER variable in a findOne query input lets you find data connected to the logged-in user.

Retrieving one row by its ID

The getRow function facilitates the retrieval of exactly one row identified by its primary key, the id.

Subscribing database events

The onChange function lets you know whenever something changes in the database, whether it's adding, updating, or deleting data. It's like having a notification system for your app's data. The feature is core to a fully event-driven architecture.

To subscribe to database events:

1. Add the onChange function to the logic board.

2. Specify a subscriber name, typically the app's name, in the first input box.

3. Trigger onChange to activate the subscription.

4. Connect the function's callback box to a memorizer for handling database events.

In the example below, the onChange function is called tagger

Subscribing database events

Checking database connection

The isConnected function allows you to verify the database connection status. It doesn't require any input and returns a boolean value.

Checking database connection

Resetting the database

The reset function clears the entire database, including all tables and data.

Reset function after successful execution

Proceed with caution, as a database reset cannot be undone.