Advanced aggregations in db.select()

[AlexBlokh]

As a subsequent improvement to subqueries in db.select(), we want to further improve our API with introduction of advanced SQL aggregation patterns.

First of all we want to enable developers with support of json_agg, row_to_json and coalesce functions, which are powering our relational queries under the hood

db.select({
  ...getTableColumns(users),
  posts: coalesce(db.select(jsonAgg(rowToJson())).from(posts).where(eq(posts.authorId, users.id)), [])
}).from(users);

// which would be mostly the equivalent of relational query below

db.query.users.findMany({
  with: {
    posts: true
  }
});

we use lateral joins in dialect that support them for performance reasons, in others above queries with sub-queries in select would be mostly identical

To enable this API we would need to introduce subqueries support in db.select() and enable support for db.select(aggregationOperator).from(table) apart from Record<string, SQL | Table | Column ... >

We've spent quite some time internally to design the response type of db.select(jsonAgg()) or db.select(count()) or other top level aggregation functions, since drizzle is meant to follow SQL-like convention for SQL query build both in statement building and response type.

Though we need to take DX in consideration too, that's why we do have db.$count operator explicitly distinguished with $ to solve DX problem of

const res = db.select({ count: sql`count(*)::integer` }).from(users);
const count = res[0]!.count;

// as opposed to 
const count = db.$count(users);

// which you can use in subqueries like
const res = db.select({
   ...getTableColumns(users),
   postsCount: db.$count(posts, eq(posts.authorId, users.id)),
}).from(users);

We took in consideration mental model of developers querying database via database browsers.
When they do:

select * from users;

they expect to see tabular data with column names and rows, as opposed to:

select count(*) from users;

is when they care only about a single number.

Taking all that in count we decided to let users pass top level db.select(count()).from(users) which will translate directly to select count(*) from users sql, while result value and result type we flatten:

const result = db.select(count()).from(users);
// ^ number[] 
// which is in this particular case would be an array of 1 number element, which will let you 

const [value] = db.select(count()).from(users);

this API will unlock further convenient aggregations like

const [value] = db.select(sum(product.price)).from(products);
const [value] = db.select(max(order.value)).from(orders);

and most importantly will let you build your own advanced relational queries with ease:

const result = db.select({
   ...getTableColumns(users),
   totalPostsCount: db.select(count()).from(posts).where(eq(posts.authorId, users.id)),
   recentPosts: coalesce(db.select(jsonAgg(rowToJson())).from(posts).where(eq(posts.authorId, users.id)), []).orderBy(desc(posts.date)).limit(10),
}).from(users).limit(50);

we've already played around with coalesce typing, which will trim | null while preserving type of an entity if both are arrays

type Coalesce<TArgs extends any[]> = TArgs extends [infer Arg, ...infer Tail]
  ? (Tail extends [any, ...any[]] ? null extends Arg ? Exclude<Arg, null> extends infer NewArg ? (
          | NewArg
          | (Coalesce<Tail> extends infer TailRes
            ? NewArg extends any[] ? TailRes extends [] ? never : TailRes : TailRes
            : never)
        )
      : never
    : Arg
    : Arg)
  : never;

type test = Coalesce<[{ key: "val"}[], []]>;
//      ^ would be of type { key: "val" }[], as opposed to { key: "val" }[] | []

[kravetsone]

This is awesome

[adamgreg]

This looks great! Is it on the horizon now subqueries are supported in db.select()?