Creating a Next.js Dashboard for SQL Data Visualization

Creating dashboards for visualizing SQL data has been a relevant demand for a long time. In contrast, technologies for building web apps change quite frequently, so developers meet this unchangeable demand differently every year.

In this article, I’ve decided to share my latest professional experience developing a web app for visualizing data from a database.

Choosing technologies

One of the requirements I had was to use MySQL. However, I could freely choose the rest of my toolkit, including a framework.

React has always been popular among web developers for building rich user interfaces. I’ve also had a great experience using React in my previous projects and decided to refresh my skills with it.

But React is not actually a framework anymore. Reading through the React documentation, I’ve found out that from now on, projects should be created using one of the following React-based frameworks:

• Next.js
• Remix
• Gatsby
• Expo (for React Native)

According to the React team, using a framework from the start ensures that the features used by most web applications are built-in, such as routing, HTML generation, data fetching, etc.

In this way, when your project reaches the point when such features are needed, you do not need to look for additional libraries and configure them for your project. Instead, all features are already available for you, making development much smoother. You can see more details on the React website.

This left me with one question…

Which framework should I use?

As with everything in this world, there is no universal React framework that suits every need.

I’ve decided to stop on Next.js for my project, and here is why:

• Next.js is positioned as a default go-to React framework.
• The latest Next.js 13 was developed in close collaboration with the React team, so the framework fulfills the team’s vision of an ideal full-stack React framework.
• Next.js is a part of Jamstack development architecture. Jamstack websites are known for their SEO-friendliness, user experience, speed, and performance.
• Next.js supports server-side rendering and static site generation, which can dramatically increase the serving speed of your web app. You can also mark specific React components to be rendered on the client side, making Next.js very flexible. Here you can learn when to use server and client components.
• Next.js 13 is now using the fetch API, so there is no need to always declare the getServersideProps and getStaticProps methods. Read more about data fetching in Next.js 13.
• Next.js 13 introduces a lot of exciting new features and major improvements! Read more details about changes in Next.js 13.

The framework is chosen, but it’s not enough. We also need tools for connecting to MySQL and visualizing its data.

Choosing data visualization tools

You should carefully consider which third-party tools to use in your project since there are so many options and not every one of them might suit your needs. For me, the perfect choices were Flexmonster and Recharts.

Both of these tools are flexible, powerful, and easy to use.

• Flexmonster is primarily a pivot table and allows working with different datasets, such as MySQL. It can also be integrated with various frameworks, including Next.js! Flexmonster’s documentation even provides a dedicated tutorial for integrating this tool with Next.js. Flexmonster is a paid tool, but it offers a 30-day trial, which was more than enough for me to get acquainted with the product and determine if it suits my use cases.
• Recharts is a charting library with a wide selection of built-in charts and customization options. The library pairs especially well with Next.js applications since it uses React’s component-based architecture. Recharts is a free tool released under the MIT license, which is worth considering.

My deciding factor was the easy setup of Flexmonster, Recharts, and their communication. While Flexmonster connects to the MySQL dataset, processes its data, and visualizes the data in the pivot table, Recharts can use the processed data to visualize it in charts.

If you are wondering whether these tools fit your project, check out the best tools for data visualization in React article. It covers in great detail the different options on the market, their pros and cons, as well as their use cases.

Now that the preparations are complete let’s create the actual app!

Create a Next.js project

First, a basic Next.js app must be created with the following command:

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

Notice the --ts and --app arguments. They enable TypeScript and the new App Router feature, which this tutorial assumes in further instructions.

You will also be given prompts to enable other features, such as ESLint or Tailwind CSS. For simplicity, answer No. But if you know what you’re doing and these features are necessary for your project – feel free to enable them.

Also, it’s best to remove unnecessary data from the newly created project. For the purposes of this tutorial, we won’t need the public/ folder so you may delete it. If you think you might need this folder for other reasons, feel free to leave it where it is.

In addition, change the app/page.tsx file, so it has the following contents:

"use client"
import styles from './page.module.css'

export default function Home() {
  return (
    <main className={styles.main}>
    </main>
  );
}

It’s important that the page is marked as a Client Component because we will add features that can be used only when the page is rendered on the client side.

