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
In this article, I\u2019ve decided to share my latest professional experience developing a web app for visualizing data from a database.<\/p>\n
One of the requirements I had was to use MySQL<\/a>. However, I could freely choose the rest of my toolkit, including a framework.<\/p>\n 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 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 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 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<\/a>.<\/p>\n This left me with one question\u2026<\/p>\n As with everything in this world, there is no universal React framework that suits every need.<\/p>\n I\u2019ve decided to stop on Next.js<\/a> for my project, and here is why:<\/p>\n The framework is chosen, but it\u2019s not enough. We also need tools for connecting to MySQL and visualizing its data.<\/p>\n 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<\/a> and Recharts<\/a>.<\/p>\n Both of these tools are flexible, powerful, and easy to use.<\/p>\n 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 If you are wondering whether these tools fit your project, check out the 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 Now that the preparations are complete let\u2019s create the actual app!<\/p>\n First, a basic Next.js app must be created with the following command:<\/p>\n Notice the You will also be given prompts to enable other features, such as ESLint<\/a> or 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 Also, it\u2019s best to remove unnecessary data from the newly created project. For the purposes of this tutorial, we won\u2019t need the In addition, change the app\/page.tsx<\/em> file, so it has the following contents:<\/p>\n It\u2019s important that the page is marked as a Client Component<\/a> because we will add features that can be used only when the page is rendered on the client side.<\/p>\n Finally, the unnecessary styles should be deleted from the app\/page.module.css<\/em> file so it has the following content:<\/p>\n Next, we need to add Flexmonster and Recharts to our newly created project. Let\u2019s start with Flexmonster.<\/p>\n First, install the Flexmonster CLI<\/a>:<\/p>\n Next, download the Flexmonster wrapper for React:<\/p>\n Now let\u2019s add Flexmonster to our page:<\/p>\n The SSR<\/a> is disabled because Flexmonster uses the window object<\/a>, so it cannot be rendered on a server.<\/p>\n<\/li>\n Now Flexmonster is ready to be used. Let\u2019s move to Recharts.<\/p>\n Start by installing Recharts with the following command:<\/p>\n Then, import the LineChart<\/a> component with its child components and insert them after the pivot table:<\/p>\n 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 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 React states<\/a> come in handy. Add the following code to your page component:<\/p>\n Now, the data for Recharts will be stored in the chartsData variable, and thanks to the Lastly, we need to tell Recharts to use chartsData as its source of data by adding the following props:<\/p>\n In the next section, let\u2019s fill the chartsData with the data from Flexmonster so our pivot table and charts are synced.<\/p>\n In the 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 To keep the tutorial simple, Watch this video to see how it works:<\/p>\n\n
Which framework should I use?<\/h5>\n
\n
Choosing data visualization tools<\/h5>\n
\n
Create a Next.js project<\/h4>\n
\r\nnpx create-next-app next-sql --ts --app && cd next-sql\r\n<\/pre>\n
--ts<\/code> and --app<\/code> arguments. They enable TypeScript<\/a> and the new App Router<\/a> feature, which this tutorial assumes in further instructions.<\/p>\npublic\/<\/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\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.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>\nEmbed data visualization tools<\/h4>\n
Embedding Flexmonster<\/h5>\n
npm install -g flexmonster-cli<\/pre>\n
flexmonster add react-flexmonster<\/pre>\n
\n
@import \"flexmonster\/flexmonster.css\";<\/pre>\n<\/li>\n
\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\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>\nForwardRefPivot<\/code>):\n\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>\npivotRef<\/code>):\n\r\n export default function Home() {\r\n const pivotRef: React.RefObject<Pivot> = React.useRef<Pivot>(null);\r\n }\r\n<\/pre>\n<\/li>\nForwardRefPivot<\/code> component from step 4 and pass the ref object as its prop:\n\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>\nEmbedding Recharts<\/h5>\n
npm install recharts<\/pre>\n
\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>\ninterface 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\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>\nuseState<\/a><\/code> and useEffects<\/a><\/code> React Hooks, we will know when it\u2019s changed.<\/p>\n\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>\nConnecting Flexmonster and Recharts<\/h5>\n
\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>\nprepareFunction<\/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 Field List<\/a> in Flexmonster.<\/p>\n