Relational Database

The Database class is designed to simplify interactions with SQL databases like PostgreSQL, MySQL, and SQLite. It provides a consistent and powerful API for defining tables, managing relationships, and performing Create, Read, Update, and Delete (CRUD) operations without writing raw SQL.

You must create an instance of this class to connect to your database.

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

create

Initializes the database connection and loads any existing table models.

If you want to use the internal database this step is not needed, simply use the functions available under the existing instance called internal-database.

Parameters

options: An object for configuring the database connection.
- dialect: The database dialect (e.g., 'postgres', 'mysql', 'sqlite').
- database: The name of the database.
- username: The username for authentication.
- password: The password for authentication.
- host: The hostname or IP address of the database server.
- port: An optional port number.
- ssl: Whether to use SSL for the connection. Defaults to true for some dialects.

Example

# options
dialect: 'postgres'
database: 'mydb'
username: 'user'
password: 'pass'
host: 'localhost'
ssl: true

Connection & Database Management

isConnected

Checks if the database connection is currently active and authenticated.

Output

Returns true if connected, false otherwise.

getAllTables

Retrieves a list of all tables that currently exist in the database.

Output

An array of table name strings.

reset

DANGER: This function drops and recreates the entire database, deleting ALL tables and data. Use with extreme caution.

Output

Returns true if the reset was successful.

Schema & Table Definition

defineTable

Defines a table's schema. If the table doesn't exist, it will be created. If it exists, this function will attempt to alter it by adding any new fields.

When creating a table, additional fields will automatically be added to the table (if not already explicitly specified):

id: Serves as the table's primary key. It's a UUID by default.
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.

Parameters

name: The name of the table (e.g., 'users').
fields: An object defining the table's columns.
- Keys are the field names (in camelCase).
- Values can be a simple string for the data type or an object for more options.
- Supported data types: string, text, integer, number, boolean, date, json, jsonb, file.
options: An optional object.
- trackHistory: If true, a separate history table will be created to log all changes to records in this table.

When using postgres, we highly recommend to use the jsonb datatype for JSON data. The advantage being that nested properties of the JSON object can be part of filtering expressions later.

Being a best practice in database design and software development, use English and (in our case) camelCase notation for naming tables and fields in your database. Examples include firstName and dateOfBirth. In particular, avoid using spaces, underscores, dashes and other special characters.

Example 1: Simple table

# name
users
# fields
name: string
email: uniquestring
age: integer

Example 2: Table with custom primary key and options

# name
products
# fields
id: { type: 'string', primaryKey: true }
name: string
price: number
specs: jsonb

Always use id for the name of the primary key. Otherwise things may not work as expected.

Advanced Field Configuration

For more control, you can provide an object for a field's value with the following properties:

type: The data type string (e.g., 'string', 'integer'). This is required.
primaryKey (boolean): If true, sets this field as the primary key. This will override the default id field.
unique (boolean): If true, ensures all values in this column are unique. You can use 'uniquestring' or 'uniqueinteger' as a shorthand type.
allowNull (boolean): If false, this field must have a value.
defaultValue: Sets a default value if none is provided. This can be a literal value (e.g., 'active', 0) or a special Sequelize value (like DataTypes.NOW for the current time).
autoIncrement (boolean): If true, automatically increments an integer primary key for each new row.
references: Used internally by association functions (optionallyHasOne, etc.) to create foreign key relationships.
onDelete / onUpdate: Defines the behavior when a referenced foreign key is deleted or updated (e.g., CASCADE, SET NULL).

Example: Advanced table with custom primary key and constraints

# name
'employees'
# fields
employeeId: { type: 'integer', primaryKey: true, autoIncrement: true }
email: { type: 'string', allowNull: false, unique: true }
status: { type: 'string', defaultValue: 'active' }
hireDate: { type: 'date', defaultValue: 'NOW' } // Uses database's NOW()

getTableSchema

Retrieves the schema definition for a given table.

Parameters

name: The name of the table.

deleteTable

Deletes an entire table and all its data.

Parameters

name: The name of the table to delete.

Querying and Filtering Data

getTableData

Fetches rows from a table, with powerful options for filtering, sorting, selecting specific fields, and formatting the output. This is the primary function for reading data from the database.

