PostgreSQL/CockroachDB ALTER TABLE ADD UNIQUE syntax is invalid for named unique constraints

[cjzamora]

What version of Inquire are you using?

@stackpress/inquire@0.10.5, @stackpress/inquire-pg@0.10.5, stackpress@0.10.5

What OS are you experiencing this issue?

macOS Darwin arm64

Describe the Bug

Bug: PostgreSQL/CockroachDB alter unique constraint SQL uses invalid ADD UNIQUE syntax

Summary

@stackpress/inquire generates invalid PostgreSQL/CockroachDB SQL when adding a
new named unique constraint through an ALTER TABLE migration.

It currently emits:

ALTER TABLE "sender_intent" ADD UNIQUE "work_email_unique" ("work_email")

PostgreSQL/CockroachDB expects:

ALTER TABLE "sender_intent"
ADD CONSTRAINT "work_email_unique" UNIQUE ("work_email")

Affected Versions

• @stackpress/inquire: 0.10.5
• @stackpress/inquire-pg: 0.10.5
• stackpress: 0.10.5
• Database: CockroachDB/PostgreSQL-compatible connection through pg

Reproduction

1. Start with an existing table generated without a unique key on a field.
2. Add @unique to an existing field in a Stackpress schema.

Example:

workEmail String
          @label("Work Email")
          @unique @searchable
          @field.email
          @is.required("Work email is required")
          @is.email("Work email must be valid")

3. Run generation and push:

yarn generate
yarn push

4. stackpress push attempts this SQL:

ALTER TABLE "sender_intent" ADD UNIQUE "work_email_unique" ("work_email")

Actual Result

CockroachDB rejects the generated SQL:

error: at or near "work_email_unique": syntax error
detail: source SQL:
ALTER TABLE "sender_intent" ADD UNIQUE "work_email_unique" ("work_email")
                                       ^
hint: try \h ALTER TABLE
code: 42601

Expected Result

The PostgreSQL dialect should emit a named unique constraint using
ADD CONSTRAINT:

ALTER TABLE "sender_intent"
ADD CONSTRAINT "work_email_unique" UNIQUE ("work_email")

Suspected Source

The issue appears to come from the PostgreSQL dialect alter builder.

In @stackpress/inquire/esm/dialect/Pgsql.js, the build.unique.add branch
emits:

query.push(`ALTER TABLE ${this.q}${build.table}${this.q} `
  + `ADD UNIQUE ${this.q}${key}${this.q} (${this.q}${build.unique.add[key].join(`${this.q}, ${this.q}`)}${this.q})`);

The CJS build has the same pattern.

Verified Local Workaround

Changing the PostgreSQL dialect to emit ADD CONSTRAINT ... UNIQUE ... fixes
the failing push:

query.push(`ALTER TABLE ${this.q}${build.table}${this.q} `
  + `ADD CONSTRAINT ${this.q}${key}${this.q} UNIQUE (${this.q}${build.unique.add[key].join(`${this.q}, ${this.q}`)}${this.q})`);

After applying that local dependency patch, yarn push succeeded and printed:

ALTER TABLE "sender_intent"
ADD CONSTRAINT "work_email_unique" UNIQUE ("work_email")

Notes

Inline unique constraints during CREATE TABLE were not the problem. The
failure only appeared when adding a new unique constraint to an existing table
through the generated ALTER TABLE path.

Expected Behavior

When adding a named unique constraint to an existing PostgreSQL/CockroachDB table, @stackpress/inquire should generate valid ALTER TABLE ... ADD CONSTRAINT ... UNIQUE ... SQL.

Expected SQL:

ALTER TABLE "sender_intent"
ADD CONSTRAINT "work_email_unique" UNIQUE ("work_email")

### To Reproduce

1. Start with an existing Stackpress model/table that does not have a unique constraint on a field.

2. Add `@unique` to an existing field in `schema.idea`:

```idea
workEmail String
          @label("Work Email")
          @unique @searchable
          @field.email
          @is.required("Work email is required")
          @is.email("Work email must be valid")

3. Generate the updated client/schema output:

yarn generate

4. Push the schema/database changes:

yarn push

5. The push fails because the generated SQL is:

ALTER TABLE "sender_intent" ADD UNIQUE "work_email_unique" ("work_email")

This is invalid for PostgreSQL/CockroachDB named unique constraints.

Include git repo/fork so we can easily reproduce the issue

No response

[cblanquera]

fixed in 0.10.6