NestJS 13 series - Using a database

Navigating the Literary Galaxy: Integrating Prisma with PlanetScale in Your Next.js App

Greetings, fellow developers and bibliophiles! Today, we embark on a cosmic journey, exploring the seamless integration of Prisma with PlanetScale in your Next.js app. Buckle up, as we delve into the realms of database management to create a stellar web application centered around books and their esteemed authors.

Setting the Galactic Stage

Before we dive into the technical intricacies, let’s briefly introduce our star players: Prisma and PlanetScale.

• Prisma: A modern database toolkit that simplifies database access in your Node.js applications. It provides a type-safe and auto-generated query builder, making database interactions a breeze.

• PlanetScale: A serverless MySQL-compatible database designed for developers who crave scalability and simplicity. With PlanetScale, you can scale your database effortlessly without compromising on performance.

Preparing Your Spaceship

Assuming you already have a Next.js app up and running, let’s prepare it for our cosmic literary adventure.

1. Install Prisma: Open your terminal and install Prisma as a development dependency:

pnpm add prisma

Then, initialize Prisma:

pnpx prisma init

2. Configure Prisma: Modify the generated prisma/schema.prisma file to connect to your PlanetScale database. Update the url field with your PlanetScale connection string.

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

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

Building a Planetary Database of Books and Authors

1. PlanetScale Setup: If you haven’t already, create an account on PlanetScale and set up a new database. Obtain the connection string and replace the placeholder in the Prisma configuration.

Define Your Models: In the schema.prisma file, define your data models. Let’s create models for Author and Book.

// prisma/schema.prisma
model Author {
  id    Int     @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name  String
  books Book[]
}

model Book {
  id       Int     @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt  title    String
  genre    String
  author   Author  @relation(fields: [authorId], references: [id])
  authorId Int
}

Run the following command to apply your changes to the database:

pnpx prisma db push

Interstellar Querying with Prisma

Now that your literary database is set up, let’s make some interstellar queries in your Next.js app.

1. Generate Prisma Client: Run the following command to generate the Prisma Client:

pnpx prisma generate

3. Seed the Database: In your prisma folder, add these files. /prisma/data.js

const authors = [{
    name: "J.K. Rowling"
}, {
    name: "Christopher Moore"
}];
const books = [{
        title: "Harry Potter and the Philosopher's Stone",
        genre: "Fantasy",
        authorId: 1,
    },
    {
        title: "Fantastic Beasts and Where to Find",
        genre: "Fantasy",
        authorId: 1,
    },
    {
        title: "A Dirty Job: A Novel",
        genre: "Fantasy",
        authorId: 2,
    },
    {
        title: "Shakespeare for Squirrels: A Novel",
        genre: "Fantasy",
        authorId: 2,
    },
];

exports.authors = authors;
exports.books = books;

// prisma/seed.js
const {
    PrismaClient
} = require("@prisma/client");
const {
    authors,
    books
} = require("./data.js");
const prisma = new PrismaClient();

const load = async () => {
    try {
        console.log("Seeding data...");
        await prisma.book.deleteMany();
        await prisma.author.deleteMany();
        console.log("Seeding data... deletes");
        await prisma.$queryRaw`ALTER TABLE Author AUTO_INCREMENT = 1`;
        await prisma.$queryRaw`ALTER TABLE Book AUTO_INCREMENT = 1`;

        console.log("Seeding data... reset");
        console.log("Seeding data... authors", authors);
        await prisma.author.createMany({
            data: authors,
        });
        console.log("Seeding data... 1");
        await prisma.book.createMany({
            data: books,
        });
        console.log("Seeding data... 2");
    } catch (e) {
        console.error(e);
        process.exit(1);
    } finally {
        await prisma.$disconnect();
    }
};
load();

Now add this to your package.json scripts section

 "seed": "node prisma/seed.js"

From the terminal run this command to seed the database.

pnpm seed    

You can explore the data with

pnpx prisma studio  

2. Query the Database: Create a page for Books, use the Prisma Client to query your PlanetScale database.

// src/app/[lng]/books/page.tsx
import Link from "next/link";
import {
    PrismaClient
} from "@prisma/client";
const prisma = new PrismaClient();

interface Props {
    params: {
        lng: string;
    };
}
export default async function SecondPage(props: Props) {
    const books = await prisma.book.findMany();
    return ( <
        main style = {
            {
                display: "flex",
                flexDirection: "column",
                gap: "1.5em"
            }
        } >
        <
        h1 > Books < /h1> <
        Link href = {
            `/${props.params.lng}/`
        } > Home < /Link> <
        section > {
            books.map((book) => ( <
                div key = {
                    book.id
                } >
                <
                h2 > {
                    book.title
                } < /h2> <
                /div>
            ))
        } <
        /section> <
        /main>
    );
}

Launching into the Cosmic Literary Future

Congratulations! You’ve successfully integrated Prisma with PlanetScale in your Next.js app, creating a literary galaxy filled with books and their esteemed authors. Your web application is now equipped with a powerful database and a scalable architecture, ready to explore the wonders of the written word in the digital cosmos.

As you continue your development journey, may your code be as elegant as a well-crafted novel, your queries as efficient as a gripping plot, and your web app reach new literary heights!