What approach can you recommend for data grouping?

[rastym]

For example, let's consider the following structure. If we perform a JOIN on these tables, we'll end up with two records for one user.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
INSERT INTO users (username, email) VALUES
    ('user1', '[email protected]');
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO posts (title, content, user_id) VALUES
    ('Post 1', 'Content of post 1', 1),
    ('Post 2', 'Content of post 2', 1),

How can we process the data to obtain such a structure?:
User { id, username, email, posts: [{ title: 'Post 1', content: 'Content of post 1', user_id: 1 }] }
Of course, we can write parsers each time, use helper functions from lodash, but is there a more fundamental approach? Thank you!

[wsporto]

I have been exploring this feature in TypeSQL (a similar library but for mysql). The approach I have used is to annotate the queries with -- @nested as below:

-- @nested
 SELECT
  *
FROM users u
INNER JOIN posts p on p.user_id = u.id
WHERE u.id = :id

Then TypeSQL will generate the function selectUserPostsNested:

const user = await selectUserPostsNested(conn, {id: 1});

The result type will have the structure:

{ id: 1, username: 'user1', email: '[email protected]', posts: [{ title: 'Post 1', content: 'Content of post 1', user_id: 1 }] }

[lorefnon]

You can either use:

1. Lateral joins to fetch associated data and build hierarchy in the same (complex) query. Look at the queries generated by libraries like Zapatos or drizzle.
2. You can make multiple simple queries (one for each level of association) and combine the fetched collections into a hierarchy using collection-joiner - a library I maintain to handle this in a type-safe manner.

[rastym]

@wsporto , @lorefnon thanks for your response!

[AustinShelby]

One solution is to query the posts as a jsonb type, write your own custom jsonb parser (using zod for example), register it as a type parser for your pg client in TypeScript, and override the jsonb type in pgtyped config.

First creating the custom jsonb parser. src/jsonbParser.ts

import { z } from "zod";

const post = z.object({
  id: z.number(),
  title: z.string(),
  content: z.string(),
  user_id: z.number(),
});

export const jsonbParser = post.array();

export type JsonbParser = z.TypeOf<typeof jsonbParser>;

Registering it as a custom type parser for pg client. src/pgClient.ts

import { Pool, types } from "pg";
import { jsonbParser } from "./jsonbParser";

types.setTypeParser(types.builtins.JSONB, (val: string) =>
  jsonbParser.parse(JSON.parse(val))
);

export const pgPool = new Pool({
  user: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  host: "localhost",
  database: "app",
  port: 5432,
});

Overriding types in pgtyped config. pgtyped.config.json

{
  "transforms": [
    {
      "mode": "sql",
      "include": "**/*.sql",
      "emitTemplate": "{{dir}}/{{name}}.queries.ts"
    }
  ],
  "srcDir": "./queries/",
  "failOnError": false,
  "camelCaseColumnNames": false,
  "db": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "dbName": "app",
    "password": "postgres"
  },
  "typesOverrides": {
    "jsonb": "./src/jsonbParser.ts#JsonbParser"
  }
}

Writing the query. queries/getUser.sql

/* @name GetUser */
SELECT users.id,
       users.username,
       users.email,
       jsonb_agg_strict(posts) AS posts
FROM users
LEFT JOIN posts ON users.id = posts.user_id
WHERE users.id = :userId
GROUP BY users.id;

This will give you the following types as a result.

interface IGetUserResult {
  email: string;
  id: number;
  posts: JsonbParser | null;
  username: string;
}

type JsonbParser = {
    id: number;
    title: string;
    content: string;
    user_id: number;
}[]

If you have any other models that you are querying as jsonb you can extend the jsonb parser by chaining it with other optional possible validators using .or().