DT
Drizzle Team•4w ago
OlegBezr

Common Fields Logic For Multiple Tables

I have a set of tables inheriting the same set of columns: 
export const base = {
  id: text()
    .primaryKey()
    .$defaultFn(() => Crypto.randomUUID()),
  local_created_at: integer({ mode: "timestamp_ms" })
    .notNull()
    .$defaultFn(() => new Date()),
  local_updated_at: integer({ mode: "timestamp_ms" })
    .notNull()
    .$defaultFn(() => new Date())
    .$onUpdateFn(() => new Date()),
  is_deleted: integer({ mode: "boolean" }).notNull().default(false),
  synched: integer({ mode: "boolean" }).notNull(),
}

export const table1 = {
  ...base,
  some_custom_fields...
}

export const table2 = {
  ...base,
  some_custom_fields...
}
export const base = {
  id: text()
    .primaryKey()
    .$defaultFn(() => Crypto.randomUUID()),
  local_created_at: integer({ mode: "timestamp_ms" })
    .notNull()
    .$defaultFn(() => new Date()),
  local_updated_at: integer({ mode: "timestamp_ms" })
    .notNull()
    .$defaultFn(() => new Date())
    .$onUpdateFn(() => new Date()),
  is_deleted: integer({ mode: "boolean" }).notNull().default(false),
  synched: integer({ mode: "boolean" }).notNull(),
}

export const table1 = {
  ...base,
  some_custom_fields...
}

export const table2 = {
  ...base,
  some_custom_fields...
}
I want to define a base class with common functionality for these two tables. It should look something like that: 
export class BaseRepository<
  Schema extends SQLiteTable<TableConfig> & {
    id: AnySQLiteColumn & IndexColumn;
    local_created_at: AnySQLiteColumn;
    local_updated_at: AnySQLiteColumn;
    is_deleted: AnySQLiteColumn;
    synched: AnySQLiteColumn;
  }, {
  protected schema: Schema;

  constructor(schema: Schema) {
    this.schema = schema;
  }

  async save(payload: InferInsertModel<Schema>) {
    const results = await db
      .insert(this.schema)
      .values(payload)
      .onConflictDoUpdate({
        target: this.schema.id,
        set: payload,
      })
      .returning();

    return results?.[0];
  }

  async getAllNotSynced() {
    return await db
      .select()
      .from(this.schema)
      .where(eq(this.schema.synched, false));
  }

  other_cool_helper_functions...
}
export class BaseRepository<
  Schema extends SQLiteTable<TableConfig> & {
    id: AnySQLiteColumn & IndexColumn;
    local_created_at: AnySQLiteColumn;
    local_updated_at: AnySQLiteColumn;
    is_deleted: AnySQLiteColumn;
    synched: AnySQLiteColumn;
  }, {
  protected schema: Schema;

  constructor(schema: Schema) {
    this.schema = schema;
  }

  async save(payload: InferInsertModel<Schema>) {
    const results = await db
      .insert(this.schema)
      .values(payload)
      .onConflictDoUpdate({
        target: this.schema.id,
        set: payload,
      })
      .returning();

    return results?.[0];
  }

  async getAllNotSynced() {
    return await db
      .select()
      .from(this.schema)
      .where(eq(this.schema.synched, false));
  }

  other_cool_helper_functions...
}
How do I do so properly to avoid maximum of the TypeScript pain? So far can't figure out a clean solution.
3 Replies
JustWayne
JustWayne•3w ago
I found out that it's probably not a good idea to share the same instance of a column definition e.g. don't do this const shared_cols = { col1: integer(), col2: text(), }; const my_table = pgTable("some_table", { ...shared_cols }); because pgTable() modifies those instances of the columns. So instead you should do function shared_cols() { return { col1: integer(), col2: text(), }; } and here's one example of my usage of that: 
/**
 * Shared column builders for revision tracking.
 * @example
 * const my_table = pgTable("my_table", { id: text().primaryKey(), ...tracking.create() })
 */
const tracking = {
  /** Columns to track Create. */
  create() {
    return {
      created_at: timestampCreate(),
      created_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
    } as const;
  },
  /** Columns to track Create and Update. */
  createUpdate() {
    return {
      created_at: timestampCreate(),
      updated_at: timestampUpdate(),
      created_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
      updated_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
    } as const;
  },
  /** Columns to track Create, Update and (Soft) Delete. */
  createUpdateDelete() {
    return {
      created_at: timestampCreate(),
      updated_at: timestampUpdate(),
      deleted_at: timestampTz(),
      created_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
      updated_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
      deleted_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
    } as const;
  },
} as const;
/**
 * Shared column builders for revision tracking.
 * @example
 * const my_table = pgTable("my_table", { id: text().primaryKey(), ...tracking.create() })
 */
