Database Integration

Data is an integral part of any web application. In the previous lessons, we simulated the process of saving data to databases by logging them to the console instead. But that was only a temporary solution, and in order to create a functional web application, we have to discuss how to use Next.js with a database.

In the JavaScript chapter, we introduced the concept of an ORM, which stands for Object-Relational Mapping. It is a programming technique that allows us to map database tables to objects in any programming language, making database interactions much more intuitive.

Of course, you can manage the database interactions manually, but it is generally recommended to use an ORM framework instead, because these frameworks offer:

Simplified Database Interaction: ORM frameworks can greatly simplify complex database operations with their built-in querying methods such as create(), update(), and more.
Cross-Database Compatibility: Most ORM frameworks offer compatibility for multiple databases such as SQLite, MySQL, PostgreSQl, and more. They offer a consistent API for different databases, allowing you to switch between them without altering the codebase.
Scalability: ORMs are great for projects that might grow more complex in the future, as they abstract many low-level details and provide features like schema migrations and type safety.
Consistent Practices: This is especially helpful when you work under a team setting, as ORMs enforces consistent practices and reduces the need for raw SQL knowledge.

Setting up Prisma

In this lesson, we are still going to use Prisma.js as an example, as it is the most popular choice among Next.js developers.

First, install Prisma with the following command:

bash

1npm install prisma --save-dev

After the package is installed, then use the following npx command to initialize Prisma:

bash

1npx prisma init

If everything is successful, you should get the following output:

text

1✔ Your Prisma schema was created at prisma/schema.prisma
2  You can now open it in your favorite editor.
3
4. . .

And a prisma directory should be generated under the root directory of the project, and you should find a schema file inside.

text

1.
2├── .env                  <===== Environment variables
3├── README.md
4├── jsconfig.json
5├── next.config.mjs
6├── package-lock.json
7├── package.json
8├── postcss.config.mjs
9├── prisma                <===== Prisma folder
10│   └── schema.prisma     <===== Schema file
11├── public
12├── src
13└── tailwind.config.js

Connecting to a database

Next, we need a database. Again, we are going to use SQLite, as it is a single file-based database, and requires minimum setup.

All you need to do is create a db.sqlite under prisma.

text

1.
2├── .env                  <===== Environment variables
3├── README.md
4├── jsconfig.json
5├── next.config.mjs
6├── package-lock.json
7├── package.json
8├── postcss.config.mjs
9├── prisma
10│   ├── db.sqlite         <===== SQLite database
11│   └── schema.prisma     <===== Schema
12├── public
13├── src
14└── tailwind.config.js

And then, in the schema file, you must tell Prisma how to connect to this database by defining a datasource:

schema.prisma

prisma

1generator client {
2  provider = "prisma-client-js"
3}
4
5datasource db {
6  provider = "sqlite"
7  url      = env("DATABASE_URL")
8}

Make sure the provider is set to sqlite, and the connection URL is retrieved from the .env file.

.env

env

1DATABASE_URL="file:./db.sqlite"

And in this case, the URL is pointed at the db.sqlite. Notice that we are using a relative path, which is relative to the schema file, schema.prisma.

Create a schema

Now, take a closer look at the schema file. This is where we define the structure of our database. For instance, you can declare a new User table like this:

prisma

1generator client {
2  provider = "prisma-client-js"
3}
4
5datasource db {
6  provider = "sqlite"
7  url      = env("DATABASE_URL")
8}
9
10model User {
11  id    Int     @id @default(autoincrement())
12  email String  @unique
13  name  String?
14}

Inside this User table, there are three columns, id, email, and name.

id is an integer, and by default, it will increment automatically for each additional record. This id column is also used for indexing purposes, marked by @id.

email is a string, and each record should contain a unique email.

name is also a string, but it is optional, as indicated by the question mark (?).

Next, you can apply this schema to the database by running the following command. The technical term for this process is called running database migration.

bash

1npx prisma migrate dev --name init

If everything works without errors, you should get the following output.

text

1Environment variables loaded from .env
2Prisma schema loaded from prisma/schema.prisma
3Datasource "db": SQLite database "db.sqlite" at "file:./db.sqlite"
4
5Applying migration `20241219030527_init`
6
7The following migration(s) have been created and applied from new schema changes:
8
9migrations/
10  └─ 20241219030527_init/
11    └─ migration.sql
12
13Your database is now in sync with your schema.
14
15Running generate... (Use --skip-generate to skip the generators)
16
17✔ Generated Prisma Client (v6.1.0) to ./node_modules/@prisma/client in 46ms

Installing the Prisma client

Finally, there's one more thing we need, and that is the Prisma client.

bash

1npm install @prisma/client

Do not be confused with the prisma package we installed at the beginning of this lesson, the Prisma client is a separate package that allows you to interact with the database programmatically in a JavaScript application.

For example, the Prisma client comes with a few methods that allows you to perform CRUD (Create, Read, Update, Delete) operations to the database.

create()

The create() method is used to create and save a new record.

page.jsx

