Drizzle syntax equivalent of raw SQL doesn't return the same values

[DanClubb]

The following drizzle syntax:

db.select()
  .from(tasks)
  .innerJoin(lists, eq(tasks.listId, lists.id))
  .where(eq(lists.boardId, input.boardId))

returns

{
   lists: {
       boardId: number;
       id: number;
       name: string;
       createdAt: Date;
       updatedAt: Date | null;
       position: number;
   };
  tasks: {
       id: number;
       name: string;
       createdAt: Date;
       updatedAt: Date | null;
       position: number;
       listId: number;
       description: string | null;
   };
} [] | undefined

where as raw sql syntax:

db.execute(sql`
  SELECT ${tasks}.*
  FROM ${tasks}
  INNER JOIN ${lists} ON ${tasks.listId} = ${lists.id}
  WHERE ${lists.boardId} = ${input.boardId};`)

returns:

[
  {
    id: number;
    name: string;
    createdAt: Date;
    updatedAt: Date | null;
    position: number;
    listId: number;
    description: string | null;
  }
]
but of type ExecutedQuery<any[] | Record<string, any>> | undefined

This type makes it hard for me to use so I would like to query with drizzle syntax.

Are the two queries I've shown here not equivalent to each other? If not how can I achieve the same result with drizzle syntax or how can I retype the data return from the sql syntax?

[Angelelz]

There are several things going on here:
1.- Those are not strictly the same. When you do db.select() drizzle is listing all the columns in the query to ensure the columns will come back in a known order. Also, you are only selecting the columns from the tasks table in your raw query.
2.- Drizzle is mapping the result to different objects per table in javascript, so you have easier nullability on the joined table. Drizzle is also making sure that if there are repeated column names, those column names will not colide.
3.- Depending on your dialect and driver, db.execute() will accept a generic parameter that describes the shape of a row in your raw query. That's the way you can have types there. You are currently getting the default type with anys.

If you want the same shape as the raw query you can do:

db.select({
    ...getTableColumns(tasks),
    ...getTableColumns(lists)   // <--- There will be name clashes here, id, name, createdAt, etc.
  })
  .from(tasks)
  .innerJoin(lists, eq(tasks.listId, lists.id))
  .where(eq(lists.boardId, input.boardId))

If you want to type your raw query:

db.execute<{ theShapeOfYourRowHere }>(sql`
  SELECT ${tasks}.*
  FROM ${tasks}
  INNER JOIN ${lists} ON ${tasks.listId} = ${lists.id}
  WHERE ${lists.boardId} = ${input.boardId};`)