Building with Purpose 3: Using Prisma and PostgreSQL for the database part

Now that we have the authentication implemented, we need to define the database.

We first need to add the necessary dependencies.

pnpm add @prisma/client prisma pg

Create the database:

psql -U postgres

CREATE DATABASE yourdatabase;

With Postgres running, it’s now time for Prisma.

We’ll begin by running the command that will initialize it:

npx prisma init

This will create the prisma folder with the schema.prisma inside.

By default, this will be the content of the schema.prisma file (you’ll also see comments):

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

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

We’re going to use it as it is, so there's no need to modify it.

The next step is to add at least one model to it. We’re going to add a basic user. Clerk will handle our users, but if we want to add more fields to our user (maybe we’ll want to save some configurations), we’ll need them in our database. These users will be updated via webhooks, which we’ll configure later on when setting up the frontend.

Let’s add the user model:

model User {
    id       String @id @default(cuid())
    email    String @unique
    password String
}

Once this is done, the next step is to add the DATABASE_URL in the .env file. You’ll see that Prisma adds it automatically after the init command, so you just need to modify its parameters to make it work.

DATABASE_URL="postgresql://youruser:yourpassword@localhost:5432/yourdatabase?schema=public"

Right after, you can run the following command to generate the prisma client based on your schema and the migration files:

npx prisma migrate dev --name init

Now that we have the client initialised, it's time to run the studio to see if we did it correctly.

For this we’ll run the npx prisma studio command. This command will open http://localhost:5555 and you should see something like this.

If this pops up, everything is running as expected 💪.

We are now going to create a user to retrieve it with Prisma using the Add record button.

After the user is created, it’s time to add Prisma to Fastify. To do so, we are adding this code to the index.ts file.

We need to import the PrismaClient, declare a new prisma const, and create a GET request to retrieve the user we just created.

// Other imports
import { PrismaClient } from "@prisma/client"; ⬅️⬅️⬅️

dotenv.config();

const prisma = new PrismaClient(); ⬅️⬅️⬅️
const server = Fastify({
    logger:
        process.env.NODE_ENV !== "production"
            ? {
                    transport: {
                        target: "pino-pretty",
                        options: {
                            ignore: "pid,hostname",
                            colorize: true,
                        },
                    },
                }
            : true,
});

server.register(clerkPlugin, {
    publishableKey: process.env.CLERK_PUBLISHABLE_KEY,
    secretKey: process.env.CLERK_SECRET_KEY,
});
⬇️⬇️⬇️
server.get("/users", async (req, reply) => {
    const users = await prisma.user.findMany();
    reply.send(users);
});
⬆️⬆️⬆️
// rest of the code

Our route is now created. It’s time to test it with Postman.

We got our user correctly, yay! 🥳

---

Not explained here

• Installing and setting up PostgreSQL locally

That's it for this part.

Salut, Jordi.

Check the repository HERE.