Building a Full-Stack App with Next.js Postgres and Prisma

This blog will guide you through building a full-fledged application using Next.js PostgreSQL. By leveraging the powerful ORM, Prisma, you can streamline your database operations and enhance your development workflow.

We will cover everything from setting up your project to deploying it on Vercel. To get started, we will have to install Prisma Client to interact with your database. Additionally, you will initialize Prisma by installing necessary libraries, creating a new directory with a schema file, and managing environmental variables and database connections.

Prerequisites

Before diving in, ensure you have a basic understanding of Next.js and PostgreSQL. Additionally, you will need to have the following tools and accounts:

• Node.js and npm installed on your machine

• A Vercel account for deployment

• A PostgreSQL database ready for connection

• Familiarity with JavaScript and some experience with SQL

Setting Up the Project

Initialize a New Next.js Project

To get started, we need to set up a new Next.js project. This will serve as the foundation for our application. Follow these steps:

1. Create a New Next.js Project: Use the create-next-app command to initialize a new Next.js project. This command sets up the project directory with all the necessary files and dependencies.

Copy
npx create-next-app nextjs-postgres
cd nextjs-postgres

2. Project Directory Structure: Once the command completes, you'll have a project directory structured for a Next.js application, ready for customization and development.

Setting Up PostgreSQL

Next, we need to set up a PostgreSQL database, which will be used to store and manage our application data. Follow these steps:

1. Create a New PostgreSQL Database: You can create a PostgreSQL database locally or use a cloud service like Vercel Postgres. For local setup, ensure you have PostgreSQL installed and running. Create a new database using the following command:

Copy
CREATE DATABASE mydatabase;

2. Set Up Vercel PostgreSQL Database: For production or easier management, you might prefer using Vercel's PostgreSQL database service. Sign in to your Vercel account and set up a new PostgreSQL database in the storage tab.

3. Database Connection URL: Obtain the database connection URL. This URL will be used to connect your Next.js application to the PostgreSQL database. The connection string typically looks like this:

Copy
postgresql://user:password@localhost:5432/mydatabase

4. Configure Environment Variables: Create an .env file in your project root directory to store your database connection URL securely. Add the following line to your .env file:

5. Verify Connection: Ensure your Next.js application can connect to the PostgreSQL database by testing the connection. This can be done later when setting up Prisma.

Prisma Setup

Installing Prisma

To integrate Prisma with your Next.js project, follow these steps:

1. Install Prisma: Start by installing Prisma as a development dependency. This will also install the Prisma CLI.

The npx prisma init command creates a new prisma directory in your project, containing a prisma schema file. This file is where you'll define your database schema.

Configuring Prisma

Now that Prisma is installed, we need to configure it to connect to our PostgreSQL database and set up the necessary schema. ****Prisma allows you to execute a database query to interact with your database efficiently.

1. Set Up the prisma.schema File: Open the prisma/schema.prisma file created during the installation process. This is where you'll define your database models and connection settings.

2. Configure the Database Connection: In the prisma.schema file, configure the datasource db block to use the database connection url from your .env file. This URL tells Prisma how to connect to your PostgreSQL database.

Defining the Database Schema

Creating Models

Defining your database schema with Prisma involves creating models that represent the structure of your data. These models are defined in the prisma.schema file. Follow these steps to create your models:

1. Open the prisma.schema File: Navigate to the prisma directory and open the schema.prisma file.

2. Define Your Models: In the schema.prisma file, define the models that correspond to the tables in your PostgreSQL database. For example, you might create a User model to represent users in your application.

In this example:

• The User model has fields for id, name, and email, with id set as the primary key and email marked as unique.

• The Post model includes fields for id, title, content, authorId, and a relation to the User model.

3. Set Default Values and Constraints: You can specify default values and constraints for your fields, such as @default(autoincrement()) for auto-incrementing IDs and @unique for unique fields.

Applying Database Migrations

After defining your models, the next step is to apply these changes to your PostgreSQL database by running migrations. Migrations are used to update the database schema in a controlled and repeatable way.

1. Create Migration Files: Use Prisma CLI to create migration files based on the models you defined in the schema.prisma file.

