jakeleventhal
jakeleventhal
Explore posts from servers
PPrismaTTCTheo's Typesafe CultDTDrizzle Team
PostsComments
DTDrizzle Team
Created by jakeleventhal on 5/30/2024 in #help
Best way to get all unique strings from string[] column
I have a column listings.tags which is a postgres string array. I want a query to find all unique strings that match a query. Is there a better or even more drizzle-y way to do this vs. my current code: 
  const tagsSubquery = db
    .select({
      tag: sql<string>`UNNEST(${listings.tags})`.as('tag')
    })
    .from(listings)
    .where(eq(listings.marketplaceIntegrationId, marketplaceIntegrationId))
    .as('tags');
  const [{ tags }] = await db
    .select({
      tags: sql<SearchTagsResponse>`ARRAY_AGG(DISTINCT ${tagsSubquery.tag})`
    })
    .from(tagsSubquery)
    .where(
      and(
        // Not using ilike function here because it causes a type error https://github.com/drizzle-team/drizzle-orm/issues/2395
        sql`${tagsSubquery.tag} ilike ${`%${searchTerm}%`}`,
        excludedTags?.length ? notInArray(tagsSubquery.tag, excludedTags) : undefined
      )
    )
    .limit(10);
  const tagsSubquery = db
    .select({
      tag: sql<string>`UNNEST(${listings.tags})`.as('tag')
    })
    .from(listings)
    .where(eq(listings.marketplaceIntegrationId, marketplaceIntegrationId))
    .as('tags');
  const [{ tags }] = await db
    .select({
      tags: sql<SearchTagsResponse>`ARRAY_AGG(DISTINCT ${tagsSubquery.tag})`
    })
    .from(tagsSubquery)
    .where(
      and(
        // Not using ilike function here because it causes a type error https://github.com/drizzle-team/drizzle-orm/issues/2395
        sql`${tagsSubquery.tag} ilike ${`%${searchTerm}%`}`,
        excludedTags?.length ? notInArray(tagsSubquery.tag, excludedTags) : undefined
      )
    )
    .limit(10);
1 replies
DTDrizzle Team
Created by jakeleventhal on 5/10/2024 in #help
Best way to get count in extras
await db.query.productSets
  .findMany({
    extras: {
      productsCount:
        sql`(SELECT count(*) from ${products} WHERE "Product"."productSetId" = ${productSets.id})`.as(
          'productsCount'
        )
    },
    with: { products: true }
  })
await db.query.productSets
  .findMany({
    extras: {
      productsCount:
        sql`(SELECT count(*) from ${products} WHERE "Product"."productSetId" = ${productSets.id})`.as(
          'productsCount'
        )
    },
    with: { products: true }
  })
is there a better way to do this without the sql operator? I want to just use drizzle code (to make more complex queries). if i try to, i get weird issues. similarly, if i replace "Product"."productSetId" with ${products.productSetId}, it tells me productSetId does not exist on the ProductSet table (it uses the wrong table)
2 replies
DTDrizzle Team
Created by jakeleventhal on 3/26/2024 in #help
How to check for non-backwards compatible schema changes
I want to create a check in my CI pipeline to see if there is a schema change that is not backwards compatible. How can I do this? I see there is a command for drizzle-kit check:{dialect}, but that doesn't really solve my use case. Basically what I want to know is whether or not the schema change I have will work without truncation of tables.
1 replies
DTDrizzle Team
Created by jakeleventhal on 3/21/2024 in #help
What is the best way to order by exact matches
No description
1 replies
DTDrizzle Team
Created by jakeleventhal on 3/19/2024 in #help
How do you use batch API with local development
If you use Neon, but do local development using postgres-js in docker, how can you write code that uses batch API? it seems like there should be some sort of shim that runs batches as a transaction under the hood to allow the same drizzle code to run for drivers that don't support batches.
3 replies
DTDrizzle Team
Created by jakeleventhal on 3/19/2024 in #help
Parallel transactions not working with AWS data API
https://github.com/aws/aws-sdk-js/issues/4615 Has anyone figured out a way around this issue?
1 replies
DTDrizzle Team
Created by jakeleventhal on 1/24/2024 in #help
How do you compare counts in a query?
WHERE (SELECT COUNT(*) FROM products p WHERE p.productSetId = ps.id) = 
      (SELECT COUNT(*) FROM products p 
       INNER JOIN listingVariants lv ON p.id = lv.productId 
       WHERE p.productSetId = ps.id);
