DatabaseNodeEditor Documentation

Overview

The DatabaseNodeEditor is a properties panel editor designed for managing database connector nodes within the Vantage analytics platform. It facilitates the creation and modification of database queries and writes by providing users with an intuitive interface for selecting databases, browsing schemas, and composing queries. The component integrates with multiple database types, enabling seamless interaction with various data sources.

Purpose

The primary purpose of the DatabaseNodeEditor is to:

• Enable users to select database credentials and connectors.
• Provide a schema browser to navigate through tables and columns.
• Allow users to compose SQL and NoSQL queries in a contextual editor.
• Offer functionalities to create or edit database tables.
• Facilitate column mapping when writing data.

Settings

The DatabaseNodeEditor comprises several settings that govern its functionality. Below is a detailed breakdown of each setting.

1. Credential Selection

• Name: credentialId
• Input Type: String (Dropdown)
• Description: This setting allows users to choose a database credential from a dropdown list. The selection affects what database schema is loaded for the editor.
• Default Value: An empty string (""), indicating no credential is selected.

2. Table Selection

• Name: table
• Input Type: String (Dropdown)
• Description: This setting is employed to select a specific table from the currently loaded schema based on the selected credentials. Changes here will load the respective columns.
• Default Value: An empty string (""), indicating no table is selected.

3. Query Text Area

• Name: query
• Input Type: String (Text Area)
• Description: This field serves as the editor for writing SQL or NoSQL queries. Users can insert column names directly from the column list.
• Default Value: An empty string (""), meaning no query is written initially.

4. Error Handling

• Name: error
• Input Type: String
• Description: This property is used to display error messages to the users if any operations related to credential loading or table loading fail.
• Default Value: null, indicating that there is no error initially.

5. Loading States

• Name: credLoading, tablesLoading, columnsLoading
• Input Type: Boolean
• Description: These boolean flags determine whether the application is in a loading state for credentials, tables, or columns, respectively. During loading, the UI may show loading indicators.
• Default Value: false, indicating not in the loading state.

6. Schema Expansion

• Name: schemaExpanded
• Input Type: Boolean
• Description: This setting controls the visibility of the schema browser—the expandable section that shows tables and columns.
• Default Value: true, meaning the schema browser is expanded by default when the editor is opened.

7. Table Builder and Editor Modals

• Name: showTableBuilder, showTableEditor
• Input Type: Boolean
• Description: These boolean states manage the visibility of modals for creating new tables or editing existing ones within the selected database schema.
• Default Value: false, indicating that these modals are closed by default.

How It Works

The DatabaseNodeEditor operates by utilizing state and lifecycle management. Upon loading, it fetches available database credentials from an API. After the user selects a credential, it retrieves the available tables and columns associated with that credential.

1. Credential Change Detection: When a user selects a database credential, the component updates the following fields:

   • credentialRef (with details about the selected credential).
   • databaseId (with the selected credential ID).
   • table (reset to an empty string).
   • query (reset to an empty string).

2. Table and Column Loading: As the user selects a table, the component fetches related columns from the API based on the selected credential and table settings.

3. Query Building: Users can type their queries into the text area or insert column names by clicking on them in the column list.

4. Dynamic Modals and Schema Navigation: Users can dynamically create or modify tables through modal dialogs, enhancing the overall experience of managing database schemas.

Data Expectations

The DatabaseNodeEditor expects the following types of data:

• Credential Data: An array of credential objects containing ids, labels, and service keys.
• Table Data: An array of table objects, each containing properties like name and type.
• Column Data: An array of column objects, each containing properties like name, type, nullable, and primaryKey.

The component gathers this data through API endpoints and updates its state accordingly based on user interactions.

Use Cases & Examples

Use Case 1: Querying from a Relational Database

A data analyst needs to query data from a PostgreSQL database to analyze sales performance. They can use the DatabaseNodeEditor to select their database credential, choose the appropriate table, and write a SQL query to aggregate sales data.

Use Case 2: Creating a New Table

A database administrator wants to create a new user table in a MySQL database. Using the DatabaseNodeEditor, they can select the MySQL database credential, open the table builder modal, define the new user schema, and create the table directly from the editor.

Detailed Example Configuration

Use Case: Querying Sales Data

1. Database Selection:

   • Credential ID: 1 (Selects PostgreSQL credential)

2. Table Selection:

   • Table: sales

3. Query Configuration:

   • Query: SELECT * FROM sales WHERE quantity > 100;

{
   "credentialId": "1",
   "table": "sales",
   "query": "SELECT * FROM sales WHERE quantity > 100;"
}

In this configuration, the user has effectively set up the editor to query sales from a PostgreSQL database with a condition to filter results.

Additional Notes

The DatabaseNodeEditor is integral in enabling data-driven decision-making within businesses by allowing easy interaction with diverse data sources. Users must ensure their credentials have the necessary permissions to access the selected tables and perform the intended operations. Proper handling of errors and clear user feedback is crucial to maintaining a smooth user experience within this component.