Schema Management¶

This document covers creating and dropping tables, managing indexes and constraints after the fact, and evolving a schema over time.

Creating Tables¶

Create tables for a list of models with Database.create_tables():

db.create_tables([User, Tweet, Favorite])

By default Peewee uses CREATE TABLE IF NOT EXISTS, making it safe to call on application startup. To disable this, pass safe=False.

db.create_tables([User, Tweet, Favorite], safe=False)

To create a single table:

Tweet.create_table()

Database.create_tables() respects foreign key dependencies: if Tweet references User, User’s table is created first regardless of the order in which they appear in the list.

Indexes declared in Meta.indexes and via Model.add_index() are created along with the table.

Note

create_tables creates missing tables but does not apply schema changes to existing ones. For that, see Schema Migrations.

Dropping Tables¶

db.drop_tables([User, Tweet, Favorite])

By default Peewee uses DROP TABLE IF EXISTS, making it safe to call multiple times. To disable this, pass safe=False.

db.drop_tables([User, Tweet, Favorite], safe=False)

Pass cascade=True on Postgresql to drop dependent objects and let the database handle dependency resolution:

db.drop_tables([User, Tweet, Favorite], cascade=True)

To drop a single table:

User.drop_table()

SchemaManager¶

SchemaManager provides finer-grained control over DDL operations. Each model exposes an instance at Model._schema.

Creating and dropping indexes independently:

# Create just the indexes for a model (table already exists).
User._schema.create_indexes()

# Drop a specific index.
User._schema.drop_index(User.username)

Adding a foreign key constraint after table creation (useful when circular foreign keys are involved - see Circular foreign key dependencies in the models document):

# The table exists but the constraint was deferred.
User._schema.create_foreign_key(User.favorite_tweet)

Note

SQLite does not support adding foreign key constraints to existing tables. On SQLite, create_foreign_key will result in an OperationalError.

Truncating a table:

User._schema.truncate_table()  # No cascade.
User._schema.truncate_table(cascade=True)  # Postgresql only.

Schema Migrations¶

Peewee ships two layers of migration tooling in playhouse: the migrate module, which provides a Python interface for making schema changes, and the migrations runner, which runs versioned migration scripts.

Migration runner¶

The runner applies plain-python migration scripts in numeric order, recording each by name in a history table. The CLI is installed as pwmigrate:

# Identifies differences between application code and schema, then
# generates a migration file.
$ pwmigrate app.settings.db generate "add karma" app.models
migrations/0002_add_karma.py

# Equivalent to above, but using a database URL instead of the
# dotted-path:
$ pwmigrate postgresql:///my_db generate "add karma" app.models

$ pwmigrate app.settings.db up
applied: 0002_add_karma

Scripts define up(migrator, db) and, optionally, down(migrator, db). generate builds the migration from a schema diff against your model definitions, while create writes a bare template.

Migrate module¶

The playhouse.migrate module provides a set of helper functions for common schema changes, applied through a SchemaMigrator:

from playhouse.migrate import *

db = SqliteDatabase(...)

migrator = SchemaMigrator.from_database(db)

first_name = TextField(default='')
last_name  = TextField(default='')

with db.atomic():
    migrate(
        migrator.add_column('person', 'first_name', first_name),
        migrator.add_column('person', 'last_name',  last_name),
        migrator.drop_column('person', 'name'),
    )

Supported operations:

Add, rename, or drop columns.
Make columns nullable or not nullable.
Change a column’s type.
Rename a table.
Add or drop indexes and constraints.
Add or drop column default values.

Raw SQL migrations¶

For changes the migrate module does not cover, execute ALTER TABLE statements directly:

with db.atomic():
    db.execute_sql('ALTER TABLE tweet ADD COLUMN view_count INTEGER DEFAULT 0')

SQLite limitations¶

SQLite has limited ALTER TABLE support depending on which version is installed. Some functionality can be emulated using a detailed fallback path which moves the existing table, recreates a new table, then copies into the newly-created table.

Version-specific or limited functionality:

DROP COLUMN (3.35.0, fallback for older).
RENAME COLUMN (3.25.0, fallback for older).
ALTER COLUMN ... SET/DROP NOT NULL (3.53.0, fallback for older).
ALTER COLUMN ... DEFAULT ... (uses fallback)
ALTER COLUMN ... TYPE ... (uses fallback)
ADD/DROP CONSTRAINT (3.53.0, limited to certain operations).

For more complex SQLite schema changes, the standard workaround is to:

Create a new table with the desired schema.
Copy data with INSERT INTO new_table SELECT ... FROM old_table.
Drop the old table.
Rename the new table.

The playhouse migrate module transparently handles the above workaround for older SQLite versions.

Introspecting an Existing Schema¶

Database.get_tables() returns the names of all tables in the database:

db.get_tables()
# ['user', 'tweet', 'favorite']

Database.get_columns() returns column metadata for a table as a list of ColumnMetadata instances:

for col in db.get_columns('tweet'):
    print(col.name, col.data_type, col.null)

Database.get_indexes() returns index metadata as a list of IndexMetadata instances:

for idx in db.get_indexes('user'):
    print(idx.name, idx.columns, idx.unique)

Database.get_foreign_keys() returns foreign key metadata as a list of ForeignKeyMetadata instances:

for fk in db.get_foreign_keys('tweet'):
    print(fk.column, '->', fk.dest_table, fk.dest_column)

Database.get_views() returns a list of views in the database as a list of ViewMetadata instances:

for view in db.get_views():
    print(view.name, view.sql)

Generating models from an existing database¶

The pwiz - Model Generator command-line tool introspects an existing database and emits Python model definitions:

pwiz -e postgresql my_database > models.py
pwiz -e sqlite my_app.db > models.py