Parameters

name: The name of the table to query.
options: An optional object to refine the query.
- filter: An array defining the conditions that rows must meet to be included. See the "Filtering Explained" section below for details.
- fields: An array of strings to select specific columns. If omitted, all columns are returned. You can also select from related tables using dot notation (e.g., 'user.name').
- order: An array specifying the sort order, in the format ['fieldName', 'DIRECTION'], where DIRECTION is 'ASC' for ascending or 'DESC' for descending.
- limit: An integer specifying the maximum number of rows to return.
- offset: An integer specifying the number of rows to skip from the beginning of the result set. Useful for pagination.
- autoJoin: If true (the default), data from tables related via associations will be automatically included in the result.
- locale: A locale string (e.g., 'en-US', 'de-DE') to format date and time values in the output.
- dateStyle / timeStyle: Strings that control the verbosity of formatted dates and times ('full', 'long', 'medium', 'short'). Can also be 'hidden' to omit that part of the timestamp.

Example

Goal: Receive the full user's table including all potential associated tables (potentially expensive call)

# name
users

Filtering Explained

The filter option allows you to build precise queries. It uses a special array syntax that can be nested to create complex logic.

Simple Conditions A simple condition is an array with three elements: [fieldName, operator, value].

fieldName: The name of the column you want to filter on.
operator: A string representing the comparison to perform.
value: The value to compare against.

Compound Conditions You can combine multiple conditions using the logical operators 'and' or 'or'. This creates a nested structure.

AND: [ [condition1], 'and', [condition2] ] - Both conditions must be true.
OR: [ [condition1], 'or', [condition2] ] - At least one of the conditions must be true.

Available Operators

Operator(s)

Description

Example Value

=, eq, is

Equals

'John' or 100

<>, ne, isnot

Not equals

'John' or 100

>, gt

Greater than

99

>=, gte

Greater than or equal to

100

<, lt

Less than

100

<=, lte

Less than or equal to

100

contains

The string field contains the value (case-insensitive)

'oh' (matches 'John')

notcontains

The string field does not contain the value

'Peter'

startswith

The string field starts with the value

'J'

endswith

The string field ends with the value

'oe' (matches 'Doe')

between

The value is between two values in an array

[18, 30] or ['A', 'D']

in

The value is one of several possibilities in an array

['active', 'pending']

Examples

Example 1: Simple filter and field selection Goal: Get the name and email for all users with the status 'active'.

# name
users
# options
filter: ['status', '=', 'active']
fields: ['name', 'email']

Example 2: Using between for a date range Goal: Find all orders placed in January 2025.

# name
orders
# options
filter: ['createdAt', 'between', ['2025-01-01', '2025-01-31T23:59:59Z']]

Example 3: Compound 'AND' filter Goal: Find products that are in stock (quantity > 0) AND cost more than 50.

# name
products
# options
filter: [ ['quantity', '>', 0], 'and', ['price', '>', 50] ]

Example 4: Compound 'OR' filter with startswith Goal: Find all users whose name starts with 'A' OR 'B'.

# name
users
# options
filter: [ ['name', 'startswith', 'A'], 'or', ['name', 'startswith', 'B'] ]

Example 5: Using in with sorting and limiting Goal: Get the 5 most recent high-priority ('high' or 'critical') tickets.

# name
tickets
# options
filter: ['priority', 'in', ['high', 'critical']]
order: ['createdAt', 'DESC']
limit: 5

Output An array of objects, where each object represents a row from the table that matches the specified criteria.

findRow

Finds and returns the first row that matches the provided filter. Returns null if no match is found.

Parameters

table: The name of the table.
options: An object containing a filter and optional fields.

Data Manipulation (CRUD)

addRow

Adds a single new row to a table.

Parameters

table: The name of the table.
data: An object where keys are the column names (in camelCase) and values are the data to insert.

Example

# table
users
# data
name: Jane Doe
email: [email protected]
age: 34

addRows

Adds multiple rows to a table in a single, efficient bulk operation.

Parameters

table: The name of the table.
data: An array of data objects to insert.

Example

# table
products
# data
[
  { name: 'Thingamajig', price: 19.99, stock: 100 },
  { name: 'Widget', price: 25.50, stock: 250 }
]

