ALTER DATABASE

Manage Basic Details

Rename database:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER SCHEMA database_1
    RENAME TO database_1_new`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.alterDatabase(
    'database_1',
    (schema) => schema.name('database_1_new'),
    { desc: 'Alter description' }
);

Note

While the function-based syntax may read "alter database", the "schema" kind is implied by default. To enforce the terminology, set options.kind === 'database':

client.alterDatabase(..., { desc: 'Alter description', kind: 'database' });

Alter deeply:

// Function-based syntax
const savepoint = await client.alterDatabase(
    'database_1',
    (schema) => {
        schema.name('database_1_new');
        schema.table('table_1').name('table_1_new');
        schema.table('table_1').column('col_1').name('col_1_new');
    },
    { desc: 'Alter description' }
);

Tip

The equivalent SQL syntax would require three client.query() calls:

1. ALTER DATABASE... RENAME TO...
2. ALTER TABLE... RENAME TO...
3. ALTER TABLE... RENAME COLUMN...

Manage Tables

Add tables:

// (a): SQL syntax
const savepoint = await client.query(
    `CREATE TABLE database_1.table_1 (
        col_1 varchar UNIQUE,
        col_2 varchar
    )`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.alterDatabase(
    'database_1',
    (schema) => {
        schema.table({
            name: 'table_1',
            columns: [
                { name: 'col_1', type: 'varchar', uniqueKey: true },
                { name: 'col_2', type: 'varchar' }
            ]
        });
    },
    { desc: 'Alter description' }
);

Note

Where the table implied by name already exists, the table is modified with the diff between the existing schema and the new schema.

[!TIP]

To add an IF NOT EXISTS flag to each CREATE TABLE operation, set options.existsChecks === true.

client.alterDatabase(..., { desc: 'Alter description', existsChecks: true });

--> Drop tables:

// (a): SQL syntax
const savepoint = await client.query(
    `DROP TABLE database_1.table_1`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.alterDatabase(
    'database_1',
    (schema) => {
        schema.table('table_1', false);
    },
    { desc: 'Alter description' }
);

[!TIP]

To add an IF EXISTS flag to each DROP TABLE operation, set options.existsChecks === true.

client.alterDatabase(..., { desc: 'Alter description', existsChecks: true });

-->

Tip

PostgreSQL:

To add a CASCADE or RESTRICT flag to each DROP TABLE operation, use options.cascadeRule.

client.alterDatabase(..., { desc: 'Alter description', cascadeRule: 'CASCADE' });