Models and Fields

Model classes and their associated Field instances provide a direct mapping to database tables and columns. Model instances correspond to rows in the database table, and their attributes are the column values for the given row.

The following code shows the typical way you will define your database connection and model classes.

from peewee import *

db = SqliteDatabase('my_app.db')

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

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

class Tweet(BaseModel):
    user = ForeignKeyField(User, related_name='tweets')
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)
  1. Create an instance of a Database.

    db = SqliteDatabase('my_app.db')
    

    The db object will be used to manage the connections to the Sqlite database. In this example we’re using SqliteDatabase, but you could also use one of the other database engines.

  2. Create a base model class which specifies our database.

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

    It is good practice to define a base model class which establishes the database connection. This makes your code DRY as you will not have to specify the database for subsequent models.

    Model configuration is kept namespaced in a special class called Meta. This convention is borrowed from Django. Meta configuration is passed on to subclasses, so our project’s models will all subclass BaseModel. There are many different attributes you can configure using Model.Meta.

  3. Define a model class.

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

    Model definition uses the declarative style seen in other popular ORMs like SQLAlchemy or Django. Note that we are extending the BaseModel class so the User model will inherit the database connection.

    We have explicitly defined a single username column with a unique constraint. Because we have not specified a primary key, peewee will automatically add an auto-incrementing integer primary key field named id.

Note

If you would like to start using peewee with an existing database, you can use pwiz, a model generator to automatically generate model definitions.

Fields

The Field class is used to describe the mapping of Model attributes to database columns. Each field type has a corresponding SQL storage class (i.e. varchar, int), and conversion between python data types and underlying storage is handled transparently.

When creating a Model class, fields are defined as class attributes. This should look familiar to users of the django framework. Here’s an example:

class User(Model):
    username = CharField()
    join_date = DateTimeField()
    about_me = TextField()

There is one special type of field, ForeignKeyField, which allows you to represent foreign-key relationships between models in an intuitive way:

class Message(Model):
    user = ForeignKeyField(User, related_name='messages')
    body = TextField()
    send_date = DateTimeField()

This allows you to write code like the following:

>>> print some_message.user.username
Some User

>>> for message in some_user.messages:
...     print message.body
some message
another message
yet another message

For full documentation on fields, see the Fields API notes

Field initialization arguments

Parameters accepted by all field types and their default values:

  • null = False – boolean indicating whether null values are allowed to be stored
  • index = False – boolean indicating whether to create an index on this column
  • unique = False – boolean indicating whether to create a unique index on this column. See also adding composite indexes.
  • verbose_name = None – string representing the “user-friendly” name of this field
  • help_text = None – string representing any helpful text for this field
  • db_column = None – string representing the underlying column to use if different, useful for legacy databases
  • default = None – any value to use as a default for uninitialized models
  • choices = None – an optional iterable containing 2-tuples of value, display
  • primary_key = False – whether this field is the primary key for the table
  • sequence = None – sequence to populate field (if backend supports it)
  • constraints = None - a list of one or more constraints, e.g. [Check('price > 0')]
  • schema = None – optional name of the schema to use, if your db supports this.

Field types table

Field Type Sqlite Postgresql MySQL
CharField varchar varchar varchar
TextField text text longtext
DateTimeField datetime timestamp datetime
IntegerField integer integer integer
BooleanField smallint boolean bool
FloatField real real real
DoubleField real double precision double precision
BigIntegerField integer bigint bigint
DecimalField decimal numeric numeric
PrimaryKeyField integer serial integer
ForeignKeyField integer integer integer
DateField date date date
TimeField time time time
BlobField blob bytea blob
UUIDField not supported uuid not supported

Some fields take special parameters...

Field type Special Parameters
CharField max_length
DateTimeField formats
DateField formats
TimeField formats
DecimalField max_digits, decimal_places, auto_round, rounding
ForeignKeyField rel_model, related_name, to_field, on_delete, on_update, extra

Note

Both default and choices could be implemented at the database level as DEFAULT and CHECK CONSTRAINT respectively, but any application change would require a schema change. Because of this, default is implemented purely in python and choices are not validated but exist for metadata purposes only.

To add database (server-side) constraints, use the constraints parameter.

DateTimeField, DateField and TimeField

The three fields devoted to working with dates and times have special properties which allow access to things like the year, month, hour, etc.

DateField has properties for:

  • year
  • month
  • day

TimeField has properties for:

  • hour
  • minute
  • second

DateTimeField has all of the above.

These properties can be used just like any other expression. Let’s say we have an events calendar and want to highlight all the days in the current month that have an event attached:

# Get the current time.
now = datetime.datetime.now()

# Get days that have events for the current month.
Event.select(Event.event_date.day.alias('day')).where(
    (Event.event_date.year == now.year) &
    (Event.event_date.month == now.month))

Creating a custom field

It isn’t too difficult to add support for custom field types in peewee. In this example we will create a UUID field for postgresql (which has a native UUID column type).