const tracking = {
  /** Columns to track Create. */
  create() {
    return {
      created_at: timestampCreate(),
      created_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
    } as const;
  },
  /** Columns to track Create and Update. */
  createUpdate() {
    return {
      created_at: timestampCreate(),
      updated_at: timestampUpdate(),
      created_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
      updated_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
    } as const;
  },
  /** Columns to track Create, Update and (Soft) Delete. */
  createUpdateDelete() {
    return {
      created_at: timestampCreate(),
      updated_at: timestampUpdate(),
      deleted_at: timestampTz(),
      created_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
      updated_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
      deleted_by: text().references((): AnyPgColumn => users.id, {
        onDelete: "set null",
      }),
    } as const;
  },
} as const;
Regarding the use of AnyPgColumn - this has solved some TypeScript issues whereby the foreign key fields (created_by, etc) were not being typed correctly due to the reference. Also, using as const I think helps TypeScript to know that the field types are stable and won't change. Other than that, I use types like PgTable and others when I have functions that must work with multiple tables: 
// #region Table shapes

export type TblIdOnly = PgTable & Record<"id", AnyPgColumn>;
export type TblIdTrackRemoval = PgTable &
  Record<"id" | "deleted_by" | "deleted_at", AnyPgColumn>;
export type TblIdTrackAll = PgTable &
  Record<
    | "id"
    | "created_at"
    | "created_by"
    | "deleted_at"
    | "deleted_by"
    | "updated_at",
    AnyPgColumn
  >;
export type TblIdTrackMaybe = PgTable &
  (
    | Record<"id", AnyPgColumn>
    | (Record<"id", AnyPgColumn> &
        Partial<
          Record<
            | "created_at"
            | "created_by"
            | "deleted_at"
            | "deleted_by"
            | "updated_at",
            AnyPgColumn
          >
        >)
  );
// #endregion
// #region Table shapes

export type TblIdOnly = PgTable & Record<"id", AnyPgColumn>;
export type TblIdTrackRemoval = PgTable &
  Record<"id" | "deleted_by" | "deleted_at", AnyPgColumn>;
export type TblIdTrackAll = PgTable &
  Record<
    | "id"
    | "created_at"
    | "created_by"
    | "deleted_at"
    | "deleted_by"
    | "updated_at",
    AnyPgColumn
  >;
export type TblIdTrackMaybe = PgTable &
  (
    | Record<"id", AnyPgColumn>
    | (Record<"id", AnyPgColumn> &
        Partial<
          Record<
            | "created_at"
            | "created_by"
            | "deleted_at"
            | "deleted_by"
            | "updated_at",
            AnyPgColumn
          >
        >)
  );
// #endregion
e.g. 
/** Selects rows matching an array of ids or a single id. */
export async function getMany<
  D extends DbClient,
  T extends TblIdOnly | TblIdTrackRemoval,
  S extends () => SelectedFields<PgColumn, PgTable>,
>(
  db: D,
  table: T,
  params: {
    list: Required<Pick<ListOptions, "id">>;
    select: S;
    /** Custom filter condition handlers. */
    filterHandlers?: FilterHandlers<keyof T["_"]["columns"] & string>;
    /** AND conditions to apply. */
    where?: FilterConditions;
  },
) {
  const {
    select: selection,
    list: { id },
    filterHandlers,
    where,
  } = params;
  const conditions: Conditions = [];
  if (Array.isArray(id)) {
    conditions.push(inArray(table.id, id));
  } else {
    conditions.push(eq(table.id, id));
  }
  if ("deleted_at" in table) {
    conditions.push(isNull(table.deleted_at));
  }
  if (where) {
    buildConditions(table, where, conditions, filterHandlers);
  }
  const items = (await db
    .select(selection())
    .from(table as any)
    .where(and(...conditions))) as SelectResult<ReturnType<S>, "single", {}>[];
  const total = items.length;
  return {
    total,
    items,
  };
}
/** Selects rows matching an array of ids or a single id. */
export async function getMany<
  D extends DbClient,
  T extends TblIdOnly | TblIdTrackRemoval,
  S extends () => SelectedFields<PgColumn, PgTable>,
