Sinergix Posts - Answer Overflow

Sinergix

•Created by Sinergix on 3/20/2025 in #help

Generated columns / JSONB-ish querying

I am new to PostgreSQL databases and have found Drizzle to be the perfect option for interacting with a database in my application. I have been running into a problem which can most easily be explained with an example, and that I know I will have to deal with repeatedly in the future as well. I have an app that allows users to sort product offerings using filters. Offerings have various properties which must always be present (title, description, etc) and a large amount of optionally relevant properties (documentation, location, terms, etc) which should be available for filtering (filter only offerings located in "Kansas"). Currently I am storing all the potential properties as columns in a table "offeringProperties", but this is not scalable, and this issue will continue to crop up. I am using Zod to define schemas for all data in my application, but would like database level and Drizzle level guarantees as well, which the property table so far has been able to provide. It looks like Drizzle, even with Drizzle RQB v2 does not support complex JSONB queries. With that all said, what exactly is "best practices" when it comes to defining type-safe/db-safe/query-safe queryable data? I have heard of generated columns from JSONB data, where we can query those generated tables, but I can't find if Drizzle supports it? Schema/Drizzle table example:

// Offering related tables
export const offeringProperties = pgTable('offering_attributes', {
        // ...summarized and simplified
    id: serial('id').primaryKey().notNull(),
    location: text().notNull(),
    deadline: timestamp({ mode: 'date' }).notNull(),
    amount: numeric().notNull(),
    total: numeric().notNull(),
    minimumAmount: numeric().notNull(),
    rate: numeric().notNull(),
    description: text().notNull()
});

// Offering related tables
export const offeringProperties = pgTable('offering_attributes', {
        // ...summarized and simplified
    id: serial('id').primaryKey().notNull(),
    location: text().notNull(),
    deadline: timestamp({ mode: 'date' }).notNull(),
    amount: numeric().notNull(),
    total: numeric().notNull(),
    minimumAmount: numeric().notNull(),
    rate: numeric().notNull(),
    description: text().notNull()
});

Any advice would be super appreciated!

1 replies

Gaming

Programming