Next.js + Prisma 7 + SQLite: The Modern Way to Use SQL with libSQL

Setting up a modern fullstack project with Next.js, TypeScript, Prisma 7, and SQLite using the NEW libSQL adapter.

🚀 Full Showcase Project

Want to see this in action? Check out the complete implementation with a beautiful board-style UI, interactive components, and real-time updates:

**📦 GitHub Repository — Full Project**

The showcase includes:

• 📊 Dashboard with real-time statistics
• 🎨 Modern board layout (Published/Drafts columns)
• ⚡ Interactive cards with edit/delete actions
• 🔄 Optimistic UI updates
• 🎯 Advanced form handling with state management

This guide covers the fundamentals. For the complete, production-ready implementation, visit the repository above.

Table of Contents

• The Version Problem
• Quick Start Guide
• Project Structure
• Configuration Files
• Code Examples
• Resources

The Version Problem

When using Prisma 7 with the @prisma/adapter-libsql adapter, there's a known incompatibility with recent versions of @libsql/client. The solution is to pin the exact version to 0.8.1:

{
  "dependencies": {
    "@libsql/client": "0.8.1",
    "@prisma/adapter-libsql": "^7.1.0",
    "@prisma/client": "^7.1.0"
  }
}

Why not newer versions?

Prisma expects a specific libSQL client interface that changed in later versions. If you install @libsql/client@^0.15.15 or higher, you'll encounter incompatible type errors at compile time or runtime.

Quick Start Guide

1. Installation

# Create Next.js project
npx create-next-app@latest my-app --typescript --tailwind --app
cd my-app

# Install Prisma dependencies (CRITICAL VERSIONS)
pnpm add @prisma/client@^7.1.0 @prisma/adapter-libsql@^7.1.0 @libsql/client@0.8.1
pnpm add -D prisma@^7.1.0 dotenv tsx

2. Initialize Database

pnpm prisma init
pnpm prisma generate
pnpm prisma migrate dev --name init
pnpm db:seed  # Optional: populate with sample data
pnpm dev

Now open http://localhost:3000

Project Structure

nextjs-prisma-sqlite/
├── app/
│   ├── actions/           # Server Actions
│   │   ├── users.ts
│   │   └── posts.ts
│   ├── components/        # Client Components
│   │   ├── CreateUserForm.tsx
│   │   └── UserCard.tsx
│   ├── layout.tsx
│   └── page.tsx          # Server Component
├── lib/
│   └── prisma.ts         # Prisma client singleton
├── prisma/
│   ├── schema.prisma     # Database schema
│   └── seed.ts           # Seed data (optional)
├── .env                  # Environment variables
└── package.json

Configuration Files

package.json

{
  "name": "nextjs-prisma-sqlite",
  "version": "0.1.0",
  "private": true,
  "scripts": {
    "dev": "next dev",
    "build": "next build",
    "start": "next start",
    "prisma:generate": "prisma generate",
    "prisma:migrate": "prisma migrate dev",
    "prisma:studio": "prisma studio",
    "db:seed": "tsx prisma/seed.ts"
  },
  "dependencies": {
    "@libsql/client": "0.8.1",
    "@prisma/adapter-libsql": "^7.1.0",
    "@prisma/client": "^7.1.0",
    "next": "^16.0.7",
    "react": "^19.2.1",
    "react-dom": "^19.2.1"
  },
  "devDependencies": {
    "@tailwindcss/postcss": "^4",
    "@types/node": "^20",
    "@types/react": "^19",
    "@types/react-dom": "^19",
    "dotenv": "^17.2.3",
    "prisma": "^7.1.0",
    "tsx": "^4.21.0",
    "eslint": "^9",
    "eslint-config-next": "16.0.7",
    "tailwindcss": "^4",
    "typescript": "^5"
  }
}

prisma/schema.prisma

Important: Note that the database URL is NO longer defined in the schema. Instead, it’s loaded from the .env file via the adapter configuration.

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
}
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

.env

DATABASE_URL="file:./prisma/dev.db"

Key changes in Prisma 7:

• Database URL is now loaded via dotenv in the adapter configuration
• No url = env("DATABASE_URL") in schema.prisma
• The adapter handles the connection using environment variables

