NestJS & DrizzleORM: A Great Match.

NestJS is widely regarded for its modularity, which simplifies the process of developing new applications. A critical component of any application is database management, and many developers have traditionally relied on TypeORM in conjunction with @nestjs/typeorm to establish database connections efficiently.

The ORM Landscape

A variety of Object-Relational Mapping (ORM) tools are available to developers, including TypeORM, Prisma, Sequelize, and MikroORM. Recently, DrizzleORM has gained significant attention due to its lightweight and type-safe approach. In particular, the combination of Drizzle with SQLite has proven to be both efficient and straightforward to implement.

However, for developers transitioning from TypeORM, the absence of an equivalent integration for NestJS was noticeable. While configuring DrizzleORM manually is not overly complex, a more seamless integration was desired. This gap led to the creation of @sixaphone/nestjs-drizzle, a wrapper designed to facilitate the use of DrizzleORM with multiple connections within a NestJS application, in a manner similar to TypeORM.

What is DrizzleORM (aka Drizzle)?

Before we dive into NestJS and the Drizzle module, let's take a step back and clarify what drizzle is. [DrizzleORM) is a TypeScript-first ORM (Object-Relational Mapping) that provides type-safe database queries with minimal overhead. Its syntax is very SQL-like and if you know SQL you know drizzle. They provide a variety of tools for developers to define their schemas, handle migrations and make use of a large number of databases like PostgreSQL, MySQL, SQLite, as well as external providers like Turso and Neon. Unlike other ORMs where you define entities or have a separate schema file, drizzle allows you to define a schema-object on how your database table should look like and from there it allows you to manage the schema and records related to it. It acts more as a library to help you build your migrations/schema and write queries in a fluent way, rather than to constrain you in a way a fully fledged ORM would.

Why Drizzle?

Given the range of ORM solutions available, one may question the necessity of adopting DrizzleORM. TypeORM has long been established as a widely used and well-supported ORM, while Prisma is also a common choice among developers. However, DrizzleORM presents several key advantages that make it an attractive alternative.

Drizzle vs Prisma

Prisma uses its own query engine and requires a separate Prisma Client process, while Drizzle runs directly on Node.js. Another thing is that when you use Prisma you ship the entire Prisma client which is heavy while Drizzle aims to be light-weight.

When defining a schema in Prisma you have to learn their syntax for the schema.prisma file and generate the types while drizzle opts to use pure objects for your definitions.

Due to their implementation, Prisma has to run all results through their engine while Drizzle does not, causing less overhead and faster queries.

Prisma does offer a large toolset like Prisma studio and a huge ecosystem of supported providers. However, while drizzle does not have the same support as Prisma, together with drizzle-kit you can use migrations, drizzle studio, and other features that are not in the base package.

Drizzle vs TypeORM

TypeORM favors the use of classes for entity definitions. If you come from an Object-oriented language then it will make you feel right at home. It also has a large list of providers and supported databases. However, because they use classes they have to instantiate them which is a heavier tool on memory.

It is also worth noting that drizzle is easier to set up and use typescript inference. While TypeORM does not have a fancy studio built in, they do provide migration support for complex query syntax and complex relation and inheritance patterns. One can see that TypeORM, because it has been around for so long, is a very mature and feature-rich ORM for more experienced developers and larger, more complex projects. However, this does not mean that Drizzle cannot compete, it only needs more time and love to get to the same maturity.

General Drizzle advantages

Both Prisma and TypeORM require you to use a dedicated syntax to define your entities. Be it a class in javascript or in a schema.prisma file. Drizzle on the other hand just requires an object with the definition to work with your entity. This makes it a lot easier to include in your project as it works around your project rather than the other way. While TypeORM has a great query builder and can be both SQL-like and not SQL-like, drizzle too offers both forms of syntax for simpler and more advanced queries. Prisma on the other hand has only non-SQL-like syntax due to their custom client. And perhaps the biggest advantage of drizzle is the fact that in today's ecosystem, it has great support for edge and serverless applications. It is also great to use as a standalone tool for migrations. Whereas Prisma and TypeORM require you to incorporate them in your code. Drizzle is much more flexible there.

