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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2
3export const users = pgTable('users', {
4 id: serial().primaryKey(),
5 email: text().unique().notNull(),
6 firstName: text().notNull(),
7 lastName: text().notNull(),
8 password: text().notNull(),
9});
10
11export const databaseSchema = {
12 users,
13};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
1npx drizzle-kit generate --name add-users-table1CREATE TABLE IF NOT EXISTS "users" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "email" text NOT NULL,
4 "firstName" text NOT NULL,
5 "lastName" text NOT NULL,
6 "password" text NOT NULL,
7 CONSTRAINT "users_email_unique" UNIQUE("email")
8);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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2
3export const users = pgTable('users', {
4 id: serial().primaryKey(),
5 email: text().unique().notNull(),
6 firstName: text('first_name').notNull(),
7 lastName: text('first_name').notNull(),
8 password: text().notNull(),
9});
10
11export const databaseSchema = {
12 users,
13};When we do that, Drizzle uses the explicitly provided column names when generating a migration.
1CREATE TABLE IF NOT EXISTS "users" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "email" text NOT NULL,
4 "first_name" text NOT NULL,
5 "password" text NOT NULL,
6 CONSTRAINT "users_email_unique" UNIQUE("email")
7);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.
1import { Inject, Injectable } from '@nestjs/common';
2import { Pool } from 'pg';
3import { CONNECTION_POOL } from './database.module-definition';
4import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
5import { databaseSchema } from './database-schema';
6
7@Injectable()
8export class DrizzleService {
9 public db: NodePgDatabase<typeof databaseSchema>;
10 constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {
11 this.db = drizzle(this.pool, {
12 schema: databaseSchema,
13 casing: 'snake_case',
14 });
15 }
16}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.
1import { defineConfig } from 'drizzle-kit';
2import { ConfigService } from '@nestjs/config';
3import 'dotenv/config';
4import { EnvironmentVariables } from './src/utilities/environment-variables';
5
6const configService = new ConfigService<EnvironmentVariables, true>();
7
8export default defineConfig({
9 schema: './src/database/database-schema.ts',
10 out: './drizzle',
11 dialect: 'postgresql',
12 dbCredentials: {
13 host: configService.get('POSTGRES_HOST'),
14 port: configService.get('POSTGRES_PORT'),
15 user: configService.get('POSTGRES_USER'),
16 password: configService.get('POSTGRES_PASSWORD'),
17 database: configService.get('POSTGRES_DB'),
18 },
19 casing: 'snake_case',
20});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.
1import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';
2import { relations } from 'drizzle-orm';
3
4export const users = pgTable('users', {
5 id: serial().primaryKey(),
6 email: text().unique().notNull(),
7 firstName: text().notNull(),
8 lastName: text().notNull(),
9 password: text().notNull(),
10});
11
12export const articles = pgTable('articles', {
13 id: serial().primaryKey(),
14 title: text().notNull(),
15 content: text().notNull(),
16 authorId: integer('author_id')
17 .references(() => users.id)
18 .notNull(),
19});
20
21export const articlesAuthorsRelation = relations(articles, ({ one }) => ({
22 author: one(users, {
23 fields: [articles.authorId],
24 references: [users.id],
25 }),
26}));
27
28export const databaseSchema = {
29 users,
30 articles,
31 articlesAuthorsRelation,
32};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
1import { Inject, Injectable } from '@nestjs/common';
2import { Pool } from 'pg';
3import { CONNECTION_POOL } from './database.module-definition';
4import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
5import { databaseSchema } from './database-schema';
6
7@Injectable()
8export class DrizzleService {
9 public db: NodePgDatabase<typeof databaseSchema>;
10 constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {
11 this.db = drizzle(this.pool, { schema: databaseSchema });
12 }
13}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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2
3export const users = pgTable('users', {
4 id: serial().primaryKey(),
5 email: text().unique().notNull(),
6 firstName: text().notNull(),
7 lastName: text().notNull(),
8 password: text().notNull(),
9});1import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';
2import { users } from '../users/users.schema';
3import { relations } from 'drizzle-orm';
4
5export const articles = pgTable('articles', {
6 id: serial().primaryKey(),
7 title: text().notNull(),
8 content: text().notNull(),
9 authorId: integer('author_id')
10 .references(() => users.id)
11 .notNull(),
12});
13
14export const articlesAuthorsRelation = relations(articles, ({ one }) => ({
15 author: one(users, {
16 fields: [articles.authorId],
17 references: [users.id],
18 }),
19}));Now, we need to configure the Drizzle ORM to look for the schema in all files ending with .schema.ts.
1import { defineConfig } from 'drizzle-kit';
2import { ConfigService } from '@nestjs/config';
3import 'dotenv/config';
4import { EnvironmentVariables } from './src/utilities/environment-variables';
5
6const configService = new ConfigService<EnvironmentVariables, true>();
7
8export default defineConfig({
9 schema: './src/**/*.schema.ts',
10 out: './drizzle',
11 dialect: 'postgresql',
12 dbCredentials: {
13 host: configService.get('POSTGRES_HOST'),
14 port: configService.get('POSTGRES_PORT'),
15 user: configService.get('POSTGRES_USER'),
16 password: configService.get('POSTGRES_PASSWORD'),
17 database: configService.get('POSTGRES_DB'),
18 },
19});Finally, we still need to provide the Drizzle ORM with an object that contains all schemas and relationships.
1import { users } from '../users/users.schema';
2import { articlesAuthorsRelation, articles } from '../articles/articles.schema';
3
4export const databaseSchema = {
5 users,
6 articles,
7 articlesAuthorsRelation,
8};We can use it in our DrizzleService to provide all the necessary information.
1import { Inject, Injectable } from '@nestjs/common';
2import { Pool } from 'pg';
3import { CONNECTION_POOL } from './database.module-definition';
4import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
5import { databaseSchema } from './database-schema';
6
7@Injectable()
8export class DrizzleService {
9 public db: NodePgDatabase<typeof databaseSchema>;
10 constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {
11 this.db = drizzle(this.pool, { schema: databaseSchema });
12 }
13}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.