Getting started with Prisma ORM and SQLite in Next.js 13

Using Object-Relational Mapping (ORM), such as Prisma or Sequelize, is a neat way to talk to database without getting hands dirty with complex SQL commands. Prisma is one of the best solutions out there for plugging a Node.js app with database such as SQLite or PostgreSQL. In this tutorial, we’ll plug Prisma in Next.js 13 app and use SQLite as database.

Next.js 13 requires minimum of Node.js 16.14. Moreover, you have to have a bit of understanding how JavaScript in general and React.js in particular work. Instructions given here will work in Linux, macOS and WSL (Windows). Assuming you have met the minimum requirements, enter following command in terminal to create Next.js 13 app:

npx create-next-app@13

Keep pressing Enter at each prompt for defaults. After installation, go inside my-app folder:

cd my-app

Now, install Prisma as development dependency:

npm i prisma --save-dev

Also install Prisma client:

npm i @prisma/client

Then create Prisma scaffolding with SQLite as database:

npx prisma init --datasource-provider sqlite

Open prisma/schema.prisma and observe following code – SQLite is already configured as a result of above command.

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

Now, insert following code at the end of prisma/schema.prisma:

model Post {
  id   Int  @id  @default(autoincrement()) 
  title  String
  body   String   
  createdAt DateTime? @default(now())
  updatedAt DateTime? @updatedAt
}

Then, execute the following command in order to run database migration:

npx prisma migrate dev --name init

Next, go inside prisma folder:

cd prisma

and observe that the database file (dev.db) has been created. Let’s stop here for a while and play with SQLite. Being inside prisma folder, enter following command to ignite SQLite shell (assuming you already have sqlite3 installed in your system – if not, then it’s just one command away in Debian-based Linux – sudo apt install sqlite3):

sqlite3 dev.db

You are inside sqlite3 shell. Now enter:

.tables

and notice as Post table gets listed. Here, you can directly insert records inside Post table using pure SQL. So, let’s insert a record:

insert into Post (title,body) values ('First','This is the first post.');

and then enter another record:

insert into Post (title,body) values ('Second','Of course, this is second post.');

Enter following command to exit SQLite shell:

.quit

Then go back to parent folder (my-app) by executing:

cd ..

Coming back to Next.js, let’s utilize Prisma client and retrieve all records from Post table and then render them inside a Next.js component. So, open app/page.tsx and replace contents with following code:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

export default async function Home() {
  const data = await prisma.post.findMany()
  return (
    <div className="p-4 m-4">
      <div className="relative overflow-x-auto shadow-md sm:rounded-lg">
        <table className="w-full text-sm text-left text-gray-500">
          <thead className="text-xs text-gray-700 uppercase bg-gray-50">
            <tr>
              <th scope="col" className="px-6 py-3">
                ID
              </th>
              <th scope="col" className="px-6 py-3">
                Title
              </th>
              <th scope="col" className="px-6 py-3">
                Body
              </th>
            </tr>
          </thead>
          <tbody>
            {data &&
              data.map((item, i) => (
                <tr key={i} className="bg-white border-b">
                  <td scope="row" className="px-6 py-4 font-medium text-gray-900 whitespace-nowrap">
                    {item.id}
                  </td>
                  <td scope="row" className="px-6 py-4 font-medium text-gray-900 whitespace-nowrap">
                    {item.title}
                  </td>
                  <td scope="row" className="px-6 py-4 font-medium text-gray-900 whitespace-nowrap">
                    {item.body}
                  </td>
                </tr>
              ))
            }
          </tbody>
        </table>
      </div>
    </div>
  )
}

Save and start development server by executing:

npm run dev

Now browse to http://localhost:3000

In next tutorial, we’ll utilize this setup in building APIs (using Route Handlers) within Next.js 13 app.