Databases

Overview

io.Manager supports connecting to PostgreSQL databases. The following sections outline the steps and requirements for configuring io.Manager to work with PostgreSQL databases and the options for initializing or migrating the database schema.

Connecting to PostgreSQL Databases

To enable io.Manager to connect to a PostgreSQL database, you must either set the necessary environment variables, or provide the required configuration at runtime.

Depending on the deployment approach you have chosen, you have the following options:

If you are using the basic deployment scenario from the template repository approach, you must set properly the necessary environment variables.
If you are using the NPM packages for deployment, or the advanced deployment scenario from the template repository approach, you must provide the necessary configuration settings at runtime when initializing the io.Manager Server.

The following sections provide examples of both options.

Environment Variables

To configure io.Manager to connect to a PostgreSQL database, you must register all of the following environment variables with the proper values. The API_STORE_TYPE environment variable must be set to postgresql. All other variables must be set with values according to your specific environment:

Environment Variable	Description
`API_STORE_TYPE`	Type of the database. Must be set to `postgresql`.
`API_STORE_POSTGRESQL`	PostgreSQL connection URL.
`API_STORE_POSTGRESQL_CREATE_DB`	If set to `true`, will automatically create and initialize the database and the tables necessary for io.Manager. Set to `false` if you want to do this separately. For more details on database schema creation and migration, see the Database Schema section.
`API_STORE_POSTGRESQL_DB_NAME`	Database name for the PostgreSQL connection URL.
`API_STORE_POSTGRESQL_NATIVE_PG_DRIVER`	If set to `true`, will use the `pg-native` implementation. ⚠️ Note that `pg-native` isn't a dependency of the `@interopio/manager` package and must be installed separately.
`API_STORE_POSTGRESQL_SCHEMA_NAME`	PostgreSQL schema name.

Example settings:

API_STORE_TYPE=postgresql
API_STORE_POSTGRESQL=postgresql://my_user:password@localhost:5432
API_STORE_POSTGRESQL_DB_NAME=my_db
API_STORE_POSTGRESQL_SCHEMA_NAME=public
API_STORE_POSTGRESQL_CREATE_DB=true
API_STORE_POSTGRESQL_NATIVE_PG_DRIVER=false

Runtime Configuration

To configure io.Manager to connect to a PostgreSQL database, you must provide the necessary settings at runtime when initializing the io.Manager Server. Use the store property of the optional Config object and provide a PostgreSQLStoreConfig object as its value.

The following example demonstrates configuring the connection to a PostgreSQL database when initializing the io.Manager Server:

import { start } from "@interopio/manager";

// Configuration for the io.Manager Server.
const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    // Configuration for connecting to a PostgreSQL database.
    store: {
        type: "postgresql",
        connection: "postgresql://my_user:password@localhost:5432",
        dbName: "my_db",
        schemaName: "my_schema"
    }
};

// Initializing the io.Manager Server.
const server = await start(config);

The store object has the following properties:

Property	Type	Description
`connection`	`string`	Required. PostgreSQL connection URL.
`createDatabaseAndTables`	`boolean`	If `true` (default), will automatically create and initialize the database and the tables necessary for io.Manager. Set to `false` if you want to do this separately. For more details on database schema creation and migration, see the Database Schema section.
`dbName`	`string`	Database name for the PostgreSQL connection URL. Defaults to `"test"`.
`native`	`boolean`	If `true`, will use the `pg-native` implementation. Defaults to `false`. ⚠️ Note that `pg-native` isn't a dependency of the `@interopio/manager` package and must be installed separately.
`poolConfig`	`object`	Knex.js pool configuration. For more details, see the official Knex.js documentation.
`schemaName`	`string`	PostgreSQL schema name. Defaults to `"public"`.
`type`	`"postgresql"`	Required. Type of the data store. Must be set to `"postgresql"` when using a PostgreSQL database.

Database Schema

