We have updated Flexmonster Software License Agreement, effective as of September 30, 2024. Learn more about what’s changed.
All documentation
  • Introduction
  • Connecting to data source
    1. Supported data sources
    2. Connecting to other data sources
  • Browser compatibility
  • Documentation for older versions
  • 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, PostgreSQL, Oracle, and Microsoft Azure SQL.

    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 on the client side.

    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 SQLServer=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.

    To see how the connection with Flexmonster Data Server is configured in the component, refer to the Configuring the report 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:

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

    For MySQL, MariaDB, PostgreSQL, Oracle

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

    For Microsoft SQL Server

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

    Learn more about calling stored procedures in your database:

    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 on the client side.

    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.

    To see how the connection with Flexmonster Data Server is configured in the component, refer to the Configuring the report 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 on the client side.

    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.

    To see how the connection with Flexmonster Data Server is configured in the component, refer to the Configuring the report section.

    Run Flexmonster Data Server

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

    on Windows

    flexmonster-data-server.exe

    on macOS or Ubuntu/Linux

    ./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

    On the client side, the report should be configured as follows:

    new Flexmonster({
      container: "pivotContainer",
      componentFolder: "node_modules/flexmonster/",
      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").

    Display non-Latin characters correctly

    If your data contains non-Latin characters, ensure you have set UTF-8 encoding for your data and page so the data is displayed 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.

    What's next?