drizzle-orm
drizzle-orm copied to clipboard
Published
20 hours ago •
drizzle-team
drizzle-team
[BUG]: Nested object in select query
What version of drizzle-orm are you using?
0.29.3
What version of drizzle-kit are you using?
0.20.14
Describe the Bug
My code is
const feedbacks = await drizzleClient
.select({
...getTableColumns(feedbackFirst),
project: {
...getTableColumns(project),
},
userFrom: {
...getTableColumns(users),
userAbout: {
...getTableColumns(userAbout),
avatar: {
...getTableColumns(avatars),
photo: {
...getTableColumns(photos),
}
}
}
}
})
.from(feedbackFirst)
.innerJoin(project, eq(project.prid, feedbackFirst.prid))
.innerJoin(users, eq(users.uid, feedbackFirst.uidFrom))
.innerJoin(userAbout, eq(userAbout.uid, users.uid))
.innerJoin(avatars, eq(avatars.uid, users.uid))
.innerJoin(photos, eq(photos.phid, avatars.phid))
There is an error near avatar
Object literal may only specify known properties, and avatar does not exist in type
Despite of the type error, the query result is correct, with all nesting. How to fix the type error? And is it possible?
Expected behavior
The result has the correct types
Environment & setup
webstorm 2023.3.5 ts 5 node 21.4.0 macos sonoma 14.4
I believe its impossible to tell if this is a bug or not without seeing your drizzle schema file
import { pgTable, pgSchema, foreignKey, pgEnum, bigint, integer, numeric, timestamp, varchar, smallint, uniqueIndex, boolean, bigserial, json, text, real, unique, index, time, date, serial, doublePrecision, primaryKey } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"
export const language = pgEnum("language", ['french', 'spanish', 'russian', 'english'])
export const cldata = pgSchema("cldata");
export const feedbackFirst = cldata.table("feedback_first", {
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
fid: bigint("fid", { mode: "number" }).default(sql`nextval('cldata.s_fid'::regclass)`).primaryKey().notNull(),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
prid: bigint("prid", { mode: "number" }).notNull().references(() => project.prid),
uidFrom: integer("uid_from").references(() => users.uid),
aidTo: integer("aid_to").references(() => artist.aid),
comfort: numeric("comfort"),
focus: numeric("focus"),
details: numeric("details"),
responsible: numeric("responsible"),
comment: varchar("comment", { length: 300 }),
missed: boolean("missed").notNull(),
date: timestamp("date", { precision: 6, withTimezone: true, mode: 'string' }),
},
(table) => {
return {
aidUidPrid: uniqueIndex("aid_uid_prid").on(table.prid, table.uidFrom, table.aidTo),
}
});
export const project = cldata.table("project", {
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
prid: bigint("prid", { mode: "number" }).default(sql`nextval('cldata.s_prid'::regclass)`).primaryKey().notNull(),
uidStarted: integer("uid_started").notNull(),
started: timestamp("started", { precision: 6, withTimezone: true, mode: 'string' }).notNull(),
description: varchar("description", { length: 2000 }),
place: varchar("place", { length: 60 }),
timesheet: varchar("timesheet", { length: 2000 }),
resultslink: varchar("resultslink"),
fromdate: timestamp("fromdate", { precision: 6, withTimezone: true, mode: 'string' }),
todate: timestamp("todate", { precision: 6, withTimezone: true, mode: 'string' }),
fullday: boolean("fullday"),
aid: integer("aid").references(() => artist.aid),
gone: boolean("gone").default(false),
tfp: integer("tfp"),
},
(table) => {
return {
pridFromdateTodate: uniqueIndex("prid_fromdate_todate").on(table.prid, table.fromdate, table.todate, table.gone),
ind2: uniqueIndex("project_ind2").on(table.uidStarted, table.started),
}
});
export const artist = cldata.table("artist", {
aid: integer("aid").default(sql`nextval('cldata.s_aid'::regclass)`).primaryKey().notNull(),
uid: integer("uid").notNull().references(() => users.uid, { onDelete: "restrict" } ),
atype: smallint("atype").notNull().references(() => dAtype.id, { onDelete: "restrict" } ),
hourprice: integer("hourprice").default(0).notNull(),
experience: smallint("experience").notNull().references(() => dExperience.id, { onDelete: "restrict" } ),
tfp: smallint("tfp").notNull().references(() => dTfp.id, { onDelete: "restrict" } ),
haveStudio: smallint("have_studio").default(1).notNull(),
priceList: varchar("price_list", { length: 30000 }),
rating: doublePrecision("rating"),
reviews: smallint("reviews").default(0),
companyName: varchar("company_name"),
active: boolean("active").default(true).notNull(),
comfort: doublePrecision("comfort"),
focus: doublePrecision("focus"),
details: doublePrecision("details"),
responsible: doublePrecision("responsible"),
quality: doublePrecision("quality"),
quantity: doublePrecision("quantity"),
intime: doublePrecision("intime"),
},
(table) => {
return {
uidAid: uniqueIndex("uid_aid").on(table.aid, table.uid),
uidAtype: uniqueIndex("artist_uid_atype").on(table.uid, table.atype),
}
});
export const users = cldata.table("users", {
uid: integer("uid").default(sql`nextval('cldata.s_uid'::regclass)`).primaryKey().notNull(),
password: varchar("password").notNull(),
email: varchar("email", { length: 70 }).notNull(),
regtime: timestamp("regtime", { precision: 6, withTimezone: true, mode: 'string' }).notNull(),
authprovider: varchar("authprovider").notNull(),
vkId: varchar("vk_id"),
fbId: varchar("fb_id"),
salt: varchar("salt").notNull(),
userchannel: varchar("userchannel").notNull(),
blocked: smallint("blocked"),
blockedDate: date("blocked_date"),
issup: smallint("issup"),
deviceId: text("device_id"),
isProfileComplete: boolean("is_profile_complete").default(true).notNull(),
},
(table) => {
return {
uidKey: uniqueIndex("users_uid_key").on(table.uid),
emailUnique: unique("email_unique").on(table.email),
userchannelUnique: unique("userchannel_unique").on(table.userchannel),
}
});
export const prResults = cldata.table("pr_results", {
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
id: bigint("id", { mode: "number" }).default(sql`nextval('cldata.s_pr_result'::regclass)`).primaryKey().notNull(),
uid: integer("uid"),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
prid: bigint("prid", { mode: "number" }).references(() => project.prid, { onDelete: "cascade", onUpdate: "cascade" } ),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
phid: bigint("phid", { mode: "number" }),
},
(table) => {
return {
pridPhid: uniqueIndex("prid_phid").on(table.prid, table.phid),
}
});
export const photos = cldata.table("photos", {
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
phid: bigint("phid", { mode: "number" }).default(sql`nextval('cldata.s_phid'::regclass)`).primaryKey().notNull(),
uid: integer("uid").notNull(),
filename: varchar("filename").notNull(),
date: timestamp("date", { precision: 6, withTimezone: true, mode: 'string' }).notNull(),
incloud: boolean("incloud").default(false),
clouduploadtime: timestamp("clouduploadtime", { withTimezone: true, mode: 'string' }),
localdeleted: boolean("localdeleted").default(false).notNull(),
seoFilename: varchar("seo_filename"),
altText: varchar("alt_text"),
caption: varchar("caption"),
keywords: varchar("keywords"),
author: varchar("author"),
credentials: varchar("credentials"),
copyright: varchar("copyright"),
noSeo: boolean("no_seo"),
},
(table) => {
return {
idFilename: index("id_filename").on(table.phid, table.filename),
}
});
export const userAbout = cldata.table("user_about", {
uid: integer("uid").primaryKey().notNull().references(() => users.uid, { onDelete: "restrict" } ),
firstname: varchar("firstname", { length: 30 }).notNull(),
lastname: varchar("lastname", { length: 30 }).notNull(),
legalentity: smallint("legalentity").references(() => dLegalentity.id),
residence: smallint("residence").references(() => dCountry.id, { onDelete: "set null" } ),
passportData: varchar("passport_data", { length: 100 }).default(sql`'null'::character varying`),
enddate: timestamp("enddate", { precision: 6, withTimezone: true, mode: 'string' }),
aboutText: varchar("about_text", { length: 2000 }),
website: varchar("website"),
instagram: varchar("instagram"),
facebook: varchar("facebook"),
vkontakte: varchar("vkontakte"),
primaryLink: varchar("primary_link"),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
adid: bigint("adid", { mode: "number" }).references(() => address.adid, { onDelete: "set null" } ),
birthdate: date("birthdate"),
defaultLang: varchar("default_lang", { length: 16 }),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
avatarPhid: bigint("avatar_phid", { mode: "number" }),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
signPhid: bigint("sign_phid", { mode: "number" }),
phoneCountryCode: smallint("phone_country_code"),
phone: varchar("phone", { length: 32 }),
primaryNameEn: varchar("primary_name_en").notNull(),
gender: smallint("gender").default(1).notNull().references(() => dGender.id),
answeringTime: smallint("answering_time"),
agreeRate: smallint("agree_rate"),
regexpEngName: varchar("regexp_eng_name", { length: 150 }),
phoneconfirm: boolean("phoneconfirm").default(false),
wechat: varchar("wechat"),
whatsapp: varchar("whatsapp"),
youtube: varchar("youtube"),
tiktok: varchar("tiktok"),
specializations: integer("specializations").array(),
lastOnline: timestamp("last_online", { mode: 'string' }),
defaultLangId: integer("default_lang_id").references(() => dLanguages.langId),
},
(table) => {
return {
allUa: uniqueIndex("all_ua").on(table.uid, table.firstname, table.lastname, table.birthdate),
}
});
export const dLegalentity = cldata.table("d_legalentity", {
id: smallint("id").default(sql`nextval('cldata.d_legalentity_id_seq'::regclass)`).primaryKey().notNull(),
nameEn: varchar("name_en").notNull(),
nameRu: varchar("name_ru").notNull(),
},
(table) => {
return {
idKey: uniqueIndex("d_legalentity_id_key").on(table.id),
dLegalentityNameEngKey: unique("d_legalentity_name_eng_key").on(table.nameEn),
dLegalentityNameRusKey: unique("d_legalentity_name_rus_key").on(table.nameRu),
}
});
export const dCountry = cldata.table("d_country", {
nameRu: varchar("name_ru").notNull(),
nameEn: varchar("name_en").notNull(),
id: integer("id").primaryKey().notNull(),
countrycode: varchar("countrycode"),
show: boolean("show"),
phoneCode: varchar("phone_code", { length: 255 }),
defcurrency: smallint("defcurrency"),
},
(table) => {
return {
idKey: uniqueIndex("d_country_id_key").on(table.id),
}
});
export const address = cldata.table("address", {
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
adid: bigint("adid", { mode: "number" }).default(sql`nextval('cldata.s_adid'::regclass)`).primaryKey().notNull(),
uid: integer("uid").notNull().references(() => users.uid),
city: integer("city").notNull().references(() => dCity.cityId),
zipcode: varchar("zipcode"),
exactAddr: varchar("exact_addr", { length: 100 }),
fromDate: timestamp("from_date", { precision: 6, withTimezone: true, mode: 'string' }).default(sql`NULL::timestamp with time zone`),
toDate: timestamp("to_date", { precision: 6, withTimezone: true, mode: 'string' }).default(sql`NULL::timestamp with time zone`),
},
(table) => {
return {
allInd: uniqueIndex("all_ind").on(table.adid, table.uid, table.city, table.fromDate, table.toDate),
}
});
export const dGender = cldata.table("d_gender", {
id: integer("id").default(sql`nextval('cldata.s_gid'::regclass)`).primaryKey().notNull(),
nameEn: varchar("name_en").notNull(),
nameRu: varchar("name_ru").notNull(),
},
(table) => {
return {
idKey: uniqueIndex("d_gender_id_key").on(table.id),
}
});
export const dLanguages = cldata.table("d_languages", {
langId: integer("lang_id").default(sql`nextval('cldata.d_languages_lang_id_seq'::regclass)`).primaryKey().notNull(),
nameRu: varchar("name_ru", { length: 30 }).notNull(),
nameEn: varchar("name_en", { length: 30 }).notNull(),
});
export const avatars = cldata.table("avatars", {
uid: integer("uid").notNull(),
// You can use { mode: "bigint" } if numbers are exceeding js number limitations
phid: bigint("phid", { mode: "number" }).notNull(),
date: timestamp("date", { withTimezone: true, mode: 'string' }),
text: varchar("text"),
},
(table) => {
return {
avatarsPkey: primaryKey({ columns: [table.uid, table.phid], name: "avatars_pkey"})
}
});
export const dAtype = cldata.table("d_atype", {
id: smallint("id").default(sql`nextval('cldata.s_atype'::regclass)`).primaryKey().notNull(),
nameEn: varchar("name_en").notNull(),
nameRu: varchar("name_ru").notNull(),
worktype: boolean("worktype").default(false),
name: json("name"),
description: json("description"),
},
(table) => {
return {
idKey: uniqueIndex("d_atype_id_key").on(table.id),
}
});
export const dExperience = cldata.table("d_experience", {
id: smallint("id").default(sql`nextval('cldata.d_experience_id_seq'::regclass)`).primaryKey().notNull(),
nameEn: varchar("name_en").notNull(),
nameRu: varchar("name_ru").notNull(),
},
(table) => {
return {
idKey: uniqueIndex("d_experience_id_key").on(table.id),
}
});
export const dTfp = cldata.table("d_tfp", {
id: smallint("id").default(sql`nextval('cldata.s_tfp'::regclass)`).primaryKey().notNull(),
nameEn: varchar("name_en").notNull(),
nameRu: varchar("name_ru").notNull(),
},
(table) => {
return {
idKey: uniqueIndex("d_tfp_id_key").on(table.id),
}
});
export const dCity = cldata.table("d_city", {
cityId: integer("city_id").primaryKey().notNull(),
nameRu: varchar("name_ru", { length: 100 }),
nameEn: varchar("name_en", { length: 100 }),
countryId: integer("country_id").references(() => dCountry.id, { onUpdate: "cascade" } ),
latitude: real("latitude"),
longtitude: real("longtitude"),
show: boolean("show"),
population: integer("population"),
},
(table) => {
return {
country: uniqueIndex("d_city_country").on(table.cityId, table.countryId, table.population),
iCityEn: uniqueIndex("i_city_en").on(table.cityId, table.nameRu, table.nameEn, table.countryId, table.latitude, table.longtitude, table.population),
iCityRu: uniqueIndex("i_city_ru").on(table.cityId, table.nameRu, table.countryId, table.latitude, table.longtitude, table.population),
}
});
export const dCountry = cldata.table("d_country", {
nameRu: varchar("name_ru").notNull(),
nameEn: varchar("name_en").notNull(),
id: integer("id").primaryKey().notNull(),
countrycode: varchar("countrycode"),
show: boolean("show"),
phoneCode: varchar("phone_code", { length: 255 }),
defcurrency: smallint("defcurrency"),
},
(table) => {
return {
idKey: uniqueIndex("d_country_id_key").on(table.id),
}
});
Another clarification. The only first-level nested object has no type issues
Your userAbout table doesnt have an avatar property? You have avatarPhid
userAbout doesnt has an avatar prop, only avatarPhid. The avatar prop I want to have in select result. It can be any name
ok that makes sense apologies still getting up to speed with Postgres. But you havent let drizzle know that avatarPhid references an id on the avatar table?
Yes, avatarPhid isnt fk. And I prefer not to use fk in this case. Thats the main reason why I broke up with prisma 🙂
Oh i see so you want it to dynamically generate the typescript based on the query and not the relations you have provided in the drizzle schema... jeez.. afraid I wont be any help
Up for this issue. First level doesn't have type issue... Can't adding nested object inside the first level of object... The query is doing fine, though, despite the type issue.
Found this solution with type-preserve and without subqueries
export function jsonBuildObject<T extends SelectedFields<any, any>>(shape: T) {
const chunks: SQL[] = []
Object.entries(shape).forEach(([key, value]) => {
if (chunks.length > 0) {
chunks.push(sql.raw(`,`))
}
chunks.push(sql.raw(`'${key}',`))
// json_build_object formats to ISO 8601 ...
if (is(value, PgTimestampString)) {
chunks.push(sql`timezone('UTC', ${value})`)
} else {
chunks.push(sql`${value}`)
}
})
return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
chunks
)}), '{}')`
}
const res = await tx
.select({
...getTableColumns(commPhotos),
user: jsonBuildObject({
...getTableColumns(users),
userAbout: jsonBuildObject({
...getTableColumns(userAbout),
avatar: jsonBuildObject({
...getTableColumns(avatars),
photo: jsonBuildObject({
...getTableColumns(photos),
}),
}),
}),
}),
})
.from(commPhotos)
.leftJoin(users, eq(users.uid, commPhotos.uid))
.leftJoin(userAbout, eq(userAbout.uid, users.uid))
.leftJoin(avatars, eq(avatars.phid, userAbout.avatarPhid))
.leftJoin(photos, eq(photos.phid, avatars.phid))
.where(eq(commPhotos.commid, dto.commentId))
.limit(1)
There is well known feature of typeORM that when you use select and joins (not the relational object builder), there is a leftJoinAndSelect which when used will give the nested data between one-many relation etc. Like drizzle provides with its simple findMany but the filters for joined tables are not applied on global where. So it would help if the select can also give us nested data.
Having a look at this, as I'm in the same situation, seems to be purely a types thing, as indeed it does work as expected.
The specific types, emitted in drizzle-orm/operations.d.ts (in 0.29, not sure if that has changed, but the issue persists in 0.32.2) are:
export type SelectedFields<TColumn extends Column, TTable extends Table> = Record<string, SelectedFieldsFlat<TColumn>[string] | TTable | SelectedFieldsFlat<TColumn>>;
If we look at SelectedFieldsFlat it's:
export type SelectedFieldsFlat<TColumn extends Column> = Record<string, TColumn | SQL | SQL.Aliased>;
Which as the name tells, must be a flat record, no nesting. So maybe this is intentional because some of the db adaptors won't support nesting? I'm trying on postgres.
same thing here! Definitely a bummer that the types don't agree, because the query also works for me, but now all my trpc code thinks it's broken. would love to see this implemented!
I asked about this in the drizzle discord too, because in my case I was initially using the column name name and it thought I was referencing the name field on PgColumn
+1, it seems that's impossible to use nesting without the type errors, despite the query goes fine
