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
1npx create-next-app nextjs-postgres
2cd 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
1CREATE 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
1postgresql://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:

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

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.

Copy
1npm install prisma --save-dev
2npx prisma init

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.

Copy
1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4}
5
6generator client {
7  provider = "prisma-client-js"
8}

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.

Copy
1model User {
2  id    Int    @id @default(autoincrement())
3  name  String
4  email String @unique
5}
6
7model Post {
8  id        Int      @id @default(autoincrement())
9  title     String
10  content   String
11  authorId  Int
12  author    User     @relation(fields: [authorId], references: [id])
13  createdAt DateTime @default(now())
14}

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.

Copy
1npx prisma migrate dev --name init

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.

Copy
1npx prisma migrate status

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.

Copy
1npx prisma generate

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.

Copy
1npx prisma studio

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.

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

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:

Copy
1module.exports = {
2  env: {
3    DATABASE_URL: process.env.DATABASE_URL,
4  },
5};

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.

Copy
1mkdir lib
2touch lib/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.

Copy
1// lib/prisma.js
2import { PrismaClient } from '@prisma/client';
3
4const prisma = new PrismaClient();
5export default prisma;

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:

Copy
1// pages/api/users.js
2import prisma from '../../lib/prisma';
3
4export default async function handle(req, res) {
5  const users = await prisma.user.findMany();
6  res.json(users);
7}

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.

Copy
1npm run dev

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.

Copy
1// pages/api/posts.js
2import prisma from '../../lib/prisma';
3
4export default async function handle(req, res) {
5  if (req.method === 'POST') {
6    const { title, content } = req.body;
7    const result = await prisma.post.create({
8      data: {
9        title,
10        content,
11      },
12    });
13    res.status(201).json(result);
14  } else {
15    res.status(405).json({ message: 'Method not allowed' });
16  }
17}

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.

Copy
1// pages/new-post.js
2import { useState } from 'react';
3import Router from 'next/router';
4
5const NewPost = () => {
6  const [title, setTitle] = useState('');
7  const [content, setContent] = useState('');
8
9  const submitData = async (e) => {
10    e.preventDefault();
11    try {
12      const body = { title, content };
13      await fetch('/api/posts', {
14        method: 'POST',
15        headers: { 'Content-Type': 'application/json' },
16        body: JSON.stringify(body),
17      });
18      Router.push('/');
19    } catch (error) {
20      console.error(error);
21    }
22  };
23
24  return (
25    <div>
26      <form onSubmit={submitData}>
27        <input
28          type="text"
29          placeholder="Title"
30          value={title}
31          onChange={(e) => setTitle(e.target.value)}
32          required
33        />
34        <textarea
35          placeholder="Content"
36          value={content}
37          onChange={(e) => setContent(e.target.value)}
38          required
39        />
40        <button type="submit">Create Post</button>
41      </form>
42    </div>
43  );
44};
45
46export default NewPost;

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.

Copy
1// pages/index.js
2import prisma from '../lib/prisma';
3
4export async function getServerSideProps() {
5  const posts = await prisma.post.findMany();
6  return {
7    props: { posts },
8  };
9}
10
11const Home = ({ posts }) => (
12  <div>
13    {posts.map((post) => (
14      <div key={post.id}>
15        <h2>{post.title}</h2>
16        <p>{post.content}</p>
17      </div>
18    ))}
19  </div>
20);
21
22export default Home;

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

Copy
1// pages/index.js
2import prisma from '../lib/prisma';
3
4export async function getStaticProps() {
5  const posts = await prisma.post.findMany();
6  return {
7    props: { posts },
8    revalidate: 10, // Revalidate every 10 seconds
9  };
10}
11
12const Home = ({ posts }) => (
13  <div>
14    {posts.map((post) => (
15      <div key={post.id}>
16        <h2>{post.title}</h2>
17        <p>{post.content}</p>
18      </div>
19    ))}
20  </div>
21);
22
23export default Home;

Updating Data

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

1. API Route for Updating Data:

Copy
1// pages/api/posts/[id].js
2import prisma from '../../../lib/prisma';
3
4export default async function handle(req, res) {
5  const postId = req.query.id;
6
7  if (req.method === 'PUT') {
8    const { title, content } = req.body;
9    const result = await prisma.post.update({
10      where: { id: Number(postId) },
11      data: { title, content },
12    });
13    res.status(200).json(result);
14  } else {
15    res.status(405).json({ message: 'Method not allowed' });
16  }
17}

2. Frontend Form to Update Data:

Copy
1// pages/edit-post.js
2import { useState } from 'react';
3import Router, { useRouter } from 'next/router';
4
5const EditPost = ({ post }) => {
6  const [title, setTitle] = useState(post.title);
7  const [content, setContent] = useState(post.content);
8  const router = useRouter();
9
10  const submitData = async (e) => {
11    e.preventDefault();
12    try {
13      const body = { title, content };
14      await fetch(`/api/posts/${post.id}`, {
15        method: 'PUT',
16        headers: { 'Content-Type': 'application/json' },
17        body: JSON.stringify(body),
18      });
19      Router.push('/');
20    } catch (error) {
21      console.error(error);
22    }
23  };
24
25  return (
26    <div>
27      <form onSubmit={submitData}>
28        <input
29          type="text"
30          placeholder="Title"
31          value={title}
32          onChange={(e) => setTitle(e.target.value)}
33          required
34        />
35        <textarea
36          placeholder="Content"
37          value={content}
38          onChange={(e) => setContent(e.target.value)}
39          required
40        />
41        <button type="submit">Update Post</button>
42      </form>
43    </div>
44  );
45};
46
47export async function getServerSideProps({ params }) {
48  const post = await prisma.post.findUnique({
49    where: { id: Number(params.id) },
50  });
51  return {
52    props: { post },
53  };
54}
55
56export default EditPost;

Deleting Data

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

1. API Route for Deleting Data:

Copy
1// pages/api/posts/[id].js
2import prisma from '../../../lib/prisma';
3
4export default async function handle(req, res) {
5  const postId = req.query.id;
6
7  if (req.method === 'DELETE') {
8    const result = await prisma.post.delete({
9      where: { id: Number(postId) },
10    });
11    res.status(200).json(result);
12  } else {
13    res.status(405).json({ message: 'Method not allowed' });
14  }
15}

2. Frontend Button to Delete Data:

Copy
1// components/PostList.js
2import { useRouter } from 'next/router';
3
4const PostList = ({ posts }) => {
5  const router = useRouter();
6
7  const deletePost = async (id) => {
8    try {
9      await fetch(`/api/posts/${id}`, {
10        method: 'DELETE',
11      });
12      router.reload();
13    } catch (error) {
14      console.error(error);
15    }
16  };
17
18  return (
19    <div>
20      {posts.map((post) => (
21        <div key={post.id}>
22          <h2>{post.title}</h2>
23          <p>{post.content}</p>
24          <button onClick={() => deletePost(post.id)}>Delete</button>
25        </div>
26      ))}
27    </div>
28  );
29};
30
31export default PostList;

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.

Copy
1// pages/index.js
2import prisma from '../lib/prisma';
3
4export async function getServerSideProps() {
5  const posts = await prisma.post.findMany();
6  return {
7    props: { posts },
8  };
9}
10
11const Home = ({ posts }) => (
12  <div>
13    <h1>Posts</h1>
14    {posts.map((post) => (
15      <div key={post.id}>
16        <h2>{post.title}</h2>
17        <p>{post.content}</p>
18        <a href={`/posts/${post.id}`}>Read more</a>
19      </div>
20    ))}
21  </div>
22);
23
24export default Home;

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

Copy
1// pages/posts/[id].js
2import prisma from '../../lib/prisma';
3
4export async function getServerSideProps({ params }) {
5  const post = await prisma.post.findUnique({
6    where: { id: Number(params.id) },
7  });
8  return {
9    props: { post },
10  };
11}
12
13const Post = ({ post }) => (
14  <div>
15    <h1>{post.title}</h1>
16    <p>{post.content}</p>
17  </div>
18);
19
20export default Post;

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

