Database Tooling¶

This section covers the playhouse modules for managing connections, database URLs, schema migrations, introspection, code generation, and testing.

On this page

Database URLs
Connection Pooling
Schema Migrations
Migration Runner
Schema Diff
Reflection
pwiz - Model Generator
Test Utilities

Database URLs ¶

The playhouse.db_url module lets you configure Peewee from a connection string, which is common in twelve-factor applications where database credentials live in environment variables.

import os
from playhouse.db_url import connect

db = connect(os.environ.get('DATABASE_URL', 'sqlite:///default.db'))

Pass additional keyword arguments in the query string:

db = connect('postgres://user:pass@host/db?max_connections=20')

URL format: scheme://user:password@host:port/dbname?option=value

Common schemes:

Scheme	Database class
`sqlite:///path`	`SqliteDatabase`
`postgres://`	`PostgresqlDatabase`
`postgresext://`	`PostgresqlExtDatabase`
`mysql://`	`MySQLDatabase`

Connection pool implementations:

Scheme	Database class
`sqlite+pool:///path`	`PooledSqliteDatabase`
`postgres+pool://`	`PooledPostgresqlDatabase`
`postgresext+pool://`	`PooledPostgresqlExtDatabase`
`mysql+pool://`	`PooledMySQLDatabase`

Alternate drivers:

Scheme	Database class
`psycopg3://`	`Psycopg3Database`
`psycopg3+pool://`	`PooledPsycopg3Database`
`cockroachdb://`	`CockroachDatabase`
`cockroachdb+pool://`	`PooledCockroachDatabase`
`cysqlite://`	`CySqliteDatabase`
`cysqlite+pool://`	`PooledCySqliteDatabase`
`apsw://`	`APSWDatabase`
`mariadbconnector://`	`MariaDBConnectorDatabase`
`mariadbconnector+pool://`	`PooledMariaDBConnectorDatabase`
`mysqlconnector://`	`MySQLConnectorDatabase`
`mysqlconnector+pool://`	`PooledMySQLConnectorDatabase`

connect(url, unquote_password=False, unquote_user=False, **connect_params)¶

Parameters

url – the URL for the database, see examples.
unquote_password (bool) – unquote special characters in the password.
unquote_user (bool) – unquote special characters in the user.
connect_params – additional parameters to pass to the Database.

Parse url and return an appropriate Database instance. A URL without a database name raises ValueError.

Examples:

sqlite:///my_app.db - SQLite file in the current directory.
sqlite:///:memory: - in-memory SQLite.
sqlite:////absolute/path/to/app.db - absolute path SQLite.
postgresql:///dbname - database running locally.
postgresql://user:password@host:5432/dbname
mysql://user:password@host:3306/dbname

parse(url, unquote_password=False, unquote_user=False)¶

Parameters

url – the URL for the database, see connect() above for examples.
unquote_password (bool) – unquote special characters in the password.
unquote_user (bool) – unquote special characters in the user.

Parse a URL and return a dictionary with database, host, port, user, and password keys plus any extra connect parameters from the query string.

Useful if you need to construct a database class manually:

params = parse('postgres://user:pass@host:5432/mydb')
db = MyCustomDatabase(**params)

register_database(db_class, *names)¶

Parameters

db_class – A subclass of Database.
names – A list of names to use as the scheme in the URL.

Register a custom database class under one or more URL scheme names so that connect() can instantiate it:

register_database(ClickHouseDatabase, 'clickhouse')
db = connect('clickhouse:///my-db')

The playhouse.pool module contains a number of Database classes that provide transparent connection pooling for peewee databases. The pool works by overriding the methods on the Database class that open and close connections to the backend, and works as a drop-in replacement.

In multi-threaded or web applications, each thread gets its own connection. The pool maintains up to max_connections open connections at any time. The application only needs to ensure that connections are closed when work is done (typically at the end of an HTTP request), so the connection can be returned to the pool. Closing a pooled connection returns it to the pool rather than actually disconnecting.

from playhouse.pool import PooledPostgresqlDatabase

db = PooledPostgresqlDatabase(
    'my_app',
    user='postgres',
    max_connections=32,
    stale_timeout=300)

Tip

Pooled database implementations may be safely used as drop-in replacements for their non-pooled counterparts.

Commonly-used pool implementations:

PooledPostgresqlDatabase
PooledMySQLDatabase
PooledSqliteDatabase

