CODEWITHSUNDEEP
pythonsqlalchemyalembic
Eelco
Eelco

Reputation: 379

Create partial index with SqlAlchemy and Alembic

I have the following model with the index: 

class User(TableMixin, Base):
    username = Column(String, nullable=False, unique=False)
    password = Column(String, nullable=False, unique=False)

    Index('ix_active_username', "active", username,
          unique=True,
          postgresql_where=("active" is True)
          )

With the TabbleMixin class

class TableMixin():
    id = Column(Integer, primary_key=True)
    active = Column(Boolean, nullable=False, default=True)

The index I wish to create in my postgres db:

CREATE UNIQUE INDEX unique_active_username on user (username, active) 
where active = True;

Active is between quotes because it's not recognized since it's not recognized inside the User class.

After I ran "alembic revision --autogenerate" the alembic file is empty. Does anyone know what I'm doing wrong?

EDIT: All I had to do was put the index outside the scope of the class. And change the where to 

postgresql_where=(User.active == true())

All Code:

class User(TableMixin, Base):
    username = Column(String, nullable=False, unique=False)
    password = Column(String, nullable=False, unique=False)

class TableMixin():
    id = Column(Integer, primary_key=True)
    active = Column(Boolean, nullable=False, default=True)

Index('ix_active_username', User.active, User.username,
      unique=True,
      postgresql_where=(User.active == true())
      )

thanks to Ilja Everilä!

Upvotes: 7

Views: 3529

Answers (1)

Eelco
Eelco

Reputation: 379

The complete solution:

class User(TableMixin, Base):
    username = Column(String, nullable=False, unique=False)
    password = Column(String, nullable=False, unique=False)

class TableMixin():
    id = Column(Integer, primary_key=True)
    active = Column(Boolean, nullable=False, default=True)

Index('ix_active_username', User.active, User.username,
      unique=True,
      postgresql_where=(User.active == true())
      )

Upvotes: 10

Related Questions