Finally, the unnecessary styles should be deleted from the app/page.module.css file so it has the following content:

.main {
  display: flex;
  flex-direction: column;
  justify-content: space-between;
  align-items: center;
  padding: 6rem;
  min-height: 100vh;
}

Embed data visualization tools

Next, we need to add Flexmonster and Recharts to our newly created project. Let’s start with Flexmonster.

Embedding Flexmonster

First, install the Flexmonster CLI:

npm install -g flexmonster-cli

Next, download the Flexmonster wrapper for React:

flexmonster add react-flexmonster

Now let’s add Flexmonster to our page:

1. Import Flexmonster styles into the app/global.css file in your project:

   @import "flexmonster/flexmonster.css";

2. Create the PivotWrapper Client Component (e.g., app/PivotWrapper.tsx) that will wrap FlexmonsterReact.Pivot:

     "use client"
     import * as React from "react";
     import * as FlexmonsterReact from "react-flexmonster";
     import Flexmonster from "flexmonster";
   
     type 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 Next.js page (e.g., app/page.tsx) as a dynamic component without SSR:

     import dynamic from "next/dynamic";
   
     const PivotWrapper = dynamic(() => import("@/app/PivotWrapper"), {
       ssr: false,
       loading: () => <p>Loading Flexmonster...</p>
     });
   

   The SSR is disabled because Flexmonster uses the window object, so it cannot be rendered on a server.

4. In the same page file, create an additional component 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. Inside the page component (e.g., Home), create an empty ref object (e.g., pivotRef):

     export default function Home() {
       const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
     }
   

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

     export default function Home() {
       const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
   
       return (
         <main className={styles.main}>
           <ForwardRefPivot
             ref={pivotRef}
             toolbar={true}
           />
         </main>
       );
     }
   

Now Flexmonster is ready to be used. Let’s move to Recharts.

Embedding Recharts

Start by installing Recharts with the following command:

npm install recharts

Then, import the LineChart component with its child components and insert them after the pivot table:

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

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

  return (
    <main className={styles.main}>
      <ForwardRefPivot
        ref={pivotRef}
        toolbar={true}
      />

      <LineChart width={1000} height={300}>
        <Line type="monotone" stroke="#8884d8" />
        <CartesianGrid stroke="#ccc" />
        <XAxis />
        <YAxis />
      </LineChart>
    </main>
  );
}

Technically, we’ve added Recharts to our page, but we need to fill it with data. Let’s first create interfaces that will describe data for Recharts:

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

Then, we need to create a variable that will hold the Recharts data. But remember, our data will come from Flexmonster, which can change at runtime. So we need some way of tracking changes to this data. That’s where React states come in handy. Add the following code to your page component:

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

  // Subscribe on Recharts data changes
  React.useEffect(() => {
    console.log("Charts data changed!");
  }, [chartsData]);
  // The rest of the code
}

Now, the data for Recharts will be stored in the chartsData variable, and thanks to the useState and useEffects React Hooks, we will know when it’s changed.

Lastly, we need to tell Recharts to use chartsData as its source of data by adding the following props:

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

In the next section, let’s fill the chartsData with the data from Flexmonster so our pivot table and charts are synced.

Connecting Flexmonster and Recharts

In the app/page.tsx file, let’s create a function that transforms the data from Flexmonster so it can be accepted by Recharts:

import { GetDataValueObject } from "flexmonster";

function prepareDataFunction(rawData: Flexmonster.GetDataValueObject): RechartsData | null {
  // If there is no data, return null
  if (!rawData.data.length)
    return null;
  
  // Initialize chartsData object
  const chartsData: RechartsData = {
    data: [],
    xName: rawData.meta["r0Name" as keyof typeof rawData.meta],
    lineName: rawData.meta["v0Name" as keyof typeof rawData.meta]
  };
  
  // Transform Flexmonster data so it can be processed by Recharts
  // The first rawData element is skipped because it contains a grand total value, not needed for our charts
  for (let i = 1, dataObj, chartDataObj: RechartsDataObject; i < rawData.data.length; i++) {
    dataObj = rawData.data[i];
    chartDataObj = {};
    chartDataObj[chartsData.xName] = dataObj["r0" as keyof typeof dataObj];
    chartDataObj[chartsData.lineName] = dataObj["v0" as keyof typeof dataObj];
    chartsData.data.push(chartDataObj);
  }
  
  return chartsData;
}

