Real-time page views with Next.js, Turso and Drizzle ORM

This article Real-time page views with Next.js, Turso and Drizzle ORM was originally posted on News47ell.com. For more articles like this, click here.

If you're searching for a simple and effective method to incorporate real-time page views into your Next.js website, you've come across the perfect tutorial on this topic. In this article, I will guide you through the process of integrating real-time page views into your Next.js website using Turso and Drizzle ORM.

Turso: A Game-Changing SQLite Edge Database

Let me introduce you to Turso, the groundbreaking SQLite edge database that will revolutionize your website. Turso is built on the robust libSQL framework and offers an astonishing free plan that lasts indefinitely. With this plan, you'll enjoy a generous 8 GB of total storage and the ability to create up to 3 databases across 3 different locations. Prepare to have your expectations completely exceeded!

Drizzle ORM: The Cutting-Edge Object-Relational Mapping Library

Drizzle ORM is an advanced object-relational mapping library designed specifically for Node.js and TypeScript applications. This powerhouse provides comprehensive support for multiple databases, migrations, and query building. It's like having a turbocharged engine powering your website!

Setup Turso database

• Install the Turso CLI (For more options Click here):

brew install chiselstrike/tap/turso

• Sign up to Turso:

turso auth signup

• Create a new database

turso db create [db-name]

• Get the URL of your database (Starts with libsql://):

turso db show [db-name]

• Access your database shell:

turso db shell [db-name]

• Create a new table for views:

CREATE TABLE IF NOT EXISTS views (
    slug TEXT PRIMARY KEY,
    title TEXT,
    count INTEGER
);

• Create an auth token

turso db tokens create [db-name] -e none

• Now inside .env file, add the following:

DATABASE_URL=libsql://[db-url]
DATABASE_AUTH_TOKEN=[auth-token]

Connect Next.js to Turso

In order to connect our site to the database, we need to use Drizzle ORM. All we have to do is install couple of packages and set them up.

• Install Drizzle ORM and libSQL client:

npm i drizzle-orm @libsql/client

• Create a file lib/turso.ts to initialize your Turso client

import { createClient } from '@libsql/client'
import { drizzle } from 'drizzle-orm/libsql'
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'

const connection = createClient({
    url: process.env.DATABASE_URL || '',
    authToken: process.env.DATABASE_AUTH_TOKEN,
})

export const db = drizzle(connection)

export const viewsTable = sqliteTable('views', {
    slug: text('slug').primaryKey(),
    count: integer('count').notNull().default(0),
})

• Now we create app/api/views/[slug]/route.ts and use it to increment and fetch page views

import { eq } from 'drizzle-orm'
import { type NextRequest, NextResponse } from 'next/server'
import { z } from 'zod'

import { db, viewsTable } from '@/lib/turso'

interface Options {
    params: {
        slug: string
    }
}

export const GET = async (request: NextRequest, { params }: Options) => {
    const slug = z.string().parse(params.slug)

    const data = await db.select().from(viewsTable).where(eq(viewsTable.slug, slug)).all()

    const count = !data.length ? 0 : Number(data[0].count)

    return NextResponse.json({ count })
}

export const POST = async (request: NextRequest, { params }: Options) => {
    const slug = params.slug

    const data = await db.select().from(viewsTable).where(eq(viewsTable.slug, slug)).all()

    const count = !data.length ? 0 : Number(data[0].count)

    await db
    .insert(viewsTable)
    .values({
      slug,
      count: 1,
    })
    .onConflictDoUpdate({
      target: viewsTable.slug,
      set: {
        count: count + 1,
      },
    })
    .returning()
    .get()

    return NextResponse.json({ count: count + 1 })
}

• Finally, we create ViewCounter.tsx component to display the page views which we can use in our blog posts

'use client'

import { useEffect } from 'react'
import useSWR from 'swr'

import fetcher from '@/lib/fetcher'
import { PostView } from '@/lib/types'

export default function ViewsCounter({ slug, trackView }: { slug: string; trackView: boolean }) {
    const { data } = useSWR<PostView>(`/api/views/${slug}`, fetcher)
    const views = new Number(data?.count || 0)

    useEffect(() => {
        const registerView = () => {
            fetch(`/api/views/${slug}`, {
                method: 'POST',
            })
        }

        if (trackView) {
            registerView()
        }
    }, [slug])

    return (
        <p className="font-mono text-sm tracking-tighter">
            {data ? `${views.toLocaleString()} views` : '--- views'}
        </p>
    )
}

Conclusion

Now we have a working page views counter that is connected to our Turso database. You can use this method to add page views to any website that is built with Next.js.