To add a custom field type you need to first identify what type of column the field data will be stored in. If you just want to add python behavior atop, say, a decimal field (for instance to make a currency field) you would just subclass DecimalField. On the other hand, if the database offers a custom column type you will need to let peewee know. This is controlled by the Field.db_field attribute.

Let’s start by defining our UUID field:

class UUIDField(Field):
    db_field = 'uuid'

We will store the UUIDs in a native UUID column. Since psycopg2 treats the data as a string by default, we will add two methods to the field to handle:

  • The data coming out of the database to be used in our application
  • The data from our python app going into the database
import uuid

class UUIDField(Field):
    db_field = 'uuid'

    def db_value(self, value):
        return str(value) # convert UUID to str

    def python_value(self, value):
        return uuid.UUID(value) # convert str to UUID

Now, we need to let the database know how to map this uuid label to an actual uuid column type in the database. There are 2 ways of doing this:

  1. Specify the overrides in the Database constructor:
db = PostgresqlDatabase('my_db', fields={'uuid': 'uuid'})
  1. Register them class-wide using Database.register_fields():
# Will affect all instances of PostgresqlDatabase
PostgresqlDatabase.register_fields({'uuid': 'uuid'})

That is it! Some fields may support exotic operations, like the postgresql HStore field acts like a key/value store and has custom operators for things like contains and update. You can specify custom operations as well. For example code, check out the source code for the HStoreField, in playhouse.postgres_ext.

Creating model tables

In order to start using our models, its necessary to open a connection to the database and create the tables first. Peewee will run the necessary CREATE TABLE queries, additionally creating any constraints and indexes.

# Connect to our database.
db.connect()

# Create the tables.
db.create_tables([User, Tweet])

Note

Strictly speaking, it is not necessary to call connect() but it is good practice to be explicit. That way if something goes wrong, the error occurs at the connect step, rather than some arbitrary time later.

Note

Peewee can determine if your tables already exist, and conditionally create them:

# Only create the tables if they do not exist.
db.create_tables([User, Tweet], safe=True)

After you have created your tables, if you choose to modify your database schema (by adding, removing or otherwise changing the columns) you will need to either:

  • Drop the table and re-create it.
  • Run one or more ALTER TABLE queries. Peewee comes with a schema migration tool which can greatly simplify this. Check the schema migrations docs for details.

Model options and table metadata

In order not to pollute the model namespace, model-specific configuration is placed in a special class called Meta (a convention borrowed from the django framework):

from peewee import *

contacts_db = SqliteDatabase('contacts.db')

class Person(Model):
    name = CharField()

    class Meta:
        database = contacts_db

This instructs peewee that whenever a query is executed on Person to use the contacts database.

Note

Take a look at the sample models - you will notice that we created a BaseModel that defined the database, and then extended. This is the preferred way to define a database and create models.

Once the class is defined, you should not access ModelClass.Meta, but instead use ModelClass._meta:

>>> Person.Meta
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: type object 'Preson' has no attribute 'Meta'

>>> Person._meta
<peewee.ModelOptions object at 0x7f51a2f03790>

The ModelOptions class implements several methods which may be of use for retrieving model metadata (such as lists of fields, foreign key relationships, and more).

>>> Person._meta.fields
{'id': <peewee.PrimaryKeyField object at 0x7f51a2e92750>, 'name': <peewee.CharField object at 0x7f51a2f0a510>}

>>> Person._meta.primary_key
<peewee.PrimaryKeyField object at 0x7f51a2e92750>

>>> Person._meta.database
<peewee.SqliteDatabase object at 0x7f519bff6dd0>

There are several options you can specify as Meta attributes. While most options are inheritable, some are table-specific and will not be inherited by subclasses.

Option Meaning Inheritable?
database database for model yes
db_table name of the table to store data no
indexes a list of fields to index yes
order_by a list of fields to use for default ordering yes
primary_key a CompositeKey instance yes
table_alias an alias to use for the table in queries no

Here is an example showing inheritable versus non-inheritable attributes:

>>> db = SqliteDatabase(':memory:')
>>> class ModelOne(Model):
...     class Meta:
...         database = db
...         db_table = 'model_one_tbl'
...
>>> class ModelTwo(ModelOne):
...     pass
...
>>> ModelOne._meta.database is ModelTwo._meta.database
True
>>> ModelOne._meta.db_table == ModelTwo._meta.db_table
False

Indexes and Unique Constraints

Peewee can create indexes on single or multiple columns, optionally including a UNIQUE constraint.

Single column indexes are defined using field initialization parameters. The following example adds a unique index on the username field, and a normal index on the email field:

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

Multi-column indexes are defined as Meta attributes using a nested tuple. Each database index is a 2-tuple, the first part of which is a tuple of the names of the fields, the second part a boolean indicating whether the index should be unique.

class Transaction(Model):
    from_acct = CharField()
    to_acct = CharField()
    amount = DecimalField()
    date = DateTimeField()

    class Meta:
        indexes = (
            # create a unique on from/to/date
            (('from_acct', 'to_acct', 'date'), True),

            # create a non-unique on from/to
            (('from_acct', 'to_acct'), False),
        )

Non-integer Primary Keys, Composite Keys and other Tricks

