Shubham-Sinha Posts - Answer Overflow

Shubham-Sinha

•Created by Shubham-Sinha on 8/14/2023 in #help

How to dynamically select columns in relation query with proper type inference ?

const result = await db.query.products.findMany({
    columns: {
      id: true,
      title: true,
      retailPrice: true,
      discount: true,
      price: true,
    },
    orderBy: orderBy,
    limit: perPage,
    offset: meta.offset,
  })

const result = await db.query.products.findMany({
    columns: {
      id: true,
      title: true,
      retailPrice: true,
      discount: true,
      price: true,
    },
    orderBy: orderBy,
    limit: perPage,
    offset: meta.offset,
  })

The above example generated following types

const result: {
    title: string;
    description: string | null;
    id: string;
    retailPrice: number;
    discount: number;
    price: number;
}[]

const result: {
    title: string;
    description: string | null;
    id: string;
    retailPrice: number;
    discount: number;
    price: number;
}[]

But if I use

const avalaibleColumns = ["id", "title", "price"]
const result = await db.query.products.findMany({
    columns: {
      id:  avalaibleColumns.includes("id"),
      title: avalaibleColumns.includes("title"),
      retailPrice: avalaibleColumns.includes("retailPrice"),
      discount: avalaibleColumns.includes("discount"),
      price: avalaibleColumns.includes("price"),
    },
    orderBy: orderBy,
    limit: perPage,
    offset: meta.offset,
  })

const avalaibleColumns = ["id", "title", "price"]
const result = await db.query.products.findMany({
    columns: {
      id:  avalaibleColumns.includes("id"),
      title: avalaibleColumns.includes("title"),
      retailPrice: avalaibleColumns.includes("retailPrice"),
      discount: avalaibleColumns.includes("discount"),
      price: avalaibleColumns.includes("price"),
    },
    orderBy: orderBy,
    limit: perPage,
    offset: meta.offset,
  })

const result: {
    title: string;
    description: string | null;
    id: string;
    ... 20 more ...;
    attributes: Record<...> | null;
}[]

const result: {
    title: string;
    description: string | null;
    id: string;
    ... 20 more ...;
    attributes: Record<...> | null;
}[]

7 replies

DTDrizzle Team

•Created by Shubham-Sinha on 7/11/2023 in #help

Error: Error connecting to database: fetch is not defined

Trying to use neon-http with SST

import { neonConfig, neon, Pool } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { Config } from "sst/node/config";

neonConfig.fetchConnectionCache = true;

const sql = neon(Config.NEON_CONNECTION_STRING);

export const db = drizzle(sql);

import { neonConfig, neon, Pool } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { Config } from "sst/node/config";

neonConfig.fetchConnectionCache = true;

const sql = neon(Config.NEON_CONNECTION_STRING);

export const db = drizzle(sql);

1 replies

DTDrizzle Team

•Created by Shubham-Sinha on 6/26/2023 in #help

How to fetch related table rows in many_to_many

I have three tables : products, collections and collections_products. Product can belong to many collections and collection can have many products.

const result = await db.query.products.findMany({
    with: {
      store: true,
      collection: true,
    },
    orderBy: desc(products.displayScore)
  })

const result = await db.query.products.findMany({
    with: {
      store: true,
      collection: true,
    },
    orderBy: desc(products.displayScore)
  })

When using 👆 I get this error: could not identify an equality operator for type json schema.ts

const products = pgTable(
  "products",
  {
    ...id,
    title: varchar("title").notNull(),
    storeId: cuid("store_id").references(() => stores.id, {
      onDelete: "cascade",
    })
  },
  products => ({
    primary: primaryKey(products.id)
  })
);

const productRelations = relations(products, ({ many, one }) => ({
  collectionsProducts: many(collectionsProducts),
  store: one(stores, {
           fields: [collections.storeId],
           references: [stores.id],
         }),
}));

const collections = pgTable(
  "collections",
  {
    ...id,
    name: varchar("name", { length: 50 }).notNull(),
  },
  collections => ({
    primary: primaryKey(collections.id),

  })
);

