BinaryArtifex Posts - Answer Overflow

BinaryArtifex

Explore posts from servers

•Created by BinaryArtifex on 1/6/2024 in #help

how to add ulid compatibility to postgres?

Is there a way to create a custom ulid type with postgres to use as a primary key? it could either be automatically generated from the database side or passed in from the client. it was easy enough to create a custom type using mysql that was essentially just a char(26), however im trying to implement something a little closer to this article - https://blog.daveallie.com/ulid-primary-keys. any advice would be greatly appreciated. This is also in the context of having a supabase postgres instance.

2 replies

DTDrizzle Team

•Created by BinaryArtifex on 8/31/2023 in #help

help with a generic mysql2 connection

gday folks, im trying to create what is essentially a generic mysql2 connection that is compatible with planetscale but also compatible with a local msyql2 server running in docker, for instance...my production database is planetscale hosted and any sort of dev or testing i want to do locally. My connection at the moment is literally just drizzle(connection, { mode: DB_MODE, schema }) however due to the sheer amount of options within the configuration im concerned im missing something crucial for the continued integration with planetscale with such a minimal config. granted the port, user, password, schema, etc are all in the uri, but is there anything else i should be considering?

2 replies

DTDrizzle Team

•Created by BinaryArtifex on 7/31/2023 in #help

onDuplicateKeyUpdate ???

i don't suppose anyone knows how to actually use this method? zero documentation on it and the planetscale dbClient only recognises this method after the values function. There is no "onConflictDoUpdate" or "onConflictDoNothing"....i just want it so if there is an item with the same slug, ignore the query...

export async function seed(client: DbClient) {
  await client
    .insert(customerMedicalCategory)
    .values(
      data.map((item) => ({
        id: ulid(),
        name: item.name,
        slug: paramCase(item.name),
        description: item.description
      })),
    ).onDuplicateKeyUpdate(???)
}

export async function seed(client: DbClient) {
  await client
    .insert(customerMedicalCategory)
    .values(
      data.map((item) => ({
        id: ulid(),
        name: item.name,
        slug: paramCase(item.name),
        description: item.description
      })),
    ).onDuplicateKeyUpdate(???)
}

2 replies

DTDrizzle Team

•Created by BinaryArtifex on 7/29/2023 in #help

onConflictDoNothing does not exist on planetscale client

i am getting an "Property onConflictDoNothing does not exist on type" on the following script....

export async function lookupNationalitySeed(client: DbClient) {
  await client.insert(lookupNationality).values(
    data.map((nationality) => ({
      id: ulid(),
      name: nationality,
      slug: paramCase(nationality),
    })),
  ).onConflictDoNothing()
}

export async function lookupNationalitySeed(client: DbClient) {
  await client.insert(lookupNationality).values(
    data.map((nationality) => ({
      id: ulid(),
      name: nationality,
      slug: paramCase(nationality),
    })),
  ).onConflictDoNothing()
}

the client is a type of PlanetScaleDatabase<typeof schema> the only typesafe method i get is onDuplicateKeyUpdate

6 replies

DTDrizzle Team

•Created by BinaryArtifex on 7/29/2023 in #help

cannot set alias for composite primary key, getting (errno 1059) (sqlstate 42000) errors

My table has the following schema ->

export const emergencyContactPhoneNumber = mysqlTable(
  "emergency_contact_phone_number",
  {
    emergencyContactId: ulid("emergency_contact_id").notNull(),
    phoneNumberId: ulid("phone_number_id").notNull(),
    description: varchar("description", { length: 10 }).notNull(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (table) => ({
    primaryKey: primaryKey(table.emergencyContactId, table.phoneNumberId),
  }),
);

export const emergencyContactPhoneNumber = mysqlTable(
  "emergency_contact_phone_number",
  {
    emergencyContactId: ulid("emergency_contact_id").notNull(),
    phoneNumberId: ulid("phone_number_id").notNull(),
    description: varchar("description", { length: 10 }).notNull(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (table) => ({
    primaryKey: primaryKey(table.emergencyContactId, table.phoneNumberId),
  }),
);

when migrating to planetscale i get the following error ->

DatabaseError: target: mycompany.-.primary: vttablet: rpc error: code = InvalidArgument desc = Identifier name 'emergency_contact_phone_number_emergency_contact_id_phone_number_id' is too long

DatabaseError: target: mycompany.-.primary: vttablet: rpc error: code = InvalidArgument desc = Identifier name 'emergency_contact_phone_number_emergency_contact_id_phone_number_id' is too long

the generated sql produces this monster index for the composite primary key

CREATE TABLE `emergency_contact_phone_number` (
    `emergency_contact_id` char(26) NOT NULL,
    `phone_number_id` char(26) NOT NULL,
    `description` varchar(10) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `emergency_contact_phone_number_emergency_contact_id_phone_number_id` PRIMARY KEY(`emergency_contact_id`,`phone_number_id`)
);

CREATE TABLE `emergency_contact_phone_number` (
    `emergency_contact_id` char(26) NOT NULL,
    `phone_number_id` char(26) NOT NULL,
    `description` varchar(10) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `emergency_contact_phone_number_emergency_contact_id_phone_number_id` PRIMARY KEY(`emergency_contact_id`,`phone_number_id`)
);

how the heck can i set an alias for a composite primary key?

13 replies

Gaming

Programming