Making a Subsequent.js Dashboard for SQL Information Visualization

Developing dashboards for visualizing SQL knowledge has been a related call for for a very long time. Against this, applied sciences for construction internet apps alternate rather often, so builders meet this unchangeable call for another way yearly.

On this article, I’ve determined to percentage my newest skilled revel in creating a internet app for visualizing knowledge from a database.

Opting for applied sciences

One of the most necessities I had used to be to make use of MySQL. Alternatively, I may just freely make a choice the remainder of my toolkit, together with a framework.

React has all the time been fashionable amongst internet builders for construction wealthy person interfaces. I’ve additionally had a perfect revel in the use of React in my earlier tasks and determined to refresh my talents with it.

However React isn’t in reality a framework anymore. Studying in the course of the React documentation, I’ve discovered that to any extent further, tasks must be created the use of one of the most following React-based frameworks:

• Subsequent.js
• Remix
• Gatsby
• Expo (for React Local)

In step with the React staff, the use of a framework from the beginning guarantees that the options utilized by maximum internet programs are integrated, comparable to routing, HTML era, knowledge fetching, and so forth.

On this means, when your mission reaches the purpose when such options are wanted, you do not want to search for further libraries and configure them on your mission. As an alternative, all options are already to be had for you, making construction a lot smoother. You’ll see extra main points at the React website online.

This left me with one query…

Which framework must I exploit?

As with the whole lot on this global, there is not any common React framework that fits each and every want.

I’ve determined to forestall on Subsequent.js for my mission, and here’s why:

• Subsequent.js is situated as a default go-to React framework.
• The newest Subsequent.js 13 used to be evolved in shut collaboration with the React staff, so the framework fulfills the staff’s imaginative and prescient of a super full-stack React framework.
• Subsequent.js is part of Jamstack construction structure. Jamstack internet sites are recognized for his or her Search engine marketing-friendliness, person revel in, velocity, and function.
• Subsequent.js helps server-side rendering and static website online era, which is able to dramatically build up the serving velocity of your internet app. You’ll additionally mark particular React elements to be rendered at the shopper facet, making Subsequent.js very versatile. Right here you’ll be informed when to make use of server and shopper elements.
• Subsequent.js 13 is now the use of the fetch API, so there is not any wish to all the time claim the getServersideProps and getStaticProps strategies. Learn extra about knowledge fetching in Subsequent.js 13.
• Subsequent.js 13 introduces a large number of thrilling new options and primary enhancements! Learn extra information about adjustments in Subsequent.js 13.

The framework is selected, nevertheless it’s no longer sufficient. We additionally want equipment for connecting to MySQL and visualizing its knowledge.

Opting for knowledge visualization equipment

You must moderately believe which third-party equipment to make use of to your mission since there are such a large amount of choices and no longer each and every considered one of them would possibly fit your wishes. For me, the very best possible choices have been Flexmonster and Recharts.

Either one of those equipment are versatile, tough, and simple to make use of.

• Flexmonster is essentially a pivot desk and lets in running with other datasets, comparable to MySQL. It may also be built-in with quite a lot of frameworks, together with Subsequent.js! Flexmonster’s documentation even supplies a devoted educational for integrating this software with Subsequent.js. Flexmonster is a paid software, nevertheless it provides a 30-day trial, which used to be greater than sufficient for me to get accustomed to the product and decide if it fits my use circumstances.
• Recharts is a charting library with a big selection of integrated charts and customization choices. The library pairs particularly neatly with Subsequent.js programs because it makes use of React’s component-based structure. Recharts is a loose software launched underneath the MIT license, which is value taking into consideration.

My deciding issue used to be the straightforward setup of Flexmonster, Recharts, and their communique. Whilst 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.

If you’re questioning whether or not those equipment suit your mission, take a look at the best possible equipment for knowledge visualization in React article. It covers in nice element the other choices available on the market, their execs and cons, in addition to their use circumstances.

Now that the arrangements are whole let’s create the true app!

Create a Subsequent.js mission

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