Why this Library?

While Drizzle excels at database operations, integrating it with NestJS often means writing custom modules for different database types. This leads to boilerplate code and another dependency to maintain.

Our library eliminates this overhead by providing a unified solution that works across all supported databases out of the box. Unlike existing alternatives that require specific modules per database, we offer a single, comprehensive package that doesn't compromise on configurability.

What sets this apart? A built-in repository pattern with helper methods to accelerate development, plus utility classes that simplify type inference across different database interfaces. This means you can write more database-agnostic code while maintaining type safety.

Getting started

Hands-on is a good way to get a grasp so let's build a URL redirector using a local SQLite database and a Turso connection. We will start by creating a new nestjs project and by adding all required dependencies including the library.

1. Create a new NestJS project:

nest new url-redirect

2. Generate the URL module:

nest g module url

3. Install dependencies:

npm i @sixaphone/nestjs-drizzle
npm i -D drizzle-kit

Getting set up

Drizzle is un-opinionated, allowing for flexible schema setup according to individual preferences. For the purposes of this post, a database folder will be created within the src directory, following the structure outlined below:

src/
  |__database/
  |      |__url.entity.ts
  |      |__schema.ts
  |__url/
  |__app.module.ts
  |__main.ts

Defining Schema and Entities

The way we define entities is how we would usually define a drizzle entity. It's just a POJO (plain old javascript object), but it uses a helper method to adjust it to the underlying connection. Now this means that we would have to re-define our entities for different databases, but it is not a challenging task, as we usually stick to the first database we choose until a very critical point.

// url.entity.ts
import { sql } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

So all we do here is just import some required methods from drizzle and the specific database methods.

export const urls = sqliteTable('urls', {
  id: integer('id').primaryKey(),
  target: text('target').notNull(),
  slug: text('slug').unique().notNull(),
  createdAt: text('created_at')
    .default(sql`(CURRENT_TIMESTAMP)`)
    .notNull(),
});

Then we define our actual entity by calling the method. Here we use sqliteTable, but depending on our connection that would change. The first parameter is the actual name of the table and then our POJO for how our schema should look like.

export type CreateUrl = typeof urls.$inferInsert;
export type SelectUrl = typeof urls.$inferSelect;

As mentioned before drizzle offers great type inference. In TypeORM you would have to defined what a create or select model should have. In Prisma you are baraged with a bunch of types that I dont even want to name. Drizzle just has two. A select and a create. From those you can build out all other types.

// schema.ts
import { urls } from './url.entity';
export const schema = {
  urls,
};
export type Schema = typeof schema;

Now we just define a single entry point for all our entities and export them. What I also do is export the type of schema, because we will need it for later.

Connecting with ourselves and the database

As is the case in some applications we can have multiple connections. Could be read-write replicas or similar cases.. And through the DrizzleModule of the library we can easily instantiate multiple of those connections at once and re-use them. Every connection we make is named and we can make constants for those names to re-use them much more efficiently.

// constants.ts
export const DBS = {
  LOCAL: 'local',
  TURSO: 'turso',
};

To setup a local SQLite connection we can do the following:

DrizzleModule.forRoot({
  name: DBS.LOCAL,
  type: 'sqlite',
  url: 'file:url.db',
  schema,
})

The schema and DBS come from the respective files, but you can see we are utilizing NestJS's DynamicModule builder forRoot to create a global connection to our database. The type has a nice autocomplete for us to know what values we can use, and URL, since we are using SQLite is going to be a local file path that we named url.db.

If we want to add Turso we can do it as follows:

DrizzleModule.forRootAsync({
  name: DBS.TURSO,
  useFactory: (tursoConfig) => ({
    type: 'sqlite',
    url: tursoConfig.databaseUrl!,
    authToken: tursoConfig.authToken!,
    schema,
  }),
  imports: [ConfigModule.forFeature(tursoConfig)],
  inject: [tursoConfig.KEY],
}),

