db.execute types appear to be wrong in effect/drizzle

[tmcw]

What version of Effect is running?

3.19.2

What steps can reproduce the bug?

For the given code:

const rows = yield* db.execute<{
  id: number;
  created_at: number;
}>(sql`select id, created_at from example order by created_at desc limit 1`);

If you were to treat db.execute like it works with drizzle (outside of Effect)`, the return type of this would be

result: QueryResult<{
    id: number;
    created_at: number;
}>

And you could have code like

const rows = await db.execute<{
  id: number;
  created_at: number;
}>(sql`select id, created_at from example order by created_at desc limit 1`);

console.log(rows.rows[0].id);

But with drizzle, the type of the result is

const rows: {
    id: number;
    created_at: number;
}[]

But this is wrong: the actual returned type when you're using effect looks like:

[
  Result {
    command: 'SELECT',
    rowCount: 1,
    oid: null,
    rows: [ [Object] ],
    fields: [ [Field], [Field] ],
    _parsers: [ [Function: parseInteger], [Function: parseBigInteger] ],
    _types: TypeOverrides { _types: [Object], text: {}, binary: {} },
    RowCtor: null,
    rowAsArray: false,
    _prebuiltEmptyResultObject: { id: null, created_at: null }
  }
]

So really Effect's return value is more like

result: [QueryResult<{
    id: number;
    created_at: number;
}>]

A tuple of one with the QueryResult inside.

It looks like the problem might be here, in which the execute method gets some special treatment

https://github.com/Effect-TS/effect/blob/main/packages/sql-drizzle/src/internal/patch.ts#L52

What is the expected behavior?

The type parameter of db.execute should work in a way that reflects drizzle default behavior, or at least a predictable way.

What do you see instead?

The type parameter does not reflect the actual data shape.

Additional information

No response

[mikearnaldi]

we'll need help from the Drizzle team here