pythonsqlalchemyalembic

How to create postgresql's sequences in Alembic


I'm using alembic to maintain my tables. At the same time, I update my models using the declarative way.

This is one the alembic's table:

op.create_table(
    'groups',
    Column('id', Integer, Sequence('group_id_seq'), primary_key=True),
    Column('name', Unicode(50)),
    Column('description', Unicode(250)),
)

And the model is like the following:

class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, Sequence('group_id_seq'), primary_key=True)
    name = Column(Unicode(50))
    description = Column(Unicode(250))

    def __init__(self, name, description):
        self.description = description
        self.name = name

You can see, I'm using the Sequence in both the alembic migration and in the declarative model.

But I have noticed that when using PostgreSQL (v9.1) no sequences are created by alembic, and so the models fail to create instances since they will use the nextval(<sequence name>) clause.

So, how can I create my alembic migrations so that the sequences are truly generated in postgresql?


Solution

  • Found a hint at https://bitbucket.org/zzzeek/alembic/issue/60/autogenerate-for-sequences-as-well-as#comment-4100402

    Following the CreateSequence found in the previous link I still have to jump through several hoops to make my migrations works in SQLite and PostgreSQL. Currently I have:

    def dialect_supports_sequences():
        return op._proxy.migration_context.dialect.supports_sequences
    
    
    def create_seq(name):
        if dialect_supports_sequences():
           op.execute(CreateSequence(Sequence(name)))
    

    And then call the create_seq whenever I need it.

    Is this the best practice?