Drizzle integration select keys gets overridden by column names

[Twitch0125]

Environment

node: v22.1.0 pnpm: 9.1.4 better-sqlite3: ^11.0.0 db0: ^0.1.4 drizzle-orm: ^0.31.0

Reproduction

https://github.com/Twitch0125/db0-drizzle-select-bug

Describe the bug

I first noticed this in a nuxt project but it seems to be happening using only db0.

when passing an object to select() from the db0 drizzle integration it doesn't seem to honor the given keys, unlike using a drizzle instance directly.

import sqlite from "db0/connectors/better-sqlite3";

const db = createDatabase(sqlite())
const res = await db0DrizzleClient
  .select({
    name: table.name,
    contact: table.ratings_contact, //we'd expect `ratings_contact` to be put on the `contact` key
    power: table.ratings_power,
    gap: table.ratings_gap,
  })
  .from(table);

//but res looks like this instead
/*  [{
    name: 'bob',
    ratings_contact: 40, //uses the db column instead
    ratings_power: 60,
    ratings_gap: 40
  }]
*/

Additional context

No response

Logs

No response

[amandesai01]

I suspect this is because schemas are not supported yet.

https://github.com/unjs/db0/blob/62c0e0157034b052625c4b2491ec769ce10d72dc/src/integrations/drizzle/index.ts#L15

[Twitch0125]

Yeah that seems likely. I wouldn't have guessed it was schema support but it seems to be the same kind of thing happening in https://github.com/unjs/db0/issues/70

[pi0]

(sorry i'm little bit busy on other projects but in the meantime if anyone want to pick on this to support it is more than welcome ❤️ )

[amandesai01]

@pi0 I am available to work on it. Can I get some pointers on how exactly do you want to implement it? (Assuming something special is needed since it was left as a TODO)

[pi0]

Nothing special on db0 side but i think we need to:

• Carefully study impl in other drizzle connectors
• Test Schema via a simple migration perhaps

[amandesai01]

Got it, I will raise a PR and will wait for active feedbacks from your end.

[amandesai01]

Just a quick question, might be basic, why are we using SQLite Dialect by default? How does it work when someone uses Postgres for example with drizzle?

[pi0]

We should add MySQL and Postgres dialect support too indeed. Main goal of current db0 RC was to start with sqlite compatible providers.