WHERE (SELECT COUNT(*) FROM products p WHERE p.productSetId = ps.id) = 
      (SELECT COUNT(*) FROM products p 
       INNER JOIN listingVariants lv ON p.id = lv.productId 
       WHERE p.productSetId = ps.id);
how would you do this in drizzle without just using the sql operator
1 replies
DTDrizzle Team
Created by jakeleventhal on 1/22/2024 in #help
Getting seemingly incorrect relation error
export const productSetRelations = relations(productSets, ({ many, one }) => ({
  parentProductSetTemplate: one(productSetTemplates, {
    fields: [productSets.parentProductSetTemplateId],
    references: [productSetTemplates.id],
    relationName: 'parentProductSetTemplate'
  }),
  productSetTemplate: one(productSetTemplates, {
    fields: [productSets.productSetTemplateId],
    references: [productSetTemplates.id]
  })
}));

export const productSetTemplateRelations = relations(productSetTemplates, ({ many, one }) => ({
  productSet: one(productSets),
  prototypeProductSets: many(productSets, { relationName: 'parentProductSetTemplate' }),
}));
export const productSetRelations = relations(productSets, ({ many, one }) => ({
  parentProductSetTemplate: one(productSetTemplates, {
    fields: [productSets.parentProductSetTemplateId],
    references: [productSetTemplates.id],
    relationName: 'parentProductSetTemplate'
  }),
  productSetTemplate: one(productSetTemplates, {
    fields: [productSets.productSetTemplateId],
    references: [productSetTemplates.id]
  })
}));

export const productSetTemplateRelations = relations(productSetTemplates, ({ many, one }) => ({
  productSet: one(productSets),
  prototypeProductSets: many(productSets, { relationName: 'parentProductSetTemplate' }),
}));
Error: There are multiple relations between "productSets" and "ProductSetTemplate". Please specify relation name
Error: There are multiple relations between "productSets" and "ProductSetTemplate". Please specify relation name
Shouldnt this be sufficient for relation names? If not, i dont see how i could even add one? my schema was working with prisma so i dont think i need to add another FK
11 replies
DTDrizzle Team
Created by jakeleventhal on 1/20/2024 in #help
Anyone have any idea about this bug?
https://github.com/drizzle-team/drizzle-orm/issues/1816
14 replies
DTDrizzle Team
Created by jakeleventhal on 1/18/2024 in #help
Many to many relation not enough information
export const orderRelations = relations(orders, ({ many, one }) => ({
  stripeTransactions: many(stripeTransactions),
}));

export const stripeTransactionRelations = relations(stripeTransactions, ({ many }) => ({
  orders: many(orders)
}));

export const orderToStripeTransactionRelations = relations(orderToStripeTransactions, ({ one }) => ({
  order: one(orders, {
    fields: [orderToStripeTransactions.a],
    references: [orders.id]
  }),
  stripeTransaction: one(stripeTransactions, {
    fields: [orderToStripeTransactions.b],
    references: [stripeTransactions.id]
  })
}));
export const orderRelations = relations(orders, ({ many, one }) => ({
  stripeTransactions: many(stripeTransactions),
}));

export const stripeTransactionRelations = relations(stripeTransactions, ({ many }) => ({
  orders: many(orders)
}));