The io.Manager schema for PostgreSQL databases can be created or migrated in two ways: automatically, when starting the io.Manager Server, or by using a schema creation script.

Automatic Creation & Migration

io.Manager provides an automated functionality for creating or migrating the database schema. This functionality uses Knex.js migrations internally. The creation or migration of the database schema is executed on startup of the server, only if required.

To enable io.Manager to use this automated functionality, the user configured in io.Manager for connecting to the database must meet at least one of the following requirements:

the user is a SUPERUSER;
the user has the CREATEDB permission;

Schema Creation Script

If you don't want to use the automated functionality of io.Manager for creating and migrating the database schema, you can use the SQL script provided in this section. Execute the script before initializing the io.Manager Server in order to create the necessary database and tables.

Prerequisites

Using this script means that you must disable the automated functionality of io.Manager for creating or migrating the database schema.

If you are using the basic deployment scenario from the template repository approach, you must set the API_STORE_POSTGRESQL_CREATE_DB environment variable to false:

API_STORE_POSTGRESQL_CREATE_DB=false

If you are using the NPM packages for deployment, or the advanced deployment scenario from the template repository approach, you must set the createDatabaseAndTables property to false in the optional Config object for initializing the io.Manager Server:

import { start } from "@interopio/manager";

const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    store: {
        type: "postgresql",
        connection: "postgresql://my_user:password@localhost:5432",
        dbName: "my_db",
        schemaName: "my_schema",
        // Disable the automated functionality for creating the database.
        createDatabaseAndTables: false
    }
};

const server = await start(config);

SQL Script for Creating the Database

⚠️ Note that my_schema and my_user in the script must already be created. Replace my_schema and my_user with the actual schema and user names where necessary.

-- io.Manager schema initialization script for PostgreSQL databases.

SET search_path = my_schema;

