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

What version of drizzle-orm are you using?

0.25.4

What version of drizzle-kit are you using?

0.17.6

Describe the Bug

I got broken results with leftJoin with same columns.

Details

My schema (partial)

import { sqliteTable, text, integer, uniqueIndex } from 'drizzle-orm/sqlite-core';
import { InferModel } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey().notNull(),
  displayName: text('displayName').notNull(),
});
export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().notNull(),
  ownerId: integer('ownerId').references(() => users.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
});

… and run next query to reduce has many pattern.

  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();

I got broken results.

[
  {
    user: { id: 13, displayName: 'kotaro chikuba (mizchi)' },
    post: { id: 1, ownerId: 'xx', title: 'x', content: undefined }
  },
  {
    user: { id: 1, displayName: 'kotaro chikuba (mizchi)' },
    post: { id: 1, ownerId: 'xxx', title: 'ueoaueoa', content: undefined }
  },
  {
    user: { id: 2, displayName: 'kotaro chikuba (mizchi)' },
    post: {
      id: 1,
      ownerId: 'xxxeeoa',
      title: 'ueoaueoa',
      content: undefined
    }
  }
]
  • id is swapped.
  • post data is shifted.

Running query is here.

select "users"."id", "posts"."id", "posts"."ownerId", "posts"."title", "posts"."content" from "users" left join "posts" on "posts"."ownerId" = "users"."id"

I think that duplicate id columns have been executed without as, causing the ORM to be misaligned when it repackages them into the object.

If one id is not referenced, this bug is resolved.

Expected behavior

I expect I can use both id and correct columns.

[
  {
    user: { id: 1, displayName: 'kotaro chikuba (mizchi)' },
    post: { id: 13, ownerId: 1, title: 'titile', content: 'body' }
  },
]

Environment & setup

I’m using pnpm.

pnpm add drizzle-kit drizzle-orm -D

D1 setup

import { drizzle } from 'drizzle-orm/d1';

export function createClient(db: D1Database) {
  return drizzle(db);
}

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 24
  • Comments: 30 (7 by maintainers)

Commits related to this issue

Most upvoted comments

Good news, my fix landed with wrangler@3.57.2, and I can confirm that the problem is gone.

@AndriiSherman @dankochetov, you can close the issue!

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.

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

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)

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 have implemented a type-safe generic function that you can copy if needed.

Image
function getTableAliasedColumns<T extends AnyTable<TableConfig>>(table: T) {
  type DataType = InferSelectModel<T>
  const tableName = getTableName(table)
  const columns = getTableColumns(table)
  return Object.entries(columns).reduce(
    (acc, [columnName, column]) => {
      ;(acc as any)[columnName] = sql`${column}`.as(
        `${tableName}_${columnName}`,
      )
      return acc
    },
    {} as {
      [P in keyof DataType]: SQL.Aliased<DataType[P]>
    },
  )
}
const query = db
  .select({
    modList: getTableAliasedColumns(modList),
    user: getTableAliasedColumns(user),
  })
  .from(modList)
  .innerJoin(user, eq(user.id, modList.twitterUserId))
  .where(eq(modList.id, 'modlist-1'))
const [result] = await db.batch([query])
console.log(result[0].modList.name)

@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

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"
            }
          }
        ]
      }
    ]
  }

@brenfwd @ElGatoLoco @keisanng Since this issue is mainly about D1, I’ll be closing this as it seems solved for that driver. If you’re experiencing issues with another driver (like Bun SQLite), please open another issue with a reproducible example.

If anyone still has this issue with the D1 driver, feel free to reopen this issue.

I have very similar issue with Bun SQLite driver and the following statement.

db
.select() // explicitly stating columns here doesn't seem to help
.from(ingredientsRecipes)
.innerJoin(recipes, eq(recipes.id, ingredientsRecipes.recipeId))
.innerJoin(ingredients, eq(ingredients.id, ingredientsRecipes.ingredientId))
.toSQL();

Below is the generated SQL.

select
  "ingredients_recipes"."ingredient_id",
  "ingredients_recipes"."recipe_id",
  "ingredients_recipes"."quantity",
  "recipes"."id",
  "recipes"."name",
  "ingredients"."id",
  "ingredients"."name",
  "ingredients"."kcal",
  "ingredients"."unit"
from "ingredients_recipes"
inner join "recipes" on "recipes"."id" = "ingredients_recipes"."recipe_id"
inner join "ingredients" on "ingredients"."id" = "ingredients_recipes"."ingredient_id";

By looking at the result, it seems you’re getting your references messed up when having multiple join statements, since somehow value of ingredients.unit ends up in ingredients.name field. If I remove one of the joins, everything looks fine.

FWIW, also getting this issue with the Bun sqlite driver

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

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