Additional implementations:

playhouse.cysqlite_ext - PooledCySqliteDatabase
playhouse.mysql_ext - PooledMariaDBConnectorDatabase
playhouse.mysql_ext - PooledMySQLConnectorDatabase
playhouse.postgres_ext - PooledPostgresqlExtDatabase
playhouse.postgres_ext - PooledPsycopg3Database
playhouse.cockroachdb - PooledCockroachDatabase

Note

Applications using Peewee’s asyncio integration do not need to use a special pooled database. Async databases use a connection pool by default.

class PooledDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)¶

Common mixin class used for specific backend implementations.

Parameters

database (str) – The name of the database or database file.
max_connections (int) – Maximum number of concurrent connections. Pass None for no limit.
stale_timeout (int) – Seconds after which an idle connection is considered stale and will be discarded next time it would be reused.
timeout (int) – Seconds to block when all connections are in use. 0 blocks indefinitely, None (default) raises immediately.

Connections will not be closed exactly when they exceed their stale_timeout. Instead, stale connections are only closed when a new connection is requested.

If the pool is exhausted and no timeout is configured, a MaxConnectionsExceeded is raised.

manual_close()¶: Close the current connection permanently without returning it to the pool. Use this when a connection has entered a bad state.

close_idle()¶: Close all pooled connections that are not currently in use.

close_stale(age=600)¶

Parameters: age (int) – Age at which a connection should be considered stale.
Returns: Number of connections closed.

Close in-use connections that have exceeded age seconds. Use with caution.

close_all()¶: Close all connections including those currently in use. Use with caution.

class PooledSqliteDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)¶: Pool implementation for SQLite databases. Extends SqliteDatabase.

class PooledPostgresqlDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)¶: Pool implementation for Postgresql databases. Extends PostgresqlDatabase.

class PooledMySQLDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)¶: Pool implementation for MySQL / MariaDB databases. Extends MySQLDatabase.

Additional implementations exist for:

Schema Migrations ¶

The playhouse.migrate module provides a lightweight API for making incremental schema changes to an existing database without writing raw SQL.

The peewee migration philosophy is that tools relying on database introspection, versioning, and auto-detection are often brittle and complex. Migrations can be written as simple python scripts and executed from the command-line. The runner below adds bookkeeping and can be used for migration generation and execution.

Supported schema-altering operations:

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

Operations¶

Add columns:

# Non-null fields must supply a default value.
migrate(
    migrator.add_column('comment', 'pub_date', DateTimeField(null=True)),
    migrator.add_column('comment', 'body', TextField(default='')),
)

Add a foreign key (the column name must include the _id suffix that Peewee appends by default):

user_fk = ForeignKeyField(User, field=User.id, null=True)
migrate(
    migrator.add_column('tweet', 'user_id', user_fk),
)

Rename a column:

migrate(
    migrator.rename_column('story', 'pub_date', 'publish_date'),
    migrator.rename_column('story', 'mod_date', 'modified_date'),
)

Drop a column:

migrate(migrator.drop_column('story', 'old_field'))

Nullable / not nullable:

migrate(
    migrator.drop_not_null('story', 'pub_date'),  # Allow NULLs.
    migrator.add_not_null('story', 'modified_date'),  # Disallow NULLs.
)

Change type:

# Change a VARCHAR(...) to a TEXT field.
migrate(migrator.alter_column_type('person', 'email', TextField()))

Rename table:

migrate(migrator.rename_table('story', 'stories'))

Drop table:

migrate(migrator.drop_table('story', safe=True))

Add / drop indexes:

# Specify table, column(s), and unique/non-unique.
migrate(
    # Create an index on the `pub_date` column.
    migrator.add_index('story', ('pub_date',), False),

    # Create a unique index on the category and title fields.
    migrator.add_index('story', ('category_id', 'title'), True),

    # Drop the pub-date + status index.
    migrator.drop_index('story', 'story_pub_date_status'),
)

# Only index published stories (sqlite and postgres).
story = Table('story')
migrate(migrator.add_index('story', ('pub_date',), False,
                           where=(story.c.status == 'published')))

# Unique index treating NULLs as equal (postgres 15+).
migrate(migrator.add_index('story', ('external_id',), True,
                           nulls_distinct=False))

Add / drop constraints:

from peewee import Check

