Peewee - Create Index

By using Peewee ORM, it is possible to define a model which will create a table with index on single column as well as multiple columns.

As per the Field attribute definition, setting unique constraint to True will create an index on the mapped field. Similarly, passing index=True parameter to field constructor also create index on the specified field.

In following example, we have two fields in MyUser model, with username field having unique parameter set to True and email field has index=True.

class MyUser(Model):
   username = CharField(unique=True)
   email = CharField(index=True)
   class Meta:
      database=db
      db_table= MyUser 

As a result, SQLiteStudio graphical user interface (GUI) shows indexes created as follows −

In order to define a multi-column index, we need to add indexes attribute in Meta class inside definition of our model class. It is a tuple of 2-item tuples, one tuple for one index definition. Inside each 2-element tuple, the first part of which is a tuple of the names of the fields, the second part is set to True to make it unique, and otherwise is False.

We define MyUser model with a two-column unique index as follows −

class MyUser (Model):
   name=TextField()
   city=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table= MyUser 
      indexes=(
         (( name ,  city ), True),
      )

Accordingly, SQLiteStudio shows index definition as in the following figure −

Index can be built outside model definition as well.

You can also create index by manually providing SQL helper statement as parameter to add_index() method.

MyUser.add_index(SQL( CREATE INDEX idx on MyUser(name); ))

Above method is particularly required when using SQLite. For MySQL and PostgreSQL, we can obtain Index object and use it with add_index() method.

ind=MyUser.index(MyUser.name)
MyUser.add_index(ind)