PostgreSQL Databases

In the previous chapter, we explored the basics of PostgreSQL and why it is widely considered one of the most advanced open-source relational databases. In this chapter, we’ll dive deeper into PostgreSQL databases: what they are, how they work, and how to create, manage, and connect them within a real-world Next.js project.

1. What is a Database in PostgreSQL?

In PostgreSQL, a database is a structured collection of data. Each PostgreSQL server instance can manage multiple databases, and each database is independent from the others. This means tables, schemas, users, and objects inside one database are not directly accessible from another without special configuration.

For example, if you are building a SaaS application, you might use:

• app_prod → Production data
• app_dev → Development/testing data
• app_analytics → Reporting and analytics

2. Creating a Database

PostgreSQL allows you to create databases using SQL commands, the psql CLI, or graphical tools like pgAdmin. Let’s look at the main approaches:

2.1 Using SQL Command

CREATE DATABASE myappdb;

This command creates a new database called myappdb. You can only run this if you have sufficient privileges (typically as the postgressuperuser or another role with CREATEDB rights).

2.2 Using psql CLI

psql -U postgres
postgres=# CREATE DATABASE myappdb;

Here, -U postgres means we are logging in as the default postgres superuser. Once inside the interactive shell, you can run the SQL command.

2.3 Using PgAdmin GUI

If you prefer a graphical approach:

1. Open pgAdmin
2. Right-click on Databases → Create → Database
3. Enter the database name (e.g., myappdb)
4. Save

3. Connecting to a Database in Next.js

To interact with PostgreSQL from a Next.js app, we use thepg package (Node.js PostgreSQL client). Let’s set up a reusable connection.

3.1 Install pg

npm install pg

3.2 Create a Database Utility

In your project, create a file lib/db.js:

// lib/db.js
import { Pool } from "pg";

const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "myappdb", // your PostgreSQL database
  password: "yourpassword",
  port: 5432,
});

export default pool;

Using Pool is better than a single client connection because it manages multiple database requests efficiently.

3.3 Test the Connection

Create a test API route in Next.js:

// pages/api/testdb.js
import pool from "@/lib/db";

export default async function handler(req, res) {
  try {
    const result = await pool.query("SELECT NOW()");
    res.status(200).json({ time: result.rows[0] });
  } catch (error) {
    console.error("Database connection error:", error);
    res.status(500).json({ error: "Database connection failed" });
  }
}

Visit /api/testdb in your browser. If everything is set up correctly, you’ll see the current timestamp from PostgreSQL.

4. Listing Databases

To view all databases in your PostgreSQL instance, use:

\l

Or, equivalently:

SELECT datname FROM pg_database;

5. Dropping a Database

To remove a database (be very careful with this!):

DROP DATABASE myappdb;

You cannot drop a database while connected to it; you must connect to another database (such as postgres) first.

6. Best Practices

• Use separate databases for production, development, and testing.
• Never use the postgres default database for application data.
• Always create dedicated roles for applications instead of using the postgres superuser.
• Use environment variables in Next.js (.env.local) for credentials:

DB_USER=postgres
DB_PASSWORD=yourpassword
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myappdb

// lib/db.js with env vars
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,
});

7. Summary

In this chapter, we explored PostgreSQL databases: what they are, how to create them, and how to connect to them in a Next.js app using the pg library. We also covered listing databases, dropping them, and following best practices like using environment variables and separate databases for each environment.

In the next chapter, we’ll dive into schemas and understand how PostgreSQL organizes objects within databases.