# Add a CHECK() constraint to enforce the price cannot be negative.
migrate(migrator.add_constraint(
    'products',
    'price_check',
    Check('price >= 0')))

# Remove the price check constraint.
migrate(migrator.drop_constraint('products', 'price_check'))

# Add a UNIQUE constraint on the first and last names.
migrate(migrator.add_unique('person', 'first_name', 'last_name'))

Column defaults:

# Add a default value:
migrate(migrator.add_column_default('entry', 'status', 'draft'))

# Use a function expression (not supported in SQLite):
migrate(migrator.add_column_default('entry', 'created_at', fn.NOW()))

# SQLite-compatible function syntax:
migrate(migrator.add_column_default(
    'entry',
    'created_at',
    "(datetime('now'))"))

# Remove a default:
migrate(migrator.drop_column_default('entry', 'status'))

Raw SQL:

# Interleave data fixes with schema changes in a single plan.
migrate(
    migrator.add_column('entry', 'score', IntegerField(null=True)),
    migrator.sql('UPDATE entry SET score = %s', (0,)),
    migrator.add_not_null('entry', 'score'),
)

params are bound by the database driver, so the placeholder is the driver’s own: %s for postgres and mysql, ? for sqlite.

Note

Postgres users may need to set the search-path when using a non-standard schema. This can be done as follows:

migrator = PostgresqlMigrator(db)
migrate(
    migrator.set_search_path('my_schema'),
    migrator.add_column('table', 'field', TextField(default='')),
)

Migration API¶

migrate(*operations)¶

Execute one or more schema-altering operations.

Usage:

migrate(
    migrator.add_column('t', 'col', CharField(default='')),
    migrator.add_index('t', ('col',), False),
)

class SchemaMigrator(database)¶

Parameters: database – a Database instance.

The SchemaMigrator is responsible for generating schema-altering statements.

classmethod from_database(database)¶

Parameters: database (Database) – database instance to generate migrations for.
Returns: SchemaMigrator instance appropriate to provided database.

Factory method that returns the appropriate SchemaMigrator subclass for the given database.

migrate(*operations)¶: Execute one or more schema-altering operations. Equivalent to the module-level migrate() helper.

migration_context(atomic=True)¶: Context manager wrapping a migration run in the appropriate per-dialect safety: a transaction when the database supports transactional DDL (and atomic is true), and on sqlite the foreign_keys pragma is additionally disabled for the duration, as table-rewrites would otherwise fire ON DELETE CASCADE.

add_column(table, column_name, field, allow_not_null=False)¶

Parameters

table (str) – Name of the table to add column to.
column_name (str) – Name of the new column.
field (Field) – A Field instance.
allow_not_null (bool) – Emit the column definition as-is, skipping the add-nullable-then-backfill sequence described below.

Add a new column to the provided table. The field provided will be used to generate the appropriate column definition.

If the field is not nullable it must specify a default value, unless allow_not_null is set.

Note

For non-null columns, the following occurs:

column is added as allowing NULLs
UPDATE query is executed to populate the default value
column is changed to NOT NULL

With allow_not_null=True the column is added NOT NULL in a single statement and no default is required. Existing rows are the caller’s responsibility: postgres and sqlite refuse to add the column, while MySQL backfills its implicit default.

drop_column(table, column_name, cascade=True)¶

Parameters

table (str) – Name of the table to drop column from.
column_name (str) – Name of the column to drop.
cascade (bool) – Whether the column should be dropped with CASCADE.

rename_column(table, old_name, new_name)¶

Parameters

table (str) – Name of the table containing column to rename.
old_name (str) – Current name of the column.
new_name (str) – New name for the column.

add_not_null(table, column)¶

Parameters

table (str) – Name of table containing column.
column (str) – Name of the column to make not nullable.

drop_not_null(table, column)¶

Parameters

table (str) – Name of table containing column.
column (str) – Name of the column to make nullable.

add_column_default(table, column, default)¶

Parameters

table (str) – Name of table containing column.
column (str) – Name of the column to add default to.
default – New default value for column. See notes below.

Peewee attempts to properly quote the default if it appears to be a string literal. Otherwise the default will be treated literally. Postgres and MySQL support specifying the default as a peewee expression, e.g. fn.NOW(), but Sqlite users will need to use "(datetime('now'))" instead.

drop_column_default(table, column)¶

Parameters

table (str) – Name of table containing column.
column (str) – Name of the column to remove default from.

