[BUG]: Generate columns : The doc info is invalid

[maxpaleo]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.37.0

What version of drizzle-kit are you using?

0.29.1

Other packages

No response

Describe the Bug

I spent the past 3 hours trying to figure out an error I kept on running into with generate columns.
These are the docs I referenced: https://orm.drizzle.team/docs/generated-columns

Every example shown in the doc will throw an error

export const test = pgTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`hello "world"!`),
});
// Error: error: syntax error at or near "world"

export const test = pgTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`hello "world"!`),
});
// Error: error: syntax error at or near "world"

Is this article deprecated? Or what is the correct to assign text to generated columns?

[m-atthart]

I'm having the same issue. Here's a repro: https://drizzle.run/yctyikzrddx9ah8s6j3l9qts
Go to schema page to see examples. The first two schemas are working examples, all the others are not working examples.

After looking at the debug view and doing my own tests, it seems the string is just missing quotations. If you write sql in the template string as if you're writing sql and not a js string, it works.

The docs display the correct output sql that drizzle will produce, but it just doesn't work with postgres. As a temporary fix, just format your generated output like I did in the users_manual_sql table (bottom of this comment. I used two different ways to reference other columns for demo purposes), or ask Claude to write what you want in sql, then use that.

For example, if you have the following table:

export const usersString = pgTable("users_string", {
  firstName: text("first_name").notNull(),
  lastName: text("last_name").notNull(),
  fullName: text("full_name").generatedAlwaysAs(`full name`),
});

It will produce the following sql:

CREATE TABLE IF NOT EXISTS "users_string" (
	"first_name" text NOT NULL,
	"last_name" text NOT NULL,
	"full_name" text GENERATED ALWAYS AS (full name) STORED
);

which will give you the error: syntax error at or near "name"

If you make the table like this however:

export const usersManualSql = pgTable("users_manual_sql", {
  firstName: text("first_name").notNull(),
  lastName: text("last_name").notNull(),
  greeting: text("greeting").generatedAlwaysAs(`'hi ' || first_name || ' ' || "users_manual_sql"."last_name"`),
});

Then it will produce the following sql:

CREATE TABLE IF NOT EXISTS "users_manual_sql" (
	"first_name" text NOT NULL,
	"last_name" text NOT NULL,
	"greeting" text GENERATED ALWAYS AS ('hi ' || first_name || ' ' || "users_manual_sql"."last_name") STORED
);

Which will give you the correct data e.g.:

{
  "firstName": "Timya",
  "lastName": "Tighe",
  "greeting": "hi Timya Tighe"
}

[maxpaleo]

Hi @m-atthart , I appreciate the response. After a couple of tries based on what you mentionned I was able to work around it.

For anyone facing the same issue, below is a working example that will generate a col value using 2 values from the row being inserted.

The example uses a generateSqlHandle() helper function that formats the sql to generate a handle from one or two values.

/**
 * Helper function to generate the sql that will create a handle from a column value.
 * - Optionally pass in a unique column to append to the handle.
 *
 * Example:
 * Name: US Bank Id: 37
 * Ouput: us-bank-37
 */
function generateSqlHandle(column: PgColumn, uniqueColumn?: PgColumn): SQL {
  return sql`lower(replace(${column} || ' ' ||  ${uniqueColumn}, ' ', '-'))`;
}

export const example = pgTable("organization", {
  ...DEFAULTS.ids,
  name: text().notNull(),

  /* Handle is generated from the name and id */
  handle: text()
    .generatedAlwaysAs(
      (): SQL => generateSqlHandle(organization.name, organization.id),
    )
    .notNull()
    .unique(),
});