API with NestJS #180. Organizing Drizzle ORM schema with PostgreSQL

December 23, 2024

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

As our application grows, it gets increasingly important to create a file structure that’s easy to maintain. Also, if we care about it from the start, it is easier to achieve.

In this article, we learn how to organize the database schema when working with the Drizzle ORM and NestJS.

Handling column names

When working with PostgreSQL, we use the pgTable function to specify the schema of a particular table.

database-schema.ts

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

export const users = pgTable('users', {

id: serial().primaryKey(),

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

firstName: text().notNull(),

lastName: text().notNull(),

password: text().notNull(),

});

export const databaseSchema = {

users,

};

Above, we specify a bunch of properties, such as email and firstName. When we generate a migration, Drizzle specifies the column names in our Database based on the property names we chose.

If you want to know more about migrations with the Drizzle ORM, check out API with NestJS #176. Database migrations with the Drizzle ORM

1	npx drizzle-kit generate --name add-users-table

0000_add-users-table.sql

CREATE TABLE IF NOT EXISTS "users" (

"id" serial PRIMARY KEY NOT NULL,

"email" text NOT NULL,

"firstName" text NOT NULL,

"lastName" text NOT NULL,

"password" text NOT NULL,

CONSTRAINT "users_email_unique" UNIQUE("email")

);

Typically, columns in SQL databases use the snake_case convention. However, since TypeScript and JavaScript commonly use the camelCase convention, Drizzle uses it for column names. To handle that, we can explicitly provide the name of our columns.

database-schema.ts

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

export const users = pgTable('users', {

id: serial().primaryKey(),

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

firstName: text('first_name').notNull(),

lastName: text('first_name').notNull(),

password: text().notNull(),

});

export const databaseSchema = {

users,

};

When we do that, Drizzle uses the explicitly provided column names when generating a migration.

0000_add-users-table.sql

CREATE TABLE IF NOT EXISTS "users" (

"id" serial PRIMARY KEY NOT NULL,

"email" text NOT NULL,

"first_name" text NOT NULL,

"password" text NOT NULL,

CONSTRAINT "users_email_unique" UNIQUE("email")

);

Converting the name conventions automatically

Alternatively, we can tell the Drizzle ORM to map the camelCase to snake_case alternatively. To do that, we need to provide the casing option when initializing the Drizzle ORM.

drizzle.service.ts

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

import { Pool } from 'pg';

import { CONNECTION_POOL } from './database.module-definition';

import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';

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

@Injectable()