alter_column_type(table, column, field, cast=None)¶

Parameters

table (str) – Name of the table.
column (str) – Name of the column to modify.
field (Field) – Field instance representing new data type.
cast – (postgres-only) specify a cast expression if the data-types are incompatible, e.g. column_name::int. Can be provided as either a string or a Cast instance.

Alter the data-type of a column. This method should be used with care, as using incompatible types may not be well-supported by your database.

rename_table(old_name, new_name)¶

Parameters

old_name (str) – Current name of the table.
new_name (str) – New name for the table.

drop_table(table, safe=False, cascade=False, schema=None)¶

Parameters

table (str) – Name of the table to drop.
safe (bool) – Use IF EXISTS.
cascade (bool) – Append CASCADE (not supported by sqlite).
schema (str) – Optional schema qualification.

add_index(table, columns, unique=False, using=None, where=None, nulls_distinct=None)¶

Parameters

table (str) – Name of table on which to create the index.
columns (list) – List of columns which should be indexed.
unique (bool) – Whether the new index should specify a unique constraint.
using (str) – Index type (where supported), e.g. GiST or GIN.
where – Expression for a partial index (sqlite and postgres).
nulls_distinct (bool) – For unique indexes, whether NULL values are treated as distinct (postgres 15+).

drop_index(table, index_name)¶

Parameters

table (str) – Name of the table containing the index to be dropped.
index_name (str) – Name of the index to be dropped.

sql(sql, params=None)¶: Raw SQL as an operation, letting data fixes interleave with schema changes in a single migrate() call.

add_constraint(table, name, constraint)¶

Parameters

table (str) – Table to add constraint to.
name (str) – Name used to identify the constraint.
constraint – either a Check() constraint or for adding an arbitrary constraint use SQL.

drop_constraint(table, name)¶

Parameters

table (str) – Table to drop constraint from.
name (str) – Name of constraint to drop.

add_unique(table, *column_names)¶

Parameters

table (str) – Table to add constraint to.
column_names (str) – One or more columns for UNIQUE constraint.

class PostgresqlMigrator(database)¶

set_search_path(schema_name)¶: Set the Postgres search path for subsequent operations.

class SqliteMigrator(database)¶

SQLite supports or emulates most schema-altering operations, but the code path is dependent on the library version.

SQLite 3.53.0 added ALTER TABLE support for table constraints, so add_constraint and drop_constraint work on 3.53 and newer. Older versions raise NotImplementedError. add_unique is not supported on any version, as sqlite’s ADD CONSTRAINT does not extend to UNIQUE constraints.

class MySQLMigrator(database)¶: MySQL-specific subclass.

Migration Runner ¶

The playhouse.migrations module is a migration runner built using the migration tooling. The runner records which scripts have been applied and runs pending migrations in order. The CLI is installed as pwmigrate (or use python -m playhouse.migrations).

The following commands are available:

initial: create an initial migration containing a snapshot of the application models.
create: create a bare skeleton migration file.
generate: generate a migration based on changes detected between the database schema and model definitions.
diff: display differences between the database schema and model definitions.
up: apply any pending migrations.
down: roll back one or more applied migrations.
fake: record pending migrations as applied without running them.
status: show which migrations have been applied and which are pending.

The following sections show basic usage for common scenarios.

New application¶

Given a models module:

# models.py, imported as `app.models` in examples.
from peewee import *

database = SqliteDatabase('app.db')

class User(database.Model):
    username = CharField(unique=True)
    email = CharField()

Store the project defaults in a .pwmigrate file in the project root (see pwmigrate config file), so commands need no arguments:

# .pwmigrate
database = app.models.database
models = app.models
# directory = migrations/
# table = schema_migration

Then generate the initial migration. initial reads the models and creates a migration suitable for running on an empty database:

$ pwmigrate initial
migrations/0001_initial.py

Equivalent to above, passing the database URL and models module explicitly:

$ pwmigrate sqlite:///app.db initial app.models
migrations/0001_initial.py

The generated file is a python script. The model being created is recorded here as a frozen copy so that future changes to the application code do not affect the migration.

# Generated from a schema diff on 2026-08-02 21:28.
from peewee import *

def up(migrator, db):
    class User(Model):
        username = CharField(unique=True)
        email = CharField()
        class Meta:
            database = db
            table_name = 'user'
    db.create_tables([User])


def down(migrator, db):
    migrator.migrate(migrator.drop_table('user'))

