Skip to main content

Data Setup

Introduction​

With Netuno, application development starts with building the database, through the creation of forms and fields.

And when working as a team or when the application development needs to be moved to another environment, it is necessary to create a database backup along with the code.

So at the destination, the database will need to be reconfigured, and then the application can run as it was developed at its origin.

The problem with this process is the effort of always remembering to create a new backup whenever there is a change in the database. And the more people involved in development and/or the more environments there are for publishing the application, the more complicated this process becomes.

So to solve this problem, Netuno provides an application setup system, which automatically generates the database structure (schema) and additionally allows the loading of initial data to be programmed manually through additional scripts.

It also allows changing the database server type, ensuring that the setup runs normally and the application runs without any problems. This means that the application can be developed in H2DataBase and, with the schemas generated automatically, you can change the application's database connection to PostgreSQL or MariaDB. On the first run of the application on the new database, everything will be rebuilt, regardless of the database server type change.

Thus, it allows the database to be completely changed without impacting the development already done.

The setup programming files are located inside the application folder at:

  • server/setup

Setup Configuration​

In the application environment configurations located at:

  • /apps/MY_APP/config/_development.json
  • /apps/MY_APP/config/_production.json

You can control the behavior of 'setup' through the following configurations:

    ...
    "setup": {
        "enabled": true,
        "schema": {
            "auto_create": true,
            "execution": true
        },
        "scripts": {
            "execution": true
        }
    },
    ...
enabled: boolean > default true​

Defines whether setup is active or not, and thus enables or disables the execution of data setup operations on the first run of the application.

schema.auto_create: boolean > default true​

Allows Netuno to know whether it should generate database schema files whenever a form or field is created or changed.

schema.execution: boolean > default true​

Controls whether the schema should be executed during setup operations performed on the first run of the application.

scripts.execution: boolean > default true​

Enables or disables the execution of additional scripts during setup operations performed on the first run of the application.

Schema​

When forms and fields are created or changed in the Netuno web interface, schema files are generated inside the setup folder as follows:

  • server/setup/_schema-FORM_NAME.js

There will be one _schema-*** file for each form in the application.

These files should not be changed manually because they are generated automatically by Netuno.

So, if you change the content of these files, on the next schema generation they will be replaced automatically, and changes made directly to the files will be lost.

For automatic database schema generation to run, make sure that the schema.auto_create configuration is active and that the setup folder exists inside the server folder.

Scripts​

Custom programmed code scripts are files located in the setup folder that do not start with _.

The main goal of these scripts is to load data, ensuring that the database contains the minimum information required for the application to work correctly.

So when the application is reconfigured in another environment, it will be automatically loaded with the initial data and will be ready to start being used.

To perform these data loading operations, you should use the functions of the _db resource.

The scripts are executed after the schemas are executed, in alphanumeric order of the names of files that do not start with _.

Starting file names with a numeric sequence is ideal for defining the execution order, so custom scripts are executed in the order of the file names that do not start with _.

Insert Custom Data​

And to save the work of making database queries to check whether data insert can be performed or not, Netuno provides the insertIfNotExists function.

This function checks whether the data exists exactly as defined in the database; if it does not exist, it creates it.

An example of its use is loading the Code and Name fields of the Territory Type form, where the table name is territory_type and it has the code and name columns; to do so, simply create the file with the following content:

server/setup/001-territory_type.js
_db.insertIfNotExists("territory_type", _val.map()
    .set("code", "1001")
    .set("name", "Continental")
);
_db.insertIfNotExists("territory_type", _val.map()
    .set("code", "1002")
    .set("name", "Archipelagic")
);
_db.insertIfNotExists("territory_type", _val.map()
    .set("code", "2003")
    .set("name", "Maritime")
);
_db.insertIfNotExists("territory_type", _val.map()
    .set("code", "3004")
    .set("name", "Aerial")
);

Processing Cycle​

As explained, the setup only runs on the first run of the application.

And the execution order is:

  1. _start - runs the initialization file, which can contain any type of custom programming:
    • MY_APP/server/setup/_start.js
  2. _cleanup - runs the removal of forms, reports, and their respective fields programmatically:
    • MY_APP/server/setup/_cleanup.js
  3. _schema-* - runs all scripts with the _schema- prefix; contains the commands for creating forms, reports, and their respective fields:
    • MY_APP/server/setup/_schema-autogenerated_structure.js
    • MY_APP/server/setup/_schema-category.js
    • MY_APP/server/setup/_schema-product.js
    • MY_APP/server/setup/_schema-customer.js
  4. 001-other_scripts - runs the custom programmed scripts files; these are all files that do not start with _ and that will likely contain the necessary initial data inserts or other custom developments, for example:
    • MY_APP/server/setup/001-insert_custom_data.js
    • MY_APP/server/setup/002-category.js
    • MY_APP/server/setup/003-product.js
    • MY_APP/server/setup/004-customer.js
  5. _end - runs the finalization file, which can contain any type of custom programming:
    • MY_APP/server/setup/_end.js

In other words, custom code can be created in the start and end scripts to perform some operations that make sense to run before setup or even when it is completed.

It is worth reinforcing that this processing cycle runs whenever the application is run for the first time, which is why it is necessary to ensure that data existence is checked before creating it.

Make sure the following environment configurations are active to allow this process to run in the MY_APP/config/_development.json or MY_APP/config/_production.json files, depending on the environment:

  • Enables or completely disables the execution of any setup operation:

    setup.enabled = true|false

  • Enables or disables the execution of _schemas-:

    setup.schema.execution = true|false

  • Enables or disables the execution of scripts:

    setup.scripts.execution = true|false

Cleanup​

To program the removal of forms and fields, we should use the _cleanup.js script, which ensures that for all environments that synchronize the project files, when setup runs, the structure will be removed if it exists.

Example of the cleanup script:

server/setup/_cleanup.js
// Remove the zone form:
_form.dropIfExists("zone");
// Remove the subarea_id field from the area form:
_form.dropFieldIfExists("area", "subarea_id");

DBML​

Whenever Netuno runs the data setup, the following file is generated:

  • server/setup/_db-schema.dbml

This is the file with the entire data structure that can be used with Artificial Intelligence.

Based on the DBML content, Artificial Intelligence understands the data structure and can generate accurate SQL queries.

Try using this file as an attachment in the prompt and ask it to generate complex SQL queries; you will get good results.

Conclusion​

The application setup process in Netuno has the following main functions:

  • _schema-: automatically generates the entire structure of forms and reports with their respective fields, which helps ensure the consistency of the application's database structure, especially when the application runs against a new empty database.
  • scripts: perform database operations to ensure that the application contains the required data preloaded whenever it is run for the first time.

This way, Netuno allows applications to work in new environments, even if the database type changes, ensuring the application works correctly without the need to perform database backups and restore.

Additionally, it allows all configuration code generated automatically and manually to be versioned with GIT, for example.