Multiple PostgreSQL schemas with Drizzle ORM

PostgreSQL uses schemas as namespaces within the database to hold tables and other structures, such as indexes. In this article, we explain how to use them with the Drizzle ORM and how they can be beneficial.

The public schema

Out of the box, PostgreSQL creates a schema called public for each new database.

1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2 
3export const articles = pgTable('articles', {
4  id: serial('id').primaryKey(),
5  title: text('title').notNull(),
6  content: text('content').notNull(),
7});
8 
9export const databaseSchema = {
10  articles,
11};

Let’s investigate a migration that Drizzle ORM creates for the above table.

If you want to know more about migrations with the Drizzle ORM, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

1npx drizzle-kit generate --name create-articles-table

1CREATE TABLE IF NOT EXISTS "articles" (
2  "id" serial PRIMARY KEY NOT NULL,
3  "title" text NOT NULL,
4  "content" text NOT NULL, 
5);

We can see that the above migration does not explicitly mention the public schema. By default, when we don’t specify the schema, PostgreSQL assumes that we want to use the public schema.

What’s interesting, the Drizzle ORM holds the information about our migrations in a separate schema called drizzle.

When we make a SQL query and don’t specify the schema, PostgreSQL also assumes that we want to use the public schema.

1SELECT * FROM articles;

How PostgreSQL chooses the default schema

PostgreSQL controls the default schema through the built-in variable called search_path. It determines the order of schemas PostgreSQL looks at when making a query that does not specify the schema explicitly.

1SHOW search_path;

By default, search_path contains "$user", public. The first part refers to the name of the current user, which we can verify through the current_user variable.

1SELECT current_user;

Therefore, PostgreSQL first tries to look for the articles table in the admin schema. Since it does not exist, it tries the public schema.

To switch the default schema, we need to change the search_path variable.

1SET search_path TO another_schema_name;

We can go back to the default value in a straightforward way.

1SET search_path TO DEFAULT;

We can prepend the table’s name with the intended schema if we want to be explicit.

1SELECT * FROM public.articles;

Using other schemas

To create new schemas with the Drizzle ORM, we need the pgSchema function.

1import { pgSchema } from 'drizzle-orm/pg-core';
2 
3export const usersDataSchema = pgSchema('users_data');
4 
5// ...

Now, we need to use its output with our new tables.

1import { serial, text, pgSchema, integer } from 'drizzle-orm/pg-core';
2 
3export const usersDataSchema = pgSchema('users_data');
4 
5export const addresses = usersDataSchema.table('addresses', {
6  id: serial('id').primaryKey(),
7  street: text('street').notNull(),
8  city: text('city').notNull(),
9  country: text('country').notNull(),
10});
11 
12export const users = usersDataSchema.table('users', {
13  id: serial('id').primaryKey(),
14  email: text('email').unique().notNull(),
15  name: text('name').notNull(),
16  password: text('password').notNull(),
17  addressId: integer('address_id')
18    .unique()
19    .references(() => addresses.id),
20});
21 
22// ...
23 
24export const databaseSchema = {
25  addresses,
26  users,
27  // ...
28};

What’s interesting is that we can create relationships between tables in different schemas. For example, we can add the authorId column to our articles.

1import { serial, text, pgTable, pgSchema, integer } from 'drizzle-orm/pg-core';
2 
3export const usersDataSchema = pgSchema('users_data');
4 
5export const addresses = usersDataSchema.table('addresses', {
6  id: serial('id').primaryKey(),
7  street: text('street').notNull(),
8  city: text('city').notNull(),
9  country: text('country').notNull(),
10});
11 
12export const users = usersDataSchema.table('users', {
13  id: serial('id').primaryKey(),
14  email: text('email').unique().notNull(),
15  name: text('name').notNull(),
16  password: text('password').notNull(),
17  addressId: integer('address_id')
18    .unique()
19    .references(() => addresses.id),
20});
21 
22export const articles = pgTable('articles', {
23  id: serial('id').primaryKey(),
24  title: text('title').notNull(),
25  content: text('content').notNull(),
26  authorId: integer('author_id')
27    .references(() => users.id)
28    .notNull(),
29});
30 
31export const databaseSchema = {
32  articles,
33  addresses,
34  users,
35};

Thanks to defining the users_data schema above, the Drizzle ORM will attach its name to SQL queries when necessary.

Let’s create a migration and inspect the output.

1npx drizzle-kit generate --name create-users-data

Now, we can see that the Drizzle ORM appends the schema’s name when creating our tables.

1CREATE SCHEMA "users_data";
2CREATE TABLE IF NOT EXISTS "users_data"."users" (
3  "id" serial PRIMARY KEY NOT NULL,
4  "email" text NOT NULL,
5  "name" text NOT NULL,
6  "password" text NOT NULL,
7  "address_id" integer,
8  CONSTRAINT "users_email_unique" UNIQUE("email"),
9  CONSTRAINT "users_address_id_unique" UNIQUE("address_id")
10);
11--> ...

For example, we can use the above table for authentication.

1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5 
6@Injectable()
7export class UsersService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  async getByEmail(email: string) {
11    const user = await this.drizzleService.db.query.users.findFirst({
12      where: eq(databaseSchema.users.email, email),
13    });
14 
15    if (!user) {
16      throw new NotFoundException();
17    }
18 
19    return user;
20  }
21 
22  // ...
23}

When we turn on the logger, we can see that the Drizzle ORM appends the name of the schema when making various SQL queries as well.

If you want to know more about logging with Drizzle ORM, take a look at API with NestJS #166. Logging with the Drizzle ORM

1Query: select "id", "email", "name", "password", "address_id" from "users_data"."users" where "users"."email" = $1 limit $2 -- params: ["john@smith.com", 1]

Advantages of multiple schemas

Using multiple schemas in PostgreSQL gives us several advantages. If we have multiple tables, organizing them into schemas can make it easier to navigate our database. Also, we can use schemas to manage access permissions in our database in order to restrict some users from using a specific schema. It can come in handy if we have multiple users interacting with our database. On top of that, we can have backups and other routine maintenance tasks target particular schemas without affecting the whole database.

Additionally, schemas can help us resolve naming conflicts that could happen if different teams work separately on the database. As long as they use dedicated schemas, they can use tables or indexes with the same names.

Summary

With schemas, we can manage our data in a more secure and readable way. It can come in handy, especially in complex environments with databases that have multiple users.

To learn how to work with schemas, we first wrote raw SQL queries to see how PostgreSQL works when we don’t specify the schema explicitly. Then, we used the Drizzle ORM to create more schemas with various tables. Learning how to do that can make our database more straightforward to manage, especially if our database is big and multiple users interact with it.