Apply it using up:

$ pwmigrate up
applied: 0001_initial

Applying a migration runs its script and records the name in a schema_migration table in the database.

Existing database¶

To get started with an existing database schema, generate an initial migration that captures the current state. Then fake the initial migration. We fake it instead of actually running it, since the schema already exists. The fake command will record the initial migration as having been run:

$ pwmigrate app.models.database initial app.models
migrations/0001_initial.py

$ pwmigrate app.models.database fake  # Fake it against real database.
faked: 0001_initial

Now the migrations and schema are in agreement. Creating an initial migration is always a good idea, as it allows you to re-create your entire schema on a fresh database using pwmigrate.

We can apply the above initial migration to stand up a test SQLite database, for example:

$ pwmigrate sqlite:///testing.db up
applied: 0001_initial

Tip

If model class definitions do not exist yet, they can be generated with pwiz:

$ pwiz -e postgresql -u postgres my_db > app/models.py

Making changes¶

In this example we’ll add a field to our model class:

class User(database.Model):
    username = CharField(unique=True)
    email = CharField()
    karma = IntegerField(default=0)  # This field is new.

Then we can run two commands to verify the change was picked up and generate a migration:

diff shows any changes that were identified.
generate writes the new migration.

Both commands read the database and models module from the .pwmigrate file set up earlier (see pwmigrate config file).

# Prints a list of differences between code and database schema.
$ pwmigrate diff
add column user.karma

# Generates a migration.
$ pwmigrate generate add_karma
migrations/0002_add_karma.py

The generated up() adds the column:

def up(migrator, db):
    migrator.migrate(migrator.add_column('user', 'karma', IntegerField(default=0)))


def down(migrator, db):
    migrator.migrate(migrator.drop_column('user', 'karma'))

Peewee’s migration will populate default= for basic value types (str, int, float, bool), for decimal.Decimal values, and for the common callables datetime.datetime.now and utcnow, datetime.date.today, time.time, time.time_ns, uuid.uuid4, dict and list, adding any needed import. An enum member renders as its value. Anything else will be marked with a TODO comment at the top of the migration script.

The differ matches columns by name, so a renamed field diffs as an add plus a drop. When the data must survive, replace the generated pair with a rename_column() operation.

Run the migration:

$ pwmigrate up
applied: 0002_add_karma
$ pwmigrate status
[x] 0001_initial  2026-08-04 09:49:33
[x] 0002_add_karma  2026-08-04 09:49:33

Deployments run pwmigrate <db> up, or call run(db) from the application’s startup code. Schema Diff describes what generation covers.

Migration files¶

Peewee migrations are python files with a numeric prefix, defining up(migrator, db) and, optionally, down(migrator, db). The migrator is a SchemaMigrator bound to the connected database and db is the Database, so inline models can subclass db.Model.

For anything the differ cannot write, such as a data migration, scaffold the next numbered file with create and fill in the body:

$ pwmigrate create create_notes
migrations/0003_create_notes.py

# migrations/0003_create_notes.py
from peewee import *

def up(migrator, db):
    class User(db.Model):  # Stub for the FK target.
        pass

    class Note(db.Model):  # Copy model definition to decouple from app code.
        user = ForeignKeyField(User)
        content = TextField()

    db.create_tables([Note])

def down(migrator, db):
    migrator.migrate(migrator.drop_table('note'))

Data migrations are plain python between (or instead of) schema operations.

Generated files have the same shape. Treat one as a starting point, not a finished migration. Anything the differ cannot express is flagged with a TODO comment:

# Generated from a schema diff on 2026-08-03 16:17.
from peewee import *

# TODO: user.email: dropped column cannot be restored by down()

def up(migrator, db):
    class User(Model):
        class Meta:
            database = db
            table_name = 'user'

    class Note(Model):
        user = ForeignKeyField(User)
        content = TextField()
        class Meta:
            database = db
            table_name = 'note'
    db.create_tables([Note])

    migrator.migrate(migrator.add_column('user', 'karma', IntegerField(default=0)))
    migrator.migrate(migrator.add_index('tweet', ('user_id', 'flags')))
    migrator.migrate(migrator.drop_column('user', 'email'))


def down(migrator, db):
    migrator.migrate(migrator.drop_index('tweet', 'tweet_user_id_flags'))
    migrator.migrate(migrator.drop_column('user', 'karma'))
    migrator.migrate(migrator.drop_table('note'))

