client.alterDatabase()

Programmatically perform an ALTER DATABASE operation.

Syntax

client.alterDatabase(
    alterSpec: string | { name: string, tables?: string[] },
    callback: (databaseSchema: DatabaseSchemaAPI) => void,
    options?: QueryOptions
): Promise<Savepoint | boolean>;

Param	Type	Description
alterSpec	string or object	A database name, or an object specifying the name and, optionally, a list of tables to be included in the retrieved database schema instance.
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

alterSpec patterns

Specify database by name:

// Change DB name
const savepoint = await client.alterDatabase(
    'database_1',
    (databaseSchema) => {
        databaseSchema.name('database_1_new');
    },
    { desc: 'Renaming for testing purposes' }
);

or by an object, with an optional list of tables to be altered along with it:

// Change DB name
const savepoint = await client.alterDatabase(
    { name: 'database_1', tables: [] },
    (databaseSchema) => {
        databaseSchema.name('database_1_new');
    }, 
    { desc: 'Renaming for testing purposes' }
);

Subtree manipulation

Include tables in the schema to be altered:

// Manipulate tables
const savepoint = await client.alterDatabase(
    { name: 'database_1', tables: ['table_1'] },
    (databaseSchema) => {
        // Change DB name
        databaseSchema.name('database_1_new');
        // Drop a table
        databaseSchema.table('table_1', false);
        // A add a table
        databaseSchema.table({
            name: 'table_2',
            columns: [{
                name: 'column_1',
                type: 'varchar'
            }],
        });
    }, 
    { desc: 'Major revamp ahead of next app version' }
);

Alter to the column level:

// Manipulate columns
const savepoint = await client.alterDatabase(
    { name: 'database_1', tables: ['table_1', 'table_2'] },
    (databaseSchema) => {
        // Change a column's name and type
        databaseSchema.table('table_1').column('column_1').name('column_1_new');
        databaseSchema.table('table_1').column('column_1').type('varchar');
        // Drop a column
        databaseSchema.table('table_1').column('column_2', false);
        // Add a column
        databaseSchema.table('table_2').column({
            name: 'column_1',
            type: 'varchar'
        });
    }, 
    { desc: 'Exteensive changes to support planned features' }
);

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