This command creates a new migration file with the name init and applies it to your database. The migrate dev command is used for local development and includes a prompt to reset the database if needed.

2. Check the Migration Status: Verify that the migration was applied successfully by checking the status of your database.

This command shows the current state of your migrations and ensures that your database schema is up to date.

3. Generate Prisma Client: After applying migrations, generate the Prisma client to reflect the changes in your database schema.

This command regenerates the Prisma client in the node_modules directory, enabling you to use it to interact with your updated database schema.

4. Verify the Changes: You can use Prisma Studio, a visual editor for your database, to verify the changes and inspect your data.

This command opens Prisma Studio in your browser, allowing you to view and manage your database content easily.

Following these steps, you have successfully defined your database schema with Prisma, created the necessary models, and applied migrations to update your PostgreSQL database. This sets the foundation for building and interacting with your data in your Next.js application.

Connecting to the Database

Setting Up Environment Variables

To securely connect your Next.js application to your PostgreSQL database, you need to configure environment variables. This setup ensures that sensitive information, such as your database connection URL, is not hard-coded in your application.

1. Create an .env File: In the root directory of your project, create an .env file. This file will store your environment variables.

Replace user, password, localhost, and mydatabase with your actual PostgreSQL credentials and database name.

2. Update next.config.js: Ensure that Next.js is configured to load these environment variables. In your next.config.js file, add the following configuration:

This ensures that your application can access the DATABASE_URL during runtime.

Initializing Prisma Client

With your environment variables set up, you can now initialize the Prisma client to connect to your PostgreSQL database. The Prisma client will allow you to interact with your database through your Next.js application.