lib/prisma.ts

import 'dotenv/config';
import { PrismaClient } from '@prisma/client';
import { PrismaLibSql } from '@prisma/adapter-libsql';

const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) {
  throw new Error('DATABASE_URL is not configured');
}

// Global singleton to avoid multiple instances in development
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
  prismaAdapter: PrismaLibSql | undefined;
};

// Create the libSQL adapter
const adapter =
  globalForPrisma.prismaAdapter ??
  new PrismaLibSql({
    url: databaseUrl,
  });

// Create Prisma client with the adapter
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    adapter,
    log: ['query', 'error', 'warn'],
  });

// In development, save instances globally
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
  globalForPrisma.prismaAdapter = adapter;
}

Key points:

• Import dotenv/config at the top to load environment variables
• Use PrismaLibSql from @prisma/adapter-libsql
• Pass the adapter to the Prisma client constructor
• Implement singleton pattern to avoid multiple connections in development
• Save both client and adapter in the global object

prisma/seed.ts (optional)

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()
async function main() {
  // Create users with posts
  const alice = await prisma.user.create({
    data: {
      name: 'Alice',
      email: 'alice@example.com',
      posts: {
        create: [
          {
            title: 'Hello World',
            content: 'This is my first post',
            published: true,
          },
          {
            title: 'Draft Post',
            content: 'This is a draft',
            published: false,
          },
        ],
      },
    },
  })
  const bob = await prisma.user.create({
    data: {
      name: 'Bob',
      email: 'bob@example.com',
    },
  })
  console.log({ alice, bob })
}
main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Code Examples

Note: The examples below cover the fundamentals. For advanced patterns like interactive cards, board layouts, and optimistic updates, check the full showcase project.

Server Component with Data Fetching

app/page.tsx

import { prisma } from '@/lib/prisma'

export const revalidate = 30 // ISR every 30 seconds
async function getUsers() {
  const users = await prisma.user.findMany({
    include: { posts: true },
    orderBy: { createdAt: 'desc' },
  })
  return users
}
async function getAllPosts() {
  const posts = await prisma.post.findMany({
    include: { author: true },
    orderBy: { createdAt: 'desc' },
  })
  return posts
}
export default async function Home() {
  // Parallel data fetching
  const [users, allPosts] = await Promise.all([
    getUsers(),
    getAllPosts()
  ])
  const publishedPosts = allPosts.filter(p => p.published)
  const draftPosts = allPosts.filter(p => !p.published)
  return (
    <main className="container mx-auto p-8">
      <h1 className="text-3xl font-bold mb-8">Users & Posts</h1>

      <section className="mb-8">
        <h2 className="text-2xl font-semibold mb-4">Users ({users.length})</h2>
        <div className="grid gap-4">
          {users.map(user => (
            <div key={user.id} className="border rounded-lg p-4">
              <h3 className="text-xl font-semibold">{user.name}</h3>
              <p className="text-gray-600">{user.email}</p>
              <p className="text-sm mt-2">Posts: {user.posts.length}</p>
            </div>
          ))}
        </div>
      </section>
      <section>
        <h2 className="text-2xl font-semibold mb-4">
          Published Posts ({publishedPosts.length})
        </h2>
        <div className="grid gap-4">
          {publishedPosts.map(post => (
            <article key={post.id} className="border rounded-lg p-4">
              <h3 className="text-xl font-semibold">{post.title}</h3>
              <p className="text-gray-600">{post.content}</p>
              <p className="text-sm mt-2">By: {post.author.name}</p>
            </article>
          ))}
        </div>
      </section>
    </main>
  )
}

Server Actions with Error Handling

app/actions/users.ts

'use server'

