comparison of drizzle vs kysely

drizzle.ts

import { drizzle } from "drizzle-orm/d1";
import { AutoRouter, type IRequest, error } from "itty-router";
import { eq } from "drizzle-orm";

import { relations } from "drizzle-orm";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const posts = sqliteTable("posts", {
	id: integer("id").primaryKey({ autoIncrement: true }),
	title: text("title"),
	content: text("content"),
});

export const comments = sqliteTable("comments", {
	id: integer("id").primaryKey({ autoIncrement: true }),
	postId: integer("postId"),
	content: text("content"),
});

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

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

type CFArgs = [Env, ExecutionContext];

export const router = AutoRouter<IRequest, CFArgs>();

router
	.get("/posts", async (req, env) => {
		const db = drizzle(env.DB, {
			schema: { comments, posts, commentRelations, postRelations },
		});

		const data = await db.query.posts.findMany({
			with: {
				comments: true,
			},
		});
		return data;
	})
	.get("/post/:id", async (req, env) => {
		const db = drizzle(env.DB, {
			schema: { comments, posts, commentRelations, postRelations },
		});
		const postId = Number(req.params.id);
		if (Number.isNaN(postId)) return error(400, { message: "invalid post ID" });

		const data = await db.query.posts.findFirst({
			where: eq(posts.id, postId),
		});
		return data;
	})
	.delete("/post/:id", async (req, env) => {
		const db = drizzle(env.DB, {
			schema: { comments, posts, commentRelations, postRelations },
		});
		const postId = Number(req.params.id);
		if (Number.isNaN(postId)) return error(400, { message: "invalid post ID" });

		const data = await db.query.posts.findFirst({
			where: eq(posts.id, postId),
		});
		if (!data) return error(404, { message: "post not found" });
		await db.delete(posts).where(eq(posts.id, postId));
		return { status: "ok" };
	});

export default { ...router };

kysely.ts

import { AutoRouter, type IRequest, error } from "itty-router";
import { Kysely, ParseJSONResultsPlugin } from "kysely";
import { D1Dialect } from "kysely-d1";
import { jsonArrayFrom } from "kysely/helpers/sqlite";

interface PostTable {
	id: number
	title: string
	content: string
}

interface CommentTable {
	id: number
	postId: number
	content: string
}

interface Database {
	posts: PostTable
	comments: CommentTable
}

type CFArgs = [Env, ExecutionContext];

export const router = AutoRouter<IRequest, CFArgs>();

router
	.get("/posts", async (req, env) => {
		const db = new Kysely<Database>({
			dialect: new D1Dialect({ database: env.DB }),
			plugins: [new ParseJSONResultsPlugin()],
		});

		const data = await db
			.selectFrom("posts")
			.selectAll()
			.select((eb) => [
				jsonArrayFrom(eb
					.selectFrom("comments")
					.whereRef("comments.postId", "=", "posts.id")
					.select(["comments.id", "comments.content"]))
					.as("comments"),
			])
			.execute();
		return data;
	})
	.get("/post/:id", async (req, env) => {
		const db = new Kysely<Database>({
			dialect: new D1Dialect({ database: env.DB }),
			plugins: [new ParseJSONResultsPlugin()],
		});

		const postId = Number(req.params.id);
		if (Number.isNaN(postId)) return error(400, { message: "invalid post ID" });


		const data = await db
			.selectFrom("posts")
			.selectAll("posts")
			.select((eb) => [
				jsonArrayFrom(eb
					.selectFrom("comments")
					.whereRef("comments.postId", "=", "posts.id")
					.select(["comments.id", "comments.content"]))
					.as("comments"),
			])
			.where("posts.id", "=", postId)
			.limit(1)
			.executeTakeFirst();

		return data;
	})
	.delete("/post/:id", async (req, env) => {
		const db = new Kysely<Database>({
			dialect: new D1Dialect({ database: env.DB }),
			plugins: [new ParseJSONResultsPlugin()],
		});

		const postId = Number(req.params.id)
		if (Number.isNaN(postId))
			return error(400, { message: "invalid post ID" })

		const data = await db
			.selectFrom("posts")
			.selectAll("posts")
			.where("posts.id", "=", postId)
			.limit(1)
			.executeTakeFirst();
		if (!data) return error(404, { message: "post not found" })

		await db.deleteFrom("posts").where("posts.id", "=", postId).execute();
		return { status: "ok" }
	});

export default { ...router };