Data sources guide

This tutorial describes how to connect the Data Server used as a console application to your data. To connect the Data Server installed as a Windows/Unix service to your data, see this documentation page.

Server configurations vary depending on the data source type: database, JSON, or CSV.

Connecting to databases

Flexmonster Data Server supports the following databases: MySQL, MariaDB, Microsoft SQL Server, Microsoft Azure SQL, PostgreSQL, and Oracle.

To connect to a database with Flexmonster Data Server, specify the Type, DatabaseType, ConnectionString, and Indexes properties in the flexmonster-config.json file. For example:

{
  "DataSources": [
    {
      "Type": "database",
      "DatabaseType": "mysql"
      "ConnectionString":
        "Server=localhost;Port=3306;Uid=root;Pwd=password;Database=database_name",
      "Indexes": {
        "index_database": {
          "Query": "SELECT * FROM tablename"
        }
      }
    }
  ]
}

"index_database" is a dataset identifier. It will be used to configure the data source in Flexmonster Pivot.

Query is an SQL query to execute. It can contain a stored procedure.

DatabaseType specifies the type of the database: "mysql", "mariadb", "mssql" (Microsoft SQL Server and Microsoft Azure SQL), "postgresql", or "oracle".

ConnectionString is a connection string for the database. Here are some example connection strings for each supported database type:

• MySQL and MariaDB: "Server=localhost;Port=3306;Uid=;Pwd=;Database= "
• Microsoft SQL Server: "Server=(localdb)\\MSSQLLocalDB;Uid=;Pwd=;Database= "
• PostgreSQL: "Server=localhost;Port=5432;Uid=;Pwd=;Database= "
• Oracle: "Data Source=ORCL;User Id=;Password=;"
• Microsoft Azure SQL: Server=tcp:myserver.database.windows.net,1433;Database= ;User ID=;Password=;Trusted_Connection=False;Encrypt=True; (to connect to Microsoft Azure SQL, set the "DatabaseType" to "mssql")

See the full list of configurations available for the Data Server: Configurations reference.

To start Flexmonster Data Server, refer to the Run Flexmonster Data Server section.

Setting passwords with special characters in the connection string

The Data Server parses passwords with special characters correctly unless the password contains ; or ". These symbols are treated as delimiters, so they should be escaped:

• If your password contains a semicolon (;), enclose the password in single quotes (e.g., Pwd='123;45').
• If your password contains a double quote ("), escape this symbol with a backslash (e.g., Pwd=123\"45).

Calling a stored procedure

Follow the steps below to call a stored procedure in the Data Server:

Step 1. Create a stored procedure in your database:

• MySQL
• MariaDB
• Microsoft SQL Server (also works for Microsoft Azure SQL)
• PostgreSQL
• Oracle

Step 2. Call the stored procedure in the Query property:

{
  // other properties
  "Indexes": {
    "index_database": {
      "Query": "call storedProcedure()"
    }
  }
}

{
  // other properties
  "Indexes": {
    "index_database": {
      "Query": "exec storedProcedure"
    }
  }
}

Learn more about calling stored procedures in your database:

• MySQL
• MariaDB
• Microsoft SQL Server (also works for Microsoft Azure SQL)
• PostgreSQL
• Oracle

Note Stored procedures allow executing any valid SQL query from the Data Server. To avoid unauthorized access to the database, create a database user with read-only access rights and specify this user in the connection string.

Connecting to JSON

Flexmonster Data Server supports only a specific JSON format – an array of objects, where each object is an unordered set of "key": "value" pairs. Here is an example:

[
  {
    "Color" : "green",
    "Country" : "Canada",
    "State" : "Ontario",
    "City" : "Toronto",
    "Price" : 174,
    "Quantity" : 22
  },
  ...
]

To connect to JSON with Flexmonster Data Server, specify the Type and Indexes properties in the flexmonster-config.json file. For example:

"DataSources": [
  {
    "Type": "json",
    "Indexes": {
      "index_json": {
        "Path": "../data/data.json"
      }
    }
  }
], 

"index_json" is a dataset identifier. It will be used to configure the data source in Flexmonster Pivot.

Note By default, the Data Server resolves data types for all fields automatically. If you need to set data types for some fields manually, specify the Mapping property for your index.

To start Flexmonster Data Server, refer to the Run Flexmonster Data Server section.

Connecting to CSV

To connect to CSV with Flexmonster Data Server, specify the Type and Indexes properties in the flexmonster-config.json file. For example:

"DataSources": [
  {
    "Type": "csv",
    "Indexes": {
      "index_csv": {
        "Path": "../data/data.csv"
      }
    }
  }
], 

"index_csv" is a dataset identifier. It will be used to configure the data source in Flexmonster.

Note By default, the Data Server resolves data types for all fields automatically. If you need to set data types for some fields manually, specify the Mapping property for your index.

To start Flexmonster Data Server, refer to the Run Flexmonster Data Server section.

Run Flexmonster Data Server

To start Flexmonster Data Server, run the following command in the console:

flexmonster-data-server.exe

./flexmonster-data-server

As soon as you start Flexmonster Data Server, it automatically preloads the data specified in the Indexes property. Thus, when Flexmonster Pivot requests the data, the server responds with already preloaded data.

Note The Data Server keeps preloaded data in the server's RAM, so the number of indexes you can specify is limited by the server's RAM amount.

Configuring the report

In Flexmonster Pivot, the report should be configured as follows:

const pivot = new Flexmonster({
  container: "pivotContainer",
  report: {
    dataSource: {
      type: "api",
      url: "http://localhost:9500",
      index: "index-json"
    }
  }
}); 

Note The index must match the name of the index defined when configuring Flexmonster Data Server (e.g., "index‑json").

See the full list of Flexmonster properties used to configure the dataSource object.

List of supported configuration parameters

When connecting to data using Flexmonster Data Server, you can use the following properties of the DataSourceObject:

Display non-Latin characters correctly

If your data contains non-Latin characters, ensure you have set UTF-8 encoding for your data and page. This is required to display the data correctly in the component.

About response caching

When Flexmonster Pivot requests data, Flexmonster Data Server sends a response and then caches it. If the component sends the same request again, the server responds with the data from its cache.

The Data Server’s cache has a limit. When the cache does not have enough space for a new response, the Data Server deletes one of the previously cached responses. You can manage the cache size via the CacheSizeLimit property.

The Data Server clears the cache when restarted.

See also

• Installing the Data Server as a console application
• Configurations reference
• Security and authorization guide