Sqlalchemy + Postgres: synthetic/artificial id mixin with sequence

Question

I've found the mixin pattern to be really handy for staying DRY, but I am having trouble with sequences. Note, I'm using postgres.

We use alembic migrations, and I'd really like the --autogeneration to work with this sequence, though I understand this might not be possible right now. However, it looks like setting up the sequence without an ORM identifier, prevents the sequence from being dropped later if I wanted to perform a downgrade.

Through googling, I found some explanation on how to properly setup a sequence. Essentially: separate the id and its sequence.

Current Code looks a bit like this:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declared_attr

class AutoIdMixin(object):
    """Generates an synthetic identifier primary key.
    """

    # See: http://docs.sqlalchemy.org/en/latest/core/defaults.html#associating-a-sequence-as-the-server-side-default
    @declared_attr
    def id_seq(cls):
        bases = cls.__bases__
        Base = bases[0]
        sequence_prefix = 'seq'
        schema = cls._schema_name
        sequence_id = '_'.join((sequence_prefix, schema, cls.__tablename__, 'id'))
        sequence = sa.Sequence(sequence_id, 1, 1, metadata=Base.metadata)
        return sequence

    @declared_attr
    def id(cls):
        column_id = sa.Column(sa.types.Integer, cls.id_seq.next_value(), primary_key=True)
        return column_id

With the code above, I end up with a non-helpful error:

AttributeError: Neither 'next_value' object nor 'Comparator' object has an attribute '_set_parent_with_dispatch'

Answer 1

In an RTM moment, it looks like I missed a keyword: server_default.

    @declared_attr
    def id(cls):
        sequence = cls.id_seq
        column_id = sa.Column(sa.types.Integer, server_default=sequence.next_value(), primary_key=True)
        return column_id