Set up Database

This continues the external database tutorial, after completing Build daily set processing

In this section, you’ll use a simple database schema in an existing OL Connect MariaDB instance. For clarity, you'll work with a single table named jobs, which resides in the olca_datasets schema created specifically for this sample. Each unique job is recorded in the database along with its status and associated content set ID. If a job is recreated, the flow will update the existing record with the new content set ID rather than creating a duplicate entry.

Note: If you use OL Connect, a simple way to implement an external database is to use the same database instance as the OL Connect Server. In most cases, this is a MariaDB instance containing a schema named olconnect. However, do not use this schema for custom implementations or solutions.

When adding a custom schema, be aware that it may be deleted during the uninstallation or upgrade of OL Connect, especially if the Remove User Data option is selected. To avoid data loss, always back up your custom schemas before upgrading OL Connect.

In a production environment, you might expand this design by adding lookup tables, for example, a status table that defines each status with additional details such as display colors or descriptive text and a dayset table storing information about generated batches, which can be useful for dashboards or reporting.

Create table

For the purpose of this tutorial, we’ll keep things straightforward and create a single table using the following SQL statement. You can execute this using a tool such as MySQL Workbench, which works with both MySQL and MariaDB servers or using a flow.

Copy 
CREATE TABLE IF NOT EXISTS olca_daysets.jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    job_type VARCHAR(32) NOT NULL,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    completed_date TIMESTAMP NULL DEFAULT NULL,
    contentset_id INT NULL,
    batch_id VARCHAR(64) NULL,
    status ENUM('initialized', 'queued', 'completed', 'error') DEFAULT 'initialized' NOT NULL,
    error_message TEXT NULL,
    UNIQUE (file_name),
    
    -- Optional indexes to optimize queries
    INDEX idx_status (status),
    INDEX idx_creation_date (creation_date),
    INDEX idx_batch_id (batch_id),
    INDEX idx_contentset_id (contentset_id)
);

Table fields

  • id: the primary key, with auto-increment ensuring unique identifiers for each job.

  • file_name: stores the name of the input data file. The value is unique, meaning that when a job is re-invoked for the same data file, for example, after making text changes or updating details, the existing record is updated rather than creating a new entry in the database.

  • job_type: holds a short identifier such as letter or invoice.

  • status: uses an ENUM to represent the expected job states. In a more normalized design, this could reference a lookup table.

  • creation_date: automatically populated when a new record is created.

  • completed_date: updated by the flow once the corresponding output has been generated.

  • contentset_id: links to the related content set within the OL Connect database, used when combining multiple content sets into a single output job.

  • batch_id: assigned during output processing and currently stores a timestamp-based value. In a production setup, it would typically reference a separate batches table to track grouped jobs.

Indexes are added on frequently queried fields to optimize filtering and sorting performance.

Creating the table using a flow

You can also create the table directly from within flow editor instead of using an external SQL tool. Follow these steps:

  1. Add an inject node to the canvas. This is used to trigger the flow manually.

  2. Add a template node, double click to enter its Properties, and set its Property field to msg.topic.

  3. Paste the SQL CREATE TABLE statement into the template node’s content.

  4. Add the mysql node to the flow, and double click to enter its Properties.

    • If you have not already installed this node, go to Menu > Manage Palette, Palette, Install,, search for node-red-node-mysql, and install the mysql node.

  5. For Database, click the plus sign, +, to add a new database. This opens another Properties panel.

  6. Enter the Host, Port, Username, Password, and Database (schema) name for the database.

A flow consisting of the input (timestamp), create table node, and a connected database.

The mysql node executes any SQL query provided through the msg.topic property.

In this setup, the template node injects the SQL command into msg.topic, enabling the mysql node to create the table when you manually trigger the inject node.

Tip: You can use environment variables for all connection fields, such as ${MY_DB_HOST}, ${MY_DB_PORT}, ${MY_DB_USER}, and ${MY_DB_PASSWORD}, to make your flow easier to maintain and relocatable. For information about environment variables, see Use environment variables in flows.

Continue to Store job information.