1. Create the lib/prisma.js File: In your project, create a new directory called lib (if it doesn't already exist). Inside the lib directory, create a file named prisma.js.

2. Initialize and Export the Prisma Client: In lib/prisma.js, initialize the Prisma client and export it for use throughout your project.

This code imports the PrismaClient class from the @prisma/client package, creates an instance of the Prisma client, and exports it. This client will be used to perform database operations.

3. Using Prisma Client in API Routes: Now you can use the Prisma client in your Next.js API routes or any other part of your application. For example, create an API route to fetch all users from the database:

This API route uses the Prisma client to fetch all records from the User model and returns them as a JSON response.

4. Testing the Connection: Start your Next.js development server to test the database connection and ensure everything is working correctly.

Open your browser and navigate to http://localhost:3000/api/users to see the list of users fetched from your PostgreSQL database.

With these steps, you have successfully set up environment variables to securely store your database connection URL and initialized the Prisma client to connect to your PostgreSQL database. This setup allows you to perform database operations seamlessly in your Next.js application.

Implementing CRUD Operations

Creating Data

To create new records in your PostgreSQL database using Prisma and Next.js, follow these steps:

1. Create an API Route for Creating Data: Define an API route in your Next.js application to handle POST requests for creating new data. For instance, create a new post.

This code sets up an API route that handles POST requests to create a new post. It uses the Prisma client to insert the new record into the database.

2. Frontend Form to Create Data: Create a form in your frontend to collect data and send a POST request to the API route.

Fetching Data

To retrieve data from your PostgreSQL database, you can use either server-side rendering (SSR) or static site generation (SSG). Here’s how to fetch and display data:

1. Fetch Data Using SSR: Use getServerSideProps to fetch data on each request.

2. Fetch Data Using SSG: Use getStaticProps to fetch data at build time.

Updating Data

To update existing records, create an API route to handle PUT requests.

1. API Route for Updating Data:

2. Frontend Form to Update Data:

Deleting Data

To delete records, create an API route to handle DELETE requests.

1. API Route for Deleting Data:

2. Frontend Button to Delete Data:

Ensure the PostList component is used in a page where you fetch and pass down the posts, similar to the Home component in the Fetching Data section.

Building the Frontend

Creating Pages and Components

To build a functional and interactive frontend, you need to create various pages and components that will interact with your API routes and display data fetched from your PostgreSQL database.

1. Home Page: Create a homepage to list all posts.

2. Post Page: Create a dynamic page to display individual post details.

3. New Post Page: Create a page to submit a new post.

Using API Routes

API routes are essential for handling CRUD operations in your Next.js application. Here’s how to set them up:

1. Create API Route for Posts: Set up an API route to handle CRUD operations for posts.

2. API Route for Individual Post Operations:

Styling the Application

Styling your application enhances the user experience and makes your site visually appealing. You can use CSS, CSS-in-JS, or any styling framework like Tailwind CSS.

1. Add a Global CSS File: Create a styles/global.css file and import it in _app.js.

2. Styling Components: Apply styles directly to components or use CSS modules.

3. Linking CSS Files: Ensure that your CSS files are linked correctly in your components or pages.

Deployment

Preparing for Deployment

Before deploying your Next.js application, you need to ensure that everything is configured correctly for production.

1. Environment Variables: Ensure that your environment variables are set up for production. In your .env file, add the production DATABASE_URL. When deploying to Vercel, you can add these variables through the Vercel dashboard.

In the Vercel dashboard:

• Go to your project.

• Click on the "Settings" tab.

• Navigate to "Environment Variables".

• Add your DATABASE_URL variable.

2. Optimize Your Application: Ensure your application is optimized for production by checking for any development dependencies that shouldn't be included in the production build. Also, verify that your API routes and pages are working correctly.

3. Update next.config.js: Make sure your next.config.js file is set up correctly for production. This includes ensuring any environment variables are correctly referenced.

Deploying to Vercel

Deploying your Next.js application to Vercel is straightforward. Follow these steps:

1. Create a Vercel Account: If you don't already have a Vercel account, sign up at Vercel .

2. Install Vercel CLI: Install the Vercel CLI globally on your machine. This allows you to deploy your application from the command line.

3. Login to Vercel: Log in to your Vercel account using the CLI.

4. Deploy Your Application: In the root directory of your Next.js project, run the following command to deploy your application.

This command will prompt you to link your project to a Vercel project. Follow the prompts to complete the deployment.

5. Set Environment Variables on Vercel: After deploying, go to your project dashboard on Vercel. Navigate to the "Settings" tab, then "Environment Variables". Add your DATABASE_URL and any other environment variables your application requires.

6. Re-deploy If Necessary: If you make any changes to your environment variables or application, you may need to re-deploy. You can do this easily with the Vercel CLI.

This command deploys your application to the production environment.

7. Verify Deployment: Once your deployment is complete, verify that your application is running correctly. Navigate to your Vercel project URL to see your live application.

With these steps, you will successfully deploy your Next.js application with a PostgreSQL backend to Vercel. This setup ensures that your application is ready for production use, with environment variables securely managed and optimized for performance.

Conclusion

In this blog, we explored how to build a full-fledged application using Next.js PostgreSQL, leveraging Prisma for seamless database management. We covered the initial setup of the project, configured Prisma, defined and migrated our database schema, implemented CRUD operations, and built a functional frontend.

Finally, we prepared and deployed our application to Vercel. By following these steps, you now have a robust framework to develop and deploy scalable web applications using modern technologies.

DATABASE_URL=postgresql://user:password@localhost:5432/mydatabase

npm install prisma --save-dev
npx prisma init

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

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

model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

npx prisma migrate dev --name init

npx prisma migrate status

npx prisma generate

npx prisma studio

DATABASE_URL=postgresql://user:password@localhost:5432/mydatabase

module.exports = {
  env: {
    DATABASE_URL: process.env.DATABASE_URL,
  },
};

mkdir lib
touch lib/prisma.js

// lib/prisma.js
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();
export default prisma;

// pages/api/users.js
import prisma from '../../lib/prisma';

export default async function handle(req, res) {
  const users = await prisma.user.findMany();
  res.json(users);
}

npm run dev

// pages/api/posts.js
import prisma from '../../lib/prisma';

export default async function handle(req, res) {
  if (req.method === 'POST') {
    const { title, content } = req.body;
    const result = await prisma.post.create({
      data: {
        title,
        content,
      },
    });
    res.status(201).json(result);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// pages/new-post.js
import { useState } from 'react';
import Router from 'next/router';

const NewPost = () => {
  const [title, setTitle] = useState('');
  const [content, setContent] = useState('');

  const submitData = async (e) => {
    e.preventDefault();
    try {
      const body = { title, content };
      await fetch('/api/posts', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(body),
      });
      Router.push('/');
    } catch (error) {
      console.error(error);
    }
  };

  return (
    <div>
      <form onSubmit={submitData}>
        <input
          type="text"
          placeholder="Title"
          value={title}
          onChange={(e) => setTitle(e.target.value)}
          required
        />
        <textarea
          placeholder="Content"
          value={content}
          onChange={(e) => setContent(e.target.value)}
          required
        />
        <button type="submit">Create Post</button>
      </form>
    </div>
  );
};

export default NewPost;

// pages/index.js
import prisma from '../lib/prisma';

export async function getServerSideProps() {
  const posts = await prisma.post.findMany();
  return {
    props: { posts },
  };
}

const Home = ({ posts }) => (
  <div>
    {posts.map((post) => (
      <div key={post.id}>
        <h2>{post.title}</h2>
        <p>{post.content}</p>
      </div>
    ))}
  </div>
);

export default Home;

// pages/index.js
import prisma from '../lib/prisma';

export async function getStaticProps() {
  const posts = await prisma.post.findMany();
  return {
    props: { posts },
    revalidate: 10, // Revalidate every 10 seconds
  };
}

const Home = ({ posts }) => (
  <div>
    {posts.map((post) => (
      <div key={post.id}>
        <h2>{post.title}</h2>
        <p>{post.content}</p>
      </div>
    ))}
  </div>
);

export default Home;

// pages/api/posts/[id].js
import prisma from '../../../lib/prisma';

export default async function handle(req, res) {
  const postId = req.query.id;

  if (req.method === 'PUT') {
    const { title, content } = req.body;
    const result = await prisma.post.update({
      where: { id: Number(postId) },
      data: { title, content },
    });
    res.status(200).json(result);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// pages/edit-post.js
import { useState } from 'react';
import Router, { useRouter } from 'next/router';

const EditPost = ({ post }) => {
  const [title, setTitle] = useState(post.title);
  const [content, setContent] = useState(post.content);
  const router = useRouter();

  const submitData = async (e) => {
    e.preventDefault();
    try {
      const body = { title, content };
      await fetch(`/api/posts/${post.id}`, {
        method: 'PUT',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(body),
      });
      Router.push('/');
    } catch (error) {
      console.error(error);
    }
  };

  return (
    <div>
      <form onSubmit={submitData}>
        <input
          type="text"
          placeholder="Title"
          value={title}
          onChange={(e) => setTitle(e.target.value)}
          required
        />
        <textarea
          placeholder="Content"
          value={content}
          onChange={(e) => setContent(e.target.value)}
          required
        />
        <button type="submit">Update Post</button>
      </form>
    </div>
  );
};

export async function getServerSideProps({ params }) {
  const post = await prisma.post.findUnique({
    where: { id: Number(params.id) },
  });
  return {
    props: { post },
  };
}

export default EditPost;

// pages/api/posts/[id].js
import prisma from '../../../lib/prisma';

export default async function handle(req, res) {
  const postId = req.query.id;

  if (req.method === 'DELETE') {
    const result = await prisma.post.delete({
      where: { id: Number(postId) },
    });
    res.status(200).json(result);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// components/PostList.js
import { useRouter } from 'next/router';

const PostList = ({ posts }) => {
  const router = useRouter();

  const deletePost = async (id) => {
    try {
      await fetch(`/api/posts/${id}`, {
        method: 'DELETE',
      });
      router.reload();
    } catch (error) {
      console.error(error);
    }
  };

  return (
    <div>
      {posts.map((post) => (
        <div key={post.id}>
          <h2>{post.title}</h2>
          <p>{post.content}</p>
          <button onClick={() => deletePost(post.id)}>Delete</button>
        </div>
      ))}
    </div>
  );
};

export default PostList;

// pages/index.js
import prisma from '../lib/prisma';

export async function getServerSideProps() {
  const posts = await prisma.post.findMany();
  return {
    props: { posts },
  };
}

const Home = ({ posts }) => (
  <div>
    <h1>Posts</h1>
    {posts.map((post) => (
      <div key={post.id}>
        <h2>{post.title}</h2>
        <p>{post.content}</p>
        <a href={`/posts/${post.id}`}>Read more</a>
      </div>
    ))}
  </div>
);

export default Home;

// pages/posts/[id].js
import prisma from '../../lib/prisma';

export async function getServerSideProps({ params }) {
  const post = await prisma.post.findUnique({
    where: { id: Number(params.id) },
  });
  return {
    props: { post },
  };
}

const Post = ({ post }) => (
  <div>
    <h1>{post.title}</h1>
    <p>{post.content}</p>
  </div>
);

export default Post;

// pages/new-post.js
import { useState } from 'react';
import Router from 'next/router';

const NewPost = () => {
  const [title, setTitle] = useState('');
  const [content, setContent] = useState('');

  const submitData = async (e) => {
    e.preventDefault();
    try {
      const body = { title, content };
      await fetch('/api/posts', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(body),
      });
      Router.push('/');
    } catch (error) {
      console.error(error);
    }
  };

  return (
    <div>
      <form onSubmit={submitData}>
        <input
          type="text"
          placeholder="Title"
          value={title}
          onChange={(e) => setTitle(e.target.value)}
          required
        />
        <textarea
          placeholder="Content"
          value={content}
          onChange={(e) => setContent(e.target.value)}
          required
        />
        <button type="submit">Create Post</button>
      </form>
    </div>
  );
};

export default NewPost;

// pages/api/posts/index.js
import prisma from '../../../lib/prisma';

export default async function handle(req, res) {
  if (req.method === 'POST') {
    const { title, content } = req.body;
    const result = await prisma.post.create({
      data: {
        title,
        content,
      },
    });
    res.status(201).json(result);
  } else if (req.method === 'GET') {
    const posts = await prisma.post.findMany();
    res.json(posts);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// pages/api/posts/[id].js
import prisma from '../../../lib/prisma';

export default async function handle(req, res) {
  const postId = req.query.id;

  if (req.method === 'PUT') {
    const { title, content } = req.body;
    const result = await prisma.post.update({
      where: { id: Number(postId) },
      data: { title, content },
    });
    res.status(200).json(result);
  } else if (req.method === 'DELETE') {
    const result = await prisma.post.delete({
      where: { id: Number(postId) },
    });
    res.status(200).json(result);
  } else if (req.method === 'GET') {
    const post = await prisma.post.findUnique({
      where: { id: Number(postId) },
    });
    res.json(post);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

/* styles/global.css */
body {
  font-family: Arial, sans-serif;
  margin: 0;
  padding: 0;
  background-color: #f0f0f0;
}

h1, h2 {
  color: #333;
}

a {
  color: #0070f3;
  text-decoration: none;
}

a:hover {
  text-decoration: underline;
}

// pages/_app.js
import '../styles/global.css';

function MyApp({ Component, pageProps }) {
  return <Component {...pageProps} />;
}

export default MyApp;

/* styles/Post.module.css */
.post {
  background: white;
  margin: 20px 0;
  padding: 20px;
  border-radius: 8px;
  box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
}

.title {
  margin: 0;
  font-size: 24px;
}

.content {
  margin: 10px 0 0;
}

// components/Post.js
import styles from '../styles/Post.module.css';

const Post = ({ title, content }) => (
  <div className={styles.post}>
    <h2 className={styles.title}>{title}</h2>
    <p className={styles.content}>{content}</p>
  </div>
);

export default Post;

// pages/index.js
import Link from 'next/link';
import Post from '../components/Post';

const Home = ({ posts }) => (
  <div>
    <h1>Posts</h1>
    {posts.map((post) => (
      <Post key={post.id} title={post.title} content={post.content} />
    ))}
    <Link href="/new-post">
      <a>Create a New Post</a>
    </Link>
  </div>
);

export default Home;

DATABASE_URL=postgresql://user:password@production_host:5432/production_database

module.exports = {
  env: {
    DATABASE_URL: process.env.DATABASE_URL,
  },
};

npm install -g vercel

vercel login

vercel

vercel --prod