API with NestJS #174. Multiple PostgreSQL schemas with Drizzle ORM

November 11, 2024

This entry is part 174 of 180 in the API with NestJS

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.

database-schema.ts

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

});

export const databaseSchema = {

articles,

};

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

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

000_create-articles-table.sql

CREATE TABLE IF NOT EXISTS "articles" (

"id" serial PRIMARY KEY NOT NULL,

"title" text NOT NULL,

"content" text NOT NULL,

);

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.

1	SELECT * 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.

1	SHOW 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.

1	SELECT 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.

1	SET search_path TO another_schema_name;

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

1	SET search_path TO DEFAULT;

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

1	SELECT * FROM public.articles;

Using other schemas

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

database-schema.ts

import { pgSchema } from 'drizzle-orm/pg-core';

export const usersDataSchema = pgSchema('users_data');

// ...

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

database-schema.ts

import { serial, text, pgSchema, integer } from 'drizzle-orm/pg-core';

export const usersDataSchema = pgSchema('users_data');

export const addresses = usersDataSchema.table('addresses', {

id: serial('id').primaryKey(),

street: text('street').notNull(),

city: text('city').notNull(),

country: text('country').notNull(),

});

export const users = usersDataSchema.table('users', {

id: serial('id').primaryKey(),

email: text('email').unique().notNull(),

name: text('name').notNull(),

password: text('password').notNull(),

addressId: integer('address_id')

.unique()

.references(() => addresses.id),

});

// ...

export const databaseSchema = {

addresses,

users,

// ...

};

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.

database-schema.ts

import { serial, text, pgTable, pgSchema, integer } from 'drizzle-orm/pg-core';

export const usersDataSchema = pgSchema('users_data');

export const addresses = usersDataSchema.table('addresses', {

id: serial('id').primaryKey(),

street: text('street').notNull(),

city: text('city').notNull(),

country: text('country').notNull(),

});

export const users = usersDataSchema.table('users', {

id: serial('id').primaryKey(),

email: text('email').unique().notNull(),

name: text('name').notNull(),

password: text('password').notNull(),

addressId: integer('address_id')

.unique()

.references(() => addresses.id),

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

});

export const databaseSchema = {

articles,

addresses,

users,

};

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.

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

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

0001_create-users-data.sql

CREATE SCHEMA "users_data";

CREATE TABLE IF NOT EXISTS "users_data"."users" (

"id" serial PRIMARY KEY NOT NULL,

"email" text NOT NULL,

"name" text NOT NULL,

"password" text NOT NULL,

"address_id" integer,

CONSTRAINT "users_email_unique" UNIQUE("email"),

CONSTRAINT "users_address_id_unique" UNIQUE("address_id")

);

--> ...

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

users.service.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class UsersService {

constructor(private readonly drizzleService: DrizzleService) {}

async getByEmail(email: string) {

const user = await this.drizzleService.db.query.users.findFirst({

where: eq(databaseSchema.users.email, email),

});

if (!user) {

throw new NotFoundException();

}

return user;

}

// ...

}

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

1	Query: 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.

Series Navigation<< API with NestJS #173. Storing money with Drizzle ORM and PostgreSQLAPI with NestJS #175. PUT and PATCH requests with PostgreSQL and Drizzle ORM >>