JP Posts - Answer Overflow

•Created by JP on 9/11/2024 in #help

Error when executing drizzle-kit push

I have a view in my db, debug-view here is the definition in the schema:

 
export const debugView = mysqlView("debug_view", {
    t: varchar("t", { length: 5 }),
    err: mysqlEnum("err", ['default','success','failed','error','exception','warn','info','important']).default('default').notNull(),
    schema: varchar("schema", { length: 255 }).notNull(),
    event: varchar("event", { length: 50 }).notNull(),
    waiting_events: varchar("waiting_events", { length: 10 }),
    res_id: varchar("res_id", { length: 255 }),
    state: varchar("state", { length: 255 }).notNull(),
    type: varchar("type", { length: 255 }),
    label: varchar("label", { length: 255 }).notNull(),
    failed_as_text: text("failed_as_text"),
    failed: json("failed"),
    conditions: json("conditions"),
    data: json("data"),
}).existing();

 
export const debugView = mysqlView("debug_view", {
    t: varchar("t", { length: 5 }),
    err: mysqlEnum("err", ['default','success','failed','error','exception','warn','info','important']).default('default').notNull(),
    schema: varchar("schema", { length: 255 }).notNull(),
    event: varchar("event", { length: 50 }).notNull(),
    waiting_events: varchar("waiting_events", { length: 10 }),
    res_id: varchar("res_id", { length: 255 }),
    state: varchar("state", { length: 255 }).notNull(),
    type: varchar("type", { length: 255 }),
    label: varchar("label", { length: 255 }).notNull(),
    failed_as_text: text("failed_as_text"),
    failed: json("failed"),
    conditions: json("conditions"),
    data: json("data"),
}).existing();

when I run drizzle-kit push, I'm getting this output:

[✓] Pulling schema from database...
Reading schema files:
C:\prg\work\newcomers\app\backend\src\db\schema.ts

 Warning  Found data-loss statements:
· You're about to delete debug_view table with 583 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
❯ No, abort
  Yes, I want to remove 1 table,

[✓] Pulling schema from database...
Reading schema files:
C:\prg\work\newcomers\app\backend\src\db\schema.ts

 Warning  Found data-loss statements:
· You're about to delete debug_view table with 583 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
❯ No, abort
  Yes, I want to remove 1 table,

Having this definition or not does not make any difference. I can't pass this point, the only way to continue having that view is to remove it from the DB, make the push, and re-create it. Any advice?

1 replies

DTDrizzle Team

•Created by JP on 5/15/2024 in #help

This query is to much for me :)

Hello guys, This is the Query:

const result = await db
      .select({
        contact_id: contacts.id,
        contact_name: contacts.name,
        custom_fields_data: sql`group_CONCAT(${customFields.field_key}, ': ', ${contactCustomFields.value})`,
      })
      .from(contactCustomFields)
      .innerJoin(contacts, eq(contacts.id, contactCustomFields.contact_id))
      .innerJoin(customFields, eq(customFields.id, contactCustomFields.custom_field_id))
      .groupBy(contacts.id, contacts.name)
      .execute()

const result = await db
      .select({
        contact_id: contacts.id,
        contact_name: contacts.name,
        custom_fields_data: sql`group_CONCAT(${customFields.field_key}, ': ', ${contactCustomFields.value})`,
      })
      .from(contactCustomFields)
      .innerJoin(contacts, eq(contacts.id, contactCustomFields.contact_id))
      .innerJoin(customFields, eq(customFields.id, contactCustomFields.custom_field_id))
      .groupBy(contacts.id, contacts.name)
      .execute()

One of the response items:

 json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"custom_fields_data": "data_transfer__do_not_use: 7/27/2020,total___of_people_in_family: 3,how_old_is_the_youngest_member_of_the_family: 8+,nationality: Honduras,sponsor: Julia Gutierrez,ice_check_in_date: 8/7/2019,ice_check_in_location: Nashville, TN,record_type: Emergency Support"
},

 json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"custom_fields_data": "data_transfer__do_not_use: 7/27/2020,total___of_people_in_family: 3,how_old_is_the_youngest_member_of_the_family: 8+,nationality: Honduras,sponsor: Julia Gutierrez,ice_check_in_date: 8/7/2019,ice_check_in_location: Nashville, TN,record_type: Emergency Support"
},

This is what I'm looking for, and I'm thinking that I will have to do it manually (like iterating the result): Instead of all custom fields in that field, make each one of them an individual field, like below.

 json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"address_country": "United States",
"address_city": "Nashville",
"address_line1": null,
"address_postal_code": "37211",
"address_state": "Tennessee",
"description": null,
"email": null,
"customer_status": "none",
"data_transfer__do_not_use: "7/27/2020",
"total___of_people_in_family": 3,
"how_old_is_the_youngest_member_of_the_family": "8+",
"nationality": "Honduras"
...
}

 json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"address_country": "United States",
"address_city": "Nashville",
"address_line1": null,
"address_postal_code": "37211",
"address_state": "Tennessee",
"description": null,
"email": null,
"customer_status": "none",
"data_transfer__do_not_use: "7/27/2020",
"total___of_people_in_family": 3,
"how_old_is_the_youngest_member_of_the_family": "8+",
"nationality": "Honduras"
...
}

I would love to know your opinion

2 replies

DTDrizzle Team

•Created by JP on 5/14/2024 in #help

one-to-many self reference

Hello guys, this is a simplified section of my schema:

 typescript
export const contacts = mysqlTable("contacts", {
  id: int("id").primaryKey(),
  parentId: int("parent_id"),
  ...
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "fk_contacts_parent_id"
    }),
  };
})

 typescript
export const contacts = mysqlTable("contacts", {
  id: int("id").primaryKey(),
  parentId: int("parent_id"),
  ...
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "fk_contacts_parent_id"
    }),
  };
})

contacts are families or family members, where parent_id points to the family I tried to update the above code:

  parentId: int('parent_id').references(() => contacts.id),

  parentId: int('parent_id').references(() => contacts.id),

but I'm getting this error:

'contacts' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.ts(7022)

'contacts' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.ts(7022)

and also have this:

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields),
  parent: one(contacts, {
    fields: [contacts.parentId],
    references: [contacts.id],
  }),
  familyMembers: many(contacts),
})),

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields),
  parent: one(contacts, {
    fields: [contacts.parentId],
    references: [contacts.id],
  }),
  familyMembers: many(contacts),
})),

even without the .references, the below code silently fails:

const result = await db.query.contacts.findMany({
    with: {
      parent: true,
      familyMembers: true,
    },
    limit: 1
  })

const result = await db.query.contacts.findMany({
    with: {
      parent: true,
      familyMembers: true,
    },
    limit: 1
  })

8 replies

Gaming

Programming