import { Prisma } from '@prisma/client'
import { prisma } from '@/lib/prisma'
import { revalidatePath } from 'next/cache'
export async function createUser(formData: FormData) {
  try {
    const name = formData.get('name') as string | null
    const email = formData.get('email') as string | null
    if (!email) {
      return { error: 'Email is required' }
    }
    const user = await prisma.user.create({
      data: { name: name || null, email },
      include: { posts: true },
    })
    revalidatePath('/') // Invalidate cache
    return { success: true, user }
  } catch (error) {
    console.error('Error creating user:', error)

    // Handle duplicate email
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        return { error: 'Email already registered' }
      }
    }

    return { error: 'Unexpected error' }
  }
}
export async function deleteUser(id: number) {
  try {
    // Delete related posts first
    await prisma.post.deleteMany({
      where: { authorId: id },
    })

    // Then delete user
    await prisma.user.delete({
      where: { id },
    })
    revalidatePath('/')
    return { success: true }
  } catch (error) {
    console.error('Error deleting user:', error)
    return { error: 'Error deleting user' }
  }
}
export async function updateUser(id: number, formData: FormData) {
  try {
    const name = formData.get('name') as string | null
    const email = formData.get('email') as string | null
    if (!email) {
      return { error: 'Email is required' }
    }
    const user = await prisma.user.update({
      where: { id },
      data: { name, email },
      include: { posts: true },
    })
    revalidatePath('/')
    return { success: true, user }
  } catch (error) {
    console.error('Error updating user:', error)

    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        return { error: 'Email already in use' }
      }
    }

    return { error: 'Error updating user' }
  }
}

app/actions/posts.ts

'use server'

import { prisma } from '@/lib/prisma'
import { revalidatePath } from 'next/cache'
export async function createPost(formData: FormData) {
  try {
    const title = formData.get('title') as string | null
    const content = formData.get('content') as string | null
    const authorId = formData.get('authorId') as string | null
    const published = formData.get('published') === 'true'
    if (!title || !authorId || Number.isNaN(parseInt(authorId))) {
      return { error: 'Title and author are required' }
    }
    const post = await prisma.post.create({
      data: {
        title,
        content: content || '',
        published,
        authorId: parseInt(authorId),
      },
      include: { author: true },
    })
    revalidatePath('/')
    return { success: true, post }
  } catch (error) {
    console.error('Error creating post:', error)
    return { error: 'Error creating post' }
  }
}
export async function deletePost(id: number) {
  try {
    await prisma.post.delete({ where: { id } })
    revalidatePath('/')
    return { success: true }
  } catch (error) {
    console.error('Error deleting post:', error)
    return { error: 'Error deleting post' }
  }
}
export async function togglePostPublished(id: number, published: boolean) {
  try {
    const post = await prisma.post.update({
      where: { id },
      data: { published },
      include: { author: true },
    })
    revalidatePath('/')
    return { success: true, post }
  } catch (error) {
    console.error('Error toggling post:', error)
    return { error: 'Error toggling post state' }
  }
}

Client Component Using Server Actions

app/components/CreateUserForm.tsx

'use client'

import { createUser } from '@/app/actions/users'
import { useState, useTransition } from 'react'
export function CreateUserForm() {
  const [isPending, startTransition] = useTransition()
  const [error, setError] = useState<string | null>(null)
  async function handleSubmit(formData: FormData) {
    startTransition(async () => {
      const result = await createUser(formData)

      if (result.error) {
        setError(result.error)
      } else {
        setError(null)
        // Form resets automatically
      }
    })
  }
  return (
    <form action={handleSubmit} className="space-y-4 max-w-md">
      <div>
        <input
          name="name"
          placeholder="Name"
          disabled={isPending}
          className="w-full rounded-lg border bg-slate-800 px-4 py-2"
        />
      </div>

      <div>
        <input
          name="email"
          type="email"
          placeholder="Email"
          required
          disabled={isPending}
          className="w-full rounded-lg border bg-slate-800 px-4 py-2"
        />
      </div>

      {error && (
        <p className="text-sm text-red-400">{error}</p>
      )}

      <button
        type="submit"
        disabled={isPending}
        className="w-full rounded-lg bg-blue-500 px-4 py-2 text-white disabled:opacity-50"
      >
        {isPending ? 'Creating...' : 'Create User'}
      </button>
    </form>
  )
}

Key patterns:

• useTransition() for pending states
• Server Action as form action
• Automatic form reset on success
• Disabled state during submission
• Type-safe error handling

Resources

Official Documentation

• Prisma Documentation
• Prisma libSQL Adapter
• Next.js App Router
• libSQL GitHub

**🔗 View Full Project on GitHub**

Nice Things!! by revione.