.major {
-next-app next-sql --ts --app && cd next-sql

Realize the --ts and --app arguments. They allow TypeScript and the brand new App Router characteristic, which this educational assumes in additional directions.

You’ll even be given activates to allow different options, comparable to ESLint or Tailwind CSS. For simplicity, resolution No. But when you already know what you’re doing and those options are essential on your mission – be at liberty to allow them.

Additionally, it’s best possible to take away useless knowledge from the newly created mission. For the needs of this educational, we received’t want the public/ folder so it’s possible you’ll delete it. When you assume it’s possible you’ll want this folder for different causes, be at liberty to go away it the place it’s.

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

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

export default serve as House() {
  go back (
    

    
  );
}

It’s necessary that the web page is marked as a Shopper Part as a result of we will be able to upload options that can be utilized most effective when the web page is rendered at the shopper facet.

In any case, the useless types must be deleted from the app/web page.module.css report so it has the next content material:

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

Embed knowledge visualization equipment

Subsequent, we wish to upload Flexmonster and Recharts to our newly created mission. Let’s get started with Flexmonster.

Embedding Flexmonster

First, set up the Flexmonster CLI:

npm set up -g flexmonster-cli

Subsequent, obtain the Flexmonster wrapper for React:

flexmonster upload react-flexmonster

Now let’s upload Flexmonster to our web page:

1. Import Flexmonster types into the app/international.css report to your mission:

   @import "flexmonster/flexmonster.css";

2. Create the PivotWrapper Shopper Part (e.g., app/PivotWrapper.tsx) that can 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;
     };
   
     const PivotWrapper: React.FC = ({ pivotRef, ...params }) => {
       go back (
         
       );
     };
   
     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: () => "Loading Flexmonster..."
     });
   

   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 similar web page report, create an extra element for ref forwarding (e.g., ForwardRefPivot):

     import * as React from "react";
     import { Pivot } from "react-flexmonster";
   
     const ForwardRefPivot = React.forwardRef(
       (props, ref?: React.ForwardedRef) => 
     );
   

5. Within the web page element (e.g., House), create an empty ref object (e.g., pivotRef):

     export default serve as House() {
       const pivotRef: React.RefObject = React.useRef(null);
     }
   

6. Then insert the ForwardRefPivot element from step 4 and cross the ref object as its prop:

     export default serve as House() {
       const pivotRef: React.RefObject = React.useRef(null);
   
       go back (
         
   
           
         
       );
     }
   

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

Embedding Recharts

Get started by way of putting in Recharts with the next command:

npm set up recharts

Then, import the LineChart element with its kid elements and insert them after the pivot desk:

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

export default serve as House() {
  const pivotRef: React.RefObject = React.useRef(null);

  go back (
    

      

      
        
        
        
        
      
    
  );
}

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

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

Then, we wish to create a variable that can grasp the Recharts knowledge. However bear in mind, our knowledge will come from Flexmonster, which is able to alternate at runtime. So we’d like a way of monitoring adjustments to this information. That’s the place React states come in useful. Upload the next code in your web page element:

export default serve as House() {
  // Flexmonster example ref
  const pivotRef: React.RefObject = React.useRef(null);
  // Recharts knowledge
  const [chartsData, setChartsData] = React.useState({ knowledge: [], xName: "", lineName: "" });

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

Now, the information for Recharts shall be saved within the chartsData variable, and because of the useState and useEffects React Hooks, we will be able to know when it’s modified.

Finally, we wish to inform Recharts to make use of chartsData as its supply of knowledge by way of including the next props:

Within the subsequent phase, 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 report, let’s create a serve as that transforms the information from Flexmonster so it may be accredited by way of Recharts:

import { GetDataValueObject } from "flexmonster";

serve as prepareDataFunction(rawData: Flexmonster.GetDataValueObject): RechartsData | null {
  // If there is not any knowledge, go back null
  if (!rawData.knowledge.duration)
    go back 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]
  };
  
  // Change into Flexmonster knowledge so it may be processed by way of Recharts
  // The primary rawData component is skipped as it comprises a grand overall price, no longer wanted for our charts
  for (let i = 1, dataObj, chartDataObj: RechartsDataObject; i < rawData.knowledge.duration; 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);
  }
  
  go back chartsData;
}