To keep the tutorial simple, prepareFunction returns only data for the first row and measure. If you want to display data for another field or measure, move this field or measure to the first position using the Field List in Flexmonster.

Watch this video to see how it works:

Now, let’s add a function for chart drawing in the page component itself (e.g., Home). The function will call the previously created prepareFunction and update the chartsData state to trigger re-rendering:

export default function Home() {
  // Flexmonster instance ref
  const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);
  
  // Recharts data
  const [chartsData, setChartsData] = React.useState<RechartsData>({ data: [], xName: "", lineName: "" });
  
  // Subscribe on Recharts data changes
  React.useEffect(() => {
    console.log("Charts data changed!");
  }, [chartsData]);
  
  // Function for chart drawing
  const drawChart = (rawData: GetDataValueObject) => {
    const chartsData = prepareDataFunction(rawData);
    if (chartsData) {
      setChartsData(chartsData);
    }
  }
  
  // The rest of the code
}

All that’s left is to tell Flexmonster to use this function when its data is changed. Add the following props to the ForwardRefPivot component:

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

Here, we’ve subscribed to the reportcomplete event to know when Flexmonster is ready to provide data. When the event is triggered, we immediately unsubscribe from it and use the getData method to tell Flexmonster where to pass the data and what to do when it’s updated. In our case, we can use the drawChart function for both purposes.

Also, notice the licenseKey prop. It must contain a special trial key that will allow us to connect to Recharts. You can get such a key by contacting the Flexmonster team. Once you have the key, paste it in place of XXXX-XXXX-XXXX-XXXX-XXXX.

Flexmonster and Recharts are now connected, but they are still empty! Let’s fix it by connecting to a database!

Connecting to MySQL

Since Recharts gets data from Flexmonster, only the latter should be connected to the database. Fortunately, Flexmonster provides a ready-to-use solution – Flexmonster Data Server. This is a server application, so all aggregations are performed on the server side, which saves the browser’s resources and results in faster data visualization. This fits perfectly into the Next.js philosophy of putting heavy work on the server (see the SSG and SSR features).

We can install the Data Server using the previously installed Flexmonster CLI:

flexmonster add fds -r

Once the command is finished – Flexmonster Admin Panel will open. This is a tool for configuring the Data Server and its connections, also called indexes. Let’s configure one for our MySQL database.

1. Open the Indexes tab and click Add New Index:



2. Under the Name and Type fields, enter the connection’s name (e.g., next-sql) and select the Database type:



3. The Database type should be set to MySQL by default. Enter the Connection string and the Query for your database under the respective fields. For this tutorial, I have hosted a sample database on the freedb.tech service. Feel free to use this connection string and query:

   Connection string:

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

   Query:

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

   The Refresh time indicates how often the data should be refreshed (0 means the data is not refreshed).

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

Now, all that’s left is to visualize the data.

Visualize the data in the pivot table and charts

To visualize data prepared by the Data Server, we need to create a Flexmonster report.

A report is used to predefine configurations for Flexmonster Pivot. Our interest lies in the data source configuration for SQL databases.

We will represent our report as an object (e.g., report) that contains the following data:

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

Let’s cover what each property stands for:

• dataSource contains data source configurations for the pivot table component.

  For simplicity, I’ve specified only the required properties to visualize our data. There are many more options described in Flexmonster docs.

• dataSource.type specifies the type of the data source.

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

• dataSource.url contains the Data Server’s URL where the client will send requests for data.

  Since the client and the server run on the same machine, we can use the localhost address. The port is set to 9500 because the Data Server uses it by default. You can run the Data Server on another port.

• dataSource.index identifies the dataset created in the Data Server.

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

Now, let’s pass the previously created report as a ForwardRefPivot prop:

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

Conclusion

Hooray, our Next.js data visualization app is ready and can be started with this command:

npm run build && npm start

You can also get this tutorial project on GitHub. Feel free to use it as a starting point when developing your NEXT app (pun intended)!

The post Creating a Next.js Dashboard for SQL Data Visualization appeared first on Hongkiat.