New constraint format

[ab-pm]

This is a follow-up to #467. I've had a use case now where I wanted to create a primary key constraint with a comment, and it's not easily possible. For foreign keys we now have the referencesConstraintComment, but adding more options in this scheme would be quite ugly. Also I noticed that constraint options like deferrable and deferred are placed next to the constraints, not related to the constraint itself.

I would recommend a complete redesign, with one object per constraint definition following the SQL data model. My idea:

type ConstraintOptions = {
    name: string
    comment: string
    deferrable: boolean
    deferred: boolean
}
interface ExpressionConstraint extends ConstraintOptions {
    type: 'CHECK' | 'EXCLUDE'
    expression: string
}
interface IndexConstraint extends ConstraintOptions {
    type: 'UNIQUE' | 'PRIMARY (KEY)'
    columns: string | string[]
    index: string | IndexOptions
}
interface RefConstraint extends ConstraintOptions {
    type: 'FOREIGN (KEY)'
    columns: string | string[]
    references: string | Name
    referencesColumns?: string | string[]
    match: …
    onDelete: …
    onUpdate: …
}
type Constraint = ExpressionConstraint | IndexConstraint | RefConstraint

(maybe more detailed wrt exclude constraints - but for now a simple string should suffice)

I will ignore NOT NULL, NULL, DEFAULT and IDENTITY constraints as these seem more like column options, and although technically they seem to be constraints and could be named, that seems to be ignored anyway, and they have their own ALTER COLUMN syntax.

So each column definition would simply get a constraints array property similar to what table options already have. For ease of use, we'd get the following shorthands:

• {check: expression} becomes {type: 'CHECK', expression}, same for exclude.
• {unique: columns} becomes {type: 'UNIQUE', columns}, same for primaryKey
• {references} becomes {type: 'FOREIGN', references}
• If any of these shorthand expansions collides with an already-existing type, an error is thrown - any constraint object can only represent a single constraint

For backwards compatibility (and ease of use), the column definition current format is interpreted as a shorthand as well:

• check, unique, references, primaryKey properties that contain Constraint objects automatically get their type set
• check, unique, references, primaryKey properties that contain primitives, arrays or Names will get expanded into the respective Constraint object
• options like deferrable/deferred and the RefConstraint ones get propagated down to all generated/expanded constraint objects
• the foreignKeys array would become deprecated, it would be interpreted just like the constraints array.

What do you think about this? Does it sound reasonable? Are there any backward-compatibility concerns?

[dolezel]

Sounds good, but I would rather implement it after typescript rewrite ... https://github.com/salsita/node-pg-migrate/pull/502

[ab-pm]

Cool, good I waited before rushing to the implementation :-) I should find some time to do it this or next week.