PostgreSQL Schemas

In the previous chapter, we explored PostgreSQL databases — how to create them, manage them, and connect to them in a Next.js application. In this chapter, we’ll take a closer look at schemas, which are an essential part of how PostgreSQL organizes data inside a database.

1. What is a Schema?

A schema in PostgreSQL is essentially a namespace inside a database. It allows you to group related objects such as tables, views, functions, and sequences. By using schemas, you can organize your database logically and avoid name collisions.

For example:

• public.users — users table in the public schema
• analytics.users — users table in the analyticsschema (separate from public.users)

2. The Default Schema

When you create a new PostgreSQL database, it automatically contains a schema called public. Unless specified otherwise, all objects are created inside the public schema.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

The above command is equivalent to:

CREATE TABLE public.users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

3. Creating Schemas

You can create your own schemas to better organize your data:

CREATE SCHEMA analytics;

Now you can create tables inside this schema:

CREATE TABLE analytics.page_views (
  id SERIAL PRIMARY KEY,
  url TEXT,
  viewed_at TIMESTAMP DEFAULT NOW()
);

4. Switching Between Schemas

PostgreSQL uses a search path to determine which schema to look in when you don’t explicitly specify one.

SET search_path TO analytics;

After this, if you run:

SELECT * FROM page_views;

PostgreSQL will look inside the analytics schema by default.

5. Viewing Schemas

To list all schemas in your current database:

\dn

Or run:

SELECT schema_name 
FROM information_schema.schemata;

6. Dropping Schemas

To delete a schema (and optionally all objects inside it):

DROP SCHEMA analytics;

If the schema has objects (tables, views, etc.), you’ll need:

DROP SCHEMA analytics CASCADE;

7. Using Schemas in Next.js

When writing SQL queries from Next.js, always specify the schema if your database has more than one with similar table names.

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

export default async function handler(req, res) {
  try {
    const result = await pool.query("SELECT * FROM analytics.page_views");
    res.status(200).json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Database error" });
  }
}

This ensures you’re pulling from the correct schema (analytics) and not the default public.

8. Best Practices

• Use schemas to separate concerns (e.g., public, auth, analytics).
• Avoid cluttering the public schema with too many unrelated tables.
• Always qualify table names with schema if you work with multiple schemas.
• Restrict permissions at the schema level for better security.

9. Summary

In this chapter, we explored PostgreSQL schemas, how they help organize database objects, and how to use them effectively in Next.js projects. We covered creating schemas, switching search paths, listing them, and dropping them when no longer needed.

In the next chapter, we’ll explore PostgreSQL tables, where the real data storage happens.