How to create postgresql's sequences in Alembic

Question

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?

Answer 1

Just add following to your model:

field_seq = Sequence('groups_field_seq')
field = Column(Integer, field_seq, server_default=field_seq.next_value())

And add following to your migration file (before creating table):

from sqlalchemy.schema import Sequence, CreateSequence
op.execute(CreateSequence(Sequence('groups_field_seq')))

Answer 2

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?