Mon. Feb 26th, 2024

Advertisement: Click here to learn how to Generate Art From Text

Creating dashboards for visualizing SQL knowledge has been a related demand for a very long time. In distinction, applied sciences for constructing net apps change fairly incessantly, so builders meet this unchangeable demand in a different way yearly.

On this article, I’ve determined to share my newest skilled expertise creating an internet app for visualizing knowledge from a database.

Selecting applied sciences

One of many necessities I had was to make use of MySQL. Nonetheless, I might freely select the remainder of my toolkit, together with a framework.

React has at all times been common amongst net builders for constructing wealthy consumer interfaces. I’ve additionally had an important expertise utilizing React in my earlier initiatives and determined to refresh my abilities with it.

However React shouldn’t be really a framework anymore. Studying by the React documentation, I’ve discovered that any further, initiatives needs to be created utilizing one of many following React-based frameworks:

In line with the React staff, utilizing a framework from the beginning ensures that the options utilized by most net functions are built-in, reminiscent of routing, HTML technology, knowledge fetching, and many others.

On this approach, when your challenge reaches the purpose when such options are wanted, you don’t want to search for further libraries and configure them on your challenge. As a substitute, all options are already out there for you, making growth a lot smoother. You’ll be able to see extra particulars on the React web site.

This left me with one query…

Which framework ought to I exploit?

As with all the pieces on this world, there isn’t a common React framework that fits each want.

I’ve determined to cease on Subsequent.js for my challenge, and right here is why:

The framework is chosen, but it surely’s not sufficient. We additionally want instruments for connecting to MySQL and visualizing its knowledge.

Selecting knowledge visualization instruments

You must rigorously contemplate which third-party instruments to make use of in your challenge since there are such a lot of choices and never each considered one of them would possibly fit your wants. For me, the right selections had been Flexmonster and Recharts.

Each of those instruments are versatile, highly effective, and straightforward to make use of.

  • Flexmonster is primarily a pivot desk and permits working with completely different datasets, reminiscent of MySQL. It will also be built-in with numerous frameworks, together with Subsequent.js! Flexmonster’s documentation even offers a devoted tutorial for integrating this device with Subsequent.js. Flexmonster is a paid device, but it surely presents a 30-day trial, which was greater than sufficient for me to get acquainted with the product and decide if it fits my use instances.
  • Recharts is a charting library with a wide array of built-in charts and customization choices. The library pairs particularly effectively with Subsequent.js functions because it makes use of React’s component-based structure. Recharts is a free device launched beneath the MIT license, which is value contemplating.

My deciding issue was the simple setup of Flexmonster, Recharts, and their communication. Whereas Flexmonster connects to the MySQL dataset, processes its knowledge, and visualizes the information within the pivot desk, Recharts can use the processed knowledge to visualise it in charts.

In case you are questioning whether or not these instruments suit your challenge, take a look at the finest instruments for knowledge visualization in React article. It covers in nice element the completely different choices in the marketplace, their professionals and cons, in addition to their use instances.

Now that the preparations are full let’s create the precise app!

Create a Subsequent.js challenge

First, a primary Subsequent.js app have to be created with the next command:


npx create-next-app next-sql --ts --app && cd next-sql

Discover the --ts and --app arguments. They allow TypeScript and the brand new App Router function, which this tutorial assumes in additional directions.

Additionally, you will be given prompts to allow different options, reminiscent of ESLint or Tailwind CSS. For simplicity, reply No. But when you already know what you’re doing and these options are crucial on your challenge – be at liberty to allow them.

Additionally, it’s finest to take away pointless knowledge from the newly created challenge. For the needs of this tutorial, we received’t want the public/ folder so you could delete it. When you assume you would possibly want this folder for different causes, be at liberty to depart it the place it’s.

As well as, change the app/web page.tsx file, so it has the next contents:


"use shopper"
import types from './web page.module.css'

export default operate Residence() {
  return (
    <essential className={types.essential}>
    </essential>
  );
}

It’s necessary that the web page is marked as a Consumer Part as a result of we’ll add options that can be utilized solely when the web page is rendered on the shopper facet.

Lastly, the pointless types needs to be deleted from the app/web page.module.css file so it has the next content material:

.essential {
  show: flex;
  flex-direction: column;
  justify-content: space-between;
  align-items: heart;
  padding: 6rem;
  min-height: 100vh;
}

Embed knowledge visualization instruments

Subsequent, we have to add Flexmonster and Recharts to our newly created challenge. Let’s begin with Flexmonster.

Embedding Flexmonster

First, set up the Flexmonster CLI:

npm set up -g flexmonster-cli

Subsequent, obtain the Flexmonster wrapper for React:

flexmonster add react-flexmonster

Now let’s add Flexmonster to our web page:

  1. Import Flexmonster types into the app/world.css file in your challenge:
    @import "flexmonster/flexmonster.css";
  2. Create the PivotWrapper Consumer Part (e.g., app/PivotWrapper.tsx) that may wrap FlexmonsterReact.Pivot:
    
      "use shopper"
      import * as React from "react";
      import * as FlexmonsterReact from "react-flexmonster";
      import Flexmonster from "flexmonster";
    
      kind PivotProps = Flexmonster.Params & {
        pivotRef?: React.ForwardedRef<FlexmonsterReact.Pivot>;
      };
    
      const PivotWrapper: React.FC<PivotProps> = ({ pivotRef, ...params }) => {
        return (
          <FlexmonsterReact.Pivot
            {...params}
            ref={pivotRef}
          />
        );
      };
    
      export default PivotWrapper;
    
  3. Import the PivotWrapper into your Subsequent.js web page (e.g., app/web page.tsx) as a dynamic element with out SSR:
    
      import dynamic from "subsequent/dynamic";
    
      const PivotWrapper = dynamic(() => import("@/app/PivotWrapper"), {
        ssr: false,
        loading: () => <p>Loading Flexmonster...</p>
      });
    

    The SSR is disabled as a result of Flexmonster makes use of the window object, so it can’t be rendered on a server.

  4. In the identical web page file, create an extra element for ref forwarding (e.g., ForwardRefPivot):
    
      import * as React from "react";
      import { Pivot } from "react-flexmonster";
    
      const ForwardRefPivot = React.forwardRef<Pivot, Flexmonster.Params>(
        (props, ref?: React.ForwardedRef<Pivot>) => <PivotWrapper {...props} pivotRef={ref}/>
      );
    
  5. Contained in the web page element (e.g., Residence), create an empty ref object (e.g., pivotRef):
    
      export default operate Residence() {
        const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
      }
    
  6. Then insert the ForwardRefPivot element from step 4 and cross the ref object as its prop:
    
      export default operate Residence() {
        const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
    
        return (
          <essential className={types.essential}>
            <ForwardRefPivot
              ref={pivotRef}
              toolbar={true}
            />
          </essential>
        );
      }
    

Now Flexmonster is prepared for use. Let’s transfer to Recharts.

Embedding Recharts

Begin by putting in Recharts with the next command:

npm set up recharts

Then, import the LineChart element with its little one parts and insert them after the pivot desk:


import { LineChart, Line, CartesianGrid, YAxis, XAxis } from "recharts";

export default operate Residence() {
  const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);

  return (
    <essential className={types.essential}>
      <ForwardRefPivot
        ref={pivotRef}
        toolbar={true}
      />

      <LineChart width={1000} peak={300}>
        <Line kind="monotone" stroke="#8884d8" />
        <CartesianGrid stroke="#ccc" />
        <XAxis />
        <YAxis />
      </LineChart>
    </essential>
  );
}

Technically, we’ve added Recharts to our web page, however we have to fill it with knowledge. Let’s first create interfaces that may describe knowledge for Recharts:

interface RechartsDataObject { [key: string]: any; }
interface RechartsData {
  knowledge: RechartsDataObject[];
  xName: string;
  lineName: string;
}

Then, we have to create a variable that may maintain the Recharts knowledge. However keep in mind, our knowledge will come from Flexmonster, which may change at runtime. So we want a way of monitoring modifications to this knowledge. That’s the place React states turn out to be useful. Add the next code to your web page element:


export default operate Residence() {
  // Flexmonster occasion ref
  const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
  // Recharts knowledge
  const [chartsData, setChartsData] = React.useState<RechartsData>({ knowledge: [], xName: "", lineName: "" });

  // Subscribe on Recharts knowledge modifications
  React.useEffect(() => {
    console.log("Charts knowledge modified!");
  }, [chartsData]);
  // The remainder of the code
}

Now, the information for Recharts will likely be saved within the chartsData variable, and due to the useState and useEffects React Hooks, we’ll know when it’s modified.

Lastly, we have to inform Recharts to make use of chartsData as its supply of information by including the next props:


<LineChart width={1000} peak={300} knowledge={chartsData.knowledge}>
  <Line dataKey={chartsData.lineName} kind="monotone" stroke="#8884d8" />
  <CartesianGrid stroke="#ccc" />
  <XAxis dataKey={chartsData.xName} />
  <YAxis />
</LineChart>

Within the subsequent part, let’s fill the chartsData with the information from Flexmonster so our pivot desk and charts are synced.

Connecting Flexmonster and Recharts

Within the app/web page.tsx file, let’s create a operate that transforms the information from Flexmonster so it may be accepted by Recharts:


import { GetDataValueObject } from "flexmonster";

operate prepareDataFunction(rawData: Flexmonster.GetDataValueObject): RechartsData | null {
  // If there isn't a knowledge, return null
  if (!rawData.knowledge.size)
    return null;
  
  // Initialize chartsData object
  const chartsData: RechartsData = {
    knowledge: [],
    xName: rawData.meta["r0Name" as keyof typeof rawData.meta],
    lineName: rawData.meta["v0Name" as keyof typeof rawData.meta]
  };
  
  // Rework Flexmonster knowledge so it may be processed by Recharts
  // The primary rawData ingredient is skipped as a result of it accommodates a grand whole worth, not wanted for our charts
  for (let i = 1, dataObj, chartDataObj: RechartsDataObject; i < rawData.knowledge.size; i++) {
    dataObj = rawData.knowledge[i];
    chartDataObj = {};
    chartDataObj[chartsData.xName] = dataObj["r0" as keyof typeof dataObj];
    chartDataObj[chartsData.lineName] = dataObj["v0" as keyof typeof dataObj];
    chartsData.knowledge.push(chartDataObj);
  }
  
  return chartsData;
}

To maintain the tutorial easy, prepareFunction returns solely knowledge for the primary row and measure. If you wish to show knowledge for one more discipline or measure, transfer this discipline or measure to the primary place utilizing the Subject Listing in Flexmonster.

Watch this video to see the way it works:

Now, let’s add a operate for chart drawing within the web page element itself (e.g., Residence). The operate will name the beforehand created prepareFunction and replace the chartsData state to set off re-rendering:


export default operate Residence() {
  <!-- Flexmonster occasion ref -->
  const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
  
  <!-- Recharts knowledge -->
  const [chartsData, setChartsData] = React.useState<RechartsData>({ knowledge: [], xName: "", lineName: "" });
  
  <!-- Subscribe on Recharts knowledge modifications -->
  React.useEffect(() => {
    console.log("Charts knowledge modified!");
  }, [chartsData]);
  
  <!-- Perform for chart drawing -->
  const drawChart = (rawData: GetDataValueObject) => {
    const chartsData = prepareDataFunction(rawData);
    if (chartsData) {
      setChartsData(chartsData);
    }
  }
  
  <!-- The remainder of the code -->
}

All that’s left is to inform Flexmonster to make use of this operate when its knowledge is modified. Add the next props to the ForwardRefPivot element:


<ForwardRefPivot
  ref={pivotRef}
  toolbar={true}
  <!-- Connecting Flexmonster and Recharts -->
  reportcomplete={() => {
    pivotRef.present?.flexmonster.off("reportcomplete");
    pivotRef.present?.flexmonster.getData({}, drawChart, drawChart);
  }}
  licenseKey="XXXX-XXXX-XXXX-XXXX-XXXX"
/>

Right here, we’ve subscribed to the reportcomplete occasion to know when Flexmonster is able to present knowledge. When the occasion is triggered, we instantly unsubscribe from it and use the getData technique to inform Flexmonster the place to cross the information and what to do when it’s up to date. In our case, we are able to use the drawChart operate for each functions.