Command line¶

Example usage:

# Generate the first migration from the models, assuming an empty db:
pwmigrate app.settings.db initial app.models

# Fake the initial migration (if models pre-date peewee migrations).
pwmigrate app.settings.db fake

# Or, run the initial migration if starting from a clean db.
pwmigrate app.settings.db up

# List migrations and when each was applied:
pwmigrate app.settings.db status

# Write a skeleton migration, or generate it from the model diff:
pwmigrate app.settings.db create "add karma"
pwmigrate app.settings.db generate "add karma" app.models

# Apply pending migrations, all or up through a target:
pwmigrate app.settings.db up
pwmigrate app.settings.db up 0002_add_karma

# Revert the most recent migration, or back through a target:
pwmigrate app.settings.db down
pwmigrate app.settings.db down 0002_add_karma

# Print schema drift against the models:
pwmigrate app.settings.db diff app.models

# Record all pending migrations as applied without running them:
pwmigrate app.settings.db fake

The database is given as:

dotted path to a Database instance
db_url string
a path to a sqlite database file

Models are given as a dotted module path. Every model defined in the module is used (field-less base classes are skipped and reported). Models imported into the module from elsewhere are ignored, so a package that only re-exports its models needs the explicit-list form, app.models:MODELS.

pwmigrate config file¶

Keep a .pwmigrate file at the project root, committed with the application code. It supplies per-project defaults as key = value lines:

database = app.settings.db
models = app.models
# directory = migrations/
# table = schema_migration

This allows us to run migration commands without explicitly specifying the paths each time:

$ pwmigrate diff
$ pwmigrate generate "add some fields"
$ pwmigrate status
$ pwmigrate up

Recognized keys:

database - database spec (dotted path, url, or sqlite filename)
directory - migrations directory
models - models module, read by diff, initial and generate
table - history table name

The file is read from the working directory only. A different config file is named with -c. Unrecognized keys warn on stderr. Arguments passed on the command line override any settings in the config file.

Commands¶

Command	Meaning
`status`	List migrations and applied timestamps.
`up`	Apply pending migrations in order, stopping after `target` when given.
`down`	Revert the most recent migration, or everything back through `target`, newest first.
`initial`	Generate the first migration from the models, assuming an empty database.
`create`	Write a skeleton migration file.
`generate`	Generate a migration from the schema diff.
`fake`	Record pending migrations as applied without running them, stopping after `target` when given.
`diff`	Print schema differences against a models module.

Command-line options:

Option	Meaning	Example
`-d`	Migrations directory (default `migrations`)	`-d db/schema`
`-t`	History table name (default `schema_migration`)
`-v`	Echo SQL as it executes
`-c`	Config file supplying defaults	`-c pw.conf`

status exits 0 when the database is current and 1 when migrations are pending, so it can gate a deploy. Validation and database errors exit 2.

Behavior:

Files apply in numeric order (the prefix is parsed as an integer, so 2_x.py runs before 10_y.py). Applied names are recorded in a history table (default schema_migration), exposed as a model at runner.History. The history is a set, so migrations merged in from a branch are applied even when their numbers are not the highest.
On Postgres and SQLite, each migration and its history row are wrapped in a single transaction, so a failure rolls back cleanly. MySQL DDL commits implicitly, so the history row is written only after every operation succeeds. Set atomic = False at module level to opt a migration out of transaction wrapping (e.g. for CREATE INDEX CONCURRENTLY).
On SQLite, foreign_keys pragma is turned off for the duration of each migration and restored afterwards to prevent ON DELETE CASCADE being triggered during table rebuilds (required for some operations).
A migration that does not define down() cannot be reverted (there is no requirement to write one).

Python interface¶

All migration-runner operations are available programmatically:

from playhouse.migrations import Runner

runner = Runner(db, directory='migrations')
runner.create('add karma')  # Write a skeleton file.
runner.status()             # [Migration(idx, name, path, applied), ...]
runner.up()                 # Apply everything pending, in order.
runner.up('0004_x')         # Apply pending up through 0004_x.
runner.down()               # Revert the most recent applied migration.
runner.down('0004_x')       # Revert back through 0004_x, inclusive.
runner.fake()               # Record all as applied without running.

run(db) is shorthand for Runner(db, 'migrations').up().

Generate a migration from a diff:

