Drizzle is an emerging TypeScript ORM tool that offers several advantages over Prisma. While Prisma is known for its type safety and easy-to-use API, Drizzle offers a more flexible and extensible solution, specifically when handling complex queries with multiple JOINs. The main philosophy behind Drizzle is that if you know SQL, you know Drizzle.
In this article, we’ll build a simple web API using Drizzle ORM and MySQL, specifically, a user CRUD API. We’ll demonstrate generating a backend using Drizzle, Prisma, and TypeORM, comparing the three in terms of flexibility and handling complex queries.
The full code for this tutorial is available on GitHub, so you can easily follow along and refer back to it as needed. Let’s get started!
Jump ahead:
- Prerequisites
- Installing Drizzle ORM
- Creating data models with Drizzle
- Database migrations using Drizzle
- Writing the Express handlers
- Comparing Drizzle with other ORMs
- Is Drizzle ORM production ready?
Prerequisites
Before you start, you’ll need to have Node.js ≥v16.18.0, npm ≥v8.19.2, and Yarn ≥v1.22.19 installed on your development machine:
npm i -g yarn
You’ll also need to install MySQL; you can either install it directly by using a MySQL binary or by using Docker. We won’t cover installing and setting up MySQL because it’s out of the scope of this article. Instead, I recommend following the steps in this article.
Installing Drizzle ORM
To get started with Drizzle, first, you’ll need to install it. For our example, we’ll use the MySQL database, but Drizzle also supports other databases. Install the specific Drizzle driver for MySQL with the code below:
yarn add drizzle-orm mysql2
We’ll need two main packages to connect and query the database using Drizzle. The mysql2 package is a popular Node.js package to connect and interact with MySQL. Drizzle uses its pool connections to query and mutate data:
yarn add -D drizzle-kit
We’ll also use Drizzle Kit, which will help to either generate or update our migrations. However, it doesn’t help us apply them. You’ll either have to apply the migrations yourself or use a different tool.
Creating data models with Drizzle
In this article, we’ll provide a step-by-step guide to building a web API from scratch using Drizzle ORM, focusing mainly on the experience of implementing flexible queries.
To create data models with Drizzle, you need to define the tables in your database as TypeScript objects. The data models are different from regular TypeScript objects; they rely heavily on a function-based syntax and Drizzle annotations to define the tables’ columns, relationships, and other metadata.
The example below shows a simple user table inside a schema file based on the Drizzle syntax:
import {
mysqlTable,
serial,
uniqueIndex,
varchar,
} from 'drizzle-orm/mysql-core';
export const users = mysqlTable('users', {
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
email: varchar('email', { length: 256 }),
}, (users) => ({
emailIndex: uniqueIndex('email_idx').on(users.email),
}));
The code above is very similar to defining tables in SQL, but the table structure is defined as a TypeScript object, thereby providing better type safety and code completion. By using Drizzle’s annotations and functions, you can define primary keys, indexes, foreign keys, and more.
Another thing to notice is the import of functions from the drizzle-orm/mysql-core package, which provides the necessary tools to define MySQL-specific data models. If you use a different database system, you’ll need to import a different route.
Database migrations using Drizzle
With the Drizzle Kit npm package, Drizzle can write the SQL statements needed to migrate your database by generating migration files for you. However, it’s worth noting that Drizzle doesn’t provide a tool for applying these migrations, so you’ll need to apply them using a separate tool.
But, before you can generate your migrations, you need to create a Drizzle config file for your project. In the root directory of your project, create a file called drizzle.config.json:
{
"out": "./src/db/migrations",
"schema": "./src/db/schema.ts",
"breakpoints": true
}
As shown in the example above, you need to specify the output directory for your migration files along with pointers to your schema file. You must also specify whether or not breakpoints should be enabled. Enabling breakpoints will ensure that your generated migration file won’t execute multiple DDL statements in one transaction, which isn’t supported by all database drivers.
For example, MySQL doesn’t support it, and therefore, you should enable breakpoints in our example. Once you’ve added the config file, you can use Drizzle Kit to generate or update your migrations by running the following command:
drizzle-kit generate:mysql
This will generate migration files based on your schema and output them to the specified directory. Also, because you’re using MySQL specifically, you need to use the generate:mysql command. To update your migrations with any changes to your schema, simply run the same command again, and Drizzle will generate new migration files that incorporate those changes.
Writing the Express handlers
After you’ve written the model and the migrations for your database, next, we’ll write the Express handlers. Assuming that you’ve also run your migrations, Drizzle will be able to connect to your database and query your table based on the schema you defined.
Create a new file to define the pool connection using the mysql2 package and the credentials for your database. Then, initiate a new Drizzle instance using that connection as follows:
import "dotenv/config";
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
// Create the connection
const poolConnection = mysql.createPool(process.env.DATABASE_URL as string);
export const db = drizzle(poolConnection);
For simplicity, the db constant in the code above contains the Drizzle instance in the form of a singleton, which was created using the previously-established connection pool. However, making singletons this way isn’t the best practice because a pool will already keep a cache of the connection for reuse. Instead, it’s better to use a pool and let it manage the connections.
Next, define the Express app with the necessary middleware as follows:
import express from 'express';
const app = express();
const port = process.env.PORT || 3000;
app.use(express.json());
// Insert routes here...
app.listen(port, () => {
console.log(`Server is listening on port ${port}.`);
});
We haven’t defined the routes yet. We’re creating a simple CRUD system, so we’ll need routes for creating, reading, updating, and deleting records. Next, you can make a simple GET request to retrieve all the users.
On the top of the file, we’ll add a couple more imports that will help to access the database and perform queries:
import { db } from './db';
import { users as userSchema } from './db/schema';
import { eq } from 'drizzle-orm';
Then, define the GET routes:
app.get('/users', async (req, res) => {
const users = await db.select().from(userSchema);
res.json(users);
});
app.get('/users/:id', async (req, res) => {
const { id } = req.params;
const user = await db.select().from(userSchema).where(eq(userSchema.id, Number(id)));
res.json(user);
});
These routes allow you to retrieve all the users as well as specific users by their ID from the database using Drizzle’s query-building capabilities. As you can see, Drizzle’s query builder syntax is very similar to that of SQL. You can select columns using the select() method and specify the table using the from() method.
For now, we’ve only performed the read operations. Next, we’ll add the routes for creating, updating, and deleting records:
app.post('/users', async (req, res) => {
const { name, email } = req.body;
const user = await db.insert(userSchema).values([{ name, email }]);
res.json(user);
});
app.put('/users/:id', async (req, res) => {
const { id } = req.params;
const { name, email } = req.body;
const user = await db.update(userSchema).set({ name, email }).where(eq(userSchema.id, Number(id)));
res.json(user);
});
app.delete('/users/:id', async (req, res) => {
const { id } = req.params;
const user = await db.delete(userSchema).where(eq(userSchema.id, Number(id)));
res.json(user);
});
Now, we understand how to do basic read operations, so creating, updating, and deleting operations aren’t that far off.
In this example, we defined the routes for a simple CRUD system using Drizzle ORM to access a database. Overall, Drizzle’s query-building capabilities make it relatively simple to perform database operations that are concise and readable.
It’s important to follow best practices when handling user input, like validating and sanitizing the data before sending it to the database. It’s also recommended to manage database connections properly and securely because improper connection management can lead to security vulnerabilities or performance issues.
Learn more →Comparing Drizzle with other ORMs
Drizzle is certainly a fresh and promising ORM that offers a relatively lightweight and flexible query builder, making it a good choice for simple to moderately complex database operations. It provides a good balance between simplicity and customization without sacrificing performance.
Instead of being the all-in-one solution for every kind of database and application, Drizzle’s focus is on providing a solid foundation for easily building database-driven apps. Drizzle functions more like a query-builder than anything else. At the time of writing, the biggest feature it’s missing is support for applying database migrations.
Drizzle vs. Prisma
Despite both being ORMs, Drizzle and Prisma take different approaches to database access. Drizzle focuses on providing a lightweight and flexible query builder, while Prisma is more opinionated and provides a stronger level of abstraction, allowing for easier database migrations and schema management.
Drizzle offers more flexibility when it comes to querying, and it allows you to take a more direct approach to database access. Using Drizzle is similar to using SQL; it’s not hard to perform complex JOINs with Drizzle, but it requires more manual coding than using Prisma.
Another problem occurs when using Prisma in serverless environments where you need to keep server startup time to a minimum. Prisma’s initial setup can take some time because it uses a Rust binary to maintain connections to the database. This can lead to performance issues in serverless environments where resources are limited.
On the other hand, Drizzle is perfect for serverless environments because it is written in TypeScript and doesn’t need to load a Rust binary. You can use regular connection pools in JavaScript and TypeScript. Although it may still require some setup time to initiate, it won’t be as heavy as Prisma’s setup process.
Drizzle vs. TypeORM
TypeORM is one of the most popular JavaScript ORMs. While Drizzle focuses more on syntax, TypeORM focuses more on type annotations and schema management. Both Prisma and TypeORM have the advantage of being able to create and run migrations, which, at the time of writing, Drizzle hasn’t implemented yet.
TypeORM is more lightweight than Prisma, but Drizzle still offers a lighter alternative in terms of query building and database access. One advantage that TypeORM has over Drizzle is that it doesn’t require you to install different drivers for different databases. Like Prisma, TypeORM gives you a proper abstraction layer, which Drizzle doesn’t yet provide fully.
Code comparison: Drizzle, TypeORM, and Prisma
You can check out the code repository for a comparison between Drizzle, Prisma, and TypeORM in terms of code syntax and ease of use within a small project. Although this isn’t a comprehensive comparison, it should give you an idea of how each ORM works and which one might be best suited for your project.
Is Drizzle ORM production ready?
There are a lot of ORM tools available to choose from, and the number of options is increasing all the time. For TypeScript developers, Prisma has been the go-to ORM for a long time because many developers recognize its strong focus on type safety and ease of use. However, Drizzle is a viable alternative to type safe ORMs like Prisma.
At the time of writing, Drizzle is still very new, so there is much to be desired. However, it is already usable in a production environment for several relational databases. And, if you’re familiar with any notable databases like MySQL or Postgres, you can use Drizzle confidently.
Conclusion
Drizzle offers a lightweight alternative to other ORMs like Prisma and TypeORM. Although it is still relatively new and lacks some advanced features, like migrations, it is already usable in a production environment for several relational databases. If you’re a TypeScript developer looking for a lightweight ORM with a focus on query building and ease of use, Drizzle could be the perfect option for you.
I hope you enjoyed this article, and be sure to leave a comment if you have any questions.
LogRocket: Full visibility into your web and mobile apps
LogRocket is a frontend application monitoring solution that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.
In addition to logging Redux actions and state, LogRocket records console logs, JavaScript errors, stacktraces, network requests/responses with headers + bodies, browser metadata, and custom logs. It also instruments the DOM to record the HTML and CSS on the page, recreating pixel-perfect videos of even the most complex single-page and mobile apps.
Try it for free.
