drizzle-orm: [BUG]: Broken shifted columns with leftJoin and same column name (on D1)

it seems like the types have been released in 4.20240222.0 (with a bit of delay). is there a way to get this issue sort of prioritised because especially with Lucia this is a complete blocker when trying to use D1 and drizzle.

@cyco130 thank you for the reproduction, looks like there’s still an issue on D1 side. I’ve reported it back to their team: https://github.com/cloudflare/workers-sdk/issues/3160#issuecomment-2010837504

It happens with SQLite too. This query:

qb
  .select({
    dailyListId: dailyListsTable.id,
    projectionId: todoProjectionsTable.id,
    todoId: todosTable.id,
    projectId: projectsTable.id,
  })
  .from(dailyListsTable)
  .fullJoin(
    todoProjectionsTable,
    eq(todoProjectionsTable.dailyListId, dailyListsTable.id),
  )
  .fullJoin(todosTable, eq(todosTable.id, todoProjectionsTable.todoId))
  .fullJoin(projectsTable, eq(projectsTable.id, todosTable.projectId)),

Generate with SQL query:

select "dailyLists"."id", "todoProjections"."id", "todos"."id", "projects"."id" from "dailyLists" full join "todoProjections" on "todoProjections"."dailyListId" = "dailyLists"."id" full join "todos" on "todos"."id" = "todoProjections"."todoId" full join "projects" on "projects"."id" = "todos"."projectId"

And due to how SQLite itself run the query, rows returns like this: {id: string}[].

I think the problem is on drizzle side(and aliasing will fix it)

On further testing using select compared to findmany I am not sure if this is a drizzle or D1 issue? I have data tables with some of the same common field column names like create_at, updated_at, id etc

When using select

const userEvents = await db
    .select()
    .from(event)
    .innerJoin(eventInvite, eq(event.id, eventInvite.eventId))
    .innerJoin(user, eq(eventInvite.userId, user.id))
    .where(
      sql`events.id IN (SELECT event_id FROM event_invites WHERE user_id = ${userId})`,
    );

I get the column data results shift bug and users createdAt and updatedAt fields missing

{
    "userEvents": [
      {
        "events": {
          "id": "EventId1",
          "name": "Event One",
          "location": "Location A",
          "createdAt": "2024-01-03 08:58:31",
          "updatedAt": "2024-01-03 09:19:29"
        },
        "event_invites": {
          "eventId": "EventId1",
          "userId": "UserId1",
          "inviteStatus": "No Response",
          "responseDate": null,
          "createdAt": "UserId1",
          "updatedAt": "FirstName1test"
        },
        "users": {
          "id": "LastName1",
          "firstName": "User1",
          "lastName": "user1@example.com",
          "username": "password1"
        }
      },
      {
        "events": {
          "id": "EventId1",
          "name": "Event One",
          "location": "Location A",
          "createdAt": "2024-01-03 08:58:31",
          "updatedAt": "2024-01-03 08:58:31"
        },
        "event_invites": {
          "eventId": "EventId1",
          "userId": "UserId2",
          "inviteStatus": "No Response",
          "responseDate": null,
          "createdAt": "UserId2",
          "updatedAt": "FirstName2"
        },
        "users": {
          "id": "LastName2",
          "firstName": "User2",
          "lastName": "user2@example.com",
          "username": "password2"
        }
      }
    ]
  }

However when using findmany

const invitedEventsSubquery = db
    .select({ data: eventInvite.eventId })
    .from(eventInvite)
    .where(eq(eventInvite.userId, userId));

  const userEvents = await db.query.event.findMany({
    where: inArray(event.id, invitedEventsSubquery),
    with: {
      eventInvite: {
        with: {
          user: true,
        },
      },
    },
  });

I don’t get the bug. Every column is returned where it should be in nicely nested json.

{
    "userEvents": [
      {
        "id": "EventId1",
        "name": "Event One",
        "location": "Location A",
        "createdAt": "2024-01-03 09:35:52",
        "updatedAt": "2024-01-03 09:35:52",
        "eventInvite": [
          {
            "eventId": "EventId1",
            "userId": "UserId1",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId1",
              "firstName": "FirstName1test",
              "lastName": "LastName1",
              "username": "User1",
              "email": "user1@example.com",
              "password": "password1",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 09:19:29"
            }
          },
          {
            "eventId": "EventId1",
            "userId": "UserId2",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId2",
              "firstName": "FirstName2",
              "lastName": "LastName2",
              "username": "User2",
              "email": "user2@example.com",
              "password": "password2",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 08:58:31"
            }
          },
          {
            "eventId": "EventId1",
            "userId": "UserId3",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId3",
              "firstName": "FirstName3",
              "lastName": "LastName3",
              "username": "User3",
              "email": "user3@example.com",
              "password": "password3",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 08:58:31"
            }
          }
        ]
      },
      {
        "id": "EventId3",
        "name": "Event Three",
        "location": "Location C",
        "createdAt": "2024-01-03 09:35:52",
        "updatedAt": "2024-01-03 09:35:52",
        "eventInvite": [
          {
            "eventId": "EventId3",
            "userId": "UserId1",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId1",
              "firstName": "FirstName1test",
              "lastName": "LastName1",
              "username": "User1",
              "email": "user1@example.com",
              "password": "password1",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 09:19:29"
            }
          }
        ]
      }
    ]
  }

The problem is that d1 overwrites columns if they share the same key. As it can be seen here

  const userAndPosts = await db
    .select({
      user: {
        id: users.id, // "users"."id"
      },
      post: {
        id: posts.id, // "posts"."id"
        ownerId: posts.ownerId,
        title: posts.title,
        content: posts.content
      }
    })
    .from(users)
    .leftJoin(posts, eq(posts.ownerId, users.id))
    .all();

Both posts.id and "users"."id" are returned by cloudflare as id meaning that the data is overwritten. A very dirty hack would be to use raw sql statements

  const userAndPosts = await db
    .select({
      user: {
        id: sql<type>`${users.id}`.as('u_id')
      },
      post: {
        id: sql<type>`${posts.id}`.as('p_id'),
        ownerId: posts.ownerId,
        title: posts.title,
        content: posts.content
      }
    })
    .from(users)
    .leftJoin(posts, eq(posts.ownerId, users.id))
    .all();

This works as then d1 does not overwrite the data. However this solution is very ugly and it removes type inference for the affected columns

I created a reproduction before searching for similar issues. I might as well share it, maybe it can help.

https://github.com/cyco130/drizzle-d1-bug

tracking issue on d1 side

https://github.com/cloudflare/workers-sdk/issues/3160

Seems like this is an issue on d1 side. I’ll create an issue on their repo and will pin it here to track the progress