Again we are using the DynamicModule methods, but this time, since we want to read from config we make use of the async builder to inject our config and return the relevant data.

All in all our AppModule would be

// app.module.ts
@Module({
  imports: [
    ConfigModule.forRoot({}),
    DrizzleModule.forRoot({
      type: 'sqlite',
      name: DBS.LOCAL,
      url: 'file:url.db',
      schema,
    }),
    DrizzleModule.forRootAsync({
      name: DBS.TURSO,
      useFactory: (tursoConfig) => ({
        type: 'sqlite',
        url: tursoConfig.databaseUrl!,
        authToken: tursoConfig.authToken!,
        schema,
      }),
      imports: [ConfigModule.forFeature(tursoConfig)],
      inject: [tursoConfig.KEY],
    }),
  ],
})
export class AppModule {}

Where we import the ConfigModule and then create our two connections with DrizzleModule. Thanks to the name property on the config it will create a named connection to that database and allow us to reference it by that name. That way we can create multiple connections with clients, as well as repositories.

Using the Repository and Client

Drizzle, by default, does not include repositories, a concept more commonly associated with TypeORM. Prisma, on the other hand, does not use repositories at all. In this context, the repository refers to a helper wrapper for a single instance of an entity within the schema.

Using the client

The client is the more straightforward way to use the library. It gives you access to the underlying connection, while not needing to know what connection it is, well somewhat. Trough the client we can do all operations we need for our app, and the library gives a neat util type to infer the database under the hood.

So if we want to use the client in our service it would look like this.

@Injectable()
export class UrlService {
  constructor(
    @InjectClient(DBS.LOCAL)
    private readonly drizzleClient: DrizzleDatabase<'sqlite', Schema>,
  ) {}
  public get(url: string) {
    const urls = await this.drizzleClient.select().from(urls);
    return urls;
  }
  async create(url: string) {
    const url = await this.drizzleClient.transaction((tx) => {
      return tx
        .insert(urls)
        .values({
          target: url,
          slug: new Date().getTime().toString(36),
        })
        .returning();
    });
    return url;
  }
}

Let's break this down a bit so we know what is happening.

Injecting client

constructor(
  @InjectClient(DBS.LOCAL)
  private readonly drizzleClient: DrizzleDatabase<'sqlite', Schema>,
) {}

In this example, the InjectClient helper decorator from the library is used, with a name passed as an argument. If a name was specified for the connection earlier, it must be referenced using the same name when attempting to use it. As for DrizzleDatabase<'sqlite', Schema>, the first parameter, sqlite, specifies that the SQLite dialect will be used for the client. This ensures that Drizzle limits the available methods and query builder to those that are compatible with SQLite syntax.

Why is this necessary? For example, methods like returning() are supported by both SQLite and PostgreSQL, but in MySQL, the method would be .$returningId(). The first parameter allows Drizzle to provide a type-safe way to determine the available methods for the client based on the chosen dialect.

The second parameter, Schema, refers to the type defined earlier as export type Schema = typeof schema;. This enables enhanced autocompletion when performing queries, improving the development experience.

this.drizzleClient.query.urls.findMany({});
this.drizzleClient.query.urls.findFirst({});

We already registered our schema, when we setup the DrizzleModule, but we need to do this if we want to have type-safe query usage. In case you will not use this.drizzleClient.query you do not need to setup the Schema and can omit the type.

The syntax

Here is an example of how Drizzle documentation says we can do a select query

const result = await drizzleClient.select().from(urls);

And here is how we do it with our injected client.

const urls = await this.drizzleClient.select().from(urls);

So by know you can see the pattern. After you setup the connection and inject it. You use it as if it were the vanilla drizzle client, which it in fact is. And in the code snipped above we could also do .returning() or .$returningId() depending on our dialect.

If we look at our UrlService#create method we can see another really useful thing, a 🌈 transaction🌈. That is a neat and handy tool.