upsertRow

Atomically "updates or inserts" a row. This function checks for the existence of a row and either updates it or creates a new one.

By default, the check is performed using the primary key (id) provided in the data object.

The optional uniqueKey parameter allows you to specify a different business key (like an email address or a product SKU) for this check. If you provide a uniqueKey, the function will look for a row matching that key. If found, it updates it; if not, it creates a new row.

Parameters

table: The name of the table.
data: The data object to upsert.
uniqueKey: An optional object specifying a unique key other than the primary key for collision detection.

Example 1: Upsert using the default primary key (id) Goal: Update the user with a specific ID, or create them if they don't exist.

# table
users
# data
id: 'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
name: Jane Smith
age: 36

Example 2: Upsert using a custom unique key (email) Goal: Find a user by their email. If they exist, update their age. If not, create a new user with the given name, age, and email.

# table
users
# data
name: Jane Doe
age: 35
# uniqueKey
email: '[email protected]'

changeRow

Changes the content of a specific row identified by its primary key. This is a flexible update function.

Parameters

table: The name of the table.
id: The primary key (id) of the row to change.
data: An object containing the fields and new values to set.
options: An optional object.
- patch: If true, allows for partial updates of nested JSON objects instead of replacing them entirely.

Example: Update a user's age and status

# table
users
# id
'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
# data
age: 37
status: 'active'

updateRow

Updates a row identified by its id. This function replaces the entire record with the new data object. Any fields not included in the data object may be set to null or their default value.

Parameters

table: The name of the table.
data: The data object to update. It must contain the id property.

Example: Replace an entire user record

# table
users
# data
id: 'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
name: 'Jane Smith'
email: '[email protected]'
# The age field would be set to null if it existed before

patchRow

Updates a row identified by its id. This function merges the new data into the existing record. Only the fields provided in the data object will be changed. Nested JSON objects are also merged.

Parameters

table: The name of the table.
data: The data object containing the new values. It must contain the id property.

Example: Update only a user's age

# table
users
# data
id: 'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
age: 38
# Name and email remain unchanged

deleteRow

Deletes a single row from a table identified by its primary key.

Parameters

table: The name of the table.
id: The primary key of the row to delete.

Example

# table
users
# id
'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'

clearTable

Deletes all rows from a table, but leaves the table structure intact.

Parameters

name: The name of the table to clear.

Example

# name
logs

