Derived or Computed Column from JSON with Drizzle

I'm using a frontend library to do the heavy lifting on the UI for me. Its data is stored in an object, which I'm storing in my DB as JSON. However, one valuable piece of information is nested inside this object, and I would like it to be a column of my table in the end.

Currently, I'm doing this manually on DB updates, but I think I could do it much more programmatically through a derived or computed column. How do I do that?

This is the shape of the information I'm looking at:

{
  ...
  "start": {
    "id": "abc123",
  }
}

And this is what I'm doing on Drizzle:

import { json, pgTableCreator, varchar } from "drizzle-orm/pg-core"

const createTable = pgTableCreator(
  (name) => `schema_${name}`,
)

const nodes = createTable("nodes", {
  ...
  data: json("data"),
  startId: varchar("start_id"),
})

The feature for computed/derived columns is not yet implemented. The PR is opened. You can comment there for it: PR Feat: Drizzle-ORM support for Generated columns

The other option is to use sql triggers, but I would not recommend that. They are hard to debug and trace issues. Documentation for PostgreSQL triggers

The best approach is to have a decorator class or objects that extend the drizzle schema and work with it.

Example with the help of zod and drizzle-zod. But it can be tuned without them. All should work with the NodeExtended, and the real node type should not be exposed to upper layers.

import { z } from 'zod';
import { createSelectSchema } from 'drizzle-zod';
import { json, pgTableCreator, varchar } from 'drizzle-orm/pg-core';


const createTable = pgTableCreator((name) => `schema_${name}`);

export const nodes = createTable('nodes', {
  data: json('data').notNull(),
  startId: varchar('start_id').notNull(),
});

const nodeDataSchema = z.object({
  start: z.object({
    id: z.string(),
  }),
});

type NodeData = z.infer<typeof nodeDataSchema>;

const selectNodeSchema = createSelectSchema(nodes);

interface INode extends z.infer<typeof selectNodeSchema> {}

export class NodeExtended implements INode {
  private constructor(
    private _data: NodeData,
    private _startId: string,
  ) {}

  static from(rawInput: INode) {
    const data = nodeDataSchema.parse(rawInput.data);
    return new NodeExtended(data, rawInput.startId);
  }

  set data(value: NodeData) {
    this._data = value;
    this._startId = value.start.id;
  }
  get data() {
    return this._data;
  }

  // only getter, we do not want to set this explicitly
  get startId() {
    return this._startId;
  }
}