[BUG]: Sqlite Cloudflare D1 inner joins do not work correctly

[AdiRishi]

What version of drizzle-orm are you using?

0.25.3

What version of drizzle-kit are you using?

0.17.6

Describe the Bug

Consider the following query.

await db
    .select()
    .from(customerTable)
    .innerJoin(addressTable, eq(addressTable.id, customerTable.addressId))
    .where(eq(customerTable.email, "test@test.com"))
    .get();

I have previously run the following insertion code into the address table and connected it to the given customer table

await db
  .insert(addressTable)
  .values({ countryAlpha3: "AUS" })
  .returning()
  .get()

In the response of the original query, we would expect the customer alongside the address to be returned. However, the ORM does something extremely odd. It returns this

{
  Customer: {
    id: 1,
    // .... other things
    email: 'test@test.com',
    addressId: 1
  },
  Address: {
    id: null,
    createdAt: null,
    line1: null,
    line2: null,
    city: 'AUS',
    state: null,
    countryAlpha3: undefined,
    postCode: undefined
  }
}

As you can see, it is not doing the mapping correctly (the country code ended up in the city) and the id and createdAt fields are totally missing.

I have done the following sanity checks

• I can confirm that the underlying sqlite db data is correct
• I can confirm that running the raw query against d1 with wrangler d1 execute db --local --command="query" works
• I have actually queried the address table with drizzle directly, and it does return the correct data.

I've made a github repo to show this error - https://github.com/AdiRishi/drizzle-d1-test

From what I can tell, some internal mapping happening in the ORM might be bugged.

Expected behavior

I would expect the ORM to correctly return both the customer and the address row.

I initially thought that maybe I needed to give explicit input into the select function like this

await db
    .select({
      Customer: customerTable,
      Address: addressTable,
    })
    .from(customerTable)
    .innerJoin(addressTable, eq(addressTable.id, customerTable.addressId))
    .where(eq(customerTable.email, "test@test.com"))
    .get();

But even that didn't work.
You can see the full recreation of the bug in this repository

Environment & setup

Drizzle is being used locally with Cloudflare workers and Cloudflare D1. See this repository for a recreation of the environment and bug.

[AdiRishi]

Hey guys, really excited to see the new release!

Any chance this bug will be tackled soon? Joins are fairly core to the SQL experience, and as d1 gets closer to production ready, it would be great to be able to rely on the ORM.

I suppose as an update, I did test the same scenario with the new relation syntax from drizzle-orm 0.26.0

// Add the following relation to the schema
export const customerRelations = relations(customerTable, ({ one }) => ({
  address: one(addressTable, {
    fields: [customerTable.addressId],
    references: [addressTable.id],
  }),
}));
// Then execute the following query
const customer = await db.query.customerTable.findFirst({
  with: { address: true },
  where: eq(customerTable.email, "test@test.com"),
});

This produces the correct output. Underneath it executes a fairly complex join statement (not sure why), but the result is mapped correctly in the response json.

Edit: To clarify, the new relation syntax works, but the straightforward join still exhibits the same bug in 0.26.0

[AndriiSherman]

Yeah, so we are having a conversation with Cloudflare Team as long as this is a bug on their side with getting a raw response from the database. You can see #555 on our side and cloudflare/workers-sdk#3160 on cloudflare side for tracking

[AdiRishi]

Oh I see, thanks for listing those issues out, I was unaware.

[AndriiSherman]

Tracking issue in #555