Copy
1// pages/new-post.js
2import { useState } from 'react';
3import Router from 'next/router';
4
5const NewPost = () => {
6  const [title, setTitle] = useState('');
7  const [content, setContent] = useState('');
8
9  const submitData = async (e) => {
10    e.preventDefault();
11    try {
12      const body = { title, content };
13      await fetch('/api/posts', {
14        method: 'POST',
15        headers: { 'Content-Type': 'application/json' },
16        body: JSON.stringify(body),
17      });
18      Router.push('/');
19    } catch (error) {
20      console.error(error);
21    }
22  };
23
24  return (
25    <div>
26      <form onSubmit={submitData}>
27        <input
28          type="text"
29          placeholder="Title"
30          value={title}
31          onChange={(e) => setTitle(e.target.value)}
32          required
33        />
34        <textarea
35          placeholder="Content"
36          value={content}
37          onChange={(e) => setContent(e.target.value)}
38          required
39        />
40        <button type="submit">Create Post</button>
41      </form>
42    </div>
43  );
44};
45
46export default NewPost;

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.

Copy
1// pages/api/posts/index.js
2import prisma from '../../../lib/prisma';
3
4export default async function handle(req, res) {
5  if (req.method === 'POST') {
6    const { title, content } = req.body;
7    const result = await prisma.post.create({
8      data: {
9        title,
10        content,
11      },
12    });
13    res.status(201).json(result);
14  } else if (req.method === 'GET') {
15    const posts = await prisma.post.findMany();
16    res.json(posts);
17  } else {
18    res.status(405).json({ message: 'Method not allowed' });
19  }
20}

2. API Route for Individual Post Operations:

Copy
1// pages/api/posts/[id].js
2import prisma from '../../../lib/prisma';
3
4export default async function handle(req, res) {
5  const postId = req.query.id;
6
7  if (req.method === 'PUT') {
8    const { title, content } = req.body;
9    const result = await prisma.post.update({
10      where: { id: Number(postId) },
11      data: { title, content },
12    });
13    res.status(200).json(result);
14  } else if (req.method === 'DELETE') {
15    const result = await prisma.post.delete({
16      where: { id: Number(postId) },
17    });
18    res.status(200).json(result);
19  } else if (req.method === 'GET') {
20    const post = await prisma.post.findUnique({
21      where: { id: Number(postId) },
22    });
23    res.json(post);
24  } else {
25    res.status(405).json({ message: 'Method not allowed' });
26  }
27}

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.

Copy
1/* styles/global.css */
2body {
3  font-family: Arial, sans-serif;
4  margin: 0;
5  padding: 0;
6  background-color: #f0f0f0;
7}
8
9h1, h2 {
10  color: #333;
11}
12
13a {
14  color: #0070f3;
15  text-decoration: none;
16}
17
18a:hover {
19  text-decoration: underline;
20}

Copy
1// pages/_app.js
2import '../styles/global.css';
3
4function MyApp({ Component, pageProps }) {
5  return <Component {...pageProps} />;
6}
7
8export default MyApp;

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

Copy
1/* styles/Post.module.css */
2.post {
3  background: white;
4  margin: 20px 0;
5  padding: 20px;
6  border-radius: 8px;
7  box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
8}
9
10.title {
11  margin: 0;
12  font-size: 24px;
13}
14
15.content {
16  margin: 10px 0 0;
17}

Copy
1// components/Post.js
2import styles from '../styles/Post.module.css';
3
4const Post = ({ title, content }) => (
5  <div className={styles.post}>
6    <h2 className={styles.title}>{title}</h2>
7    <p className={styles.content}>{content}</p>
8  </div>
9);
10
11export default Post;

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

Copy
1// pages/index.js
2import Link from 'next/link';
3import Post from '../components/Post';
4
5const Home = ({ posts }) => (
6  <div>
7    <h1>Posts</h1>
8    {posts.map((post) => (
9      <Post key={post.id} title={post.title} content={post.content} />
10    ))}
11    <Link href="/new-post">
12      <a>Create a New Post</a>
13    </Link>
14  </div>
15);
16
17export default Home;

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.

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

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.

Copy
1module.exports = {
2  env: {
3    DATABASE_URL: process.env.DATABASE_URL,
4  },
5};

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.

Copy
1npm install -g vercel

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

Copy
1vercel login

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

Copy
1vercel

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.

Copy
1vercel --prod

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.