export const orderToStripeTransactionRelations = relations(orderToStripeTransactions, ({ one }) => ({
  order: one(orders, {
    fields: [orderToStripeTransactions.a],
    references: [orders.id]
  }),
  stripeTransaction: one(stripeTransactions, {
    fields: [orderToStripeTransactions.b],
    references: [stripeTransactions.id]
  })
}));
when i query 
db.query.orders.findMany({
  with: { stripeTransactions: true }
})
db.query.orders.findMany({
  with: { stripeTransactions: true }
})
it tells me
Error: There is not enough information to infer relation "orders.stripeTransactions"
what other info could be needed
44 replies
DTDrizzle Team
Created by jakeleventhal on 1/18/2024 in #help
How do you define one-to-one relations in the same table?
export const listings = pgTable(
  'Listing',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    physicalListingId: text('physicalListingId'),
  },
  (table) => ({
    listingPhysicalListingIdFkey: foreignKey({
      columns: [table.physicalListingId],
      foreignColumns: [table.id],
      name: 'Listing_physicalListingId_fkey'
    })
      .onUpdate('cascade')
      .onDelete('set null'),
    physicalListingIdKey: uniqueIndex('Listing_physicalListingId_key').on(table.physicalListingId),
  })
);
export const listings = pgTable(
  'Listing',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    physicalListingId: text('physicalListingId'),
  },
  (table) => ({
    listingPhysicalListingIdFkey: foreignKey({
      columns: [table.physicalListingId],
      foreignColumns: [table.id],
      name: 'Listing_physicalListingId_fkey'
    })
      .onUpdate('cascade')
      .onDelete('set null'),
    physicalListingIdKey: uniqueIndex('Listing_physicalListingId_key').on(table.physicalListingId),
  })
);
export const listingRelations = relations(listings, ({ many, one }) => ({
  digitalListing: one(listings),
  physicalListing: one(listings, {
    fields: [listings.physicalListingId],
    references: [listings.id]
  }),
}));
export const listingRelations = relations(listings, ({ many, one }) => ({
  digitalListing: one(listings),
  physicalListing: one(listings, {
    fields: [listings.physicalListingId],
    references: [listings.id]
  }),
}));
How would you define a relationName here if there is no field for the id of the digitalListing (this schema was working in prisma world) basically i have a table of listings, and one can be linked to another the problem is that if i want to define a relationName in the config for digitalListing, it expects fields and references
21 replies
DTDrizzle Team
Created by jakeleventhal on 1/17/2024 in #help
How are you supposed to get triple-nested where queries to work?
db.query.users.findMany({
  where: undefined,
  with: {
    orders: {
      where: undefined,
      with: {
        orderItems: {
          where: undefined,
          with: {
            details: {
              where: undefined,
              ^^^^^^^^^^^^^^^^  WHERE IS NOT A VALID KEY
              with: {}
            }
          }
        }
      }
    }
  }
});
db.query.users.findMany({
  where: undefined,
  with: {
    orders: {
      where: undefined,
      with: {
        orderItems: {
          where: undefined,
          with: {
            details: {
              where: undefined,
              ^^^^^^^^^^^^^^^^  WHERE IS NOT A VALID KEY
              with: {}
            }
          }
        }
      }
    }
  }
});
3 replies
DTDrizzle Team
Created by jakeleventhal on 1/16/2024 in #help
Is there a smarter way to use optional query values
let newParentFolderId: string | undefined = getValue();

return db.query.imageFiles
  .findFirst({
    where: newParentFolderId ? eq(imageFiles.parentFolderId, newParentFolderId) : isNull(imageFiles.parentFolderId)
  })
.execute();
let newParentFolderId: string | undefined = getValue();

return db.query.imageFiles
  .findFirst({
    where: newParentFolderId ? eq(imageFiles.parentFolderId, newParentFolderId) : isNull(imageFiles.parentFolderId)
  })
