{"id":441,"date":"2024-01-25T12:00:49","date_gmt":"2024-01-25T13:00:49","guid":{"rendered":"https:\/\/upprofits.net\/?p=441"},"modified":"2024-08-30T11:27:13","modified_gmt":"2024-08-30T11:27:13","slug":"creating-a-next-js-dashboard-for-sql-data-visualization","status":"publish","type":"post","link":"https:\/\/upprofits.net\/index.php\/2024\/01\/25\/creating-a-next-js-dashboard-for-sql-data-visualization\/","title":{"rendered":"Creating a Next.js Dashboard for SQL Data Visualization"},"content":{"rendered":"<p>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.<\/p>\n<p>In this article, I\u2019ve decided to share my latest professional experience developing a web app for visualizing data from a database.<\/p>\n<h4>Choosing technologies<\/h4>\n<p>One of the requirements I had was to use <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.mysql.com\/\">MySQL<\/a>. However, I could freely choose the rest of my toolkit, including a framework.<\/p>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/\">React<\/a> has always been popular among web developers for building rich user interfaces. I\u2019ve also had a great experience using React in my previous projects and decided to refresh my skills with it.<\/p>\n<p>But React is not actually a framework anymore. Reading through the React documentation, I\u2019ve found out that from now on, projects should be created using one of the following React-based frameworks:<\/p>\n<ul>\n<li><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/\">Next.js<\/a><\/li>\n<li><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/remix.run\/\">Remix<\/a><\/li>\n<li><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.gatsbyjs.com\/\">Gatsby<\/a><\/li>\n<li><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/expo.dev\/\">Expo<\/a> (for <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/reactnative.dev\/\">React Native<\/a>)<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>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 <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/learn\/start-a-new-react-project\">React website<\/a>.<\/p>\n<p>This left me with one question\u2026<\/p>\n<h5>Which framework should I use?<\/h5>\n<p>As with everything in this world, there is no universal React framework that suits every need.<\/p>\n<p>I\u2019ve decided to stop on <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/\">Next.js<\/a> for my project, and here is why:<\/p>\n<ul>\n<li>Next.js is positioned as a <strong>default go-to React framework<\/strong>.<\/li>\n<li>The latest Next.js 13 was developed in close collaboration with the React team, so the framework fulfills the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/learn\/start-a-new-react-project#which-features-make-up-the-react-teams-full-stack-architecture-vision\">team\u2019s vision of an ideal full-stack React framework<\/a>.<\/li>\n<li>Next.js is a part of <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/jamstack.org\/\">Jamstack<\/a> development architecture. Jamstack websites are known for their SEO-friendliness, user experience, speed, and performance.<\/li>\n<li>Next.js supports <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/pages\/building-your-application\/rendering\/server-side-rendering\">server-side rendering<\/a> and <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/pages\/building-your-application\/rendering\/static-site-generation\">static site generation<\/a>, 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 <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/app\/building-your-application\/rendering\/composition-patterns#when-to-use-server-and-client-components\">when to use server and client components<\/a>.<\/li>\n<li>Next.js 13 is now using the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/app\/api-reference\/functions\/fetch\">fetch API<\/a>, so there is no need to always declare the getServersideProps and getStaticProps methods<a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/app\/building-your-application\/data-fetching\/fetching\">. Read more about data fetching in Next.js 13.<\/a><\/li>\n<li>Next.js 13 introduces a lot of exciting new features and major improvements! <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/blog\/next-13\">Read more details about changes in Next.js 13.<\/a><\/li>\n<\/ul>\n<p>The framework is chosen, but it\u2019s not enough. We also need tools for connecting to MySQL and visualizing its data.<\/p>\n<h5>Choosing data visualization tools<\/h5>\n<p>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 <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/\">Flexmonster<\/a> and <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/recharts.org\/en-US\">Recharts<\/a>.<\/p>\n<p>Both of these tools are flexible, powerful, and easy to use.<\/p>\n<ul>\n<li><strong>Flexmonster<\/strong> 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\u2019s documentation even provides a <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/integration-with-next-js\/\">dedicated tutorial for integrating this tool with Next.js<\/a>. 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.<\/li>\n<li><strong>Recharts<\/strong> 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\u2019s component-based architecture. Recharts is a free tool released under the MIT license, which is worth considering.<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>If you are wondering whether these tools fit your project, check out the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/javascript.plainenglish.io\/best-tools-for-reporting-and-data-analytics-in-react-8f24f41d0e43\">best tools for data visualization in React article<\/a>. It covers in great detail the different options on the market, their pros and cons, as well as their use cases.<\/p>\n<p>Now that the preparations are complete let\u2019s create the actual app!<\/p>\n<h4>Create a Next.js project<\/h4>\n<p>First, a basic Next.js app must be created with the following command:<\/p>\n<pre class=\"terminal\">\r\nnpx create-next-app next-sql --ts --app && cd next-sql\r\n<\/pre>\n<p>Notice the <code>--ts<\/code> and <code>--app<\/code> arguments. They enable <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.typescriptlang.org\/\">TypeScript<\/a> and the new <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/app\">App Router<\/a> feature, which this tutorial assumes in further instructions.<\/p>\n<p>You will also be given prompts to enable other features, such as <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/eslint.org\/\">ESLint<\/a> or <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/tailwindcss.com\/\">Tailwind CSS<\/a>. For simplicity, answer No. But if you know what you\u2019re doing and these features are necessary for your project \u2013 feel free to enable them.<\/p>\n<p>Also, it\u2019s best to remove unnecessary data from the newly created project. For the purposes of this tutorial, we won\u2019t need the <code>public\/<\/code> 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.<\/p>\n<p>In addition, change the <em>app\/page.tsx<\/em> file, so it has the following contents:<\/p>\n<pre class=\"terminal\">\r\n\"use client\"\r\nimport styles from '.\/page.module.css'\r\n\r\nexport default function Home() {\r\n return (\r\n <main className={styles.main}>\r\n <\/main>\r\n );\r\n}\r\n<\/pre>\n<p>It\u2019s important that the page is marked as a <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/app\/building-your-application\/rendering\/client-components\">Client Component<\/a> because we will add features that can be used only when the page is rendered on the client side.<\/p>\n<p>Finally, the unnecessary styles should be deleted from the <em>app\/page.module.css<\/em> file so it has the following content:<\/p>\n<pre class=\"terminal\">.main {\r\n display: flex;\r\n flex-direction: column;\r\n justify-content: space-between;\r\n align-items: center;\r\n padding: 6rem;\r\n min-height: 100vh;\r\n}\r\n<\/pre>\n<h4>Embed data visualization tools<\/h4>\n<p>Next, we need to add Flexmonster and Recharts to our newly created project. Let\u2019s start with Flexmonster.<\/p>\n<h5>Embedding Flexmonster<\/h5>\n<p>First, install the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/cli-overview\/\">Flexmonster CLI<\/a>:<\/p>\n<pre class=\"terminal\">npm install -g flexmonster-cli<\/pre>\n<p>Next, download the Flexmonster wrapper for React:<\/p>\n<pre class=\"terminal\">flexmonster add react-flexmonster<\/pre>\n<p>Now let\u2019s add Flexmonster to our page:<\/p>\n<ol class=\"steps\">\n<li>Import Flexmonster styles into the <em>app\/global.css<\/em> file in your project:\n<pre class=\"terminal\">@import \"flexmonster\/flexmonster.css\";<\/pre>\n<\/li>\n<li>Create the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/app\/building-your-application\/rendering\/client-components\">PivotWrapper Client Component<\/a> (e.g., <em>app\/PivotWrapper.tsx<\/em>) that will wrap <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/flexmonster-pivot-component-for-react\/\">FlexmonsterReact.Pivot:<\/a>\n<pre class=\"terminal\">\r\n \"use client\"\r\n import * as React from \"react\";\r\n import * as FlexmonsterReact from \"react-flexmonster\";\r\n import Flexmonster from \"flexmonster\";\r\n\r\n type PivotProps = Flexmonster.Params & {\r\n pivotRef?: React.ForwardedRef<FlexmonsterReact.Pivot>;\r\n };\r\n\r\n const PivotWrapper: React.FC<PivotProps> = ({ pivotRef, ...params }) => {\r\n return (\r\n <FlexmonsterReact.Pivot\r\n {...params}\r\n ref={pivotRef}\r\n \/>\r\n );\r\n };\r\n\r\n export default PivotWrapper;\r\n<\/pre>\n<\/li>\n<li> Import the PivotWrapper into your <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/pages\/building-your-application\/routing\/pages-and-layouts\">Next.js page<\/a> (e.g., <em>app\/page.tsx<\/em>) as a <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/learn\/seo\/improve\/dynamic-import-components\">dynamic component without SSR<\/a>:\n<pre class=\"terminal\">\r\n import dynamic from \"next\/dynamic\";\r\n\r\n const PivotWrapper = dynamic(() => import(\"@\/app\/PivotWrapper\"), {\r\n ssr: false,\r\n loading: () => <p>Loading Flexmonster...<\/p>\r\n });\r\n<\/pre>\n<p>The <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/pages\/building-your-application\/rendering\/server-side-rendering\">SSR<\/a> is disabled because Flexmonster uses the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/API\/Window\">window object<\/a>, so it cannot be rendered on a server.<\/p>\n<\/li>\n<li>In the same page file, create an additional component for <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/reference\/react\/forwardRef\">ref forwarding<\/a> (e.g., <code>ForwardRefPivot<\/code>):\n<pre class=\"terminal\">\r\n import * as React from \"react\";\r\n import { Pivot } from \"react-flexmonster\";\r\n\r\n const ForwardRefPivot = React.forwardRef<Pivot, Flexmonster.Params>(\r\n (props, ref?: React.ForwardedRef<Pivot>) => <PivotWrapper {...props} pivotRef={ref}\/>\r\n );\r\n<\/pre>\n<\/li>\n<li>Inside the page component (e.g., <em>Home<\/em>), create an empty ref object (e.g., <code>pivotRef<\/code>):\n<pre class=\"terminal\">\r\n export default function Home() {\r\n const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);\r\n }\r\n<\/pre>\n<\/li>\n<li>Then insert the <code>ForwardRefPivot<\/code> component from step 4 and pass the ref object as its prop:\n<pre class=\"terminal\">\r\n export default function Home() {\r\n const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);\r\n\r\n return (\r\n <main className={styles.main}>\r\n <ForwardRefPivot\r\n ref={pivotRef}\r\n toolbar={true}\r\n \/>\r\n <\/main>\r\n );\r\n }\r\n<\/pre>\n<\/li>\n<\/ol>\n<p>Now Flexmonster is ready to be used. Let\u2019s move to Recharts.<\/p>\n<h5>Embedding Recharts<\/h5>\n<p>Start by installing Recharts with the following command:<\/p>\n<pre class=\"terminal\">npm install recharts<\/pre>\n<p>Then, import the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/recharts.org\/en-US\/api\/LineChart\">LineChart<\/a> component with its child components and insert them after the pivot table:<\/p>\n<pre class=\"terminal\">\r\nimport { LineChart, Line, CartesianGrid, YAxis, XAxis } from \"recharts\";\r\n\r\nexport default function Home() {\r\n const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);\r\n\r\n return (\r\n <main className={styles.main}>\r\n <ForwardRefPivot\r\n ref={pivotRef}\r\n toolbar={true}\r\n \/>\r\n\r\n <LineChart width={1000} height={300}>\r\n <Line type=\"monotone\" stroke=\"#8884d8\" \/>\r\n <CartesianGrid stroke=\"#ccc\" \/>\r\n <XAxis \/>\r\n <YAxis \/>\r\n <\/LineChart>\r\n <\/main>\r\n );\r\n}\r\n<\/pre>\n<p>Technically, we\u2019ve added Recharts to our page, but we need to fill it with data. Let\u2019s first create interfaces that will describe data for Recharts:<\/p>\n<pre class=\"terminal\">interface RechartsDataObject { [key: string]: any; }\r\ninterface RechartsData {\r\n data: RechartsDataObject[];\r\n xName: string;\r\n lineName: string;\r\n}\r\n<\/pre>\n<p>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\u2019s where <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/learn\/managing-state\">React states<\/a> come in handy. Add the following code to your page component:<\/p>\n<pre class=\"terminal\">\r\nexport default function Home() {\r\n \/\/ Flexmonster instance ref\r\n const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);\r\n \/\/ Recharts data\r\n const [chartsData, setChartsData] = React.useState<RechartsData>({ data: [], xName: \"\", lineName: \"\" });\r\n\r\n \/\/ Subscribe on Recharts data changes\r\n React.useEffect(() => {\r\n console.log(\"Charts data changed!\");\r\n }, [chartsData]);\r\n \/\/ The rest of the code\r\n}\r\n<\/pre>\n<p>Now, the data for Recharts will be stored in the chartsData variable, and thanks to the <code><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/reference\/react\/useState\">useState<\/a><\/code> and <code><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/react.dev\/reference\/react\/useEffect\">useEffects<\/a><\/code> React Hooks, we will know when it\u2019s changed.<\/p>\n<p>Lastly, we need to tell Recharts to use chartsData as its source of data by adding the following props:<\/p>\n<pre class=\"terminal\">\r\n<LineChart width={1000} height={300} data={chartsData.data}>\r\n <Line dataKey={chartsData.lineName} type=\"monotone\" stroke=\"#8884d8\" \/>\r\n <CartesianGrid stroke=\"#ccc\" \/>\r\n <XAxis dataKey={chartsData.xName} \/>\r\n <YAxis \/>\r\n<\/LineChart>\r\n<\/pre>\n<p>In the next section, let\u2019s fill the chartsData with the data from Flexmonster so our pivot table and charts are synced.<\/p>\n<h5>Connecting Flexmonster and Recharts<\/h5>\n<p>In the <em>app\/page.tsx<\/em> file, let\u2019s create a function that transforms the data from Flexmonster so it can be accepted by Recharts:<\/p>\n<pre class=\"terminal\">\r\nimport { GetDataValueObject } from \"flexmonster\";\r\n\r\nfunction prepareDataFunction(rawData: Flexmonster.GetDataValueObject): RechartsData | null {\r\n \/\/ If there is no data, return null\r\n if (!rawData.data.length)\r\n return null;\r\n \r\n \/\/ Initialize chartsData object\r\n const chartsData: RechartsData = {\r\n data: [],\r\n xName: rawData.meta[\"r0Name\" as keyof typeof rawData.meta],\r\n lineName: rawData.meta[\"v0Name\" as keyof typeof rawData.meta]\r\n };\r\n \r\n \/\/ Transform Flexmonster data so it can be processed by Recharts\r\n \/\/ The first rawData element is skipped because it contains a grand total value, not needed for our charts\r\n for (let i = 1, dataObj, chartDataObj: RechartsDataObject; i < rawData.data.length; i++) {\r\n dataObj = rawData.data[i];\r\n chartDataObj = {};\r\n chartDataObj[chartsData.xName] = dataObj[\"r0\" as keyof typeof dataObj];\r\n chartDataObj[chartsData.lineName] = dataObj[\"v0\" as keyof typeof dataObj];\r\n chartsData.data.push(chartDataObj);\r\n }\r\n \r\n return chartsData;\r\n}\r\n<\/pre>\n<p>To keep the tutorial simple, <code>prepareFunction<\/code> 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 <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/user-interface\/#field-list\">Field List<\/a> in Flexmonster.<\/p>\n<p>Watch this video to see how it works:<\/p>\n<div class=\"su-youtube su-u-responsive-media-yes\">\n<div class=\"video-19by6\">\n<iframe loading=\"lazy\" title=\"How to use the Field List in Flexmonster Pivot Table & Charts\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/__cYJrGvvIo?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div>\n<\/div>\n<p>Now, let\u2019s add a function for chart drawing in the page component itself (e.g., <em>Home<\/em>). The function will call the previously created prepareFunction and update the chartsData state to trigger re-rendering:<\/p>\n<pre class=\"terminal\">\r\nexport default function Home() {\r\n \/\/ Flexmonster instance ref\r\n const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);\r\n \r\n \/\/ Recharts data\r\n const [chartsData, setChartsData] = React.useState<RechartsData>({ data: [], xName: \"\", lineName: \"\" });\r\n \r\n \/\/ Subscribe on Recharts data changes\r\n React.useEffect(() => {\r\n console.log(\"Charts data changed!\");\r\n }, [chartsData]);\r\n \r\n \/\/ Function for chart drawing\r\n const drawChart = (rawData: GetDataValueObject) => {\r\n const chartsData = prepareDataFunction(rawData);\r\n if (chartsData) {\r\n setChartsData(chartsData);\r\n }\r\n }\r\n \r\n \/\/ The rest of the code\r\n}\r\n<\/pre>\n<p>All that\u2019s left is to tell Flexmonster to use this function when its data is changed. Add the following props to the <code>ForwardRefPivot<\/code> component:<\/p>\n<pre class=\"terminal\">\r\n<ForwardRefPivot\r\n ref={pivotRef}\r\n toolbar={true}\r\n \/\/ Connecting Flexmonster and Recharts\r\n reportcomplete={() => {\r\n pivotRef.current?.flexmonster.off(\"reportcomplete\");\r\n pivotRef.current?.flexmonster.getData({}, drawChart, drawChart);\r\n }}\r\n licenseKey=\"XXXX-XXXX-XXXX-XXXX-XXXX\"\r\n\/>\r\n<\/pre>\n<p>Here, we\u2019ve subscribed to the <code><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/api\/reportcomplete\/\">reportcomplete<\/a><\/code> event to know when Flexmonster is ready to provide data. When the event is triggered, we immediately unsubscribe from it and use the <code><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/api\/getdata\">getData<\/a><\/code> method to tell Flexmonster where to pass the data and what to do when it\u2019s updated. In our case, we can use the <code>drawChart<\/code> function for both purposes.<\/p>\n<p>Also, notice the <code><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/api\/new-flexmonster\/#licensekey\">licenseKey<\/a><\/code> prop. It must contain a special trial key that will allow us to connect to Recharts. You can get such a key by <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/contact-our-team\/\">contacting the Flexmonster team<\/a>. Once you have the key, paste it in place of <code>XXXX-XXXX-XXXX-XXXX-XXXX<\/code>.<\/p>\n<p>Flexmonster and Recharts are now connected, but they are still empty! Let\u2019s fix it by connecting to a database!<\/p>\n<h4 id=\"connect-mysql\">Connecting to MySQL<\/h4>\n<p>Since Recharts gets data from Flexmonster, only the latter should be connected to the database. Fortunately, Flexmonster provides a ready-to-use solution \u2013 <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/intro-to-flexmonster-data-server\/\">Flexmonster Data Server<\/a>. This is a server application, so all aggregations are performed on the server side, which saves the browser\u2019s resources and results in faster data visualization. This fits perfectly into the Next.js philosophy of putting heavy work on the server (see the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/pages\/building-your-application\/rendering\/static-site-generation\">SSG<\/a> and <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/nextjs.org\/docs\/pages\/building-your-application\/rendering\/server-side-rendering\">SSR<\/a> features).<\/p>\n<p>We can install the Data Server using the previously installed Flexmonster CLI:<\/p>\n<pre class=\"terminal\">flexmonster add fds -r<\/pre>\n<p>Once the command is finished \u2013 <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/admin-panel-guide\/\">Flexmonster Admin Panel<\/a> will open. This is a tool for configuring the Data Server and its connections, also called indexes. Let\u2019s configure one for our MySQL database.<\/p>\n<ol class=\"steps\">\n<li>Open the <strong>Indexes<\/strong> tab and click <strong>Add New Index<\/strong>: <\/li>\n<figure class=\"border-solid-1\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/upprofits.net\/wp-content\/uploads\/2024\/01\/ap-add-new-index.jpg\" width=\"3104\" height=\"1824\" alt=\"Add New Index Screen\" \/><\/figure>\n<li>Under the <strong>Name<\/strong> and <strong>Type<\/strong> fields, enter the connection\u2019s name (e.g., <em>next-sql<\/em>) and select the <strong>Database type:<\/strong><\/li>\n<figure class=\"border-solid-1\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/upprofits.net\/wp-content\/uploads\/2024\/01\/ap-index-configuration.jpg\" width=\"3104\" height=\"1824\" alt=\"Index Configuration Fields\" \/><\/figure>\n<li>The <strong>Database type<\/strong> should be set to <strong>MySQL<\/strong> by default. Enter the <strong>Connection string<\/strong> and the <strong>Query<\/strong> for your database under the respective fields. For this tutorial, I have hosted a sample database on the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/freedb.tech\/\">freedb.tech<\/a> service. Feel free to use this connection string and query:\n<p><strong>Connection string:<\/strong><\/p>\n<pre class=\"terminal\">Server=sql.freedb.tech;Port=3306;Uid=freedb_dkflbvbh;pwd=NpkyU?jYv!2Zn&B;Database=freedb_next_fm<\/pre>\n<p><strong>Query:<\/strong><\/p>\n<pre class=\"terminal\">SELECT CONCAT(first_name,' ',last_name) AS full_name,salary,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM users<\/pre>\n<p>The <strong>Refresh time<\/strong> indicates how often the data should be refreshed (0 means the data is not refreshed).<\/p>\n<p>When all the configurations are set, hit <strong>Create<\/strong> to establish the connection to the database:<\/p>\n<\/li>\n<figure class=\"border-solid-1\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/upprofits.net\/wp-content\/uploads\/2024\/01\/ap-db-connection.jpg\" width=\"3104\" height=\"1824\" alt=\"Database Connection Setup\" \/><\/figure>\n<\/ol>\n<p>Now, all that\u2019s left is to visualize the data.<\/p>\n<h4>Visualize the data in the pivot table and charts<\/h4>\n<p>To visualize data prepared by the Data Server, we need to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/getting-started-with-report\/\">create a Flexmonster report<\/a>.<\/p>\n<p>A report is used to predefine configurations for Flexmonster Pivot. Our interest lies in the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/data-source\/#sql\">data source configuration for SQL databases<\/a>.<\/p>\n<p>We will represent our report as an object (e.g., <em>report<\/em>) that contains the following data:<\/p>\n<pre class=\"terminal\">const report = {\r\n dataSource: {\r\n type: \"api\",\r\n url: \"http:\/\/localhost:9500\",\r\n index: \"next-sql\"\r\n }\r\n};\r\n<\/pre>\n<p>Let\u2019s cover what each property stands for:<\/p>\n<ul>\n<li>\n<p><code>dataSource<\/code> contains data source configurations for the pivot table component.<\/p>\n<p>For simplicity, I\u2019ve specified only the required properties to visualize our data. There are many more options described in <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/api\/data-source-object\/\">Flexmonster docs<\/a>.<\/p>\n<\/li>\n<li>\n<p><code>dataSource.type<\/code> specifies the type of the data source.<\/p>\n<p>In our case, it must be set to \u201c<em>api<\/em>\u201c.<\/p>\n<\/li>\n<li>\n<p><code>dataSource.url<\/code> contains the Data Server\u2019s URL where the client will send requests for data.<\/p>\n<p>Since the client and the server run on the same machine, we can use the localhost address. The port is set to <code>9500<\/code> because the Data Server uses it by default. You can <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.flexmonster.com\/doc\/admin-panel-guide\/#port\">run the Data Server on another port<\/a>.<\/p>\n<\/li>\n<li>\n<p><code>dataSource.index<\/code> identifies the dataset created in the Data Server.<\/p>\n<p>We\u2019ve named it \u201c<em>next-sql<\/em>\u201d in the <a href=\"#connect-mysql\">Connecting to MySQL<\/a> part of this tutorial, so let\u2019s specify it here.<\/p>\n<\/li>\n<\/ul>\n<p>Now, let\u2019s pass the previously created report as a <code>ForwardRefPivot<\/code> prop:<\/p>\n<pre class=\"terminal\">\r\n<ForwardRefPivot\r\n ref={pivotRef}\r\n toolbar={true}\r\n \/\/Setting report\r\n report={report}\r\n \/\/Connecting Flexmonster and Recharts\r\n reportcomplete={() => {\r\n pivotRef.current?.flexmonster.off(\"reportcomplete\");\r\n pivotRef.current?.flexmonster.getData({}, drawChart, drawChart);\r\n }}\r\n licenseKey=\"XXXX-XXXX-XXXX-XXXX-XXXX\"\r\n\/>\r\n<\/pre>\n<h4>Conclusion<\/h4>\n<p>Hooray, our Next.js data visualization app is ready and can be started with this command:<\/p>\n<pre class=\"terminal\">npm run build && npm start<\/pre>\n<p>You can also <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/github.com\/vo-fomin\/next-sql\/tree\/main\">get this tutorial project on GitHub<\/a>. Feel free to use it as a starting point when developing your NEXT app (pun intended)!<\/p>\n<p>The post <a href=\"https:\/\/www.hongkiat.com\/blog\/nextjs-dashboard-sql-visualization\/\">Creating a Next.js Dashboard for SQL Data Visualization<\/a> appeared first on <a href=\"https:\/\/www.hongkiat.com\/blog\">Hongkiat<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u2019ve decided to share my latest professional experience developing a web app for visualizing data from a database. […]<\/p>\n","protected":false},"author":1,"featured_media":445,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-441","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-coding"],"_links":{"self":[{"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/posts\/441"}],"collection":[{"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/comments?post=441"}],"version-history":[{"count":3,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/posts\/441\/revisions"}],"predecessor-version":[{"id":452,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/posts\/441\/revisions\/452"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/media\/445"}],"wp:attachment":[{"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/media?parent=441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/categories?post=441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/upprofits.net\/index.php\/wp-json\/wp\/v2\/tags?post=441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}