Many-to-Many joins results in weird values

Hey, I am new to Prisma, switching here from prisma so I can use a typesafe orm with D1 properly. I am trying to return a many-to-many joined result:

  const teamedUser = await
    .innerJoin(users, eq(usersToTeams.user,
    .innerJoin(teams, eq(,

This already returns a correct structure:
    usersToTeams: { user: 4, team: 2 },
    users: {
      id: 2,
      email: '<email>',
      password: '<password-hash>',
      name: '<this field is undefined, but weirdly it puts in the users email>'
    teams: { id: undefined, name: undefined }

The weird things are:
- the name field of the user is not set (it's undefined in reality, if I use sqlite to look at the row), but my (maybe wrong) query fills it with the user's email??
- the teams object has only undefined values in it even though id as well as name are set
- I'd like to not select the password field of the user, how can I do that? I tried looking up partial selects in combination with joined fields, but nothing that I tried worked

I'd really appreciate help πŸ™‚ drizzle really looks cool, I just have to learn a few more things, I guess.

PS: if that matters, I am using the sqlite / D1 variant of drizzle πŸ™‚
ASAndrii Sherman5/9/2023
I'll ask about this on the cloudflare discord then, thanks a lot, Andrew!
@Andrew Sherman
The data is transferred over JSON from D1 back to your worker, and it's impossible for there to be 2 identical JSON keys in an object. I'd recommend you use SELECT "users".id as user_id, "posts".id as post_id or something like that instead

This is what a cloudflare dev said. It makes sense, SQL does return a flat key value array, right? So it's normal that there can't be two with the same name, it's just that we think it would be possible due to using nested jsons.
ASAndrii Sherman5/9/2023
@Dan Kochetov @alexblokh
ASAndrii Sherman5/9/2023
Yes, we are getting row array of arrays and then map them to the keys
ASAndrii Sherman5/9/2023
better-sqlite is working this way, and all other drivers I guess
ASAndrii Sherman5/9/2023
even d1 was working this way some time ago
As someone with more experience, What's your recommendation? Should I just change my schema and rename the columns or do you think this will resolve soon?
well, give me a sec
Okay, tell me if I can help somehow or if you have anything πŸ™‚
import { eq, sql, getTableColumns} from "drizzle-orm";

const {id: userId, ...userRest} = getTableColumns(user)
const {id: teamId, ...teamRest} = getTableColumns(team)

  user: {
    userId: sql`${userId}`.as("user_id"),
  team: {
    teamId: sql`${teamId}`.as("team_id"),
  .innerJoin(user, eq(usersToTeams.userId,
  .innerJoin(team, eq(usersToTeams.teamId,
well, that should work
but that should definitely be fixed on the wrangler side
Thanks so much Alex! I'm trying to understand it and try it and I'll come back to report
I was able to get it working with that, I could even reduce the usage of this elongated syntax to only one of the "ambidiguous" fields, so I only needed to modify how I selected the team object, not the user object.

The one thing I am not sure about is, what does the "user_id" string referring to, is it just an arbitrary unique placeholder that's used to write down the column value?
as("user_id") is for "user"."id" as user_id in SQL
Sorry for late response:

I am not that sure if this is something that cloudflare will change. It seem like the dev was certain that better-sqlite3 wouldn't have this problem for example
You seem to be certain tho that drizzle is doing it like better-sqlite3. And I am not at all educated about the inner workings of this so I can't really argue, but maybe it would make sense for someone from the drizzle team to write into the d1 channel on cloudflares discord to clarify
please give me a link to the thread
I saw they responded this:
Does that make sense and can we as drizzle-users try that out or is that something you guys would need to change?