CREATE TABLE IF NOT EXISTS groups
(
    groups_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    CONSTRAINT groups_name_unique UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS last_updated
(
    last_updated_id SERIAL PRIMARY KEY,
    applications BIGINT NOT NULL,
    layouts BIGINT NOT NULL,
    groups BIGINT NOT NULL,
    commands BIGINT NOT NULL,
    configs BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS layouts
(
    layouts_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    owner VARCHAR(255) NULL,
    public BOOLEAN NOT NULL,
    disabled BOOLEAN NOT NULL,
    "accessList" VARCHAR(255)[] NULL,
    definition TEXT NOT NULL,
    "createdBy" VARCHAR(255) NOT NULL,
    "createdOn" BIGINT NOT NULL,
    "lastModifiedBy" VARCHAR(255) NULL,
    "lastModifiedOn" BIGINT NULL,
    "default" BOOLEAN NULL,
    CONSTRAINT layouts_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS machines
(
    machines_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    os JSON NOT NULL,
    name VARCHAR(255) NOT NULL,
    displays JSON NOT NULL,
    CONSTRAINT machines_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS prefs
(
    prefs_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    app VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    data JSON NOT NULL,
    "lastUpdate" BIGINT NOT NULL,
    CONSTRAINT prefs_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS sessions
(
    sessions_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    machine VARCHAR(255) NOT NULL,
    start BIGINT NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    glue JSON NOT NULL,
    "lastDataFetch" BIGINT NULL,
    "end" BIGINT NULL,
    closed BOOLEAN NULL,
    "closeReason" VARCHAR(255) NULL,
    CONSTRAINT sessions_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS users
(
    users_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    email VARCHAR(255) NULL,
    password VARCHAR(255) NULL,
    apps VARCHAR(255)[] NOT NULL,
    groups VARCHAR(255)[] NOT NULL,
    "lastUpdated" BIGINT NULL,
    "firstName" VARCHAR(255) NULL,
    "lastName" VARCHAR(255) NULL,
    CONSTRAINT users_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS "glue42SystemConfig"
(
    "glue42SystemConfig_id" SERIAL PRIMARY KEY,
    identifier TEXT NOT NULL,
    config JSON NOT NULL,
    CONSTRAINT glue42SystemConfig_identifier_unique UNIQUE (identifier)
);

CREATE TABLE IF NOT EXISTS feedback
(
    feedback_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    date BIGINT NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    session VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    attachment TEXT NOT NULL,
    reviewed BOOLEAN NULL,
    comment TEXT NULL,
    CONSTRAINT feedback_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS crashes
(
    crashes_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    date BIGINT NOT NULL,
    "user" VARCHAR(255) NULL,
    info JSON NOT NULL,
    reviewed BOOLEAN NULL,
    comment TEXT NULL,
    CONSTRAINT crashes_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS commands_for_version
(
    commands_for_version_id SERIAL PRIMARY KEY,
    version VARCHAR(255) NOT NULL,
    commands JSON NOT NULL,
    CONSTRAINT commands_for_version_version_unique UNIQUE (version)
);

CREATE TABLE IF NOT EXISTS commands
(
    commands_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    status VARCHAR(255) NOT NULL,
    session VARCHAR(255) NOT NULL,
    machine VARCHAR(255) NOT NULL,
    "createdBy" VARCHAR(255) NOT NULL,
    "createdAt" BIGINT NOT NULL,
    command VARCHAR(255) NOT NULL,
    "commandParams" VARCHAR(255) NULL,
    "resultData" JSON NULL,
    "resultAt" BIGINT NULL,
    "resultType" VARCHAR(255) NULL,
    CONSTRAINT commands_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS audit
(
    audit_id SERIAL PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NULL,
    parent VARCHAR(255) NULL,
    date BIGINT NOT NULL,
    session VARCHAR(255) NULL,
    server VARCHAR(255) NULL,
    "entityType" VARCHAR(255) NOT NULL,
    "entityId" VARCHAR(255) NULL,
    "entityDisplayName" VARCHAR(255) NULL,
    operation VARCHAR(255) NOT NULL,
    "operationComment" VARCHAR(255) NULL,
    "newValue" JSON NULL,
    "oldValue" JSON NULL,
    request JSON NULL,
    response JSON NULL,
    CONSTRAINT audit_id_unique UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS app
(
    app_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    disabled BOOLEAN NOT NULL,
    definition JSON NOT NULL,
    public BOOLEAN NOT NULL,
    "accessList" VARCHAR(255)[] NULL,
    "createdBy" VARCHAR(255) NOT NULL,
    "createdOn" BIGINT NOT NULL,
    "lastModifiedBy" VARCHAR(255) NULL,
    "lastModifiedOn" BIGINT NULL,
    CONSTRAINT app_name_unique UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS blobs
(
    blobs_id SERIAL PRIMARY KEY,
    type VARCHAR(255) NOT NULL,
    id VARCHAR(255) NOT NULL,
    "fileName" VARCHAR(255) NOT NULL,
    data bytea NOT NULL,
    CONSTRAINT blobs_id_unique UNIQUE (id)
);

alter table "glue42SystemConfig" rename "config" to "configs";

alter table "glue42SystemConfig" add column "weight" decimal(8, 2);

alter table "users" add column "layouts" JSON ARRAY;

alter table "machines" alter column "os" drop not null;

alter table "machines" alter column "name" drop not null;

alter table "machines" alter column "displays" drop not null;

ALTER TABLE layouts
    ADD CONSTRAINT layouts__name__type__owner__unique
    UNIQUE ("name", "type", "owner");

ALTER TABLE prefs
    ADD CONSTRAINT prefs__app__user__unique
        UNIQUE ("app", "user");

-- Minimum required user permissions for the schema and the schema objects.
GRANT USAGE ON SCHEMA my_schema TO my_user;
GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO my_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO my_user;

io.Manager Documentation

Databases