export class DrizzleService {

public db: NodePgDatabase<typeof databaseSchema>;

constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {

this.db = drizzle(this.pool, {

schema: databaseSchema,

casing: 'snake_case',

});

}

If you want to know how to create the DrizzleService, take a look at API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

We also have to provide the same option when configuring the Drizzle Kit.

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

import { ConfigService } from '@nestjs/config';

import 'dotenv/config';

import { EnvironmentVariables } from './src/utilities/environment-variables';

const configService = new ConfigService<EnvironmentVariables, true>();

export default defineConfig({

schema: './src/database/database-schema.ts',

out: './drizzle',

dialect: 'postgresql',

dbCredentials: {

host: configService.get('POSTGRES_HOST'),

port: configService.get('POSTGRES_PORT'),

user: configService.get('POSTGRES_USER'),

password: configService.get('POSTGRES_PASSWORD'),

database: configService.get('POSTGRES_DB'),

casing: 'snake_case',

});

Once we add the casing: 'snake_case' parameter, Drizzle ORM automatically translates properties such as firstName to camel_case.

Organizing schema files

A straightforward way of organizing our database schema is to put it all in one TypeScript file.

database-schema.ts

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

import { relations } from 'drizzle-orm';

export const users = pgTable('users', {

id: serial().primaryKey(),

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

firstName: text().notNull(),

lastName: text().notNull(),

password: text().notNull(),

});

export const articles = pgTable('articles', {

id: serial().primaryKey(),

title: text().notNull(),

content: text().notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

});

export const articlesAuthorsRelation = relations(articles, ({ one }) => ({

author: one(users, {

fields: [articles.authorId],

references: [users.id],

}),

}));

export const databaseSchema = {

users,

articles,

articlesAuthorsRelation,

};

It’s crucial to export the databaseSchema, which includes all our schemas together with the relations such as the articlesAuthorsRelation. We then use it in our DrizzleService.

If you want to know more about the relationships with the Drizzle ORM, check out the following articles:

API with NestJS #150. One-to-one relationships with the Drizzle ORM

API with NestJS #151. Implementing many-to-one relationships with Drizzle ORM

API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL

API with NestJS #171. Recursive relationships with Drizzle ORM and PostgreSQL

drizzle.service.ts

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

import { Pool } from 'pg';

import { CONNECTION_POOL } from './database.module-definition';

import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';

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

@Injectable()

export class DrizzleService {

public db: NodePgDatabase<typeof databaseSchema>;

constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {

this.db = drizzle(this.pool, { schema: databaseSchema });

}

If we don’t provide the relations objects, such as the articlesAuthorsRelation, the relational Query API built into Drizzle ORM won’t work. It will cause the following error:

Cannot read properties of undefined (reading ‘referencedTable’)

Splitting the schema into multiple files

As our database grows, storing the entire database schema in one file becomes unmaintainable. To deal with that, we can create separate TypeScript files for every table, each ending with .schema.ts.

users.schema.ts

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

export const users = pgTable('users', {

id: serial().primaryKey(),

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

firstName: text().notNull(),

lastName: text().notNull(),

password: text().notNull(),

});

articles.schema.ts

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

import { users } from '../users/users.schema';

import { relations } from 'drizzle-orm';

export const articles = pgTable('articles', {

id: serial().primaryKey(),

title: text().notNull(),

content: text().notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

});

export const articlesAuthorsRelation = relations(articles, ({ one }) => ({

author: one(users, {

fields: [articles.authorId],

references: [users.id],

}),

}));

Now, we need to configure the Drizzle ORM to look for the schema in all files ending with .schema.ts.

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

import { ConfigService } from '@nestjs/config';

import 'dotenv/config';

import { EnvironmentVariables } from './src/utilities/environment-variables';

const configService = new ConfigService<EnvironmentVariables, true>();

export default defineConfig({

schema: './src/**/*.schema.ts',

out: './drizzle',

dialect: 'postgresql',

dbCredentials: {

host: configService.get('POSTGRES_HOST'),

port: configService.get('POSTGRES_PORT'),

user: configService.get('POSTGRES_USER'),

password: configService.get('POSTGRES_PASSWORD'),

database: configService.get('POSTGRES_DB'),

});

Finally, we still need to provide the Drizzle ORM with an object that contains all schemas and relationships.

database-schema.ts

import { users } from '../users/users.schema';

import { articlesAuthorsRelation, articles } from '../articles/articles.schema';

export const databaseSchema = {

users,

articles,

articlesAuthorsRelation,

};

We can use it in our DrizzleService to provide all the necessary information.

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

import { Pool } from 'pg';

import { CONNECTION_POOL } from './database.module-definition';

import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';

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

@Injectable()

export class DrizzleService {

public db: NodePgDatabase<typeof databaseSchema>;

constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {

this.db = drizzle(this.pool, { schema: databaseSchema });

}

Summary

In this article, we split our schema into multiple files to make it easier to maintain. While doing that, we ensured that we were still providing the Drizzle ORM with all of the necessary information, such as the relations. Besides that, we learned how to avoid manually converting the camelCase convention to snake_case. All of that makes our schemas less complicated, shorter, and easier to understand.

Series Navigation<< API with NestJS #179. Pattern matching search with Drizzle ORM and PostgreSQLAPI with NestJS #181. Prepared statements in PostgreSQL with Drizzle ORM >>