.execute();
is there a cleaner way to not have to do things this way?
6 replies
DTDrizzle Team
Created by jakeleventhal on 1/12/2024 in #help
Is this a bug with arrays?
In postgres I have a column defined like this: 
someArrayField: text('someArrayField')
  .array()
  .default(sql`ARRAY[]::text[]`)
  .notNull()
someArrayField: text('someArrayField')
  .array()
  .default(sql`ARRAY[]::text[]`)
  .notNull()
For insertions, drizzle says I don't need to define someArrayField since it has a default. Yet when I try to insert a record, I get this error: 
error: null value in column "someArrayField" of relation "SomeTable" violates not-null constraint
error: null value in column "someArrayField" of relation "SomeTable" violates not-null constraint
Am I doing this incorrectly? interestingly, on localhost, this has no issues when I use import { drizzle } from 'drizzle-orm/postgres-js'; This only has issues when deployed when I use import { drizzle } from 'drizzle-orm/neon-serverless'; I wanted to just do .default([]) but then drizzle kit tells me: 
No config path provided, using default path
Reading config file '/Users/jakeleventhal/Code/rip-technologies/packages/ecominate/database/drizzle.config.ts'
error: syntax error at or near ";"
    at /Users/jakeleventhal/Code/rip-technologies/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:24462:21
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PgPostgres.query (/Users/jakeleventhal/Code/rip-technologies/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:25423:21)
    at async Command.<anonymous> (/Users/jakeleventhal/Code/rip-technologies/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:63244:9) {
No config path provided, using default path
Reading config file '/Users/jakeleventhal/Code/rip-technologies/packages/ecominate/database/drizzle.config.ts'
error: syntax error at or near ";"
    at /Users/jakeleventhal/Code/rip-technologies/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:24462:21
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PgPostgres.query (/Users/jakeleventhal/Code/rip-technologies/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:25423:21)
    at async Command.<anonymous> (/Users/jakeleventhal/Code/rip-technologies/node_modules/.pnpm/drizzle-kit@0.20.10/node_modules/drizzle-kit/bin.cjs:63244:9) {
23 replies
DTDrizzle Team
Created by jakeleventhal on 1/11/2024 in #help
How to use sql template strings
await tx.update(inventoryItems).set({
        masterProductId: sql`
        CASE
          ${chunk
            .map((update) => `WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`)
            .join('\n')}
        END
        `
      });
await tx.update(inventoryItems).set({
        masterProductId: sql`
        CASE
          ${chunk
            .map((update) => `WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`)
            .join('\n')}
        END
        `
      });
Query: update "InventoryItem" set "masterProductId" = 
        CASE
          $1
        END
         -- params: ["WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '1232a182-035c-4534-bc4b-402f9a00bced'"]
Jan 11, 12:09:45PM debug: req.url /api/master-products/bulk/map/save 
Jan 11, 12:09:45PM error: PostgresError: syntax error at or near "END"
Query: update "InventoryItem" set "masterProductId" = 
        CASE
          $1
        END
         -- params: ["WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '1232a182-035c-4534-bc4b-402f9a00bced'"]
Jan 11, 12:09:45PM debug: req.url /api/master-products/bulk/map/save 
Jan 11, 12:09:45PM error: PostgresError: syntax error at or near "END"
this should be resolving to valid sql
55 replies
DTDrizzle Team
Created by jakeleventhal on 1/11/2024 in #help
Smarter way to update while creating
In prisma, I can do something like this: 
await prisma.blogs.createMany(
  data: blogs.map(b => ({
    name: 'asdf',
    text: 'asdfasdfs',
    users: { connect: b.userIdsToConnectTo }
  })
})
await prisma.blogs.createMany(
  data: blogs.map(b => ({
    name: 'asdf',
    text: 'asdfasdfs',
    users: { connect: b.userIdsToConnectTo }
  })
})
is there a smarter way to do this in drizzle? currently im doing this: 
await tx.insert(blogs).values(blogs)

await Promise.all(updates.map(async (update) =>
  tx
    .update(users)
    .set({ blogId: update.newBlogId })
    .where(inArray(users.id, update.userIdsToConnectTo))
    .execute()
));
await tx.insert(blogs).values(blogs)

await Promise.all(updates.map(async (update) =>
  tx
    .update(users)
    .set({ blogId: update.newBlogId })
    .where(inArray(users.id, update.userIdsToConnectTo))
    .execute()
));
this is extremely slow to do the promise.all here. it would be much more efficient to just do this upon creation of the blogs if i could just do a create many and name one trip to the db. at the very least i'd like to be able to do 2 trips (one for the creation of all the new blogs and one for the user updates)
9 replies
DTDrizzle Team
Created by jakeleventhal on 1/10/2024 in #help
Guarantee on returning order
Is there a guarantee that await db.insert(sometable).values(someArray).returning() will always return in the same order as someArray
2 replies
DTDrizzle Team
Created by jakeleventhal on 1/10/2024 in #help
Subquery/join in relational query
are the following queries identical? 
await db.query.posts.findFirst({
  where: and(
    eq(posts.type, 'blog'),
    inArray(
      posts.userId,
      db
        .select({ id: users.id })
        .from(users)
        .where(and(eq(users.id, userId), isNotNull(users.email)))
    )
  )
});
await db.query.posts.findFirst({
  where: and(
    eq(posts.type, 'blog'),
    inArray(
      posts.userId,
      db
        .select({ id: users.id })
        .from(users)
        .where(and(eq(users.id, userId), isNotNull(users.email)))
    )
  )
});
await db.query.posts.findFirst({
  with: { user: true },
  where: and(
    eq(posts.type, 'blog'),
    isNotNull(users.email)
  )
});
await db.query.posts.findFirst({
  with: { user: true },
  where: and(
    eq(posts.type, 'blog'),
    isNotNull(users.email)
  )
});
3 replies
DTDrizzle Team
Created by jakeleventhal on 1/8/2024 in #help
How to run drizzle-kit studio out of docker?
I'm trying to run drizzle-kit studio out of docker: 
drizzle-studio:
  build:
    dockerfile: ./tools/Dockerfile
  ports:
    - 4983:4983
  environment:
    DATABASE_URL: postgresql://postgres:local@db:5432/postgres
  restart: always
  command: drizzle-kit studio
drizzle-studio:
  build:
    dockerfile: ./tools/Dockerfile
  ports:
    - 4983:4983
  environment:
    DATABASE_URL: postgresql://postgres:local@db:5432/postgres
  restart: always
  command: drizzle-kit studio
When I run docker-compose, I get the following log:
ecominate-drizzle-studio-1 | Drizzle Studio is up and running on https://local.drizzle.studio
yet when i visit the browser, it says its "Connecting to the Drizzle Kit on localhost:4983" are there separate docker instructions for how to run this?
13 replies
DTDrizzle Team
Created by jakeleventhal on 1/7/2024 in #help
Is there a better way to access enum types?
// the schema
export const MarketplaceIntegrationType = pgEnum('MarketplaceIntegrationType', [
  'Amazon',
  'Walmart',
  'Etsy',
  'Shopify'
]);
type: MarketplaceIntegrationType('type').notNull()

// the code
let integrationType: typeof marketplaceIntegrations.$inferSelect.type;
// the schema
export const MarketplaceIntegrationType = pgEnum('MarketplaceIntegrationType', [
  'Amazon',
  'Walmart',
  'Etsy',
  'Shopify'
]);
type: MarketplaceIntegrationType('type').notNull()

// the code
let integrationType: typeof marketplaceIntegrations.$inferSelect.type;
is there a better way to do this other than manually creating a type to use everywhere? its not very ergonomic to type this out like that i can also do something like 
(typeof MarketplaceIntegrationType.enumValues)[number]
(typeof MarketplaceIntegrationType.enumValues)[number]
but this sucks too
6 replies