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
anddata 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:
Enter a table
name
.Enter table
fields
anddata types
.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 uniquenumber
incremented for each new row.created at
: This timestamp records the time of row creation asdate
, remaining unaltered.updated at
: This timestamp tracks the last time a row is altered or updated asdate
.
The following example shows the creation of a table named machines
with three fields. The function output shows the created table.
Data types
The following table shows the data types that can be used for fields:
Data type | Represenation |
---|---|
| Represents |
| Same as boolean, an abbreviation. |
| Represents whole numbers without decimal points. |
| Whole numbers that must be unique and cannot be empty. |
| Represents very large whole numbers. |
| Represents decimal numbers with single precision. |
| Represents decimal numbers with double precision. |
| Represents numeric values, including decimal numbers. |
| Represents text or character data. |
| Text data that must be unique and cannot be empty. |
| Represents long text or character data. |
| Represents dates and times. |
| Represents a universally unique identifier (UUID), a 128-bit number used to identify information in computer systems globally. |
| Represents structured data in JavaScript Object Notation (JSON). |
| Represents files or binary data stored as JSON. |
| 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.
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:
Add the
autoUpsertRow
function to the logic board.Add a table name as input, connect a configured form with the function.
Define a unique key for the function.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Deleting tables
The deleteTable
function 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.
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.
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 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
.
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.
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.
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.
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. |
|
| Filters values that do not match a specified value. |
|
| Filters values that are contained in a set. |
|
| Filters values that are not contained in a set. |
|
| Filters values that start with a specified string. |
|
| Filters values that end with a specified string. |
|
| Filters values that are equal to a specified value. |
|
| Filters values that are not equal to a specified value. |
|
| Filters values that are less than a specified value. |
|
| Filters values that are greater than a specified value. |
|
| Filters values that are less than or equal to a specified value. |
|
| Filters values that are greater than or equal to a specified value. |
|
| Filters values that are within a specified range. |
|
| Filters values that are null or undefined. | |
| 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 includelocale: EN-de
andlocale: fr-CA
.dateStyle
: Determines the style of the date representation, with options includingfull
,long
,medium
,short
, orhidden
. The default style ismedium
.timeStyle
: Specifies the style of the time representation, offering options such asfull
,long
,medium
,short
, orhidden
. The default style ismedium
.
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:
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:
Add the
onChange
function to the logic board.Specify a subscriber name, typically the app's name, in the first input box.
Trigger
onChange
to activate the subscription.Connect the function's callback box to a memorizer for handling database events.
In the example below, the onChange function is called tagger
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.
Resetting the database
The reset
function clears the entire database, including all tables and data.
Proceed with caution, as a database reset cannot be undone.
Last updated