The Difference Between Schemas and Databases and when to use them in Postgres

When working with PostgreSQL, one of the most powerful features is the ability to organize your data across multiple schemas within a single database. As your application grows, you might find yourself needing to query or join tables that are spread across different schemas. This post is all about understanding how schemas work in PostgreSQL and how you can effectively join tables from different schemas.

Thank me by sharing on Twitter 🙏

Understanding Databases and Schemas in PostgreSQL

Before diving into the details of cross-schema joins, let’s clarify the difference between a database and a schema in PostgreSQL. This distinction is crucial when setting up or querying your database.

In PostgreSQL, a database is the overarching container that holds everything: schemas, tables, indexes, and other database objects. When you connect to a PostgreSQL instance, you typically connect to a specific database. Inside that database, you can organize your tables and other objects into multiple schemas.

A schema is essentially a namespace within your database that helps group related tables and objects together. By default, PostgreSQL includes a schema called public, but you can create additional schemas based on your needs, such as organizing tables by different application modules or functional areas.

Why Use Multiple Schemas?

You might wonder why you would use multiple schemas instead of just putting everything into the default public schema. Using multiple schemas gives you several advantages:

Modularity: You can separate logically different parts of your application into their own namespaces.
Organization: Schemas help you avoid name conflicts by allowing tables with the same name to exist in different schemas.
Access Control: You can apply permissions at the schema level, allowing for more granular control over who can access which parts of the database.

Let’s imagine you have an e-commerce application. You might want to separate your order management and inventory management modules into different schemas:

Schema 1: sales
Table: orders
Schema 2: inventory
Table: products

Now, what if you need to query information about orders and include product details? This is where cross-schema joins come into play.

How to Join Tables Across Schemas

In PostgreSQL, joining tables across schemas is straightforward. The key is to fully qualify the table names by specifying the schema. Here’s how you can do it.

Example Scenario

You have the following tables:

sales.orders with columns: order_id, order_date, and product_id.
inventory.products with columns: product_id, product_name, and price.

You want to create a report that shows each order along with the product name and price.

The SQL Query

SQL

SELECT
  s.order_id,
  s.order_date,
  p.product_name,
  p.price
FROM
  sales.orders s
JOIN
  inventory.products p
ON
  s.product_id = p.product_id;

Explanation

sales.orders: This specifies the orders table inside the sales schema.
inventory.products: This specifies the products table inside the inventory schema.
The query joins these two tables based on the common column product_id, allowing you to pull data from both schemas in a single query.

Notice how I used table aliases (s for sales.orders and p for inventory.products) to keep the query readable. When working with longer queries or multiple joins, aliases make a significant difference in readability.

Using Schemas in a TypeScript Project

If you’re working in a TypeScript environment, especially with an ORM like TypeORM or Prisma, handling cross-schema queries might require additional configuration. For instance, in Prisma, you would need to define each schema in your schema.prisma file.

Here’s an example configuration:

TypeScript

model Order {
  id          Int     @id @default(autoincrement())
  orderDate   DateTime
  productId   Int
  Product     Product  @relation(fields: [productId], references: [id])

  @@map("orders") // Map to the 'orders' table in the sales schema
  @@schema("sales")
}

model Product {
  id          Int     @id @default(autoincrement())
  productName String
  price       Float

  @@map("products") // Map to the 'products' table in the inventory schema
  @@schema("inventory")
}

In this setup, you’re telling Prisma to map the models to specific schemas by using the @@schema directive. When you run queries using Prisma’s API, it will automatically generate the correct SQL with the fully qualified table names.

Why Properly Managing Schemas is Essential

As your application scales, organizing your database objects into schemas can help maintain clarity. Schemas also play a vital role when working in teams, allowing different modules or features to evolve independently. Additionally, they make it easier to enforce security boundaries and manage permissions.

One of the common challenges is ensuring that queries remain efficient and maintainable when joining across schemas. Fully qualifying your table names and using schemas wisely helps avoid ambiguity and reduces the chances of errors, especially when you have similarly named tables across different schemas.

Conclusion

Understanding how to work with schemas and join tables across them in PostgreSQL is a powerful skill that can greatly enhance the scalability and maintainability of your database. Whether you’re dealing with a multi-module application or just want to better organize your data, schemas provide a robust way to structure your tables and avoid clutter. The process is straightforward, but it’s also something that adds a lot of value to your overall database design.

By leveraging cross-schema joins and organizing your data strategically, you can make your queries more logical and your database easier to manage. I’ve found that using schemas has made my own projects more modular and easier to scale over time.