Introduction¶

The Schema class provides a database agnostic way of manipulating tables.

Before getting started, be sure to have configured a DatabaseManager as seen in the Basic Usage section.

from orator import DatabaseManager, Schema

config = {
    'mysql': {
        'driver': 'mysql',
        'host': 'localhost',
        'database': 'database',
        'username': 'root',
        'password': '',
        'prefix': ''
    }
}

db = DatabaseManager(config)
schema = Schema(db)

Creating and dropping tables¶

To create a new database table, the create method is used:

with schema.create('users') as table:
    table.increments('id')

The table variable is a Blueprint instance which can be used to define the new table.

To rename an existing database table, the rename method can be used:

schema.rename('from', 'to')

To specify which connection the schema operation should take place on, use the connection method:

with schema.connection('foo').create('users') as table:
    table.increments('id')

To drop a table, you can use the drop or drop_if_exists methods:

schema.drop('users')

schema.drop_if_exists('users')

Adding columns¶

To update an existing table, you can use the table method:

with schema.table('users') as table:
    table.string('email')

The table builder contains a variety of column types that you may use when building your tables:

Changing columns¶

Sometimes you may need to modify an existing column. For example, you may wish to increase the size of a string column. To do so, you can use the change method. For example, let’s increase the size of the name column from 25 to 50:

with schema.table('users') as table:
    table.string('name', 50).change()

You could also modify the column to be nullable:

with schema.table('user') as table:
    table.string('name', 50).nullable().change()

Renaming columns¶

To rename a column, you can use use the rename_column method on the Schema builder:

with schema.table('users') as table:
    table.rename_column('from', 'to')

with schema.table('posts') as table:
    table.drop_foreign('posts_user_id_foreign')
    table.rename_column('user_id', 'author_id')
    table.foreign('author_id').references('id').on('users')

Dropping columns¶

To drop a column, you can use use the drop_column method on the Schema builder:

with schema.table('users') as table:
    table.drop_column('votes')

with schema.table('users') as table:
    table.drop_column('votes', 'avatar', 'location')

Checking existence¶

You can easily check for the existence of a table or column using the has_table and has_column methods:

if schema.has_table('users'):
    # ...

if schema.has_column('users', 'email'):
    # ...

Adding indexes¶

The schema builder supports several types of indexes. There are two ways to add them. First, you may fluently define them on a column definition:

table.string('email').unique()

Or, you may choose to add the indexes on separate lines. Below is a list of all available index types:

table.index(
    ['field_with_really_long_name',
     'another_field_with_long_name'],
    name='my_uniq_idx'
)

Dropping indexes¶

To drop an index you must specify the index’s name. Orator assigns a reasonable name to the indexes by default. Simply concatenate the table name, the names of the column in the index, and the index type. Here are some examples:

Foreign keys¶

Orator also provides support for adding foreign key constraints to your tables:

table.integer('user_id').unsigned()
table.foreign('user_id').references('id').on('users')

In this example, we are stating that the user_id column references the id column on the users table. Make sure to create the foreign key column first!

You may also specify options for the “on delete” and “on update” actions of the constraint:

table.foreign('user_id')\
    .references('id').on('users')\
    .on_delete('cascade')

To drop a foreign key, you may use the drop_foreign method. A similar naming convention is used for foreign keys as is used for other indexes:

table.drop_foreign('posts_user_id_foreign')

PRAGMA foreign_keys = ON

config = {
    'sqlite': {
        'driver': 'sqlite',
        'database': ':memory:',
        'foreign_keys': False
    }
}

Dropping timestamps and soft deletes¶

To drop the timestamps, nullable_timestamps or soft_deletes column types, you may use the following methods: