Skip to main content

Next.js PostgreSQL Integration

PostgreSQL is a powerful open-source relational database system that works wonderfully with Next.js applications. In this comprehensive guide, we'll explore different approaches to integrate PostgreSQL with your Next.js projects, from direct connections to using ORMs and handling database operations within API routes.

Introduction to Next.js and PostgreSQL​

Next.js is a React framework that enables server-side rendering, static site generation, and API routes. PostgreSQL (often called "Postgres") is a robust relational database known for its reliability, feature robustness, and performance.

Combining these technologies allows you to build full-stack applications with:

  • Server-side rendered React components
  • Persistent data storage
  • Powerful querying capabilities
  • Type safety (when using TypeScript)

Prerequisites​

Before we begin, make sure you have:

  • Node.js and npm installed
  • Basic knowledge of Next.js
  • PostgreSQL installed locally or access to a PostgreSQL database
  • Basic understanding of SQL

Setting Up PostgreSQL for a Next.js Project​

1. Installing PostgreSQL​

If you haven't already installed PostgreSQL, you can download it from the official website or use Docker:

bash
# Using Docker to run PostgreSQL
docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

2. Creating a Database​

After installation, create a database for your Next.js application:

sql
CREATE DATABASE nextjs_app;

3. Setting Up a Basic Next.js Project​

Create a new Next.js project if you don't have one:

bash
npx create-next-app@latest my-postgres-app
cd my-postgres-app

Approach 1: Direct Connection Using node-postgres​

The pg package allows you to connect directly to PostgreSQL from your Next.js application.

Installation​

bash
npm install pg

Creating a Database Connection Utility​

Create a file at lib/db.js:

javascript
const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};

Setting Environment Variables​

Create a .env.local file in your project root:

DB_USER=postgres
DB_HOST=localhost
DB_NAME=nextjs_app
DB_PASSWORD=mysecretpassword
DB_PORT=5432

Make sure to add this file to your .gitignore to avoid exposing your credentials.

Creating an API Route​

Create a new file at pages/api/users.js:

javascript
import db from '../../lib/db';

export default async function handler(req, res) {
  try {
    const { rows } = await db.query('SELECT * FROM users');
    res.status(200).json(rows);
  } catch (error) {
    console.error('Error fetching users:', error);
    res.status(500).json({ error: 'Error fetching users' });
  }
}

Approach 2: Using an ORM (Prisma)​

Object-Relational Mappers (ORMs) provide a more structured approach to database operations. Prisma is a popular choice with Next.js.

Installation​

bash
npm install prisma @prisma/client
npx prisma init

Configuring Prisma​

The previous command creates a prisma directory with a schema.prisma file. Update it:

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Update your .env file:

DATABASE_URL="postgresql://postgres:mysecretpassword@localhost:5432/nextjs_app?schema=public"

Creating Database Tables​

Run the following command to create your database tables:

bash
npx prisma migrate dev --name init

Setting Up a Prisma Client​

Create a file at lib/prisma.js:

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

let prisma;

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

export default prisma;

Using Prisma in an API Route​

Create a file at pages/api/users/index.js:

javascript
import prisma from '../../../lib/prisma';

export default async function handler(req, res) {
  if (req.method === 'GET') {
    try {
      const users = await prisma.user.findMany();
      res.status(200).json(users);
    } catch (error) {
      console.error('Error fetching users:', error);
      res.status(500).json({ error: 'Error fetching users' });
    }
  } else if (req.method === 'POST') {
    try {
      const { name, email } = req.body;
      const user = await prisma.user.create({
        data: {
          name,
          email,
        },
      });
      res.status(201).json(user);
    } catch (error) {
      console.error('Error creating user:', error);
      res.status(500).json({ error: 'Error creating user' });
    }
  } else {
    res.status(405).json({ error: 'Method not allowed' });
  }
}

Creating a GET by ID Route​

Create a file at pages/api/users/[id].js:

javascript
import prisma from '../../../lib/prisma';

export default async function handler(req, res) {
  const { id } = req.query;
  const userId = parseInt(id);

  if (req.method === 'GET') {
    try {
      const user = await prisma.user.findUnique({
        where: { id: userId },
      });
      
      if (!user) {
        return res.status(404).json({ error: 'User not found' });
      }
      
      return res.status(200).json(user);
    } catch (error) {
      console.error('Error fetching user:', error);
      return res.status(500).json({ error: 'Error fetching user' });
    }
  } else {
    return res.status(405).json({ error: 'Method not allowed' });
  }
}

Using PostgreSQL Data in Your Components​

Server-Side Rendering with PostgreSQL Data​

Create a page at pages/users.js:

jsx
import prisma from '../lib/prisma';

export default function Users({ users }) {
  return (
    <div>
      <h1>Users</h1>
      <ul>
        {users.map((user) => (
          <li key={user.id}>
            {user.name} ({user.email})
          </li>
        ))}
      </ul>
    </div>
  );
}

export async function getServerSideProps() {
  const users = await prisma.user.findMany();
  
  // Convert dates to strings to be serializable
  const serializedUsers = users.map(user => ({
    ...user,
    createdAt: user.createdAt.toISOString(),
    updatedAt: user.updatedAt.toISOString()
  }));
  
  return {
    props: { users: serializedUsers },
  };
}

Static Site Generation with PostgreSQL Data​

For data that doesn't change frequently, you can use Static Site Generation:

jsx
import prisma from '../lib/prisma';
import Link from 'next/link';

export default function UsersList({ users }) {
  return (
    <div>
      <h1>All Users</h1>
      <ul>
        {users.map((user) => (
          <li key={user.id}>
            <Link href={`/users/${user.id}`}>
              <a>{user.name}</a>
            </Link>
          </li>
        ))}
      </ul>
    </div>
  );
}

export async function getStaticProps() {
  const users = await prisma.user.findMany();
  
  const serializedUsers = users.map(user => ({
    ...user,
    createdAt: user.createdAt.toISOString(),
    updatedAt: user.updatedAt.toISOString()
  }));
  
  return {
    props: { users: serializedUsers },
    revalidate: 60, // Regenerate page every 60 seconds
  };
}

Real-world Example: User Authentication System​

Let's build a simple authentication system using Next.js and PostgreSQL:

1. Extend the User Model​

Update your schema.prisma:

prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Run migration:

bash
npx prisma migrate dev --name add_password

2. Install bcrypt for Password Hashing​

bash
npm install bcrypt

3. Create a Registration API​

Create pages/api/auth/register.js:

javascript
import prisma from '../../../lib/prisma';
import bcrypt from 'bcrypt';

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  const { name, email, password } = req.body;

  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' });
  }

  try {
    // Check if user already exists
    const existingUser = await prisma.user.findUnique({
      where: {
        email,
      },
    });

    if (existingUser) {
      return res.status(400).json({ error: 'User already exists' });
    }

    // Hash the password
    const hashedPassword = await bcrypt.hash(password, 10);

    // Create the user
    const user = await prisma.user.create({
      data: {
        name,
        email,
        password: hashedPassword,
      },
    });

    // Return the user without the password
    const { password: _, ...userWithoutPassword } = user;
    return res.status(201).json(userWithoutPassword);
  } catch (error) {
    console.error('Error registering user:', error);
    return res.status(500).json({ error: 'Error registering user' });
  }
}

4. Create a Login API​

Create pages/api/auth/login.js:

javascript
import prisma from '../../../lib/prisma';
import bcrypt from 'bcrypt';

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  const { email, password } = req.body;

  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' });
  }

  try {
    // Find the user
    const user = await prisma.user.findUnique({
      where: {
        email,
      },
    });

    if (!user) {
      return res.status(400).json({ error: 'Invalid email or password' });
    }

    // Check if password matches
    const passwordMatch = await bcrypt.compare(password, user.password);

    if (!passwordMatch) {
      return res.status(400).json({ error: 'Invalid email or password' });
    }

    // Return the user without the password
    const { password: _, ...userWithoutPassword } = user;
    return res.status(200).json({
      message: 'Login successful',
      user: userWithoutPassword,
    });
  } catch (error) {
    console.error('Error logging in:', error);
    return res.status(500).json({ error: 'Error logging in' });
  }
}

5. Create Registration Form​

Create a component at components/RegisterForm.js:

jsx
import { useState } from 'react';
import { useRouter } from 'next/router';