from playhouse.migrations import Runner, template
from playhouse.schema_diff import diff_models

runner = Runner(db)
diff = diff_models(db, [User, Tweet, Note])
if diff:
    runner.create('add karma', body=template(diff))

class Runner(database, directory='migrations', table_name='schema_migration')¶

up(target=None)¶: Apply all pending migrations in order, stopping after target if given. Returns the applied names.

down(target=None)¶: Revert the most recent applied migration, or, given a target, every applied migration back through the target (newest first). Returns the reverted names.

status()¶: Return Migration namedtuples (idx, name, path, applied) merging migration files with history rows, in numeric order. applied is None when pending, path is None when the file is missing.

fake(target=None)¶: Record pending migrations as applied without running them, stopping after target if given. Returns the recorded names.

create(name, body=None)¶: Write a numbered migration file (a skeleton, unless body is given) and return its path.

History¶: The history-table model, for manual bookkeeping repair.

run(database, directory='migrations', **kwargs)¶: Apply pending migrations. Shorthand for Runner(database, directory).up().

template(diff)¶: Render a playhouse.schema_diff.SchemaDiff as a migration-file body. See Schema Diff.

Schema Diff ¶

The playhouse.schema_diff module compares model definitions against the live database schema and reports basic differences:

tables to create
columns added or removed
indexes added or removed

Columns are compared by name alone (no types, nullability or constraints), so a rename appears as an addition plus a removal. Partial and expression indexes are compared by name only. Tables in the database that no model covers are ignored. Virtual models (sqlite FTS, etc.) are skipped.

>>> from playhouse.schema_diff import diff_models
>>> diff = diff_models(db, [User, Tweet, Note])
>>> bool(diff)
True
>>> print(diff)
create table note
add column user.karma
drop column user.email
add index tweet (user_id, flags)

diff_models(database, models)¶: Compare the live schema against the given models. Returns a SchemaDiff which is falsy when everything matches.

class SchemaDiff¶

A named tuple of five lists, each mapping directly onto a SchemaMigrator call:

create_tables: model classes whose tables do not exist, in foreign-key dependency order.
add_columns: the model Field instances missing from the database.
drop_columns: (table, column_name) for database columns no model declares.
add_indexes / drop_indexes: IndexDiff entries.

class IndexDiff¶: Named tuple (table, name, columns, unique). columns is None marks a partial or expression index, detected by name but not described. Plain additions carry columns/unique and no name (the name is chosen at creation). Removals always carry the name to drop.

Reflection ¶

The playhouse.reflection module introspects an existing database and generates Peewee model classes from its schema. It is used internally by pwiz - Model Generator and DataSet.

from playhouse.reflection import generate_models

db = PostgresqlDatabase('my_app')
models = generate_models(db)   # Returns {table_name: ModelClass}

# list(models.keys())
# ['account', 'customer', 'order', 'orderitem', 'product']

# Get a reference to a generated model.
Customer = models['customer']

# Or inject into the current namespace:
# globals().update(models)

# Query generated models:
for customer in Customer.select():
    print(customer.name, customer.email)

generate_models(database, schema=None, **options)¶

Parameters

database (Database) – database instance to introspect.
schema (str) – optional schema to introspect.
options – arbitrary options, see Introspector.generate_models() for details.

Returns

a dict mapping table names to model classes.

print_model(model)¶

Print a human-readable summary of a model’s fields and indexes to stdout. Useful for interactive exploration:

>>> print_model(Tweet)
tweet
  id AUTO PK
  user INT FK: User.id
  content TEXT
  timestamp DATETIME

index(es)
  user_id
  timestamp

print_table_sql(model)¶

Print the CREATE TABLE SQL for a model class (without indexes):

>>> print_table_sql(Tweet)
CREATE TABLE IF NOT EXISTS "tweet" (
  "id" INTEGER NOT NULL PRIMARY KEY,
  "user_id" INTEGER NOT NULL,
  "content" TEXT NOT NULL,
  "timestamp" DATETIME NOT NULL,
  FOREIGN KEY ("user_id") REFERENCES "user" ("id")
)

class Introspector(metadata, schema=None)¶

Metadata can be extracted from a database by instantiating an Introspector. Rather than instantiating this class directly, it is recommended to use the factory method from_database().

classmethod from_database(database, schema=None)¶

Parameters

database – a Database instance.
schema (str) – an optional schema (supported by some databases).

