Alter

This is used to modify an existing table.

Hint

You can use migrations instead of manually altering the schema - see Migrations.


add_column

Used to add a column to an existing table.

await Band.alter().add_column('members', Integer())

drop_column

Used to drop an existing column.

await Band.alter().drop_column('popularity')

drop_table

Used to drop the table - use with caution!

await Band.alter().drop_table()

drop_db_tables / drop_db_tables_sync

If you have several tables which you want to drop, you can use drop_db_tables or drop_db_tables_sync. The tables will be dropped in the correct order based on their foreign keys.

# async version
>>> from piccolo.table import drop_db_tables
>>> await drop_db_tables(Band, Manager)

# sync version
>>> from piccolo.table import drop_db_tables_sync
>>> drop_db_tables_sync(Band, Manager)

rename_column

Used to rename an existing column.

await Band.alter().rename_column(Band.popularity, 'rating')

set_null

Set whether a column is nullable or not.

# To make a row nullable:
await Band.alter().set_null(Band.name, True)

# To stop a row being nullable:
await Band.alter().set_null(Band.name, False)

set_schema

Used to change the schema which a table belongs to.

await Band.alter().set_schema('schema_1')

Schemas are a way of organising the tables within a database. Only Postgres and Cockroach support schemas. Learn more here.

After changing a table’s schema, you need to update your Table accordingly, otherwise subsequent queries will fail, as they’ll be trying to find the table in the old schema.

Band._meta.schema = 'schema_1'

set_unique

Used to change whether a column is unique or not.

# To make a row unique:
await Band.alter().set_unique(Band.name, True)

# To stop a row being unique:
await Band.alter().set_unique(Band.name, False)