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" type is implied by default.

Alter deeply:

// Function-based syntax
const savepoint = await client.alterDatabase(
    { name: 'database_1', tables: ['table_1', 'table_2'] },
    (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' }
);

Note that the equivalent SQL syntax would require three client.query() calls:

1. ALTER DATABASE... RENAME ...
2. ALTER TABLE... RENAME ...
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' }
);

Tip

Postgres Extras:

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(
    { name: 'database_1', tables: ['table_1', 'table_2'] },
    (schema) => {
        schema.table('table_1', false);
    },
    { desc: 'Alter description' }
);

Tip

Postgres Extras

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

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

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

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