DT
Drizzle Team•2mo ago
hachikuku

Code review, building a generic DAO for DB ops

Hi all, for my SvelteKit application I was building a generic DAO for common CRUD operations as below 
import * as schema from "$lib/server/db/schemas/index";
import { eq } from "drizzle-orm";
import type { BetterSQLite3Database } from "drizzle-orm/better-sqlite3";
import type { SQLiteColumn, SQLiteTable } from "drizzle-orm/sqlite-core";

type Database = BetterSQLite3Database<typeof schema>;

type TableWithId = SQLiteTable & { id: SQLiteColumn };

export class BaseDao<T extends TableWithId> {
  constructor(
    protected db: Database,
    protected table: T,
  ) {}

  async create(data: T["$inferInsert"]) {
    const result = await this.db.insert(this.table).values(data).returning();
    return result[0] as T["$inferSelect"];
  }

  async findById(id: T["$inferSelect"]["id"]) {
    return this.db
      .select()
      .from(this.table)
      .where(eq(this.table.id, id))
      .then((result) => result[0]) as Promise<T["$inferSelect"] | undefined>;
  }

  async update(id: T["$inferSelect"]["id"], data: Partial<T["$inferInsert"]>) {
    const result = await this.db
      .update(this.table)
      .set(data)
      .where(eq(this.table.id, id))
      .returning();
    return result[0] as T["$inferSelect"];
  }

  async delete(id: T["$inferSelect"]["id"]) {
    const result = await this.db
      .delete(this.table)
      .where(eq(this.table.id, id))
      .returning();
    return result[0] as T["$inferSelect"];
  }

  async findAll() {
    return this.db.select().from(this.table) as Promise<T["$inferSelect"][]>;
  }
}
import * as schema from "$lib/server/db/schemas/index";
import { eq } from "drizzle-orm";
import type { BetterSQLite3Database } from "drizzle-orm/better-sqlite3";
import type { SQLiteColumn, SQLiteTable } from "drizzle-orm/sqlite-core";

type Database = BetterSQLite3Database<typeof schema>;

type TableWithId = SQLiteTable & { id: SQLiteColumn };

export class BaseDao<T extends TableWithId> {
  constructor(
    protected db: Database,
    protected table: T,
  ) {}

  async create(data: T["$inferInsert"]) {
    const result = await this.db.insert(this.table).values(data).returning();
    return result[0] as T["$inferSelect"];
  }

  async findById(id: T["$inferSelect"]["id"]) {
    return this.db
      .select()
      .from(this.table)
      .where(eq(this.table.id, id))
      .then((result) => result[0]) as Promise<T["$inferSelect"] | undefined>;
  }

  async update(id: T["$inferSelect"]["id"], data: Partial<T["$inferInsert"]>) {
    const result = await this.db
      .update(this.table)
      .set(data)
      .where(eq(this.table.id, id))
      .returning();
    return result[0] as T["$inferSelect"];
  }

  async delete(id: T["$inferSelect"]["id"]) {
    const result = await this.db
      .delete(this.table)
      .where(eq(this.table.id, id))
      .returning();
    return result[0] as T["$inferSelect"];
  }

  async findAll() {
    return this.db.select().from(this.table) as Promise<T["$inferSelect"][]>;
  }
}
pretty new to Drizzle so was wondering if this approach seems alright, feel like typing could be improved, would really appreciate any guidance. Cheers!
3 Replies
hachikuku
hachikukuOP•2mo ago
anyone?
dfrn
dfrn•2mo ago
This seems pretty similar to the native query functionality. Is there something I am missing?
JustWayne
JustWayne•2mo ago
This is similar to what I'm doing as far as the generic types extending from TableWithId. I'm not using classes though and I'm not always returning the default selection so I have to cast to SelectResult<R, "single", {}>[] and also sometimes I cannot even pass a table to from() without casting it as any .from(table as any) - https://gist.github.com/waynesbrain/8b295a2d359c97ae05fc132e071c9ad5

Did you find this page helpful?