ALTER DATABASE

DOCS • LANG • ALTER

---

See APIS ➞ client.query(), client.alterDatabase()

Manage Basic Details

Rename database:

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

// (b): Function-based syntax
const database = 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 actually imply the "database" kind, set options.kind === 'database':

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

Alter deeply:

// Function-based syntax
const database = 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 via client.query() would otherwise be:

1. .query('ALTER DATABASE... RENAME TO...')
2. .query('ALTER TABLE... RENAME TO...')
3. .query('ALTER TABLE... RENAME COLUMN...')

Manage Tables

See related ➞ CREATE TABLE, ALTER TABLE, DROP TABLE

Add tables:

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

// (b): Function-based syntax
const database = 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.

Drop tables:

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

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

Tip

PostgreSQL:

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

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

The RETURNING clause

Return the resulting database schema:

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

// (b): Function-based syntax
const schema = await client.alterDatabase(
    'database_1',
    (schema) => {
        schema.name('database_1_new');
    },
    { desc: 'Renaming for testing purposes', returning: 'schema' }
);

See related ➞ database.schema()

Return the associated savepoint instance:

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

// (b): Function-based syntax
const savepoint = await client.alterDatabase(
    'database_1',
    (schema) => {
        schema.name('database_1_new');
    },
    { desc: 'Renaming for testing purposes', returning: 'savepoint' }
);

See related ➞ database.savepoint()