Relational Mapping

Database + Schema

1. Create a Next.js application using bunx create-next-app@latest.¹

2. Install the Prisma package using bun add --dev prisma dotenv.

3. Initialize Prisma with SQLite (opens in a new tab) as data source provider using prisma init --datasource-provider sqlite --generator-provider prisma-client-js --output client.² Rename prisma.config.ts to prisma.config.mjs.

4. The environment variable DATABASE_URL in .env can be updated with the desired location of the database file, for example, DATABASE_URL="file:prisma/db/dev.db".

5. Update the schema, prisma/schema.prisma, with data models for a one-to-many relationship between users and their posts:

   • Each User has an identifier, a unique email, a name, and a list of posts.
   • Each Post has an identifier, a title, a text content, a published status, and a corresponding author.
   prisma/schema.prisma

   model User {
     id    String  @id @default(cuid(2))
     email String  @unique
     name  String?
     posts Post[]
   }
    
   model Post {
     id         String  @id @default(cuid(2))
     title      String
     content    String?
     published  Boolean @default(false)
     postAuthor User    @relation(fields: [author], references: [id])
     author     String
   }

6. Create the supporting SQLite database using prisma migrate dev --name init. This will generate and execute a SQL migration³ against the database⁴ to define its tables and relationships.

7. Use Prisma Studio, prisma studio, to view the database tables and edit/filter their records, if needed.

Client + Repository

8. Create a repository module, repos/users.js, to manage access to the data.

9. Install⁵ the client library and adapter using bun add @prisma/client @prisma/client-runtime-utils @prisma/adapter-libsql and use them to access the data in the repository:

   repos/users.js

   import { PrismaLibSql } from "@prisma/adapter-libsql";
   import { PrismaClient } from "@/prisma/client/client";
    
   const prisma = new PrismaClient({
     adapter: new PrismaLibSql({
       url: process.env.DATABASE_URL ?? "",
     }),
   });

   The client is custom-tailored to the data model and must be regenerated using prisma generate whenever the schema is updated⁶.

10. Create a disconnect() method that closes a client connection, which will be used after having executed a query:

    repos/users.js

    async function disconnect() {
      try {
        await prisma.$disconnect();
      } catch (e) {
        console.error(e);
        await prisma.$disconnect();
        process.exit(1);
      }
    }

11. Create a read() method that returns all users along with their posts. It should call disconnect() after executing the queries and before returning the results. Use prisma.user.findMany() and include the posts:

    repos/users.js

    export async function read() {
      const users = await prisma.user.findMany({
        include: {
          posts: true,
        },
      });
     
      await disconnect();
      return users;
    }

    The generated SQL queries can be logged by setting the log option in the PrismaClient constructor: new PrismaClient({ log: ["query"] }).

12. In a long-running application, prisma.$disconnect() should not be explicitly called. One approach is to avoid calling disconnect() and instead create a client module, repos/prisma.js, then import it in repos/users.js to cache the PrismaClient instance and reuse it across the application:

    repos/prisma.js

    import { PrismaLibSql } from "@prisma/adapter-libsql";
    import { PrismaClient } from "@/prisma/client/client";
     
    export default new PrismaClient({
      adapter: new PrismaLibSql({
        url: process.env.DATABASE_URL ?? "",
      }),
      log: ["query"],
    });
    repos/users.js

    import prisma from "@/repos/prisma";
     
    export async function read() {
      return await prisma.user.findMany({
        include: {
          posts: true,
        },
      });
    }

RESTful API

13. Import and use the repository in api/users/route.js to implement a GET method that returns all users along with their posts using read():

    app/api/users/route.js

    import * as users from "@/repos/users.js";
     
    export async function GET(request) {
      return Response.json(await users.read());
    }

14. Test the route using Postman (opens in a new tab).

Seeding

15. Create a prisma/seed.mjs script that uses the Faker (opens in a new tab) package⁷ to procedurally generate sample data and populates the database using prisma.user.create():

    prisma/seed.mjs

    import { PrismaLibSql } from "@prisma/adapter-libsql";
    import { PrismaClient } from "@/prisma/client/client";
    import { faker } from "@faker-js/faker";
     
    const prisma = new PrismaClient({
      adapter: new PrismaLibSql({
        url: process.env.DATABASE_URL ?? "",
      }),
      log: ["query"],
    });
     
    const seed = async () => {
      Array.from({ length: Math.floor(Math.random() * 60) }).forEach(
        async () =>
          await prisma.user.create({
            data: {
              email: faker.internet.email(),
              name: faker.person.fullName(),
              posts: {
                create: Array.from({
                  length: Math.floor(Math.random() * 12),
                }).map(() => ({
                  title: faker.commerce.productName(),
                  content: faker.lorem.text(),
                  published: Math.random() > 0.5,
                })),
              },
            },
          }),
      );
    };
     
    try {
      await seed();
      await prisma.$disconnect();
    } catch (e) {
      console.error(e);
      await prisma.$disconnect();
      process.exit(1);
    }

16. Add a new entry under migrations in prisma.config.mjs then manually seed the database using prisma db seed:

    prisma.config.mjs

    {
      •••
      "migrations": {
        "seed": "bun prisma/seed.mjs"
      }
    }

17. Verify that the data has been added using Prisma Studio or by sending a GET request to the API route.