The following code shows the typical way you will define your database
connection and model classes.
import datetime
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, backref='tweets')
message = TextField()
created_date = DateTimeField(default=datetime.datetime.now)
is_published = BooleanField(default=True)
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()
In the above example, because none of the fields are initialized with
primary_key=True, an auto-incrementing primary key will automatically be
created and named “id”. Peewee uses AutoField to signify an
auto-incrementing integer primary key, which implies primary_key=True.
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, backref='messages')
body = TextField()
send_date = DateTimeField(default=datetime.datetime.now)
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
Note
Refer to the Relationships and Joins document for an in-depth discussion of
foreign-keys, joins and relationships between models.
For full documentation on fields, see the Fields API notes
Field types table
Field Type |
Sqlite |
Postgresql |
MySQL |
|---|
AutoField
|
integer |
serial |
integer |
BigAutoField
|
integer |
bigserial |
bigint |
IntegerField
|
integer |
integer |
integer |
BigIntegerField
|
integer |
bigint |
bigint |
SmallIntegerField
|
integer |
smallint |
smallint |
IdentityField
|
not supported |
int identity |
not supported |
FloatField
|
real |
real |
real |
DoubleField
|
real |
double precision |
double precision |
DecimalField
|
decimal |
numeric |
numeric |
CharField
|
varchar |
varchar |
varchar |
FixedCharField
|
char |
char |
char |
TextField
|
text |
text |
text |
BlobField
|
blob |
bytea |
blob |
BitField
|
integer |
bigint |
bigint |
BigBitField
|
blob |
bytea |
blob |
UUIDField
|
text |
uuid |
varchar(40) |
BinaryUUIDField
|
blob |
bytea |
varbinary(16) |
DateTimeField
|
datetime |
timestamp |
datetime |
DateField
|
date |
date |
date |
TimeField
|
time |
time |
time |
TimestampField
|
integer |
integer |
integer |
IPField
|
integer |
bigint |
bigint |
BooleanField
|
integer |
boolean |
bool |
BareField
|
untyped |
not supported |
not supported |
ForeignKeyField
|
integer |
integer |
integer |
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 – allow null values
index = False – create an index on this column
unique = False – create a unique index on this column. See also adding composite indexes.
column_name = None – explicitly specify the column name in the database.
default = None – any value or callable to use as a default for uninitialized models
primary_key = False – primary key for the table
constraints = None - one or more constraints, e.g. [Check('price > 0')]
sequence = None – sequence name (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 – 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
index_type = None – specify a custom index-type, e.g. for Postgres you might specify a 'BRIN' or 'GIN' index.
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:
class Message(Model):
context = TextField()
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:
class Message(Model):
context = TextField()
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:
def house_defaults():
return {'beds': 0, 'baths': 0}
class House(Model):
number = TextField()
street = TextField()
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:
class Message(Model):
context = TextField()
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
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.
Note
Refer to the Relationships and Joins document for an in-depth discussion of
foreign keys, joins and relationships between models.
In peewee, accessing the value of a ForeignKeyField will return the
entire related object, e.g.:
tweets = (Tweet
.select(Tweet, User)
.join(User)
.order_by(Tweet.created_date.desc()))
for tweet in tweets:
print(tweet.user.username, tweet.message)
Note
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:
tweets = Tweet.select().order_by(Tweet.created_date.desc())
for tweet in tweets:
# WARNING: an additional query will be issued for EACH tweet
# to fetch the associated User data.
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:
tweets = Tweet.select()
for tweet in tweets:
# Instead of "tweet.user", we will just get the raw ID value stored
# in the column.
print(tweet.user_id, tweet.message)
To prevent accidentally resolving a foreign-key and triggering an additional
query, ForeignKeyField supports an initialization paramater
lazy_load which, when disabled, behaves like the "_id" attribute. For
example:
class Tweet(Model):
# ... same fields, except we declare the user FK to have
# lazy-load disabled:
user = ForeignKeyField(User, backref='tweets', lazy_load=False)
for tweet in Tweet.select():
print(tweet.user, tweet.message)
# With lazy-load disabled, accessing tweet.user will not perform an extra
# query and the user ID value is returned instead.
# e.g.:
# 1 tweet from user1
# 1 another from user1
# 2 tweet from user2
# However, if we eagerly load the related user object, then the user
# foreign key will behave like usual:
for tweet in Tweet.select(Tweet, User).join(User):
print(tweet.user.username, tweet.message)
# user1 tweet from user1
# user1 another from user1
# user2 tweet from user1
ForeignKeyField Back-references
ForeignKeyField 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
using the parameter backref:
class Message(Model):
from_user = ForeignKeyField(User, backref='outbox')
to_user = ForeignKeyField(User, backref='inbox')
text = TextField()
for message in some_user.outbox:
# We are iterating over all Messages whose from_user is some_user.
print(message)
for message in some_user.inbox:
# We are iterating over all Messages whose to_user is some_user
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.
DateField has properties for:
TimeField has properties for:
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))
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.
BitField and BigBitField
The BitField and BigBitField are new as of 3.0.0. The
former provides a subclass of IntegerField 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 BooleanField objects,
or we could use BitField instead:
class Post(Model):
content = TextField()
flags = BitField()
is_favorite = flags.flag(1)
is_sticky = flags.flag(2)
is_minimized = flags.flag(4)
is_deleted = flags.flag(8)
Using these flags is quite simple:
>>> p = Post()
>>> p.is_sticky = True
>>> p.is_minimized = True
>>> print(p.flags) # Prints 4 | 2 --> "6"
6
>>> p.is_favorite
False
>>> p.is_sticky
True
We can also use the flags on the Post class to build expressions in queries:
# Generates a WHERE clause that looks like:
# WHERE (post.flags & 1 != 0)
favorites = Post.select().where(Post.is_favorite)
# Query for sticky + favorite posts:
sticky_faves = Post.select().where(Post.is_sticky & Post.is_favorite)
Since the BitField 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
BlobField.
When bulk-updating one or more bits in a BitField, you can use
bitwise operators to set or clear one or more bits:
# Set the 4th bit on all Post objects.
Post.update(flags=Post.flags | 8).execute()
# Clear the 1st and 3rd bits on all Post objects.
Post.update(flags=Post.flags & ~(1 | 4)).execute()
For simple operations, the flags provide handy set() and clear()
methods for setting or clearing an individual bit:
# Set the "is_deleted" bit on all posts.
Post.update(flags=Post.is_deleted.set()).execute()
# Clear the "is_deleted" bit on all posts.
Post.update(flags=Post.is_deleted.clear()).execute()
Example usage:
class Bitmap(Model):
data = BigBitField()
bitmap = Bitmap()
# Sets the ith bit, e.g. the 1st bit, the 11th bit, the 63rd, etc.
bits_to_set = (1, 11, 63, 31, 55, 48, 100, 99)
for bit_idx in bits_to_set:
bitmap.data.set_bit(bit_idx)
# We can test whether a bit is set using "is_set":
assert bitmap.data.is_set(11)
assert not bitmap.data.is_set(12)
# We can clear a bit:
bitmap.data.clear_bit(11)
assert not bitmap.data.is_set(11)
# We can also "toggle" a bit. Recall that the 63rd bit was set earlier.
assert bitmap.data.toggle_bit(63) is False
assert bitmap.data.toggle_bit(63) is True
assert bitmap.data.is_set(63)
# BigBitField supports item accessor by bit-number, e.g.:
assert bitmap.data[63]
bitmap.data[0] = 1
del bitmap.data[0]
# We can also combine bitmaps using bitwise operators, e.g.
b = Bitmap(data=b'\x01')
b.data |= b'\x02'
assert list(b.data) == [1, 1, 0, 0, 0, 0, 0, 0]
assert len(b.data) == 1
BareField
The BareField 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
SearchField instead!).
BareField accepts a special parameter adapt. 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 adapt=int.
Example:
db = SqliteDatabase(':memory:')
class Junk(Model):
anything = BareField()
class Meta:
database = db
# Store multiple data-types in the Junk.anything column:
Junk.create(anything='a string')
Junk.create(anything=12345)
Junk.create(anything=3.14159)
Creating a custom field
It is easy 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 UUIDField, the following code is intended
only as an example.
Let’s start by defining our UUID field:
class UUIDField(Field):
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:
import uuid
class UUIDField(Field):
field_type = 'uuid'
def db_value(self, value):
return value.hex # convert UUID to hex string.
def python_value(self, value):
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:
# Postgres, we use UUID data-type.
db = PostgresqlDatabase('my_db', field_types={'uuid': 'uuid'})
# Sqlite doesn't have a UUID type, so we use text type.
db = SqliteDatabase('my_db', field_types={'uuid': 'text'})
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.
Field-naming conflicts
Model classes implement a number of class- and instance-methods,
for example Model.save() or Model.create(). If you declare a
field whose name coincides with a model method, it could cause problems.
Consider:
class LogEntry(Model):
event = TextField()
create = TimestampField() # Uh-oh.
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:
class LogEntry(Model):
event = TextField()
create_ = TimestampField(column_name='create')
update_ = TimestampField(column_name='update')
Indexes and Constraints
Peewee can create indexes on single or multiple columns, optionally including a
UNIQUE constraint. Peewee also supports user-defined constraints on both
models and fields.
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:
class User(Model):
username = CharField(unique=True)
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:
class Product(Model):
name = CharField(unique=True)
price = DecimalField(constraints=[Check('price < 10000')])
created = DateTimeField(
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.
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),
)
Note
Remember to add a trailing comma if your tuple of indexes contains only one item:
class Meta:
indexes = (
(('first_name', 'last_name'), True), # Note the trailing comma!
)
Advanced Index Creation
Peewee supports a more structured API for declaring indexes on a model using
the Model.add_index() method or by directly using the
ModelIndex helper class.
Examples:
class Article(Model):
name = TextField()
timestamp = TimestampField()
status = IntegerField()
flags = IntegerField()
# Add an index on "name" and "timestamp" columns.
Article.add_index(Article.name, Article.timestamp)
# Add a partial index on name and timestamp where status = 1.
Article.add_index(Article.name, Article.timestamp,
where=(Article.status == 1))
# Create a unique index on timestamp desc, status & 4.
idx = Article.index(
Article.timestamp.desc(),
Article.flags.bin_and(4),
unique=True)
Article.add_index(idx)
Warning
SQLite does not support parameterized CREATE INDEX queries. This means
that when using SQLite to create an index that involves an expression or
scalar value, you will need to declare the index using the SQL
helper:
# SQLite does not support parameterized CREATE INDEX queries, so
# we declare it manually.
Article.add_index(SQL('CREATE INDEX ...'))
See add_index() for details.
For more information, see:
Table constraints
Peewee allows you to add arbitrary constraints to your Model, 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:
class Person(Model):
first = CharField()
last = CharField()
class Meta:
primary_key = CompositeKey('first', 'last')
class Pet(Model):
owner_first = CharField()
owner_last = CharField()
pet_name = CharField()
class Meta:
constraints = [SQL('FOREIGN KEY(owner_first, owner_last) '
'REFERENCES person(first, last)')]
You can also implement CHECK constraints at the table level:
class Product(Model):
name = CharField(unique=True)
price = DecimalField()
class Meta:
constraints = [Check('price < 10000')]
Primary Keys, Composite Keys and other Tricks
The AutoField is used to identify an auto-incrementing integer
primary key. If you do not specify a primary key, Peewee will automatically
create an auto-incrementing primary key named “id”.
To specify an auto-incrementing ID using a different field name, you can write:
class Event(Model):
event_id = AutoField() # Event.event_id will be auto-incrementing PK.
name = CharField()
timestamp = DateTimeField(default=datetime.datetime.now)
metadata = BlobField()
You can identify a different field as the primary key, in which case an “id”
column will not be created. In this example we will use a person’s email
address as the primary key:
class Person(Model):
email = CharField(primary_key=True)
name = TextField()
dob = DateField()
Warning
I frequently see people write the following, expecting an auto-incrementing
integer primary key:
class MyModel(Model):
id = IntegerField(primary_key=True)
Peewee understands the above model declaration as a model with an integer
primary key, but the value of that ID is determined by the application. To
create an auto-incrementing integer primary key, you would instead write:
class MyModel(Model):
id = AutoField() # primary_key=True is implied.
Composite primary keys can be declared using CompositeKey. Note
that doing this may cause issues with ForeignKeyField, as Peewee
does not support the concept of a “composite foreign-key”. As such, I’ve found
it only advisable to use composite primary keys in a handful of situations,
such as trivial many-to-many junction tables:
class Image(Model):
filename = TextField()
mimetype = CharField()
class Tag(Model):
label = CharField()
class ImageTag(Model): # Many-to-many relationship.
image = ForeignKeyField(Image)
tag = ForeignKeyField(Tag)
class Meta:
primary_key = CompositeKey('image', 'tag')
In the extremely rare case you wish to declare a model with no primary key,
you can specify primary_key = False in the model Meta options.
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')
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:
data = load_user_csv() # load up a bunch of data
User._meta.auto_increment = False # turn off auto incrementing IDs
with db.atomic():
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
Although a better way to accomplish the above, without resorting to hacks, is
to use the Model.insert_many() API:
data = load_user_csv()
fields = [User.id, User.username]
with db.atomic():
User.insert_many(data, fields=fields).execute()
If you always want to have control over the primary key, simply do not use
the AutoField 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
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:
class MyData(BaseModel):
timestamp = DateTimeField()
value = IntegerField()
class Meta:
primary_key = False
This will yield the following DDL:
CREATE TABLE "mydata" (
"timestamp" DATETIME NOT NULL,
"value" INTEGER NOT NULL
)
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, backref='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 DeferredForeignKey we can get around the problem and still
use a foreign key field:
class User(Model):
username = CharField()
# Tweet has not been defined yet so use the deferred reference.
favorite_tweet = DeferredForeignKey('Tweet', null=True)
class Tweet(Model):
message = TextField()
user = ForeignKeyField(User, backref='tweets')
# Now that Tweet is defined, "favorite_tweet" has been converted into
# a ForeignKeyField.
print(User.favorite_tweet)
# <ForeignKeyField: "user"."favorite_tweet">
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.
To create the tables and the foreign-key constraint, you can use the
SchemaManager.create_foreign_key() method to create the constraint
after creating the tables:
# Will create the User and Tweet tables, but does *not* create a
# foreign-key constraint on User.favorite_tweet.
db.create_tables([User, Tweet])
# Create the foreign-key constraint:
User._schema.create_foreign_key(User.favorite_tweet)
Note
Because SQLite has limited support for altering tables, foreign-key
constraints cannot be added to a table after it has been created.