How to set a default value for column based on another column?

For example, if I had this schema:

export const posts = sqliteTable({
  id: integer("id").primaryKey(),
  title: text("title"),
  slug: text("slug").default(/* ??? */),
});

export const posts = sqliteTable({
  id: integer("id").primaryKey(),
  title: text("title"),
  slug: text("slug").default(/* ??? */),
});

How could I make the slug column's default be determined based on title via a slugify function?

11 Replies

Angelelz•15mo ago

I'm not sure in sqlite but in Mysql there are generated columns that can do that for you Those are currently not supported in Drizzle, but you could use custom types to make it work

psmOP•15mo ago

I have to use SQLite, I'm using Cloudflare D1

Angelelz•15mo ago

I have the following generated column in my codebase:

const customGeneratedText = (name: string, ...columns: [string, ...string[]]) =>
  customType<{ data: undefined; driverData: undefined }>({
    dataType() {
      return `text as (concat_ws(" ", ${columns.join(", ")})) invisible`;
    }
  })(name);

const customGeneratedText = (name: string, ...columns: [string, ...string[]]) =>
  customType<{ data: undefined; driverData: undefined }>({
    dataType() {
      return `text as (concat_ws(" ", ${columns.join(", ")})) invisible`;
    }
  })(name);

And I use it like this:

export const users = mysqlTable(
  "users",
  {
    id: char("id", { length: 26 }).primaryKey().notNull(),
    username: varchar("username", { length: 191 }).notNull(),
    firstName: varchar("firstName", { length: 191 }).notNull(),
    lastName: varchar("lastName", { length: 191 }).notNull(),
    email: varchar("email", { length: 191 }).notNull(),
    password: varchar("password", { length: 191 }).notNull(),
    emailVerified: datetime("emailVerified", { mode: "date", fsp: 3 }),
    image: varchar("image", { length: 191 }),
    role: mysqlEnum("role", ["owner", "admin", "user"]).notNull(),
    createdAt: datetime("createdAt", { mode: "date", fsp: 3 })
      .default(sql`(CURRENT_TIMESTAMP(3))`)
      .notNull(),
    clockedIn: datetimeUtc("clockedIn", { mode: "date", fsp: 3 }),
    theme: varchar("theme", { length: 191 }),
    tags: varchar("tags", { length: 191 }),
    search: customGeneratedText(
      "search",
      "username",
      "firstName",
      "lastName",
      "email",
      "role",
      "tags"
    )
  },
  (table) => {
    return {
      emailKey: uniqueIndex("user_email_key").on(table.email),
      usernameKey: uniqueIndex("user_username_key").on(table.username)
    };
  }
);

export const users = mysqlTable(
  "users",
  {
    id: char("id", { length: 26 }).primaryKey().notNull(),
    username: varchar("username", { length: 191 }).notNull(),
    firstName: varchar("firstName", { length: 191 }).notNull(),
    lastName: varchar("lastName", { length: 191 }).notNull(),
    email: varchar("email", { length: 191 }).notNull(),
    password: varchar("password", { length: 191 }).notNull(),
    emailVerified: datetime("emailVerified", { mode: "date", fsp: 3 }),
    image: varchar("image", { length: 191 }),
    role: mysqlEnum("role", ["owner", "admin", "user"]).notNull(),
    createdAt: datetime("createdAt", { mode: "date", fsp: 3 })
      .default(sql`(CURRENT_TIMESTAMP(3))`)
      .notNull(),
    clockedIn: datetimeUtc("clockedIn", { mode: "date", fsp: 3 }),
    theme: varchar("theme", { length: 191 }),
    tags: varchar("tags", { length: 191 }),
    search: customGeneratedText(
      "search",
      "username",
      "firstName",
      "lastName",
      "email",
      "role",
      "tags"
    )
  },
  (table) => {
    return {
      emailKey: uniqueIndex("user_email_key").on(table.email),
      usernameKey: uniqueIndex("user_username_key").on(table.username)
    };
  }
);

Now you should take away the invisible because for us this was just for searching at db level That should point you in the right direction

tonyx•10mo ago

That doesn't quite seem to work though. The generated sql looks like

"full_name" "VARCHAR(100) GENERATED ALWAYS AS (concat(first_name, '_', last name)) STORED"

"full_name" "VARCHAR(100) GENERATED ALWAYS AS (concat(first_name, '_', last name)) STORED"

note the extra "" quotes. Is there a way to unquote it?

Angelelz•10mo ago

I think it's an issue with drizzle kit..gotta wait for the fix. But really gotta wait for official generated columns support

kane•6mo ago

I got this notice when trying with drizzle-kit push

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42622',
  message: `identifier "varchar GENERATED ALWAYS AS (prefix || lpad(number::varchar, 4, '0')) STORED" will be truncated to "varchar GENERATED ALWAYS AS (prefix || lpad(number::varchar, 4,"`,
  file: 'scansup.c',
  line: '99',
  routine: 'truncate_identifier'
}

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42622',
  message: `identifier "varchar GENERATED ALWAYS AS (prefix || lpad(number::varchar, 4, '0')) STORED" will be truncated to "varchar GENERATED ALWAYS AS (prefix || lpad(number::varchar, 4,"`,
  file: 'scansup.c',
  line: '99',
  routine: 'truncate_identifier'
}

then postgres throws error

PostgresError: type "varchar GENERATED ALWAYS AS (prefix || lpad(number::varchar, 4," does not exist

PostgresError: type "varchar GENERATED ALWAYS AS (prefix || lpad(number::varchar, 4," does not exist

kane•6mo ago

ahh turns out same as this issue

kane•6mo ago

maybe patching here is a temp solution for it

kane•6mo ago

or the isPgNativeType function

tonyx•6mo ago

@kane were you able to find a fix for it?

kane•6mo ago

no need to patch it haha there's official support https://discord.com/channels/1043890932593987624/1235937884956000377/1255865234418045039 🎉

Gaming

Programming

How to set a default value for column based on another column?