Developing a Data Table for Portfolios using Next.js, Clerk, Prisma, and PostgreSQL: A Public Build

In this tutorial, we will be creating a data table for portfolios using NextJS14, Clerk, Prisma, and PostgreSQL. We will build this project in a public repository so that you can follow along and see the progress as we go.

Before we begin, let’s define what each technology is and how they will be used in this project:

1. NextJS14: Next.js is a React framework for building server-rendered applications. We will use it to create our frontend application and handle routing.

2. Clerk: Clerk is an authentication and user management tool that provides secure logins, user profiles, and account settings out of the box. We will use it to manage user authentication in our portfolio application.

3. Prisma: Prisma is a modern ORM (Object-Relational Mapping) for Node.js and TypeScript applications. It allows us to interact with our database using a type-safe API. We will use Prisma to connect to our PostgreSQL database and define our data models.

4. PostgreSQL: PostgreSQL is a powerful, open-source relational database management system. We will use it to store our portfolio data and interact with it using Prisma.

Now that we have a basic understanding of the technologies we will be using, let’s get started on creating our data table for portfolios.

Step 1: Setting up the project
First, create a new project directory and navigate to it in your terminal. Run the following commands to set up a new Next.js project:

npx create-next-app my-portfolio
cd my-portfolio

Next, install the Clerk and Prisma packages by running the following commands:

npm install @clerk/clerk-react @clerk/clerk-js prisma

Step 2: Setting up Clerk authentication
Next, we need to set up Clerk authentication in our project. Create a new Clerk account at https://www.clerk.dev/ and follow the instructions to set up a new application. Once you have created your application, retrieve your API keys and configure Clerk in your project by creating a .env.local file in the root directory of your project:

CLERK_FRONTEND_API=https://<YOUR-CLERK-FRONTEND-API>
CLERK_API=https://<YOUR-CLERK-API>

Next, create a new _app.js file in the pages directory of your project and add the following code to set up Clerk authentication:

import { ClerkProvider } from '@clerk/clerk-react';

function MyApp({ Component, pageProps }) {
  return (
    <ClerkProvider frontendApi={process.env.CLERK_FRONTEND_API}>
      <Component {...pageProps} />
    </ClerkProvider>
  );
}

export default MyApp;

Step 3: Setting up Prisma and PostgreSQL
Next, we need to set up Prisma and connect it to our PostgreSQL database. Create a new Prisma schema file named schema.prisma in the root directory of your project and add the following code:

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

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

Next, configure Prisma to use your PostgreSQL database by adding the following environment variable to your .env file:

DATABASE_URL=postgresql://<USERNAME>:<PASSWORD>@localhost:5432/<DATABASE>

Run the following command to generate the Prisma client and create the database schema:

npx prisma generate

Step 4: Creating the data model
Next, we need to define the data model for our portfolio application. Create a new file named portfolio.model.ts in the prisma directory of your project and add the following code:

import { model } from '@prisma/client';

export const Portfolio = model<Portfolio>('portfolio', {
  id: model.hashKey(),
  userId: model.stringField(),
  title: model.stringField(),
  description: model.stringField(),
  imageUrl: model.stringField(),
  createdAt: model.stringField(),
});

Finally, run the following command to generate the Prisma client and migrate the database schema:

npx prisma migrate up --experimental

Step 5: Creating the data table
Now that we have set up Clerk authentication and defined our data model, we can create the data table for portfolios. Create a new file named portfolios.ts in the api directory of your project and add the following code:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default async function handler(req, res) {
  if (req.method === 'GET') {
    const portfolios = await prisma.portfolio.findMany();
    res.status(200).json(portfolios);
  } else if (req.method === 'POST') {
    const { title, description, imageUrl } = req.body;
    const portfolio = await prisma.portfolio.create({
      data: {
        userId: req.headers.authorization,
        title,
        description,
        imageUrl,
        createdAt: new Date().toISOString(),
      },
    });
    res.status(201).json(portfolio);
  } else {
    res.status(405).json({ error: 'Method Not Allowed' });
  }
}

Finally, update the api/users.ts file to add the following code to restrict access to the portfolio endpoint to authenticated users only:

export default async function handler(req, res) {
  const user = await clerk.getAuthenticatedUser({
    res,
  });
  if (!user) {
    return res.status(401).json({ error: 'Unauthorized' });
  }

  // Your existing code here
}

Step 6: Testing the data table
To test the data table, create a new file named index.tsx in the pages directory of your project and add the following code:

import { useEffect, useState } from 'react';

export default function Home() {
  const [portfolios, setPortfolios] = useState([]);

  useEffect(() => {
    fetch('/api/portfolios')
      .then((response) => response.json())
      .then((data) => setPortfolios(data));
  }, []);

  return (
    <div>
      {portfolios.map((portfolio) => (
        <div key={portfolio.id}>
          <h2>{portfolio.title}</h2>
          <p>{portfolio.description}</p>
          <img src={portfolio.imageUrl} alt={portfolio.title} />
        </div>
      ))}
    </div>
  );
}

Run your project using the following command and navigate to http://localhost:3000 to see the data table for portfolios in action:

npm run dev

Congratulations! You have successfully created a data table for portfolios using NextJS14, Clerk, Prisma, and PostgreSQL. You can now start adding, updating, and deleting portfolios in your application. Happy coding!