hotshoe Posts - Answer Overflow

hotshoe

Explore posts from servers

•Created by hotshoe on 4/24/2023 in #help

onUpdateNow usage

is onUpdateNow simlar to prisma's @updatedAt ? I.e., updated_at col is auto-updated. If so, is there an example usage? Thanks!

3 replies

DTDrizzle Team

•Created by hotshoe on 4/20/2023 in #help

many-to-one selection as array

Hi, Let's say I have a user table, and the user can have multiple profiles. When I select using join on user_id I get back two records like so (pseudo-code):

[
  { user_id: 123, profile_id: "abc" },
  { user_id: 123, profile_id: "def" },
]

[
  { user_id: 123, profile_id: "abc" },
  { user_id: 123, profile_id: "def" },
]

What I want is a single record, where profiles are listed in an array on the user object:

[
  {
    user_id: 123,
    profiles: [
      { profile_id: "abc" },
      { profile_id: "def" },
    ]
  }
]

[
  {
    user_id: 123,
    profiles: [
      { profile_id: "abc" },
      { profile_id: "def" },
    ]
  }
]

Is there an elegant way for me to get query back in desired form (w/o needing to resort to two queries and patching together a new type)? Any help is much appreciated! Thanks,

7 replies

DTDrizzle Team

•Created by hotshoe on 4/11/2023 in #help

many-to-one transaction

Hello, I want to create transaction involving arbitrary number of inserts. For example, Artist (one table) and insert multiple Titles (another table). Not clear to me how to best accomplish based example provided:

const results = await conn.transaction(async (tx) => {
  const whenBranch = await tx.execute('INSERT INTO branches (database_id, name) VALUES (?, ?)', [42, "planetscale"])
  const whenCounter = await tx.execute('INSERT INTO slotted_counters(record_type, record_id, slot, count) VALUES (?, ?, RAND() * 100, 1) ON DUPLICATE KEY UPDATE count = count + 1', ['branch_count', 42])
  return [whenBranch, whenCounter]
})

const results = await conn.transaction(async (tx) => {
  const whenBranch = await tx.execute('INSERT INTO branches (database_id, name) VALUES (?, ?)', [42, "planetscale"])
  const whenCounter = await tx.execute('INSERT INTO slotted_counters(record_type, record_id, slot, count) VALUES (?, ?, RAND() * 100, 1) ON DUPLICATE KEY UPDATE count = count + 1', ['branch_count', 42])
  return [whenBranch, whenCounter]
})

Also, related, is there a way for me to include non db transactions as part of transaction, such that if exception thrown say, for example, copy object to S3 bucket fails, transaction also rolls back? Thanks!

6 replies

DTDrizzle Team

•Created by hotshoe on 4/4/2023 in #help

compound uniqueIndex

In Prisma, I have in my schema definition the following declaration that requires userId and timestamp tuple to be unique:

@@unique([userId, time])

@@unique([userId, time])

How can I express the same using drizzle kit schema file?

4 replies

DTDrizzle Team

•Created by hotshoe on 4/4/2023 in #help

planetscale: how to index col using drizzle?

Hi, seems most of the samples are pg. I'm using planetscale, and no having any luck figuring out how to index a col. here's simple table I'm wanting to use to test/eval:

import {
  varchar,
  text,
  mysqlTable,
  timestamp,
} from "drizzle-orm/mysql-core";

export const users = mysqlTable(
  "users",
  {
    id: varchar("id", { length: 16 }).primaryKey(),
    email: varchar("email", { length: 320 }),
    name: text("name").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    // how to index email?
  })
);

import {
  varchar,
  text,
  mysqlTable,
  timestamp,
} from "drizzle-orm/mysql-core";

export const users = mysqlTable(
  "users",
  {
    id: varchar("id", { length: 16 }).primaryKey(),
    email: varchar("email", { length: 320 }),
    name: text("name").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    // how to index email?
  })
);

Any help much appreciated. Thanks!

5 replies

Gaming

Programming