Additionally, discover the licenseKey prop. It should comprise a particular trial key that may enable us to hook up with Recharts. You will get such a key by contacting the Flexmonster staff. Upon getting the important thing, paste it instead of XXXX-XXXX-XXXX-XXXX-XXXX.

Flexmonster and Recharts are actually linked, however they’re nonetheless empty! Let’s repair it by connecting to a database!

Connecting to MySQL

Since Recharts will get knowledge from Flexmonster, solely the latter needs to be linked to the database. Fortuitously, Flexmonster offers a ready-to-use answer – Flexmonster Information Server. This can be a server software, so all aggregations are carried out on the server facet, which saves the browser’s assets and ends in quicker knowledge visualization. This matches completely into the Subsequent.js philosophy of placing heavy work on the server (see the SSG and SSR options).

We will set up the Information Server utilizing the beforehand put in Flexmonster CLI:

flexmonster add fds -r

As soon as the command is completed – Flexmonster Admin Panel will open. This can be a device for configuring the Information Server and its connections, additionally referred to as indexes. Let’s configure one for our MySQL database.

  1. Open the Indexes tab and click on Add New Index:
  2. Add New Index Screen
  3. Beneath the Title and Kind fields, enter the connection’s identify (e.g., next-sql) and choose the Database kind:
  4. Index Configuration Fields
  5. The Database kind needs to be set to MySQL by default. Enter the Connection string and the Question on your database beneath the respective fields. For this tutorial, I’ve hosted a pattern database on the freedb.tech service. Be happy to make use of this connection string and question:

    Connection string:

    Server=sql.freedb.tech;Port=3306;Uid=freedb_dkflbvbh;pwd=NpkyU?jYv!2Zn&B;Database=freedb_next_fm

    Question:

    SELECT CONCAT(first_name,' ',last_name) AS full_name,wage,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM customers

    The Refresh time signifies how typically the information needs to be refreshed (0 means the information shouldn’t be refreshed).

    When all of the configurations are set, hit Create to ascertain the connection to the database:

  6. Database Connection Setup

Now, all that’s left is to visualise the information.

Visualize the information within the pivot desk and charts

To visualise knowledge ready by the Information Server, we have to create a Flexmonster report.

A report is used to predefine configurations for Flexmonster Pivot. Our curiosity lies within the knowledge supply configuration for SQL databases.

We’ll characterize our report as an object (e.g., report) that accommodates the next knowledge:

const report = {
  dataSource: {
    kind: "api",
    url: "http://localhost:9500",
    index: "next-sql"
  }
};

Let’s cowl what every property stands for:

  • dataSource accommodates knowledge supply configurations for the pivot desk element.

    For simplicity, I’ve specified solely the required properties to visualise our knowledge. There are lots of extra choices described in Flexmonster docs.

  • dataSource.kind specifies the kind of the information supply.

    In our case, it have to be set to “api“.

  • dataSource.url accommodates the Information Server’s URL the place the shopper will ship requests for knowledge.

    For the reason that shopper and the server run on the identical machine, we are able to use the localhost deal with. The port is about to 9500 as a result of the Information Server makes use of it by default. You’ll be able to run the Information Server on one other port.

  • dataSource.index identifies the dataset created within the Information Server.

    We’ve named it “next-sql” within the Connecting to MySQL a part of this tutorial, so let’s specify it right here.

Now, let’s cross the beforehand created report as a ForwardRefPivot prop:


<ForwardRefPivot
  ref={pivotRef}
  toolbar={true}
  //Setting report
  report={report}
  //Connecting Flexmonster and Recharts
  reportcomplete={() => {
    pivotRef.present?.flexmonster.off("reportcomplete");
    pivotRef.present?.flexmonster.getData({}, drawChart, drawChart);
  }}
  licenseKey="XXXX-XXXX-XXXX-XXXX-XXXX"
/>

Conclusion

Hooray, our Subsequent.js knowledge visualization app is prepared and might be began with this command:

npm run construct && npm begin

You can too get this tutorial challenge on GitHub. Be happy to make use of it as a place to begin when creating your NEXT app (pun meant)!

Leave a Reply

Your email address will not be published. Required fields are marked *