async create(url: string) {
  const url = await this.drizzleClient.transaction((tx) => {
    return tx
      .insert(urls)
      .values({
        target: url,
        slug: new Date().getTime().toString(36),
      })
    .returning(); 
  });
  return url;
}

Now when you look at this code snipper above, where we start a transaction, insert a new record and then return the new record (which we can because we are using sqlite), how do you think it would look like using vanilla drizzle? I will let you figure that out.

Another connection

So we used one client for one connection. What if we want another one? Well if we named it properly then it is as simple as:

constructor(
  @InjectClient(DBS.LOCAL)
  private readonly drizzleClient: DrizzleDatabase<'sqlite', Schema>,
  @InjectClient(DBS.Turso)
  private readonly drizzleClientTurso: DrizzleDatabase<'sqlite', Schema>,
) {}

Utilize the repository

As already mentioned earlier, Drizzle does not natively include repositories, and it does not operate in the same way as traditional ORM repositories. The repository in this context is simply a wrapper designed to simplify the use of the client and lock it to a specific entity.

Setup for repo

Now in typeorm you know that you have to register your entities forFeature. Well we kinda need to do the same.

@Module({
  imports: [
    DrizzleModule.forFeature({
      entities: [urls],
      name: DBS.LOCAL,
    }),
  ],
  // ...
})
export class UrlModule {}

So we just go to the model we will be using the repo in, and we register them. Also, keep in mind that if you used a custom name for your connection it is important to register your entities to that connection, else the repository will not work properly. Every connection can have its own set of exposed repositories.

Using the url repository

Now we had the UrlService for the client. Let's see how that would look like if we were to use a repository

@Injectable()
export class UrlService {
  constructor(
    @InjectRepository(urls, DBS.LOCAL)
    private readonly urlRepository: DrizzleRepository<Schema, 'urls', 'sqlite'>,
  ) {}
  public async get(slug: string) {
    const [url] = await this.urlRepository.selectWhere(eq(urls.slug, slug));
    return url;
  }
  public async create(target: string) {
    const url = await this.urlRepository
      .insert({
        target,
        slug: new Date().getTime().toString(36),
      })
      .returning();
    return url;
  }
}

Not much changed. Under the hood a repository still uses the client, it just limits the usage to one single entity, like urls in this case. If we check the constructor we will see that we use the InjectRepository helper decorator to get the repository of an entity, and the first parameter is the entity type we want to use, which is the table defined by drizzle while the second param is the connection name, if you specified a custom connection of course. We can use the DrizzleRepository to register the repository. We pass it the whole schema (unfortunately 😔) and the name of the entity we defined. The last parameter is the dialect we will use for our repo.

The selectWhere method does not exist on drizzle, under the hood it is just doing .select().from(urls).where(query). It removes a lot of code we would have to type ourselves, and there in lies the usefulness. If we check insert we can see now we don't have to call values, that is because the repo will call it for us. And what about that .returning() part? Well as mention depending on the dialect we will have different options available. Those options are better explained by the drizzle team, in the docs I linked to previously.

The return of any method exposed in the repository will be a query builder of the dialect type. What that means is we can chain methods like with the client. For example:

const entities = await this.urlRepository.select().orderBy(urls.id);

The orderBy is not exposed by the repository. Once we call a method of the repo from there we build on like we would usually with a drizzle client.

In Conclusion

Drizzle is a a very powerful tool for working with Databases. It gives us more control over how we want to use it rather than to force a lot of new syntaxes and paradigms.

There is no syntax language like in Prisma, and there are not Entity decorators. We create an object and define it, and boom our Entity is done.

Want a migration? Here it is. Need a UI for viewing data? 💣 Drizzle studio. Want to access your data or mutate it and only know SQL? Drizzle don't really care.

This library aims to make using Drizzle with NestJS a breeze. If you're already familiar with Drizzle, you can jump right in. If you're new to it, you can rely on Drizzle's excellent documentation for building queries without learning another ORM syntax.

For more info on Drizzle itself, check out the DrizzleORM Documentation.