Checks Constraints Support

[OlivierCavadenti]

• Add two functions on table level :
  Add a raw check constraint with optional constraint name.
  => check(checkPredicate: string, bindings?: any[], constraintName?: string) : TableBuilder;
  Drop check constraints
  => dropChecks(checkConstraintNames: string | string[]): TableBuilder;

• Add raw or specific constraints with column builder (at table creation or alter) :

    check(checkPredicate: string, bindings?: any, constraintName?: string): ColumnBuilder;
    checkPositive(constraintName?: string): ColumnBuilder;
    checkNegative(constraintName?: string): ColumnBuilder;
    checkIn(values : string[], constraintName?: string): ColumnBuilder;
    checkNotIn(values : string[], constraintName?: string): ColumnBuilder;
    checkBetween(values : any[] | any[][], constraintName?: string): ColumnBuilder;
    checkLength(operator : string, length: number, constraintName?: string): ColumnBuilder;
    checkRegex(regex: string, constraintName?: string): ColumnBuilder;

checkBetween accepts one or multiple intervals:

knex.schema
  .createTable('check_test', function (table) {
    table.integer('price').checkBetween([
      [10, 20],
      [30, 40],
    ]);
  })

checkLength check the length of value.
checkRegex check if the column values are valid according the given regex.

• Finish [WIP] Check constraints #2364
• Closes Support CHECK #266

[intech]

I will make the necessary edits for cockroach

[intech]

@OlivierCavadenti I added an additional request to the dialect, which will solve 3 compatibility problems at once.
And added a warn message to the console with a link to an issue about an experimental mode of supporting some queries in ALTER COLUMN.

[OlivierCavadenti]

@OlivierCavadenti I added an additional request to the dialect, which will solve 3 compatibility problems at once. And added a warn message to the console with a link to an issue about an experimental mode of supporting some queries in ALTER COLUMN.

I add a little comment. I will merge here after that

[kibertoad]

@OlivierCavadenti I'm planning to start preparations for 1.0.0 release. Do you think this can still make it, or we should include it in 1.1.0 instead?

[OlivierCavadenti]

@OlivierCavadenti I'm planning to start preparations for 1.0.0 release. Do you think this can still make it, or we should include it in 1.1.0 instead?

I plan to finish doc this weekend, if it's not too late for you.

[kibertoad]

Sure, sounds good! There are also some conflicts to resolve.

[kibertoad]

is list of supported operators DB-specific? wonder if it would be feasible to narrowly type all the options

[kibertoad]

are bindings really any? Aren't they closer to Record<string, any>? Or that is not necessarily the case?

[OlivierCavadenti]

I will change it on other check function. It's seems that raw check on column make no sense (useful only on table) so this function need to be deleted.

[tx0c]

the Checks Constraints is exactly needed, wonder before this is released, can knex.raw be used inside a schema builder?

currently I am hitting an constraint when alter enum to add more possible values

  await knex.schema.alterTable(table, (t) => {
    // ... many other column altering...
    t.enu('purpose', ['value1', 'value2', 'newVal3', 'newVal4']).alter()
  })

but this column has a check already, and failed on the check, have to manually remove the check before altering enum, and add the check after altering enum

  await knex.schema.alterTable(table, (t) => {
    // ... many other column altering...
    t.dropCheck(`${table}_purpose_check')
    t.enu('purpose', ['value1', 'value2', 'newVal3', 'newVal4']).alter()
    t.check(`${table}_purpose_check')
  })

before this #4874 is released, is there a way to do it raw?

  await knex.schema.alterTable(table, (t) => {
    // ... many other column altering...
    t.raw(`DROP CONSTRAINT IF EXISTS ${table}_purpose_check;')
    t.enu('purpose', ['value1', 'value2', 'newVal3', 'newVal4']).alter()
    t.raw(`ADD CONSTRAINT IF EXISTS ${table}_purpose_check CHECK (${column} = ANY (ARRAY['${enumValues}'::text]));`)
  })

so far no luck:

migration failed with error: t.raw is not a function
TypeError: t.raw is not a function

[OlivierCavadenti]

migration failed with error: t.raw is not a function
TypeError: t.raw is not a function

raw function is not on table object but in knex. Test with 'knex.raw(....)'

the Checks Constraints is exactly needed, wonder before this is released

It will be released very soon I think, I have a busy year beginning but I think today it will be ok.

[kibertoad]

Build is green! Is it only waiting for documentation now?

[tx0c]

raw function is not on table object but in knex. Test with 'knex.raw(....)'

can that be another feature request? because it makes sense for table t and re-use under the same .alterTable group,
if starts with knex.raw that could be knex.raw('ALTER TABLE ${tableName} DROP CONSTRAINT IF EXISTS ...'), but I suggest if starts with table.raw it can be this t.raw('DROP CONSTRAINT IF EXISTS ...') without the prefix part of ALTER TABLE ${tableName} ...

  await knex.schema.alterTable(table, (t) => {
    // ... many other column altering...
    t.raw(`DROP CONSTRAINT IF EXISTS ${table}_purpose_check;')
    t.enu('purpose', ['value1', 'value2', 'newVal3', 'newVal4']).alter()
    t.raw(`ADD CONSTRAINT IF EXISTS ${table}_purpose_check CHECK (${column} = ANY (ARRAY['${enumValues}'::text]));`)
  })

[OlivierCavadenti]

Build is green! Is it only waiting for documentation now?

I push the doc, sorry for the delay :D

[OlivierCavadenti]

@kibertoad good to see this in 1.0.0 :D.
Can you ping me on open PRs that need rebased or small changes that need to be in 1.0.0 ?