>(
  db: D,
  table: T,
  params: {
    list: Required<Pick<ListOptions, "id">>;
    select: S;
    /** Custom filter condition handlers. */
    filterHandlers?: FilterHandlers<keyof T["_"]["columns"] & string>;
    /** AND conditions to apply. */
    where?: FilterConditions;
  },
) {
  const {
    select: selection,
    list: { id },
    filterHandlers,
    where,
  } = params;
  const conditions: Conditions = [];
  if (Array.isArray(id)) {
    conditions.push(inArray(table.id, id));
  } else {
    conditions.push(eq(table.id, id));
  }
  if ("deleted_at" in table) {
    conditions.push(isNull(table.deleted_at));
  }
  if (where) {
    buildConditions(table, where, conditions, filterHandlers);
  }
  const items = (await db
    .select(selection())
    .from(table as any)
    .where(and(...conditions))) as SelectResult<ReturnType<S>, "single", {}>[];
  const total = items.length;
  return {
    total,
    items,
  };
}
Oh yeah and casting the result is often, but not always, necessary in these types of functions e.g. as SelectResult<ReturnType<S>, "single", {}>[]
OlegBezr
OlegBezrOP•3w ago
@JustWayne Thank you a ton! This helped a lot. Not sure about
it's probably not a good idea to share the same instance of a column definition
Official drizzle docs recommend this approach. So far I didn't have any issues with it: https://orm.drizzle.team/docs/sql-schema-declaration Here's some code I ended up writing (SQLite): 
export type BaseTable = SQLiteTable &
  Record<
    "id" | "local_created_at" | "local_updated_at" | "is_deleted" | "synched",
    AnySQLiteColumn
  >;

export class BaseRepository<T extends BaseTable> {
  protected baseTable: T;

  constructor(baseTable: T) {
    this.baseTable = baseTable;
  }

  async getAll(includeDeleted = false) {
    if (includeDeleted) {
      return await db.select().from(this.baseTable);
    }
    return await db
      .select()
      .from(this.baseTable)
      .where(eq(this.baseTable.is_deleted, false));
  }

  async getById(id: string) {
    const sqlResult = await db
      .select()
      .from(this.baseTable)
      .where(
        and(eq(this.baseTable.id, id), eq(this.baseTable.is_deleted, false))
      )
      .limit(1);
    if (isEmpty(sqlResult)) return undefined;
    return sqlResult[0];
  }

  async getNotSynced() {
    return await db
      .select()
      .from(this.baseTable)
      .where(eq(this.baseTable.synched, false));
  }

  // Local insert always creates a new record with synched set to false
  async createLocal(data: InferInsertModel<T>) {
    const sqlResult = await db
      .insert(this.baseTable)
      .values({ ...data, synched: false })
      .returning();
    if (isEmpty(sqlResult)) return undefined;
    return sqlResult[0];
  }
  
  ...
}
export type BaseTable = SQLiteTable &
  Record<
    "id" | "local_created_at" | "local_updated_at" | "is_deleted" | "synched",
    AnySQLiteColumn
  >;

export class BaseRepository<T extends BaseTable> {
  protected baseTable: T;

  constructor(baseTable: T) {
    this.baseTable = baseTable;
  }

  async getAll(includeDeleted = false) {
    if (includeDeleted) {
      return await db.select().from(this.baseTable);
    }
    return await db
      .select()
      .from(this.baseTable)
      .where(eq(this.baseTable.is_deleted, false));
  }

  async getById(id: string) {
    const sqlResult = await db
      .select()
      .from(this.baseTable)
      .where(
        and(eq(this.baseTable.id, id), eq(this.baseTable.is_deleted, false))
      )
      .limit(1);
    if (isEmpty(sqlResult)) return undefined;
    return sqlResult[0];
  }

  async getNotSynced() {
    return await db
      .select()
      .from(this.baseTable)
      .where(eq(this.baseTable.synched, false));
  }

  // Local insert always creates a new record with synched set to false
  async createLocal(data: InferInsertModel<T>) {
    const sqlResult = await db
      .insert(this.baseTable)
      .values({ ...data, synched: false })
      .returning();
    if (isEmpty(sqlResult)) return undefined;
    return sqlResult[0];
  }
  
  ...
}
Drizzle ORM - Schema
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
JustWayne
JustWayne•3w ago
Yeah, sorry I meant to say: For the style of declarations that I use wherein I don't supply the field name twice. E.g. My whole codebase is field_name: text(), ... instead of field_name: text("field_name"), ... like you find in the docs - you can totally share your field declaration objects if you don't mind double-entering the field names. Otherwise, the object created by text() will only retain the last field name that it was applied for. That was actually a rule that I made for myself after observing the incorrect field names being applied in the meta-data structures when I was doing something like const some_table = pgTable("some_table", { xyz_field: shared.abc_field }); (which I don't do anymore anyway, because it causes the meta-data available from abc_field: text() to have the field name xyz_field) However, that subtle difference led me to say "I'm probably better off not sharing the same field declaration object meta-data in general because you never know when it might bite you in the ayy"

Did you find this page helpful?