Real-time page views with Next.js, Turso and Drizzle ORM
Tags: webdev, turso, nextjs, orm
Published: 2023-06-02 20:03:45

> This article [Real-time page views with Next.js, Turso and Drizzle ORM](https://www.news47ell.com/blog/page-views-nextjs-turso-drizzle-orm) was [originally posted on News47ell.com](https://www.news47ell.com/). For more articles like this, [click here](https://www.news47ell.com/blog).

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](https://turso.tech/), 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](https://drizzle.team/) 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](https://docs.turso.tech/tutorials/get-started-turso-cli/step-01-installation)):

```bash
brew install chiselstrike/tap/turso
```

* Sign up to Turso:

```bash
turso auth signup
```

* Create a new database

```bash
turso db create [db-name]
```

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

```bash
turso db show [db-name]
```

* Access your database shell:

```bash
turso db shell [db-name]
```

* Create a new table for `views`:

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

* Create an `auth` token

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

* Now inside `.env` file, add the following:

```bash
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:

```bash
npm i drizzle-orm @libsql/client
```

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

```tsx
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

```tsx
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

```tsx
'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.