Models and Fields

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

    1. Create an instance of a Database.

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

    3. Define a model class.

    Note

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

    The Field class is used to describe the mapping of 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:

    1. class User(Model):
    2.     username = CharField()
    3.     join_date = DateTimeField()
    4.     about_me = TextField()

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

    1. class Message(Model):
    2.     user = ForeignKeyField(User, backref='messages')
    3.     body = TextField()
    4.     send_date = DateTimeField()

    This allows you to write code like the following:

    1. >>> print(some_message.user.username)
    2. Some User
    4. >>> for message in some_user.messages:
    5. ...     print(message.body)
    6. some message
    7. another message
    8. yet another message

    For full documentation on fields, see the Fields API notes

    Note

    Don’t see the field you’re looking for in the above table? It’s easy to create custom field types and use them with your models.

    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 .
    • column_name = 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; If callable, will be called to produce value
    • primary_key = False – whether this field is the primary key for the table
    • constraints = None - a list of one or more constraints, e.g. [Check('price > 0')]
    • sequence = None – sequence to populate field (if backend supports it)
    • collation = None – collation to use for ordering the field / index
    • unindexed = False – indicate field on virtual table should be unindexed (SQLite-only)
    • choices = None – an optional iterable containing 2-tuples of value, display
    • help_text = None – string representing any helpful text for this field
    • verbose_name = None – string representing the “user-friendly” name of this field

    Some fields take special parameters…

    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.

    Default field values

    Peewee can provide default values for fields when objects are created. For example to have an IntegerField default to zero rather than NULL, you could declare the field with a default value:

    1. class Message(Model):
    2.     context = TextField()
    3.     read_count = IntegerField(default=0)

    In some instances it may make sense for the default value to be dynamic. A common scenario is using the current date and time. Peewee allows you to specify a function in these cases, whose return value will be used when the object is created. Note we only provide the function, we do not actually call it:

    1. class Message(Model):
    2.     context = TextField()
    3.     timestamp = DateTimeField(default=datetime.datetime.now)

    Note

    If you are using a field that accepts a mutable type (list, dict, etc), and would like to provide a default, it is a good idea to wrap your default value in a simple function so that multiple model instances are not sharing a reference to the same underlying object:

    1. def house_defaults():
    2.     return {'beds': 0, 'baths': 0}
    4. class House(Model):
    5.     number = TextField()
    6.     street = TextField()
    7.     attributes = JSONField(default=house_defaults)

    The database can also provide the default value for a field. While peewee does not explicitly provide an API for setting a server-side default value, you can use the constraints parameter to specify the server default:

    1. class Message(Model):
    2.     context = TextField()
    3.     timestamp = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')])

    Note

    Remember: when using the default parameter, the values are set by Peewee rather than being a part of the actual table and column definition.

    ForeignKeyField

    is a special field type that allows one model to reference another. Typically a foreign key will contain the primary key of the model it relates to (but you can specify a particular column by specifying a field).

    Foreign keys allow data to be normalized. In our example models, there is a foreign key from Tweet to User. This means that all the users are stored in their own table, as are the tweets, and the foreign key from tweet to user allows each tweet to point to a particular user object.

    In peewee, accessing the value of a will return the entire related object, e.g.:

    1. tweets = (Tweet
    2.           .select(Tweet, User)
    3.           .join(User)
    4.           .order_by(Tweet.create_date.desc()))
    5. for tweet in tweets:
    6.     print(tweet.user.username, tweet.message)

    In the example above the User data was selected as part of the query. For more examples of this technique, see the Avoiding N+1 document.

    If we did not select the User, though, then an additional query would be issued to fetch the associated User data:

    1. tweets = Tweet.select().order_by(Tweet.create_date.desc())
    2. for tweet in tweets:
    3.     # WARNING: an additional query will be issued for EACH tweet
    4.     # to fetch the associated User data.
    5.     print(tweet.user.username, tweet.message)

    Sometimes you only need the associated primary key value from the foreign key column. In this case, Peewee follows the convention established by Django, of allowing you to access the raw foreign key value by appending "_id" to the foreign key field’s name:

    1. tweets = Tweet.select()
    2. for tweet in tweets:
    3.     # Instead of "tweet.user", we will just get the raw ID value stored
    4.     # in the column.
    5.     print(tweet.user_id, tweet.message)

    allows for a backreferencing property to be bound to the target model. Implicitly, this property will be named classname_set, where classname is the lowercase name of the class, but can be overridden via the parameter backref:

    1. class Message(Model):
    2.     from_user = ForeignKeyField(User)
    3.     to_user = ForeignKeyField(User, backref='received_messages')
    4.     text = TextField()
    6. for message in some_user.message_set:
    7.     # We are iterating over all Messages whose from_user is some_user.
    8.     print(message)
    10. for message in some_user.received_messages:
    11.     # We are iterating over all Messages whose to_user is some_user
    12.     print(message)

    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.

    has properties for:

    • year
    • month
    • day

    TimeField has properties for:

    • hour
    • minute
    • second

    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:

    1. # Get the current time.
    2. now = datetime.datetime.now()
    4. # Get days that have events for the current month.
    5. Event.select(Event.event_date.day.alias('day')).where(
    6.     (Event.event_date.year == now.year) &
    7.     (Event.event_date.month == now.month))

    Note

    SQLite does not have a native date type, so dates are stored in formatted text columns. To ensure that comparisons work correctly, the dates need to be formatted so they are sorted lexicographically. That is why they are stored, by default, as YYYY-MM-DD HH:MM:SS.

    The and BigBitField are new as of 3.0.0. The former provides a subclass of that is suitable for storing feature toggles as an integer bitmask. The latter is suitable for storing a bitmap for a large data-set, e.g. expressing membership or bitmap-type data.

    As an example of using BitField, let’s say we have a Post model and we wish to store certain True/False flags about how the post. We could store all these feature toggles in their own objects, or we could use BitField instead:

    1. class Post(Model):
    2.     content = TextField()
    3.     flags = BitField()
    5.     is_favorite = flags.flag(1)
    6.     is_sticky = flags.flag(2)
    7.     is_minimized = flags.flag(4)
    8.     is_deleted = flags.flag(8)

    Using these flags is quite simple:

    We can also use the flags on the Post class to build expressions in queries:

    1. # WHERE (post.flags & 1 != 0)
    2. favorites = Post.select().where(Post.is_favorite)
    4. # Query for sticky + favorite posts:
    5. sticky_faves = Post.select().where(Post.is_sticky & Post.is_favorite)

    Since the is stored in an integer, there is a maximum of 64 flags you can represent (64-bits is common size of integer column). For storing arbitrarily large bitmaps, you can instead use BigBitField, which uses an automatically managed buffer of bytes, stored in a .

    Example usage:

    1. class Bitmap(Model):
    2.     data = BigBitField()
    4. bitmap = Bitmap()
    6. # Sets the ith bit, e.g. the 1st bit, the 11th bit, the 63rd, etc.
    7. bits_to_set = (1, 11, 63, 31, 55, 48, 100, 99)
    8. for bit_idx in bits_to_set:
    9.     bitmap.data.set_bit(bit_idx)
    11. # We can test whether a bit is set using "is_set":
    12. assert bitmap.data.is_set(11)
    13. assert not bitmap.data.is_set(12)
    15. # We can clear a bit:
    16. bitmap.data.clear_bit(11)
    17. assert not bitmap.data.is_set(11)
    19. # We can also "toggle" a bit. Recall that the 63rd bit was set earlier.
    20. assert bitmap.data.toggle_bit(63) is False
    21. assert bitmap.data.toggle_bit(63) is True
    22. assert bitmap.data.is_set(63)

    BareField

    The class is intended to be used only with SQLite. Since SQLite uses dynamic typing and data-types are not enforced, it can be perfectly fine to declare fields without any data-type. In those cases you can use BareField. It is also common for SQLite virtual tables to use meta-columns or untyped columns, so for those cases as well you may wish to use an untyped field (although for full-text search, you should use instead!).

    BareField accepts a special parameter coerce. This parameter is a function that takes a value coming from the database and converts it into the appropriate Python type. For instance, if you have a virtual table with an un-typed column but you know that it will return int objects, you can specify coerce=int.

    Example:

    1. db = SqliteDatabase(':memory:')
    3. class Junk(Model):
    4.     anything = BareField()
    6.     class Meta:
    7.         database = db
    9. # Store multiple data-types in the Junk.anything column:
    10. Junk.create(anything='a string')
    11. Junk.create(anything=12345)
    12. Junk.create(anything=3.14159)

    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.field_type attribute.

    Note

    Peewee ships with a , the following code is intended only as an example.

    Let’s start by defining our UUID field:

    1. class UUIDField(Field):
    2.     field_type = '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
    1. import uuid
    3. class UUIDField(Field):
    4.     field_type = 'uuid'
    6.     def db_value(self, value):
    7.         return value.hex  # convert UUID to hex string.
    9.     def python_value(self, value):
    10.         return uuid.UUID(value) # convert hex string to UUID

    This step is optional. By default, the field_type value will be used for the columns data-type in the database schema. If you need to support multiple databases which use different data-types for your field-data, we need to let the database know how to map this uuid label to an actual uuid column type in the database. Specify the overrides in the Database constructor:

    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 as well. For example code, check out the source code for the HStoreField, in playhouse.postgres_ext.

    Field-naming conflicts

    Model classes implement a number of class- and instance-methods, for example or Model.create(). If you declare a field whose name coincides with a model method, it could cause problems. Consider:

    1. class LogEntry(Model):
    2.     event = TextField()
    3.     create = TimestampField()  # Uh-oh.
    4.     update = TimestampField()  # Uh-oh.

    To avoid this problem while still using the desired column name in the database schema, explicitly specify the column_name while providing an alternative name for the field attribute:

    1. class LogEntry(Model):
    2.     event = TextField()
    3.     create_ = TimestampField(column_name='create')
    4.     update_ = TimestampField(column_name='update')

    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.

    1. # Connect to our database.
    2. db.connect()
    4. # Create the tables.
    5. db.create_tables([User, Tweet])

    Note

    Strictly speaking, it is not necessary to call 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

    By default, Peewee will determine if your tables already exist, and conditionally create them. If you want to disable this, specify safe=False.

    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):

    1. from peewee import *
    3. contacts_db = SqliteDatabase('contacts.db')
    5. class Person(Model):
    6.     name = CharField()
    8.     class Meta:
    9.         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:

    1. >>> Person.Meta
    2. Traceback (most recent call last):
    3.   File "<stdin>", line 1, in <module>
    4. AttributeError: type object 'Person' has no attribute 'Meta'
    6. >>> Person._meta
    7. <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).

    1. >>> Person._meta.fields
    2. {'id': <peewee.PrimaryKeyField object at 0x7f51a2e92750>, 'name': <peewee.CharField object at 0x7f51a2f0a510>}
    4. >>> Person._meta.primary_key
    5. <peewee.PrimaryKeyField object at 0x7f51a2e92750>
    7. >>> Person._meta.database
    8. <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.

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

    1. >>> db = SqliteDatabase(':memory:')
    2. >>> class ModelOne(Model):
    3. ...     class Meta:
    4. ...         database = db
    5. ...         table_name = 'model_one_tbl'
    6. ...
    7. >>> class ModelTwo(ModelOne):
    8. ...     pass
    9. ...
    10. >>> ModelOne._meta.database is ModelTwo._meta.database
    11. True
    12. >>> ModelOne._meta.table_name == ModelTwo._meta.table_name
    13. False

    Meta.primary_key

    The Meta.primary_key attribute is used to specify either a CompositeKey or to indicate that the model has no primary key. Composite primary keys are discussed in more detail here: .

    To indicate that a model should not have a primary key, then set primary_key = False.

    Examples:

    1. class BlogToTag(Model):
    2.     """A simple "through" table for many-to-many relationship."""
    3.     blog = ForeignKeyField(Blog)
    4.     tag = ForeignKeyField(Tag)
    6.     class Meta:
    9. class NoPrimaryKey(Model):
    10.     data = IntegerField()
    12.     class Meta:
    13.         primary_key = False

    Single-column indexes and constraints

    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:

    1. class User(Model):
    2.     username = CharField(unique=True)
    3.     email = CharField(index=True)

    To add a user-defined constraint on a column, you can pass it in using the constraints parameter. You may wish to specify a default value as part of the schema, or add a CHECK constraint, for example:

    1. class Product(Model):
    2.     name = CharField(unique=True)
    3.     price = DecimalField(constraints=[Check('price < 10000')])
    4.     created = DateTimeField(
    5.         constraints=[SQL("DEFAULT (datetime('now'))")])

    Multi-column indexes

    Multi-column indexes may be 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.

    Note

    Remember to add a trailing comma if your tuple of indexes contains only one item:

    1.     indexes = (
    2.         (('first_name', 'last_name'), True),  # Note the trailing comma!
    3.     )

    Peewee supports a more structured API for declaring indexes on a model using the Model.add_index() method or by directly using the helper class.

    Examples:

    1. class Article(Model):
    2.     name = TextField()
    3.     timestamp = TimestampField()
    4.     status = IntegerField()
    5.     flags = IntegerField()
    7. # Add an index on "name" and "timestamp" columns.
    8. Article.add_index(Article.name, Article.timestamp)
    10. # Add a partial index on name and timestamp where status = 1.
    11. Article.add_index(Article.name, Article.timestamp,
    12.                   where=(Article.status == 1))
    14. # Create a unique index on timestamp desc, status & 4.
    15. idx = Article.index(
    16.     Article.timestamp.desc(),
    17.     Article.flags.bin_and(4),
    18.     unique=True)
    19. Article.add_index(idx)

    For more information, see:

    Table constraints

    Peewee allows you to add arbitrary constraints to your , that will be part of the table definition when the schema is created.

    For instance, suppose you have a people table with a composite primary key of two columns, the person’s first and last name. You wish to have another table relate to the people table, and to do this, you will need to define a foreign key constraint:

    1. class Person(Model):
    2.     first = CharField()
    3.     last = CharField()
    5.     class Meta:
    6.         primary_key = CompositeKey('first', 'last')
    8. class Pet(Model):
    9.     owner_first = CharField()
    10.     owner_last = CharField()
    11.     pet_name = CharField()
    13.     class Meta:
    14.         constraints = [SQL('FOREIGN KEY(owner_first, owner_last) '
    15.                            'REFERENCES person(first, last)')]

    You can also implement CHECK constraints at the table level:

    1. class Product(Model):
    2.     name = CharField(unique=True)
    3.     price = DecimalField()
    5.     class Meta:
    6.         constraints = [Check('price < 10000')]

    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.

    1. from peewee import *
    3. class UUIDModel(Model):
    4.     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 , 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:

    1. # This works because .create() will specify `force_insert=True`.
    2. obj1 = UUIDModel.create(id=uuid.uuid4())
    4. # This will not work, however. Peewee will attempt to do an update:
    5. obj2 = UUIDModel(id=uuid.uuid4())
    6. obj2.save() # WRONG
    8. obj2.save(force_insert=True) # CORRECT
    10. # Once the object has been created, you can call save() normally.
    11. 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 instance:

    1. class BlogToTag(Model):
    2.     """A simple "through" table for many-to-many relationship."""
    3.     blog = ForeignKeyField(Blog)
    4.     tag = ForeignKeyField(Tag)
    6.     class Meta:
    7.         primary_key = CompositeKey('blog', 'tag')

    Warning

    Peewee does not support foreign-keys to models that define a CompositeKey primary key. If you wish to add a foreign-key to a model that has a composite primary key, replicate the columns on the related model and add a custom accessor (e.g. a property).

    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:

    1. data = load_user_csv() # load up a bunch of data
    3. User._meta.auto_increment = False # turn off auto incrementing IDs
    4. with db.transaction():
    5.     for row in data:
    6.         u = User(id=row[0], username=row[1])
    7.         u.save(force_insert=True) # <-- force peewee to insert row
    9. 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 (or other column type):

    1. class User(BaseModel):
    2.     id = IntegerField(primary_key=True)
    3.     username = CharField()
    5. >>> u = User.create(id=999, username='somebody')
    6. >>> u.id
    7. 999
    8. >>> User.get(User.username == 'somebody').id
    9. 999

    Models without a Primary Key

    If you wish to create a model with no primary key, you can specify primary_key = False in the inner Meta class:

    1. class MyData(BaseModel):
    2.     timestamp = DateTimeField()
    3.     value = IntegerField()
    5.     class Meta:
    6.         primary_key = False

    This will yield the following DDL:

    1. CREATE TABLE "mydata" (
    2.   "timestamp" DATETIME NOT NULL,
    3.   "value" INTEGER NOT NULL
    4. )

    Warning

    Some model APIs may not work correctly for models without a primary key, for instance and delete_instance() (you can instead use , update() and ).

    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:

    1. class Category(Model):
    2.     name = CharField()
    3.     parent = ForeignKeyField('self', null=True, backref='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:

    1. Parent = Category.alias()
    2. GrandParent = Category.alias()
    3. query = (Category
    4.          .select(Category, Parent)
    5.          .join(Parent, on=(Category.parent == Parent.id))
    6.          .join(GrandParent, on=(Parent.parent == GrandParent.id))
    7.          .where(GrandParent.name == 'some category')
    8.          .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.

    1. class User(Model):
    2.     username = CharField()
    3.     favorite_tweet = ForeignKeyField(Tweet, null=True)  # NameError!!
    5. class Tweet(Model):
    6.     message = TextField()
    7.     user = ForeignKeyField(User, backref='tweets')

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

    1. class User(Model):
    2.     username = CharField()
    3.     favorite_tweet_id = IntegerField(null=True)

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

    1. class User(Model):
    2.     username = CharField()
    3.     # Tweet has not been defined yet so use the deferred reference.
    4.     favorite_tweet = DeferredForeignKey('Tweet', null=True)
    6. class Tweet(Model):
    7.     message = TextField()
    8.     user = ForeignKeyField(User, backref='tweets')
    10. # Now that Tweet is defined, "favorite_tweet" has been converted into
    11. # a ForeignKeyField.
    12. print(User.favorite_tweet)
    13. # <ForeignKeyField: "user"."favorite_tweet">

    There is one more quirk to watch out for, though. When you call we will again encounter the same issue. For this reason peewee will not automatically create a foreign key constraint for any deferred foreign keys.

    To create the tables and the foreign-key constraint, you can use the method to create the constraint after creating the tables:

    Note

    Because SQLite has limited support for altering tables, foreign-key constraints cannot be added to a table after it has been created.