How to setup Drizzle ORM in Node.js with Typescript and Perform CRUD operation
Last updated on
Drizzle ORM is a new ORM for javascript and typescript based applications which is an alternative to Prisma.
It takes different approach than Prisma and it has a lot of advantages over prisma such as support for serverless and edge environment, faster than prisma, SQL like syntax with relational syntax too and many others…
Even though it has not reached stable status yet, you can use it in your side project or new projects too. The team is working very hard and they are awesome.
In this step by step tutorial we will implement the complete CRUD functionality using Drizzle ORM. I have not built any views or frontend for this project. I will try all the functionality in Postman.
I have used MySQL (locally) as a database to use with drizzle orm. You can use other databases too like PosgreSQL or Sqlite. There would slight change in installation and setup part. So please refer to documentation. Here is repo for this project
Step 1: Setting up express app
I have basically setup basic node.js app with all the routes and controllers setup. Download the starter file.
Let’s discuss about the folder structure.
The entry point is src/index.ts. I have initialised the express app with some middlewares and configured the routes.
In routes folder their is only one file i.e users.ts which contains all the routes.
In controllers, I have made different controllers for each operation.
Step 2: Setting up Database
I am using local mysql database for this project. Create a database with any name I am creating drizzlenode database.
Here how to create it via terminal
mysql -u username -p
create database drizzlenode;
You can verified it is created or not by running command. You will see all the databases.
show databases;
Now create a .env file in root folder
DB_URL="mysql://username:password@localhost:3306/drizzlenode"
# WINDOWS
DB_URL="mysql://username:[email protected]:3306/drizzlenode"
Step 3: Setting up Drizzle ORM
Step 3.1: Start setting up drizzle ORM by installing ORM and Drizzle Kit
npm install drizzle-orm mysql2
npm install drizzle-kit -D
In src directory make a db folder and inside db create a setup.ts file
Let setup drizzle orm with mysql
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2";
if (!process.env.DB_URL) {
throw new Error("DB credentials error");
}
const connection = mysql.createConnection(process.env.DB_URL);
export const db = drizzle(connection);
Now lets create schema. It is equivalent to prisma schema. The difference is that in drizzle orm we write schema in typescript syntax.
Create schema.ts in db folder.
import { bigint, mysqlTable, timestamp, varchar } from "drizzle-orm/mysql-core";
export const users = mysqlTable("users", {
id: bigint("id", { mode: "number", unsigned: true })
.autoincrement()
.primaryKey(),
name: varchar("name", { length: 256 }).notNull(),
email: varchar("email", { length: 256 }).notNull().unique(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull(),
});
This is a basic schema. I am creating a table users and it will have 5 columns. id which will be incremented automatically and it is also a primary key. Alternatively you can use serial but it has some issues.
name field which is of type varchar and it cannot be null. email field is a unique field and also cannot have null values. createdAt and updatedAt field will be populated automatically.
Now let’s create a migration and also push the changes to database because until now our database doesn’t know about the schema. For this we will use drizzle-kit which we have already installed as a developement dependency.
Step 3.2: Migrations
Let start by creating a drizzle config file.
Create drizzle.config.ts in root folder.
import { defineConfig } from "drizzle-kit";
if (!process.env.DB_URL) {
throw new Error("DB URL is missing");
}
export default defineConfig({
schema: "./src/db/schema.ts",
out: "./src/db/migrations",
dbCredentials: {
url: process.env.DB_URL,
},
dialect: "mysql",
});
Now let’s add 2 scripts in package.json to create generate and migrate changes to the database.
"scripts": {
"dev": "nodemon src/index.ts",
"build": "tsc",
"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate",
},
Now run generate command which will generate the migrations in our db folder. It will map the typescript schema into SQL declaration.
npm run db:generate
Output:
Now let’s run the migrate command to
npm run db:migrate
Output:
Now our database is in sync with out let perform the CRUD operation.
NOTE: Every time you make changes to schema you have to perform generate and push step.
Step 4: CRUD Operation
Step 4.1 Create Operation
Open the src/controllers/createrUser.ts file
To insert first we will have to import the db which we intialised in db/setup.ts. And with that db we will call the insert method which takes table name as an arguement and call values method in which we will pass the values.
import { db } from "../../db/setup";
import { users } from "../../db/schema";
...
await db.insert(users).values({ name: name, email: email });
Here is the complete createUser.ts code
import { Request, Response } from "express";
import { users } from "../../db/schema";
import { db } from "../../db/setup";
const createUser = async (req: Request, res: Response) => {
const { name, email }: { name: string; email: string } = req.body;
// basic validation
if (!name || typeof name !== "string" || name.trim().length === 0) {
return res
.status(400)
.json({ error: "validation_error", message: "Name is required" });
}
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!email || typeof email !== "string" || !emailRegex.test(email.trim())) {
return res.status(400).json({
error: "validation_error",
message: "Email is missing or invalid",
});
}
try {
await db.insert(users).values({ name: name.trim(), email: email.trim() });
return res.status(201).json({
message: "User added successfully",
});
} catch (error) {
console.log("Error while creating user", error);
return res
.status(500)
.json({ error: "server_error", message: "Unable to add" });
}
};
export default createUser;
Send request from your frontend or postman and pass 2 values name and email. POST REQUEST TO http://localhost:5000/users
Output:
Step 4.2 Read Operation
Step 4.2.1 Read All Users
Open the src/controllers/getUsers.ts file
In this again we will import both the db and user schema and make use of select method and get it from the desired table.
import {db} from "../../db/setup";
import {users} from"../../db/schema";
...
await db.select().from(users);
Here is the complete getUsers.ts code
import { Request, Response } from "express";
import { db } from "../../db/setup";
import { users } from "../../db/schema";
const getUsers = async (req: Request, res: Response) => {
try {
const allUsers = await db.select().from(users);
return res.status(200).json({ length: allUsers.length, data: allUsers });
} catch (error) {
console.log("Error while getting users", error);
return res
.status(500)
.json({ error: "server_error", message: "Internal Server Error" });
}
};
export default getUsers;
Send GET request to http://localhost:5000/users
Output:
Step 4.2.1 Get user by id
Open the src/controllers/getUser.ts file
import {db} from "../../db/setup";
import {users} from "../../db/schema";
...
await db.select().from(users).where(eq(users.id,Number(userId)));
Here is the complete getUser.ts code
import { Request, Response } from "express";
import { db } from "../../db/setup";
import { users } from "../../db/schema";
import { eq } from "drizzle-orm";
const getUser = async (req: Request, res: Response) => {
const { userId } = req.params;
if (!userId) {
return res
.status(400)
.json({ error: "missing_parameter", message: "userId is required" });
}
try {
const userById = await db
.select()
.from(users)
.where(eq(users.id, Number(userId)));
if (userById.length < 1) {
return res.status(404).json({ message: "User not found" });
}
return res.status(200).json({ data: userById[0] });
} catch (error) {
console.log("Error while fetching user", error);
return res
.status(500)
.json({ error: "server_error", message: "Internal Server Error" });
}
};
export default getUser;
Send GET request to http://localhost:5000/users/{id}
Output:
Step 4.3 Update Operation
Drizzle ORM provides a update method to update the table
import { db } from "../../db/setup";
import { users } from "../../db/schema";
...
await db.update(users).set({name: "new name", email : "[email protected]"}).where(eq(users.id, Number(userId)));
import { Request, Response } from "express";
import { db } from "../../db/setup";
import { users } from "../../db/schema";
import { eq } from "drizzle-orm";
const updateUser = async (req: Request, res: Response) => {
const { name, email }: { name: string; email: string } = req.body;
const { userId } = req.params;
try {
if (!userId) {
return res
.status(400)
.json({ success: false, message: "Please provide user_id to update" });
}
if (!name && !email) {
return res
.status(400)
.json({ success: false, message: "Please provide field to update" });
}
if ((name && typeof name !== "string") || name?.trim().length === 0) {
return res
.status(400)
.json({ error: "validation_error", message: "Name is required" });
}
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (
(email && typeof email !== "string") ||
email.trim().length === 0 ||
!emailRegex.test(email.trim())
) {
return res
.status(400)
.json({ error: "validation_error", message: "Email is required" });
}
const updateData: { name?: string; email?: string } = {};
if (name) {
updateData.name = name.trim();
}
if (email) {
updateData.email = email.trim();
}
await db
.update(users)
.set(updateData)
.where(eq(users.id, Number(userId)));
return res.status(200).json({ message: "User updated successfully" });
} catch (error) {
console.log("Error while updating user", error);
return res
.status(500)
.json({ error: "server_error", message: "Internal Server Error" });
}
};
export default updateUser;
Send PATCH request to http://localhost:5000/users/{id}
Output:
Step 4.3 Delete Operation
Drizzle ORM provides a delete method to delete the column the table by unique identified (here I am using id. Email field can also be used)
import { db } from "../../db/setup";
import { users } from "../../db/schema";
...
await db.delete(users).where(eq(users.id, Number(userId)));
import { Request, Response } from "express";
import { db } from "../../db/setup";
import { users } from "../../db/schema";
import { eq } from "drizzle-orm";
const deleteUser = async (req: Request, res: Response) => {
const { userId } = req.params;
if (!userId) {
return res
.status(400)
.json({ error: "missing_parameter", message: "userId is required" });
}
try {
await db.delete(users).where(eq(users.id, Number(userId)));
return res.status(200).json({ message: "User deleted successfully" });
} catch (error) {
console.log("Error while deleting user", error);
return res
.status(500)
.json({ error: "server_error", message: "Internal Server Error" });
}
};
export default deleteUser;
Send DELETE request to http://localhost:5000/users/{id}
Output:
Conclusion
So we have implement CRUD operation with Drizzle ORM on Node.js using TypeScript and MySQL. Here is the repo. Please refer this if you’re getting any error.
Please let me know if you find any difficulty. You can connect with me on discord and twitter.