Creates an Introspector instance suitable for use with the given database.

db = SqliteDatabase('my_app.db')
introspector = Introspector.from_database(db)
models = introspector.generate_models()

# User and Tweet (assumed to exist in the database) are
# peewee Model classes generated from the database schema.
User  = models['user']
Tweet = models['tweet']

generate_models(skip_invalid=False, table_names=None, literal_column_names=False, bare_fields=False, include_views=False)¶

Parameters

skip_invalid (bool) – Skip tables whose names are not valid Python identifiers.
table_names (list) – Only generate models for the given tables.
literal_column_names (bool) – Use the exact database column names as field names (rather than converting to Python naming conventions).
bare_fields (bool) – Do not attempt to detect field types, use BareField for all columns (SQLite only).
include_views (bool) – Also generate models for views.

Returns

A dictionary mapping table-names to model classes.

Introspect the database, reading in the tables, columns, and foreign key constraints, then generate a dictionary mapping each database table to a dynamically-generated Model class.

pwiz - Model Generator ¶

pwiz is a command-line tool that introspects a database and prints ready-to-use Peewee model code. If you have an existing database, running pwiz saves significant time generating the initial model definitions.

# Introspect a Postgresql database and write models to a file:
pwiz -e postgresql -u postgres my_db > models.py

# Introspect a SQLite database:
pwiz -e sqlite path/to/my.db

# Introspect a MySQL database (prompts for password):
pwiz -e mysql -u root -P my_db

# Introspect only specific tables:
pwiz -e postgresql my_db -t user,tweet,follow

Command-line options:

Option	Meaning	Example
`-e`	Database backend	`-e mysql`
`-H`	Host	`-H 10.0.0.1`
`-p`	Port	`-p 5432`
`-u`	Username	`-u postgres`
`-P`	Password (prompts interactively)
`-s`	Schema	`-s public`
`-t`	Comma-separated list of tables to include	`-t user,tweet`
`-v`	Include views
`-i`	Embed database info as a comment
`-o`	Preserve original column order
`-I`	Ignore fields whose type is unknown
`-L`	Use legacy table and column naming

Valid -e values: sqlite, mysql, postgresql, cockroachdb.

Warning

If a password is required to access your database, you will be prompted to enter it using a secure prompt.

The password will be included in the output. Specifically, at the top of the file a Database will be defined along with any required parameters - including the password.

Example output for a SQLite database with user and tweet tables:

from peewee import *

database = SqliteDatabase('example.db')

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class User(BaseModel):
    username = TextField(unique=True)

    class Meta:
        table_name = 'user'

class Tweet(BaseModel):
    content = TextField()
    timestamp = DateTimeField()
    user = ForeignKeyField(column_name='user_id', field='id', model=User)

    class Meta:
        table_name = 'tweet'

Note that pwiz detects foreign keys, unique constraints, and preserves explicit table names.

Note

The UnknownField is a placeholder that is used in the event your schema contains a column declaration that Peewee doesn’t know how to map to a field class.

Test Utilities ¶

playhouse.test_utils provides helpers for testing peewee projects.

class count_queries(only_select=False)¶

Context manager that counts the number of SQL queries executed within its block.

Parameters: only_select (bool) – If True, count only SELECT queries.

with count_queries() as counter:
    user = User.get(User.username == 'alice')
    tweets = list(user.tweets)   # Triggers a second query.

assert counter.count == 2

count¶: Number of queries executed.

get_queries()¶: Return the executed queries as a list of logging.LogRecord objects. Each record’s msg attribute is the (sql, params) tuple.

assert_query_count(expected, only_select=False)¶

Decorator or context manager that raises AssertionError if the number of queries executed does not match expected.

As a decorator:

class TestAPI(unittest.TestCase):
    @assert_query_count(1)
    def test_get_user(self):
        user = User.get_by_id(1)

As a context manager:

with assert_query_count(3):
    result = my_function_that_should_make_exactly_three_queries()

Database Tooling¶

Database URLs ¶

Connection Pooling ¶

Schema Migrations ¶

Operations¶

Migration API¶

Migration Runner ¶

New application¶

Existing database¶

Making changes¶

Migration files¶

Command line¶

pwmigrate config file¶

Commands¶

Python interface¶

Schema Diff ¶

Reflection ¶

pwiz - Model Generator ¶

Test Utilities ¶

peewee

Navigation

Related Topics