Use Next.js + TiDB to Experience OSS(Open Source Software) Insight

In the previous article Build a Hacker News Top Stories Demo with Next.js + GitHub Copilot in one hour, I show you how I begin to learn Next.js and build a Hacker News Top Stories Demo. Today, I will show you how to connect TiDB and build a OSS insight dashboard like below:

What’s TiDB?

TiDB is an open-source NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads. It is MySQL compatible and features horizontal scalability, strong consistency, and high availability.

An easy way to try TiDB is through TiDB Cloud, You can use your GitHub or Google account for the Sign Up, then create a Developer Tier for free. Please follow TiDB Cloud Quick Start to learn more about TiDB Cloud.

What’s OSS Insight?

OSS Insight is a powerful web site and saves nearly 4.8B GitHub events now. So we can know more and better open source software insights from it. Of course, OSS Insight is powered by TiDB cloud :-)

After we create a TiDB Cloud Developer Tier, we can follow the guide load some OSS Insight sample data into TiDB.

Create a Next.js Project

First, let’s create a Next.js project.

yarn create next-app next-tidb-demo

I also follow the guide to install Tailwind CSS for Next.js.

Then Set environment variables

export MYSQL_HOST=tidb.6479717b.c3f4522.ap-northeast-1.prod.aws.tidbcloud.com 
export MYSQL_PORT=4000 
export MYSQL_USER=root 
export MYSQL_DATABASE=gharchive_dev
export MYSQL_PASSWORD={your password}

Run the development server:

yarn dev

Connect to TiDB

Because TiDB is MySQL Protocol compatibility, we can use nearly any MySQL driver or ORM to connect to TiDB, here I choose serverless-mysql

yarn add serverless-mysql

Create a `lib/tidb.js` and add the following codes:

import mysql from 'serverless-mysql';
const db = mysql({
    config: {
        host: process.env.MYSQL_HOST,
        port: process.env.MYSQL_PORT,
        database: process.env.MYSQL_DATABASE,
        user: process.env.MYSQL_USER,
        password: process.env.MYSQL_PASSWORD
    }
});
export default async function executeQuery(query, values = []) {
    try {
        const results = await db.query(query, values);
        await db.end();
        return results;
    } catch (error) {
        return { error };
    }
}

Experience the OSS Insight Sample Data

Now let’s do a simple query, to know the Top 20 repositories which have the most stars.

export async function getStaticProps() {
  const results = await executeQuery(`
    SELECT repo_name, count(*) AS events_count
      FROM github_events
      WHERE type = 'WatchEvent'
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 20;
  `);
let rows = [];
  for (const row of results) {
    rows.push({
      repo_name: row.repo_name,
      events_count: row.events_count
    });
  }
return {
    props: {
      rows,
    }
  }
}

We can use `console.log` to see that thing works well.

Show the Insight with Chart

The above example just lets us query the data from TiDB cloud, we need a more beautiful way to show the data on the web site. We use Chart.js here.

yarn add react-chartjs-2 chart.js

In the following code, we use a Bar to represent the query result:

import React from 'react';
import { Bar } from 'react-chartjs-2';
import { Chart, CategoryScale, LinearScale, BarElement } from 'chart.js'
Chart.register(CategoryScale, LinearScale, BarElement)
function buildData(rows) {
    rows = rows || [];
    const labels = rows.map(row => row.repo_name)
    const data = rows.map(row => row.events_count)
    const backgroundColor = rows.map(_ => 'rgba(255, 99, 132, 0.2)')
    const borderColor = rows.map(_ => 'rgba(255, 99, 132, 1)')
    return {
        labels,
        datasets: [{
            label: '# of Top Stars',
            data,
            backgroundColor,
            borderColor,
            borderWidth: 1
        }]
    }
}
export default function TopStarRepos({ rows }) {
    const data = buildData(rows)
return {
        displayName: 'TopStarRepos',
        render() {
            return (
                <div className="mt-6 flex max-w-4xl flex-wrap items-center justify-around sm:w-full">
                    <h2>Top Star Repos</h2>
                    <Bar
                        data={data}
                        width={800}
                        height={400}
                        options={{
                            responsive: false,
                            maintainAspectRatio: false
                        }}
                    />
                </div>
            );
        }
    }
}

Deploy to Vercel

Thing goes well, the final step is to use Vercel to deploy the demo online.

You can have a try here: OSS Insight Demo

Epilogue

In the end, I still want to thank GitHub Copilot, nearly all of the codes are written by it.

I believe Next.js + TiDB is a powerful combination for our work. Using TiDB can help us simplify your data architecture, we only need to care how to use SQL to write our business logic. Using Next.js can help us build the whole web site more efficiently.

Then we can use Vercel + TiDB Cloud to host our all work, so awesome.