jsx

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const user = await prisma.user.create({
7    data: {
8      email: "amy@thedevspace.io",
9      name: "Amy Pond",
10    },
11  });
12
13  return (
14    <div>
15      <p>Name: {user.name}</p>
16      <p>Email: {user.email}</p>
17    </div>
18  );
19}

This create() method returns an object containing information regarding the record that was just created.

Open the browser and go to http://localhost:3001/, and you should see the following page.

And take a look at the db.sqlite database, a new record should be added to the User table.

findUnique()

findUnique() is used to locate and retrieve an existing record from the database, based on the provided criteria. For example:

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const user = await prisma.user.findUnique({
7    where: {
8      email: "amy@thedevspace.io",
9    },
10  });
11
12  return (
13    <div>
14      <p>Name: {user.name}</p>
15      <p>Email: {user.email}</p>
16    </div>
17  );
18}

findMany()

Used to retrieve multiple records that fits the provided criteria. Returns an array, which can be iterated using the map() method.

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const users = await prisma.user.findMany({
7    where: {
8      email: {
9        endsWith: "thedevspace.io",
10      },
11    },
12  });
13
14  return (
15    <div>
16      {users.map((user) => (
17        <>
18          <p>Name: {user.name}</p>
19          <p>Email: {user.email}</p>
20        </>
21      ))}
22    </div>
23  );
24}

update()

Updates an existing record in the database, and returns the updated result.

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const updateUser = await prisma.user.update({
7    where: {
8      email: "amy@thedevspace.io",
9    },
10    data: {
11      name: "Alice Pond",
12    },
13  });
14
15  return (
16    <div>
17      <p>Name: {updateUser.name}</p>
18      <p>Email: {updateUser.email}</p>
19    </div>
20  );
21}

updateMany()

Updates all the record that fits the criteria. Unlike findMany(), updateMany() returns a BatchPayload instead of an array.

The BatchPayload contains only one property, count, which is the number of records that were successfully modified.

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const updateUsers = await prisma.user.updateMany({
7    where: {
8      email: {
9        contains: "thedevspace.io",
10      },
11    },
12    data: {
13      role: "ADMIN",
14    },
15  });
16
17  return <div>. . .</div>;
18}

delete()

Removes a record from the database according to the provided criteria. Returns the record that was removed.

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const deleteUser = await prisma.user.delete({
7    where: {
8      email: "amy@thedevspace.io",
9    },
10  });
11
12  return <div>. . .</div>;
13}

deleteMany()

Removes all records that fit the criteria, and returns a BatchPayload.

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const deleteUsers = await prisma.user.deleteMany({
7    where: {
8      email: {
9        contains: "thedevspace.io",
10      },
11    },
12  });
13
14  return <div>. . .</div>;
15}

These methods can be used to handle database interactions in API routes, server actions, or server components, as long as the code will be executed on the server. Do not try to access the database inside client components.

There are already x instances of Prisma Client actively running.

There is a commonly encountered issue when working with Prisma.js inside Next.js applications, and that is the error:

text

1warn(prisma-client) There are already 10 instances of Prisma Client actively running.

This error is caused by hot module reloading (HMR), which is a feature that allows Next.js to reload the server whenever you make changes to the source code.

However, whenever the server reloads, a new instance of Prisma Client will be created, which will quickly exhaust the database connections.

The solution to this error is to create a singleton for the Prisma Client, and ensure it is reused across server reloads.

Create a db.js file under src/libs.

text

1src
2├── actions.js
3├── app
4│   ├── favicon.ico
5│   ├── globals.css
6│   ├── layout.jsx
7│   └── page.jsx
8└── libs
9    └── db.js        <===== Prisma Client Singleton

And add the following code:

db.js

javascript

1import { PrismaClient } from "@prisma/client";
2
3const prismaClientSingleton = () => {
4  return new PrismaClient();
5};
6
7const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();
8
9export default prisma;
10
11if (process.env.NODE_ENV !== "production") globalThis.prismaGlobal = prisma;

This code check if the current environment is in production. If in production, a new PrismaClient instance will be returned as usually, just like what we've demonstrated above.

However, when working under development environment, the global instance of the PrismaClient will be returned, which is saved to the globalThis object, which will persist across server reloads.

This makes sure you only create one PrismaClient instance in the development environment.

And in our page.jsx, when you need to use Prisma Client, instead of importing from @prisma/client and then creating a new instance like this:

jsx

1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient();
4
5export default async function Home() {
6  const user = await prisma.user.create({
7    data: {
8      email: "amy@thedevspace.io",
9      name: "Amy Pond",
10    },
11  });
12
13  return (
14    <div>
15      <p>Name: {user.name}</p>
16      <p>Email: {user.email}</p>
17    </div>
18  );
19}

Make sure you import prisma from our db.js.

jsx

1import prisma from "@/libs/db";
2
3export default async function Home() {
4  const user = await prisma.user.create({
5    data: {
6      email: "amy@thedevspace.io",
7      name: "Amy Pond",
8    },
9  });
10
11  return (
12    <div>
13      <p>Name: {user.name}</p>
14      <p>Email: {user.email}</p>
15    </div>
16  );
17}