const collectionsRelations = relations(collections, ({ many }) => ({
  collectionsProducts: many(collectionsProducts),
}));

const collectionsProducts = pgTable(
  "collections_products",
  {
    collectionId: cuid("collection_id")
      .notNull()
      .references(() => collections.id, { onDelete: "cascade" }),
    productId: cuid("product_id")
      .notNull()
      .references(() => products.id, { onDelete: "cascade" }),
  },
  t => ({
    pk: primaryKey(t.collectionId, t.productId),
  })
);

const collectionsProductsRelations = relations(
  collectionsProducts,
  ({ one }) => ({
    collection: one(collections, {
      fields: [collectionsProducts.collectionId],
      references: [collections.id],
    }),
    product: one(products, {
      fields: [collectionsProducts.productId],
      references: [products.id],
    }),
  })
);

const products = pgTable(
  "products",
  {
    ...id,
    title: varchar("title").notNull(),
    storeId: cuid("store_id").references(() => stores.id, {
      onDelete: "cascade",
    })
  },
  products => ({
    primary: primaryKey(products.id)
  })
);

const productRelations = relations(products, ({ many, one }) => ({
  collectionsProducts: many(collectionsProducts),
  store: one(stores, {
           fields: [collections.storeId],
           references: [stores.id],
         }),
}));

const collections = pgTable(
  "collections",
  {
    ...id,
    name: varchar("name", { length: 50 }).notNull(),
  },
  collections => ({
    primary: primaryKey(collections.id),

  })
);

const collectionsRelations = relations(collections, ({ many }) => ({
  collectionsProducts: many(collectionsProducts),
}));

const collectionsProducts = pgTable(
  "collections_products",
  {
    collectionId: cuid("collection_id")
      .notNull()
      .references(() => collections.id, { onDelete: "cascade" }),
    productId: cuid("product_id")
      .notNull()
      .references(() => products.id, { onDelete: "cascade" }),
  },
  t => ({
    pk: primaryKey(t.collectionId, t.productId),
  })
);

const collectionsProductsRelations = relations(
  collectionsProducts,
  ({ one }) => ({
    collection: one(collections, {
      fields: [collectionsProducts.collectionId],
      references: [collections.id],
    }),
    product: one(products, {
      fields: [collectionsProducts.productId],
      references: [products.id],
    }),
  })
);

1 replies

DTDrizzle Team

•Created by Shubham-Sinha on 6/3/2023 in #help

How to type results that includes relations ?

I have two tables: collections and products. Collection has many products.

const results = await db.query.collections.findMany({
    with: {
      products: {
        columns: {
          title: true,
          slug: true,
          images: true,
        },
      },
    },
  });

const results = await db.query.collections.findMany({
    with: {
      products: {
        columns: {
          title: true,
          slug: true,
          images: true,
        },
      },
    },
  });

I make an api request that gets the response from server. How can I add typing to fetch api response ?

3 replies

DTDrizzle Team

•Created by Shubham-Sinha on 6/2/2023 in #help

Implement full text search in postgres

Hi. I stumbled upon this issue (https://github.com/drizzle-team/drizzle-orm/issues/247) which was very useful. I then wanted to add support for weights. I was able to achieve 99% of the task but the last 1% is where I'm stuck and need help. Generated sql:

ALTER TABLE "table_name" ADD COLUMN "vec" "tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED";

ALTER TABLE "table_name" ADD COLUMN "vec" "tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED";

Expected:

ALTER TABLE "table_name" ADD COLUMN "vec" tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED;

ALTER TABLE "table_name" ADD COLUMN "vec" tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED;

https://github.com/drizzle-team/drizzle-orm/issues/247#issuecomment-1573541065

3 replies

DTDrizzle Team

•Created by Shubham-Sinha on 6/1/2023 in #help

How to implement triggers or db hooks ?

Is there a way to know if sql statement got executed successfully or not ? I wanted to run a code when row gets inserted or updated ? can custom loggers be used to implement the same ?

6 replies

Gaming

Programming