database.alterTable()

Programmatically perform an ALTER TABLE operation.

Syntax

database.alterTable(
    alterSpec: string,
    callback: (tableSchema: TableSchemaAPI) => void,
    options?: QueryOptions
): Promise<Savepoint | boolean>;

Param	Type	Description
alterSpec	string	A table name.
callback	function	A callback function that recieves the requested schema. This can be async.
options	QueryOptions	Standard QueryOptions.

Return Value

• Savepoint | boolean: a Savepoint instance (See ➞ Savepoint) or the boolean true when savepoint creation has been disabled via options.noCreateSavepoint; (Compare ➞ Query Return Value)

Usage

Change table name:

// Change DB name
const savepoint = await database.alterTable(
    'table_1',
    (tableSchema) => {
        tableSchema.name('table_1_new');
    },
    { desc: 'Renaming for testing purposes' }
);

Alter to the columns level:

// Manipulate columns
const savepoint = await database.alterTable(
    'table_1',
    (tableSchema) => {
        // Change a column's name and type
        tableSchema.column('column_1').name('column_1_new');
        tableSchema.column('column_1').type('varchar');
        // Drop a column
        tableSchema.column('column_2', false);
        // Add a column
        tableSchema.column({
            name: 'column_3',
            type: 'varchar'
        });
    }, 
    { desc: 'Exteensive changes to support planned features' }
);

Alter to the constraints level:

// Manipulate constraints
const savepoint = await database.alterTable(
    'table_1',
    (tableSchema) => {
        // Change a column's constraints
        tableSchema.column('column_1').notNull(true);
        tableSchema.column('column_1').uniqueKey(true);
        tableSchema.column('column_1').check({
            expr: { in: ['column_1', { value: 'value_1' }, { value: 'value_1' }] }
        });
        tableSchema.column('column_1').foreignKey({
            targetTable: 'table_2',
            targetColumns: ['column_1'],
        });
        // Drop a constraint
        tableSchema.column('column_2').foreignKey(false);
        // Drop a table-level constraint
        tableSchema.constraint('constraint_1', false);
        // Add a table-level constraint
        tableSchema.constraint({
            type: 'UNIQUE_KEY',
            columns: ['column_1', 'column_2'],
            name: 'constraint_2'
        });
    }, 
    { desc: 'Exteensive changes to support planned features' }
);