table not being camelcased when using joins?

For some reason when I use the following query:

    const getProductByIdResult = await db
      .select()
      .from(product)
      .leftJoin(productSnapshot, eq(product.id, productSnapshot.productId))
      .where(sql`product_id = ${input.productId}`)
      .orderBy(desc(productSnapshot.version))
      .limit(1);

    const getProductByIdResult = await db
      .select()
      .from(product)
      .leftJoin(productSnapshot, eq(product.id, productSnapshot.productId))
      .where(sql`product_id = ${input.productId}`)
      .orderBy(desc(productSnapshot.version))
      .limit(1);

I am geting the following type back:

const getProductByIdResult: {
    product: {
        id: string;
    };
    product_snapshot: {
        alloy: string;
        shape: string;
        material: string;
        productId: string;
        description: string;
        effectiveDate: string | null;
        version: number;
        thickness: number;
        dimension: string;
        weightByFoot: number;
    } | null;
}[]

const getProductByIdResult: {
    product: {
        id: string;
    };
    product_snapshot: {
        alloy: string;
        shape: string;
        material: string;
        productId: string;
        description: string;
        effectiveDate: string | null;
        version: number;
        thickness: number;
        dimension: string;
        weightByFoot: number;
    } | null;
}[]

Why is the name of the table not being camel cased?

8 Replies

Angelelz•15mo ago

Can you show your schema? I believe, by default it gives you back the names you applied in the schema definition

DYELbrah•15mo ago

export const product = pgTable("product", {
    id: text("id").primaryKey().notNull(),
});

export const productSnapshot = pgTable("product_snapshot", {
    productId: text("product_id").notNull().references(() => product.id),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    version: bigint("version", { mode: "number" }).notNull(),
    material: text("material").notNull(),
    shape: text("shape").notNull(),
    alloy: text("alloy").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    thickness: bigint("thickness", { mode: "number" }).notNull(),
    dimension: text("dimension").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    weightByFoot: bigint("weight_by_foot", { mode: "number" }).notNull(),
    description: text("description").notNull(),
    effectiveDate: timestamp("effective_date", { withTimezone: true, mode: 'string' }).defaultNow(),
},
(table) => {
    return {
        idxProductSnapshotEffectiveDate: index("idx_product_snapshot_effective_date").on(table.effectiveDate),
        idxProductSnapshotProductId: index("idx_product_snapshot_product_id").on(table.productId),
        productSnapshotPkey: primaryKey(table.productId, table.version)
    }
});

export const product = pgTable("product", {
    id: text("id").primaryKey().notNull(),
});

export const productSnapshot = pgTable("product_snapshot", {
    productId: text("product_id").notNull().references(() => product.id),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    version: bigint("version", { mode: "number" }).notNull(),
    material: text("material").notNull(),
    shape: text("shape").notNull(),
    alloy: text("alloy").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    thickness: bigint("thickness", { mode: "number" }).notNull(),
    dimension: text("dimension").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    weightByFoot: bigint("weight_by_foot", { mode: "number" }).notNull(),
    description: text("description").notNull(),
    effectiveDate: timestamp("effective_date", { withTimezone: true, mode: 'string' }).defaultNow(),
},
(table) => {
    return {
        idxProductSnapshotEffectiveDate: index("idx_product_snapshot_effective_date").on(table.effectiveDate),
        idxProductSnapshotProductId: index("idx_product_snapshot_product_id").on(table.productId),
        productSnapshotPkey: primaryKey(table.productId, table.version)
    }
});

@angelelz verified that within my schema I don't use snakecase anywhere.

Angelelz•15mo ago

You can always rename the returning object to whatever you want in the select method

const getProductByIdResult = await db
      .select({
          product: product,
          productSnapshot: productSnapshot
      })
      .from(product)
      .leftJoin(productSnapshot, eq(product.id, productSnapshot.productId))
      .where(sql`product_id = ${input.productId}`)
      .orderBy(desc(productSnapshot.version))
      .limit(1);

const getProductByIdResult = await db
      .select({
          product: product,
          productSnapshot: productSnapshot
      })
      .from(product)
      .leftJoin(productSnapshot, eq(product.id, productSnapshot.productId))
      .where(sql`product_id = ${input.productId}`)
      .orderBy(desc(productSnapshot.version))
      .limit(1);

mr_pablo•15mo ago

export const productSnapshot = pgTable("product_snapshot", its right there

DYELbrah•15mo ago

@mr_pablo_85 correct me if I'm wrong but inside the pgTable method, you're supposed to add the actual name of the table in Postgres. Thus using snakecase is fine there (or even preferred since postgres doesn't preserve casing). The same goes with columns. Example from the docs:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'), // Notice the snake case
  phone: varchar('phone', { length: 256 }),
});

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'), // Notice the snake case
  phone: varchar('phone', { length: 256 }),
});

https://orm.drizzle.team/docs/sql-schema-declaration

SQL schema declaration – DrizzleORM

Drizzle ORM | %s

mr_pablo•15mo ago

Possible bug?

DYELbrah•15mo ago

Yeah I think so too

nikhil•11mo ago

any update on this? encountering the same problem and was wondering if there's a way without specifying in the select()

Gaming

Programming

table not being camelcased when using joins?