2

I'am building a flask-sqlalchemy data model and had success creating the tables and adding data to the tables in the database. I recently decided to include Flask-Migrate around the app so I can track changes conveniently.

Using Flask-Migrate along with Flask-Script Manager I was successfully able to initialise the database using 

python model.py db init

and able to create a migration using

python model.py db migrate

but it appears that Flask-Migrate doesn't know how to handle manually declared sequences because when I run

python model.py db upgrade

i am hit with a 

psycopg2.ProgrammingError: relation "seq_inbox" does not exist

seq_inbox being the name of my manually declared sequence.

Upon inspection of the created migration in the migrations/versions directory I see this

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('inbox',
sa.Column('inbox_id', sa.Integer(), server_default=sa.text("nextval('seq_inbox')"), nullable=False),
sa.Column('message_title', sa.String(length=60), server_default='n/a', nullable=False),
sa.Column('first_name', sa.String(length=30), server_default='n/a', nullable=False),
sa.Column('last_name', sa.String(length=30), server_default='n/a', nullable=False),
sa.Column('email', sa.String(length=60), nullable=False),
sa.Column('phone_number', sa.String(length=60), server_default='n/a', nullable=False),
sa.Column('message', sa.String(length=1000), server_default='n/a', nullable=False),
sa.PrimaryKeyConstraint('inbox_id')
)

It looks to me like Alembic is interpreting my sequence name as plain text, is this correct?

How can I fix this "relation does not exist" error?

EDIT 1 When I remove the sequence declaration and reset everything I am able to run the upgrade command successfully without a hitch. But I want my named sequences to be present.

EDIT 2 Here is the error after running python models.py db upgrade

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> fb6c667b4492, empty message
Traceback (most recent call last):
  File "C:\Users\Flashspeed\AppData\Local\Programs\Python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context
    context)
  File "C:\Users\Flashspeed\AppData\Local\Programs\Python\Python35\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: relation "seq_inbox" does not exist

EDIT 3 Here is the Inbox model itself 

class Inbox(db.Model):
    __tablename__ = "inbox"

    inbox_sequence = db.Sequence(name="seq_inbox", start=1, increment=1, metadata=metadata)

    inbox_id     = db.Column(db.Integer(), inbox_sequence, server_default=inbox_sequence.next_value(), primary_key=True)
    title        = db.Column(db.String(60),   server_default=missingString, nullable=False)
    first_name   = db.Column(db.String(30),   server_default=missingString, nullable=False)
    last_name    = db.Column(db.String(30),   server_default=missingString, nullable=False)
    email        = db.Column(db.String(60),   nullable=False)
    phone_number = db.Column(db.String(60),   server_default=missingString, nullable=False)
    message      = db.Column(db.String(1000), server_default=missingString, nullable=False)
FlashspeedIfe
  • 368
  • 1
  • 6
  • 15
  • Have you tried editing the migration script so that your field is defined exactly how you want it? Alembic is not always able to automatically define your models, the migration scripts are meant to be reviewed and fixed before they are put to use. – Miguel Grinberg Aug 02 '17 at 20:55
  • @Miguel I haven't edited the migration script but I changed the original model to exclude the creation of sequences and that worked fine for all the models. I just read a few minutes ago in the Alembic autogenerate documentation that the addition and removal of sequences are not yet supported so it looks like I'am going to have to work manually if I need to make changes to the data model. – FlashspeedIfe Aug 02 '17 at 22:30
  • I have edited the original question to now include the model for the Inbox table – FlashspeedIfe Aug 02 '17 at 22:32
  • @FlashspeedIfe did you ever find a solution? – pdoherty926 Dec 16 '19 at 17:56
  • 2
    @pdoherty926 no I didn't, at the time Alembic had in its documentation that it does not support the addition and removal of sequences. [Sequence additions, removals - not yet implemented.](https://alembic.sqlalchemy.org/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect). I eventually moved to Django. – FlashspeedIfe Dec 17 '19 at 07:46
  • Thanks for following up @FlashspeedIfe. In case anyone else sees this, I was able to use a sequence by manually creating a migration script, defining the sequence in sql (e.g. `create sequence foo_id_seq start with 999 increment by 1"`) and then passing the name (and only the name) of that sequence to `db.Sequence` as the second argument to `db.Column`. – pdoherty926 Dec 17 '19 at 14:38

0 Answers0