`$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.

Relationships and Associations

optionallyHasOne

Creates a one-to-many relationship where the child record can exist without the parent (the foreign key is nullable).

Hint: "A child has zero or one parent. A parent may have many children."

Parameters

childTable: The table that will receive the foreign key.
parentTable: The table being referenced.

mandatorilyHasOne

Creates a one-to-many relationship where the child record cannot exist without the parent (the foreign key is non-nullable).

Hint: "A child must have exactly one parent. A parent may have many children."

Parameters

childTable: The table that will receive the foreign key.
parentTable: The table being referenced.

optionallyHasMany

Creates a many-to-many relationship between two tables using a junction table.

Hint: "A child can have many parents. A parent can have many children."

Parameters

childTable: The first table in the relationship.
parentTable: The second table in the relationship.

associateRow

Needed only for many-to-many relations: Associates two or more existing rows with each other after a relationship has been defined.

Parameters

sourceTable: The name of the source table.
sourceId: The ID of the row in the source table.
targetTable: The name of the target table.
targetId: The ID (or array of IDs for many-to-many) of the row(s) in the target table.

Detailed information and examples

The association functions allow you to define relationships between tables, creating a relational data model. The process typically involves three steps:

Define the tables using defineTable.
Define the relationships between them using one of the association functions.
Link specific records together by setting the foreign key (one-top-many) or using the associateRow function (many-to-many)

Example Scenario

Let's model a simple e-commerce system with customers, orders, products and shippingAddresses.

A customer can have many orders.
An order must belong to exactly one customer.
An order can contain many products.
A product can be in many orders.
A customer can have additional shippingAddresses .

Step 1: Define the Tables

# Define the customers table (call defineTable)
# name: 
customers
# fields:
name: string
email: uniquestring

# Define the orders table (call defineTable)
# name: 
orders
# fields:
orderDate: date
status: string

# Define the products table (call defineTable)
# name
products
# fields
productName: string
price: number
  
# Define the shippingAddresses table (call defineTable)
# name
shippingAddresses
# fields
address: string

Step 2: Define the Relationships

mandatorilyHasOne

Creates a one-to-many relationship where the child record cannot exist without the parent (the foreign key is non-nullable).

Hint: Use this when "A child must have exactly one parent. A parent may have many children."

Goal: An order must belong to a customer.

# childTable
orders
# parentTable
customers

This adds a non-nullable customerId foreign key to the orders table. It means in consequence, that when creating a new order record the primary key of an existing customer has to be provided to the customerId field. Step 3: Link records together
# (call the addRow function with)
# name
orders
# data
orderDate: 2025-07-05T14:48:00.000Z
status: new
customerId: 06762a2f-6bdf-46ba-82d5-6b712e124b0e

optionallyHasOne

Creates a one-to-many relationship where the child record can exist without the parent (the foreign key is nullable).

Hint: Use this when "A child has zero or one parent. A parent may have many children."

Goal: A customer can optionally have a shippingAddress.

# childTable
customers
# parentTable
shippingAddresses

This adds a nullable shippingAddressId foreign key to the customers table. It means in consequence that a new shipping address can be created without directly providing a customer. The linkage can be established at a later time via an call to changeRow for example. Step 3: Link records together
# (call e.g. the patchRow function with)
# name
customers
# data
id: b6337fe3-84ee-44e5-931e-3a1e505ecc9e
shippingAddressId: 2485af18-944e-4ce6-82c6-e126db41e0ad

optionallyHasMany

Creates a many-to-many relationship between two tables, creating a new junction table automatically.

Hint: Use this when "A child can have many parents. A parent can have many children."

Goal: An order can contain many products.

# childTable
orders
# parentTable
products

This creates a new table __orders2products to link orders and products. To link those records together you must utilize the assciateRow function. Step 3: Link records together
Goal: Link an existing order to an existing customer.
# (call associateRow)
# sourceTable
orders
# sourceId
b4d9b899-cd24-4f90-8199-144683bd177e
# targetTable
customers
# targetId
7b01e852-7492-414a-b01a-a1bc523b406
Goal: Add two products to an existing order.
# sourceTable
orders
# sourceId
b6f652df-baa1-4617-83c6-135d48836d6d
# targetTable
products
# targetId
[d44829f8-3bc1-4c80-853be1e37bfb, 7c862015-48cd-4287-b4cf-affcc087ef3d]

History Tracking

getHistoricalData

Retrieves the change history for a specific record in a table where history tracking is enabled.

Parameters

name: The name of the table.
options: An object to configure the query.
- filter: A filter to identify the single record whose history you want.
- start / stop: A time range for the history (e.g., '-1h', 'now').

"Magic" Functions (Auto-Schema)

These functions can create and alter tables on the fly based on the data provided. Use them for rapid prototyping or when dealing with unpredictable data structures.

autoUpsertRow

Upserts a row into a table. If the table or required columns do not exist, they will be created automatically based on the data provided.

Parameters

table: The name of the table.
data: The data object to upsert.
uniqueKey: An optional unique key for collision detection.

autoAddRows

Bulk-inserts data into a table. Like autoUpsertRow, it will create or alter the table schema as needed based on the structure of the first data object in the array.

Parameters

table: The name of the table.
data: An array of data objects to insert.

Last updated 1 day ago

create

Connection & Database Management

isConnected

getAllTables

reset

Schema & Table Definition

defineTable

getTableSchema

deleteTable

Querying and Filtering Data

getTableData

findRow

Data Manipulation (CRUD)

addRow

addRows

upsertRow

changeRow

updateRow

patchRow

deleteRow

clearTable

$USER Variable

Relationships and Associations

optionallyHasOne

mandatorilyHasOne

optionallyHasMany

associateRow

Example Scenario

mandatorilyHasOne

optionallyHasOne

optionallyHasMany

History Tracking

getHistoricalData

"Magic" Functions (Auto-Schema)

autoUpsertRow

autoAddRows

`$USER` Variable