Non-integer primary keys

If you would like use a non-integer primary key (which I generally don’t recommend), you can specify primary_key=True when creating a field. When you wish to create a new instance for a model using a non-autoincrementing primary key, you need to be sure you save() specifying force_insert=True.

from peewee import *

class UUIDModel(Model):
    id = UUIDField(primary_key=True)

Auto-incrementing IDs are, as their name says, automatically generated for you when you insert a new row into the database. When you call save(), peewee determines whether to do an INSERT versus an UPDATE based on the presence of a primary key value. Since, with our uuid example, the database driver won’t generate a new ID, we need to specify it manually. When we call save() for the first time, pass in force_insert = True:

# This works because .create() will specify `force_insert=True`.
obj1 = UUIDModel.create(id=uuid.uuid4())

# This will not work, however. Peewee will attempt to do an update:
obj2 = UUIDModel(id=uuid.uuid4())
obj2.save() # WRONG

obj2.save(force_insert=True) # CORRECT

# Once the object has been created, you can call save() normally.
obj2.save()

Note

Any foreign keys to a model with a non-integer primary key will have a ForeignKeyField use the same underlying storage type as the primary key they are related to.

Composite primary keys

Peewee has very basic support for composite keys. In order to use a composite key, you must set the primary_key attribute of the model options to a CompositeKey instance:

class BlogToTag(Model):
    """A simple "through" table for many-to-many relationship."""
    blog = ForeignKeyField(Blog)
    tag = ForeignKeyField(Tag)

    class Meta:
        primary_key = CompositeKey('blog', 'tag')

Manually specifying primary keys

Sometimes you do not want the database to automatically generate a value for the primary key, for instance when bulk loading relational data. To handle this on a one-off basis, you can simply tell peewee to turn off auto_increment during the import:

data = load_user_csv() # load up a bunch of data

User._meta.auto_increment = False # turn off auto incrementing IDs
with db.transaction():
    for row in data:
        u = User(id=row[0], username=row[1])
        u.save(force_insert=True) # <-- force peewee to insert row

User._meta.auto_increment = True

If you always want to have control over the primary key, simply do not use the PrimaryKeyField field type, but use a normal IntegerField (or other column type):

class User(BaseModel):
    id = IntegerField(primary_key=True)
    username = CharField()

>>> u = User.create(id=999, username='somebody')
>>> u.id
999
>>> User.get(User.username == 'somebody').id
999

Self-referential foreign keys

When creating a heirarchical structure it is necessary to create a self-referential foreign key which links a child object to its parent. Because the model class is not defined at the time you instantiate the self-referential foreign key, use the special string 'self' to indicate a self-referential foreign key:

class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', null=True, related_name='children')

As you can see, the foreign key points upward to the parent object and the back-reference is named children.

Attention

Self-referential foreign-keys should always be null=True.

When querying against a model that contains a self-referential foreign key you may sometimes need to perform a self-join. In those cases you can use Model.alias() to create a table reference. Here is how you might query the category and parent model using a self-join:

Parent = Category.alias()
GrandParent = Category.alias()
query = (Category
         .select(Category, Parent)
         .join(Parent, on=(Category.parent == Parent.id))
         .join(GrandParent, on=(Parent.parent == GrandParent.id))
         .where(GrandParent.name == 'some category')
         .order_by(Category.name))

Circular foreign key dependencies

Sometimes it happens that you will create a circular dependency between two tables.

Note

My personal opinion is that circular foreign keys are a code smell and should be refactored (by adding an intermediary table, for instance).

Adding circular foreign keys with peewee is a bit tricky because at the time you are defining either foreign key, the model it points to will not have been defined yet, causing a NameError.

class User(Model):
    username = CharField()
    favorite_tweet = ForeignKeyField(Tweet, null=True)  # NameError!!

class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, related_name='tweets')

One option is to simply use an IntegerField to store the raw ID:

class User(Model):
    username = CharField()
    favorite_tweet_id = IntegerField(null=True)

By using Proxy we can get around the problem and still use a foreign key field:

# Create a proxy object to stand in for our as-yet-undefined Tweet model.
TweetProxy = Proxy()

class User(Model):
    username = CharField()
    # Tweet has not been defined yet so use the proxy.
    favorite_tweet = ForeignKeyField(TweetProxy, null=True)

class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, related_name='tweets')

# Now that Tweet is defined, we can initialize the proxy object.
TweetProxy.initialize(Tweet)

After initializing the proxy the foreign key fields are now correctly set up. There is one more quirk to watch out for, though. When you call create_table we will again encounter the same issue. For this reason peewee will not automatically create a foreign key constraint for any deferred foreign keys.

Here is how to create the tables:

# Foreign key constraint from User -> Tweet will NOT be created because the
# Tweet table does not exist yet. `favorite_tweet` will just be a regular
# integer field:
User.create_table()

# Foreign key constraint from Tweet -> User will be created normally.
Tweet.create_table()

# Now that both tables exist, we can create the foreign key from User -> Tweet:
db.create_foreign_key(User, User.favorite_tweet)