export default function RegisterForm() {
  const [name, setName] = useState('');
  const [email, setEmail] = useState('');
  const [password, setPassword] = useState('');
  const [error, setError] = useState('');
  const router = useRouter();

  const handleSubmit = async (e) => {
    e.preventDefault();
    setError('');
    
    try {
      const res = await fetch('/api/auth/register', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({
          name,
          email,
          password,
        }),
      });
      
      const data = await res.json();
      
      if (!res.ok) {
        throw new Error(data.error || 'Something went wrong');
      }
      
      router.push('/login');
    } catch (error) {
      setError(error.message);
    }
  };

  return (
    <form onSubmit={handleSubmit}>
      {error && <p className="error">{error}</p>}
      <div>
        <label htmlFor="name">Name</label>
        <input
          id="name"
          type="text"
          value={name}
          onChange={(e) => setName(e.target.value)}
        />
      </div>
      <div>
        <label htmlFor="email">Email</label>
        <input
          id="email"
          type="email"
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          required
        />
      </div>
      <div>
        <label htmlFor="password">Password</label>
        <input
          id="password"
          type="password"
          value={password}
          onChange={(e) => setPassword(e.target.value)}
          required
        />
      </div>
      <button type="submit">Register</button>
      <style jsx>{`
        .error {
          color: red;
        }
      `}</style>
    </form>
  );
}

Best Practices for PostgreSQL in Next.js​

1. Connection Pooling​

For production applications, implement connection pooling to efficiently manage database connections:

javascript
// In lib/db.js with pg
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Maximum number of clients in the pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

2. Environment-specific Configuration​

Use different database configurations for development, testing, and production:

javascript
// In .env.development
DATABASE_URL="postgresql://postgres:password@localhost:5432/next_dev"

// In .env.production
DATABASE_URL="postgresql://postgres:password@production-server:5432/next_prod"

3. Using Prepared Statements​

Always use prepared statements to prevent SQL injection:

javascript
// Bad (vulnerable to SQL injection)
await db.query(`SELECT * FROM users WHERE email = '${email}'`);

// Good (using parameterized query)
await db.query('SELECT * FROM users WHERE email = $1', [email]);

4. Error Handling​

Implement comprehensive error handling:

javascript
try {
  const result = await db.query('SELECT * FROM users');
  return result.rows;
} catch (error) {
  console.error('Database error:', error);
  // Log error to monitoring service
  throw new Error('Failed to fetch users');
}

5. Transactions for Multiple Operations​

Use transactions when performing multiple related database operations:

javascript
// Using node-postgres
const client = await pool.connect();
try {
  await client.query('BEGIN');
  
  await client.query('INSERT INTO orders (user_id, total) VALUES ($1, $2)', [userId, total]);
  await client.query('UPDATE users SET credits = credits - $1 WHERE id = $2', [total, userId]);
  
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

// Using Prisma
await prisma.$transaction([
  prisma.order.create({ data: { userId, total } }),
  prisma.user.update({ 
    where: { id: userId }, 
    data: { credits: { decrement: total } } 
  })
]);

Deployment Considerations​

1. Database Connection Limits​

When deploying to serverless environments, be aware of connection limits:

  • Use connection pooling services like PgBouncer or AWS RDS Proxy
  • Implement proper connection management

2. Environment Variables​

Ensure your deployment platform has access to database credentials as environment variables.

3. Database Location​

Place your database near your application servers to reduce latency.

Summary​

In this guide, we've covered:

  1. Setting up PostgreSQL for Next.js projects
  2. Direct database connection using node-postgres
  3. Using Prisma ORM for simplified database operations
  4. Building API routes for database operations
  5. Server-side rendering and static site generation with database data
  6. A real-world authentication example
  7. Best practices for production applications

PostgreSQL offers a robust solution for Next.js applications that require relational data modeling, complex queries, and ACID-compliant transactions. By following the practices outlined in this guide, you can build scalable and maintainable full-stack applications with Next.js and PostgreSQL.

Additional Resources​

Exercises​

  1. Create a blog application with posts and comments stored in PostgreSQL
  2. Add pagination to a list of database records
  3. Implement a search function that queries the PostgreSQL database
  4. Build a dashboard that displays aggregated data using PostgreSQL's GROUP BY
  5. Extend the authentication system with role-based authorization

Happy coding with Next.js and PostgreSQL!


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)