To stay the academic easy, prepareFunction returns most effective knowledge for the primary row and measure. If you wish to show knowledge for some other box or measure, transfer this box or measure to the primary place the use of the Box Record in Flexmonster.

Watch this video to peer the way it works:

Now, let’s upload a serve as for chart drawing within the web page element itself (e.g., House). The serve as will name the in the past created prepareFunction and replace the chartsData state to cause re-rendering:

export default serve as House() {
  
  const pivotRef: React.RefObject = React.useRef(null);
  
  
  const [chartsData, setChartsData] = React.useState({ knowledge: [], xName: "", lineName: "" });
  
  
  React.useEffect(() => {
    console.log("Charts knowledge modified!");
  }, [chartsData]);
  
  
  const drawChart = (rawData: GetDataValueObject) => {
    const chartsData = prepareDataFunction(rawData);
    if (chartsData) {
      setChartsData(chartsData);
    }
  }
  
  
}

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

  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 tournament to grasp when Flexmonster is able to supply knowledge. When the development is induced, we right away unsubscribe from it and use the getData option to inform Flexmonster the place to cross the information and what to do when it’s up to date. In our case, we will use the drawChart serve as for each functions.

Additionally, realize the licenseKey prop. It will have to comprise a unique trial key that can permit us to connect with Recharts. You'll get this type of key by way of contacting the Flexmonster staff. After getting the important thing, paste it rather than XXXX-XXXX-XXXX-XXXX-XXXX.

Flexmonster and Recharts at the moment are hooked up, however they're nonetheless empty! Let’s repair it by way of connecting to a database!

Connecting to MySQL

Since Recharts will get knowledge from Flexmonster, most effective the latter must be hooked up to the database. Thankfully, Flexmonster supplies a ready-to-use resolution – Flexmonster Information Server. This can be a server utility, so all aggregations are carried out at the server facet, which saves the browser’s assets and ends up in sooner knowledge visualization. This suits completely into the Subsequent.js philosophy of striking heavy paintings at the server (see the SSG and SSR options).

We will be able to set up the Information Server the use of the in the past put in Flexmonster CLI:

flexmonster upload fds -r

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

1. Open the Indexes tab and click on Upload New Index:



2. Below the Identify and Sort fields, input the relationship’s title (e.g., next-sql) and choose the Database kind:



3. The Database kind must be set to MySQL by way of default. Input the Connection string and the Question on your database underneath the respective fields. For this educational, I've hosted a pattern database at the freedb.tech carrier. 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 ceaselessly the information must be refreshed (0 approach the information isn't refreshed).

   When the entire configurations are set, hit Create to ascertain the relationship to the database:

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

Visualize the information within the pivot desk and charts

To visualise knowledge ready by way of the Information Server, we wish to create a Flexmonster record.

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

We will be able to constitute our record as an object (e.g., record) that comprises the next knowledge:

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

Let’s quilt what each and every assets stands for:

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

  For simplicity, I’ve specified most effective the specified houses to visualise our knowledge. There are lots of extra choices described in Flexmonster medical doctors.

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

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

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

  For the reason that shopper and the server run at the identical system, we will use the localhost deal with. The port is about to 9500 since the Information Server makes use of it by way of default. You'll run the Information Server on some other port.

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

  We’ve named it “next-sql” partly 3 of this educational, so let’s specify it right here.

Now, let’s cross the in the past created record as a ForwardRefPivot prop:

  record={record}
  
  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 waiting and may also be began with this command:

npm run construct && npm get started

You'll additionally get this educational mission on GitHub. Be happy to make use of it as a place to begin when creating your NEXT app (pun supposed)!

The put up Making a Subsequent.js Dashboard for SQL Information Visualization gave the impression first on Hongkiat.

WordPress Website Development Source: https://www.hongkiat.com/blog/nextjs-dashboard-sql-visualization/

[ continue ]