Drizzle ORM Cheat Sheet

index.ts

import { asc, eq, gte, lte } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/mysql2';
import { createPool } from 'mysql2';
import * as schema from './schema';
import { comments, users } from './schema';

const connection = createPool({
	host: '...',
	user: '...',
	password: '...',
	database: '...'
});

const db = drizzle(connection, { schema });

// SELECT
await db.query.users.findFirst();
await db.query.users.findMany({
	offset: 3,
	limit: 5,
	orderBy: asc(users.name),
	where: gte(users.id, 2n), // select filters
	columns: { name: true }, // partial fields select
	// include relations
	with: {
		posts: true,
		comments: {
			columns: { userId: false },
			where: lte(comments.postId, 3n)
			// ...
		}
	}
});

// INSERT
await db.insert(users).values({ name: 'A' });
await db.insert(users).values([{ name: 'B' }, { name: 'C' }]);

// UPDATE
await db.update(users).set({}).where(eq(users.id, 1n));

// DELETE
await db.delete(users).where(lte(users.id, 3n));

schema.ts

import { relations } from 'drizzle-orm';
import { bigint, mysqlTable, text, varchar } from 'drizzle-orm/mysql-core';

// Define tables

export const users = mysqlTable('users', {
	id: bigint('id', { mode: 'bigint' }).notNull().autoincrement().primaryKey(),
	name: varchar('name', { length: 8 })
});

export const posts = mysqlTable('posts', {
	id: bigint('id', { mode: 'bigint' }).notNull().autoincrement().primaryKey(),
	authorId: bigint('id', { mode: 'bigint' }).references(() => users.id),
	content: text('content')
});

export const comments = mysqlTable('comments', {
	id: bigint('id', { mode: 'bigint' }).notNull().autoincrement().primaryKey(),
	userId: bigint('id', { mode: 'bigint' }).references(() => users.id),
	postId: bigint('id', { mode: 'bigint' }).references(() => posts.id),
	content: text('content')
});

// Define relations for relational queries

export const usersRelations = relations(users, ({ many }) => ({
	posts: many(posts),
	comments: many(comments)
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
	author: one(users, {
		fields: [posts.authorId],
		references: [users.id]
	}),
	comments: many(comments)
}));

export const commentsRelations = relations(comments, ({ one, many }) => ({
	post: one(posts, {
		fields: [comments.postId],
		references: [posts.id]
	}),
	authors: many(users)
}));

z.generated.sql

CREATE TABLE `comments` (
	`id` bigint,
	`content` text);
--> statement-breakpoint

CREATE TABLE `posts` (
	`id` bigint,
	`content` text);
--> statement-breakpoint

CREATE TABLE `users` (
	`id` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,
	`name` varchar(8));
--> statement-breakpoint

ALTER TABLE `comments`
  ADD CONSTRAINT `comments_id_users_id_fk` FOREIGN KEY (`id`) REFERENCES `users`
  (`id`) ON DELETE no action ON UPDATE no action;
--> statement-breakpoint

ALTER TABLE `comments`
  ADD CONSTRAINT `comments_id_posts_id_fk` FOREIGN KEY (`id`) REFERENCES `posts`
  (`id`) ON DELETE no action ON UPDATE no action;
--> statement-breakpoint

ALTER TABLE `posts`
  ADD CONSTRAINT `posts_id_users_id_fk` FOREIGN KEY (`id`) REFERENCES `users`(
  `id`) ON DELETE no action ON UPDATE no action;