Hi, first of all thanks for your work on this project.

I made some model changes in my project and noticed that a foreign key constraint wasn't getting picked up when I ran migrate. Basically I had a blog Entry model and added a User model to represent the author. I also added a foreign key from the entry to the user.

Here are portions of the models:

class Entry(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))  # New column

class User(db.Model):  # New model
    id = db.Column(db.Integer, primary_key=True)

This is the output of db migrate:

INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added column 'entry.author_id'

Here is the migration:

    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('user',
    sa.Column('id', sa.Integer(), nullable=False),
    ....
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    sa.UniqueConstraint('slug')
    )
    op.add_column(u'entry', sa.Column('author_id', sa.Integer(), nullable=True))
    ### end Alembic commands ###

Notice that the foreign key constraint is missing.

I upgraded the db and, while the author_id column is there, the foreign key constraint is not:

CREATE TABLE entry (
    id INTEGER NOT NULL, 
    title VARCHAR(100), 
        author_id INTEGER, 
    PRIMARY KEY (id), 
    UNIQUE (slug)
);

Versions:

• Flask-Migrate==1.2.0
• Flask-SQLAlchemy==1.0
• SQLAlchemy==0.9.1
• alembic==0.6.2

Hey there,

First off thanks for the module, works a charm in most cases!

I've struck an issue where if I change a column definition, it doesn't detect the changes and generate a migration script.

Eg.

cost_tax = db.Column(db.Numeric(precision=3), info={'verbose_name': 'Cost tax'})

To:

cost_tax = db.Column(db.Numeric(precision=4), info={'verbose_name': 'Cost tax'})

Does not get picked up. Unless I'm doing something wrong?

Cheers,
Ben

I'm running a script performing a db 'upgrade' from a blank db. This used to work in the past, properly upgrading the database (for instance with alembic 0.6.7 and Flask-Migrate 1.2.0).

Now when I try it on the same codebase with the latest alembic and flask migrate I get this:

  File "/usr/local/lib/python2.7/site-packages/flask_migrate/__init__.py", line 98, in upgrade
    command.upgrade(config, revision, sql = sql, tag = tag)
  File "/usr/local/lib/python2.7/site-packages/alembic/command.py", line 165, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/site-packages/alembic/script.py", line 382, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/site-packages/alembic/util.py", line 241, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 72, in <module>
    run_migrations_online()
  File "migrations/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/usr/local/lib/python2.7/site-packages/alembic/environment.py", line 742, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python2.7/site-packages/alembic/migration.py", line 296, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/usr/local/lib/python2.7/site-packages/alembic/command.py", line 154, in upgrade
    return script._upgrade_revs(revision, rev)
  File "/usr/local/lib/python2.7/site-packages/alembic/script.py", line 303, in _upgrade_revs
    revs = list(revs)
  File "/usr/local/lib/python2.7/site-packages/alembic/revision.py", line 581, in _iterate_revisions
    upper_ancestors = set(self._get_ancestor_nodes(uppers, check=True))
  File "/usr/local/lib/python2.7/site-packages/alembic/revision.py", line 551, in _iterate_related_revisions
    map_[rev_id] for rev_id in fn(rev))
  File "/usr/local/lib/python2.7/site-packages/alembic/revision.py", line 531, in <lambda>
    fn = lambda rev: rev._all_down_revisions
AttributeError: 'NoneType' object has no attribute '_all_down_revisions'

Is this a Flask-Migrate issue? An issue with Alembic?

I've created a database and schema in Postgres. I have my models and when I run python manager.py db migrate which uses Flask-Migrate, I get the error below. However, the db init command works.

sqlalchemy.exc.ProgrammingError: (ProgrammingError) no schema has been selected to create in

Now when I add __tablename__ and __table_args__ = {"schema": "name_of_schema"} to my models, I get the error below for both, db init and db migrate:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'deploy.instance_id' could not find table 'instance' with which to generate a foreign key to target column 'id'

My relationships however, look okay. I've seen many examples and they worked properly on SQLite without Flask-Migrate.

I have three tables as follows (removing most columns):

class Application(db.Model):
    __tablename__ = 'application'
    __table_args__ = {"schema":"v1"}
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=False)
    instances = db.relationship('Instance', backref='application', lazy='dynamic')

    def __repr__(self):
        return '<ID %r>' % (self.id)


class Instance(db.Model):
    __tablename__ = 'instance'
    __table_args__ = {"schema":"v1"}
    id = db.Column(db.Integer, primary_key=True)
    host = db.Column(db.String(80), unique=False)
    application_id = db.Column(db.Integer, db.ForeignKey('application.id'))
    deploys = db.relationship('Deploy', backref='instance', lazy='dynamic')

    def __repr__(self):
        return '<ID %r>' % (self.id)


class Deploy(db.Model):
    __tablename__ = 'deploy'
     __table_args__ = {"schema":"v1"}
    id = db.Column(db.Integer, primary_key=True)
    tag = db.Column(db.String(80), unique=False)
    instance_id = db.Column(db.Integer, db.ForeignKey('instance.id'))

    def __repr__(self):
        return '<ID %r>' % (self.id)

The relationships are:

• Application to Instance (one-to-many; one application many instances)
• Instance to Deploy (one-to-many; one instance many deploys)

What am I doing here? Why are my relationships wrong? When I remove all relationships and create a standalone table, I still get the first error: sqlalchemy.exc.ProgrammingError: (ProgrammingError) no schema has been selected to create in.

Any help is much appreciated.

First of all - thanks for package and great mega tutorial :)

Now to error:

python run.py db migrate && python run.py db upgrade 

the script falling with:

INFO  [alembic.migration] Context impl MySQLImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade 1676483faaa7 -> 2be4f442a3dd, empty message
Traceback (most recent call last):
  File "run.py", line 49, in <module>
    manager.run()
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/flask_script/__init__.py", line 405, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/flask_script/__init__.py", line 384, in handle
    return handle(app, *positional_args, **kwargs)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/flask_script/commands.py", line 145, in handle
    return self.run(*args, **kwargs)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/flask_migrate/__init__.py", line 98, in upgrade
    command.upgrade(config, revision, sql = sql, tag = tag)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/command.py", line 124, in upgrade
    script.run_env()
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/script.py", line 199, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/util.py", line 198, in load_python_file
    module = load_module(module_id, path)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/compat.py", line 55, in load_module
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 72, in <module>
    run_migrations_online()
  File "migrations/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/environment.py", line 652, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/migration.py", line 225, in run_migrations
    change(**kw)
  File "migrations/versions/2be4f442a3dd_.py", line 19, in upgrade
    op.drop_index('user_id', 'lal_entries')
  File "<string>", line 7, in drop_index
  File "<string>", line 1, in <lambda>
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/util.py", line 293, in go
    return fn(*arg, **kw)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/operations.py", line 716, in drop_index
    self._index(name, table_name, ['x'], schema=schema)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/ddl/impl.py", line 164, in drop_index
    self._exec(schema.DropIndex(index))
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/alembic/ddl/impl.py", line 76, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/MySQLdb/cursors.py", line 201, in execute
    self.errorhandler(self, exc, value)
  File "/home/alex/.virtualenvs/flask/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1553, "Cannot drop index 'user_id': needed in a foreign key constraint") '\nDROP INDEX user_id ON lal_entries' ()

my run.py looks like this:

def init_app():
    app = Flask(__name__, template_folder='templates')
    app.debug = True
    app.config.from_object(config)
    app.testing = True

    init_tags(app)
    app.jinja_env.filters['nl2br'] = nl2br

    app.register_blueprint(loveandloathe)#, url_prefix='/')
    app.register_blueprint(users)#, url_prefix='/users')
    app.register_blueprint(blog)

    # db.create_all(app=app)
    migrate = Migrate(app, db)

    #Flask-script
    manager = Manager(app)
    manager.add_command('db', MigrateCommand)

    login_manager.init_app(app)

    # print app.url_map

    db.init_app(app)

    return app, db, manager

app, db, manager = init_app()

if __name__ == "__main__":
   manager.run()

Migration script don't contains the statements for custom type drops.

if we have a table with an ENUM field say,

class A {
...
   col = db.Column( db.Enum('a','b','c', name='custom_type'), default='a'),
...
}

Current Migration Script has been generated as follows,

def upgrade():

 op.create_table('A',
...   
 sa.Column('type', sa.Enum('a', 'b','c', name='custom_type'), nullable=True),
...

)

def downgrade():

 op.drop_table('A')

It don't drop the custom enum type generated, so If we downgrade once next time when we upgrade it breaks as

 sqlalchemy.exc.ProgrammingError: (ProgrammingError) type "custom_types" already exists

Migration script should add the 'custom type' drop statement like below,

sa.Enum(name='custom_types').drop(op.get_bind(), checkfirst=False)

I have an existing database with data. I installed Flask_Migrate as per the docs.

First I ran a db init which created the environment, then (assuming this is correct when you have an existing DB) ran an initial migrate to capture the current state of the database. This produced a migrations script which seemed back to front. That is, upgrade function drops all the tables, downgrade creates them (ie the database I started with) This is the exact opposite of what I've seen elsewhere with an example using just alembic, where downgrade drops all the table, which makes sense.

Is this right?

I set up Flask-Migrate using your suggested changes to __init__.py and initialized the migrations and attempted to generate a file. However, this migration showed no changes to the db even though I had clearly added a users table.

.
├── Procfile
├── README.md
├── TODO.md
├── bin
├── calpoly
├── config.pyc
├── flask_migrate
├── include
├── lib
├── manage.py
├── migrations
├── requirements.txt
├── run.py
└── runtime.txt

Here's the migration:

"""empty message

Revision ID: 2812f95cc1be
Revises: None
Create Date: 2013-09-17 23:05:53.508999

"""

# revision identifiers, used by Alembic.
revision = '2812f95cc1be'
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###

And finally, the code.

I have a PostGIS enabled PostgreSQL database I want to perform migrations on, but upon python manage.py db upgrade I get (last few lines):

File "/Users/martijnv/.virtualenvs/mr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) cannot drop table spatial_ref_sys because extension postgis requires it
HINT:  You can drop extension postgis instead.
'\nDROP TABLE spatial_ref_sys' {}

Not being familiar with alembic I don't know if this is something I can make go away by manually editing the migration file, or if it's a bug in alembic, or in flask-migrate.

The latest version of alembic (alembic 0.7.5.post1) breaks some assumptions in flask migrate.

  File "/apps/python/local/lib/python2.7/site-packages/flask_migrate/__init__.py", line 9, in <module>
      alembic_version = tuple([int(v) for v in __alembic_version__.split('.')])

Filed issue with alembic but it seems this is a valid pypi package version.
https://bitbucket.org/zzzeek/alembic/issue/286/version-075post1-breaks-some-downstream

Not sure if this is a problem with alembic or flask-migrate:

When you define a column, e.g.,

class Role(db.Model):
    __tablename__ = "roles"
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(16), unique=True, index=True)

The migrate script contains something like:

op.create_index('op.f('ix_roles_type')', 'roles', ['type'], unique=True)

which obviously contains too many quotation marks. Any thoughts?

First time using Flask-Migrate, so feel free to correct any bad assumptions I have. If I remove a column from my model and I'm using sqlite as my backend, I can no longer utilize the db migrate/db upgrade features.

This isn't even really an issue with Flask-Migrate. sqlite itself does not support dropping or renaming database columns, so an ALTER TABLE table1 DROP COLUMN column2 will fail. This, I understand.

The problem I'm having is that I can't do anything from that point. Once Flask-Migrate has a migration recorded that involves DROP COLUMN, I'm stuck. I can manually go into sqlite and drop/recreate my tables, but then Flask-Migrate reports that Target database is not up to date. I can't even add the columns back into my model, because migrate won't generate new sql if the database isn't up to date.

I did work out (while writing this up in fact) that if I manually correct the database (drop and recreate table according to how I want it), and then do an UPDATE alembic_version SET version_num=${REVISION}; statement, I can get back to using migrate/upgrade. Before I figured this out, I was deleting the database and migrations directory all the time. I also think that I could edit the specific migration file directly and remove the op.drop_column lines.

I was thinking that it would be nice if the migrate script realized that op.drop_column and sqlite backend do not work together and could inform the user of that/bail out. Another thought I had was that some sort of "force-recreate" option could essentially take the current model and generate the equivalent op.drop_table and op.create_table statements with no alter statements required.

At minimum, I think this should be noted in a "troubleshooting" section of the docs. If we want to go that way, I'd even be happy to write something up in a pull request.

How do I tell migrate that I want to rename the columns i.e, issue op.alter_column instead of op.drop(old_column) and op.add_column(new_column), so that the data is preserved?

Alembic and Flask-SQLAlchemy has support to multidb. Flask-Migrate should work with them.

I have a relatively large flask application for which I'm trying to implement db migration. However, my models are not detected in the migration process.

As suggested in another issue, I deleted all of the tables, and the contents of migrations > versions followed by python manage.py db migrate but the migration yields only an empty migration script.

If I create the tables using db.create_all() then run python manage.py db init followed by python manage.py db migrate, the migration calls for the db to drop all of my tables, e.g.:

INFO  [alembic.migration] Context impl MySQLImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected removed table u'activities'
INFO  [alembic.autogenerate.compare] Detected removed table u'ema'
INFO  [alembic.autogenerate.compare] Detected removed table u'vitamins'

Any insights into why this is failing?

Management script | App structure | Example model

Database management script

#!/usr/bin/python
# -*- coding: utf-8 -*-

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand
from flask.ext.sqlalchemy import SQLAlchemy
from bari import db, app

def load_models():
    from bari.models.client import Client
    from bari.models.activity import Activity
    from bari.models.ema import EMA
    from bari.models.diagnostic import Diagnostic
    from bari.models.module import Module
    from bari.models.vitamin import Vitamin
    from bari.models.weight import Weight
    from bari.models.goal import Goal
    from bari.models.nutrition import Nutrition
    from bari.models.symptom import Symptom
    from bari.models.scheduling import Scheduling
    print 'models loaded'

app =  Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:[email protected]/dbname'

db = SQLAlchemy(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    load_models()
    manager.run()

Application structure

.
├── __init__.py
├── bari
│   ├── __init__.py
│   ├── api
│   │   ├── __init__.py
│   │   ├── activity.py
│   │   ├── build
│   │   ├── client.py
│   │   ├── diagnostic.py
│   │   ├── ema.py
│   │   ├── goal.py
│   │   ├── module.py
│   │   ├── nutrition.py
│   │   ├── research.py
│   │   ├── scheduling.py
│   │   ├── symptom.py
│   │   ├── test_api.py
│   │   ├── vitamin.py
│   │   ├── weight.py
│   ├── config.py
│   ├── db
│   │   ├── __init__.py
│   │   └── database.py
│   ├── helper
│   │   ├── __init__.py
│   │   ├── crypto.py
│   │   ├── growl.py
│   │   ├── map.py
│   │   ├── reportgen.py
│   │   ├── serialdate.py
│   │   ├── status.py
│   │   ├── weightchartgen.py
│   ├── logtest.py
│   ├── models
│   │   ├── __init__.py
│   │   ├── activity.py
│   │   ├── client.py
│   │   ├── diagnostic.py
│   │   ├── ema.py
│   │   ├── goal.py
│   │   ├── module.py
│   │   ├── nutrition.py
│   │   ├── scheduling.py
│   │   ├── symptom.py
│   │   ├── user.py
│   │   ├── vitamin.py
│   │   ├── weight.py
│   ├── tmp
│   │   ├── rpt_87447739-D010-4222-8019-A819C550EA19.pdf
│   │   ├── rpt_EFRtuTywPkD7L3DQjXHF2fwtjzPC2nGHOZtS.pdf
│   │   ├── rpt_YQXNSdOzvb05x1rwzEFnNXY5WKyAetACgIsA.pdf
│   │   └── rpt_wnRXqQATzCK7RrfklEAVkAaHAByPBj2CWoAT.pdf
│   ├── views.py
├── bari.py
├── bari_tornado.conf
├── bari_web.log
├── bariatric.py
├── db_create.py
├── generate_user
├── generate_user.c
├── logging.yaml
├── logtest.py
├── manage.py
├── migrations
│   ├── README
│   ├── alembic.ini
│   ├── env.py
│   ├── env.pyc
│   ├── script.py.mako
│   └── versions
│       └── 35c763e3c414_.py
├── run.py
├── setup.py
└── test
    ├── __init__.py
    ├── test.py

Example model definition

from bari import db
from bari.helper.serialdate import dump_datetime

class Diagnostic(db.Model):
    __tablename__ = 'dx'
    d_id = db.Column(db.Integer, primary_key = True)
    time = db.Column(db.DateTime)
    dx_code = db.Column(db.Integer)
    extra_code = db.Column(db.Integer)

    # relationship to user/reciprocal = 'diagnostics'
    client_id = db.Column(db.Integer, db.ForeignKey('clients.client_id'))
    user = db.relationship('Client', backref = 'diagnostics')

Hi,

I was just wondering if Flask-Migrate supports alembic branches : http://alembic.readthedocs.org/en/latest/branches.html

Thanks,

Yoanis.

Hi,

I know this is a limitation of Alembic but thought you might know the answer. How would you handle changes to enum types?

Cheers!

The output of the "init" command directs the operator to customize the alembic.ini file. The message shows that the location of this file is that of the root of the project, but Flask-Migrate puts this file inside the migrations folder instead.

Alembic was just updated and received some new commands. Perhaps you should wrap them?

http://alembic.readthedocs.org/en/latest/changelog.html#change-4efddca1a4935691140cffea05fbb63c

I currently have a TypeDecorator defined like so:

# in myapp/database.py
class BcryptType(TypeDecorator):
    """Coerce strings to bcrypted Password objects for the database.
    """
    impl = db.String(128)

    def process_bind_param(self, value, dialect):
        return Password(value, crypt=True)

    def process_result_value(self, value, dialect):
        # already crypted, so don't crypt again
        return Password(value, crypt=False)

Then when I generate a migration, I get

# in the migration script
def upgrade():
    #...
    sa.Column('password', sa.BcryptType(), nullable=True),

This, of course, fails because there is no sqlalchemy.BcryptType. It would be nice if Flask-Migrate knew to use the TypeDecorators impl when generating the migration.

It might be a useless thing to check for, but I already spent a few hours trying to figure out why my database was not created.

I had issues with multiple configurations and somehow the SQLALCHEMY_DATABASE_URI value was not set before initializing SQLAlchemy.
Not sure what the Flask-Migrate behaviour should be in that case...

For me everything was working fine :
db init created the migration folder
db migrationcreated the migration from my models.py
db upgrade applied the migration.

Things were more problematic when I realized that db upgrade kept applying the migration, and that my app.db was nowhere to be found.

When using virtualenv, flask-migrate creates a new dir with templates within the pwd as well as in lib/python(version)/site_packages.

.
├── bin
├── calpoly
├── flask_migrate
├── lib
├── manage.py
├── migrations
├── Procfile
├── README.md
├── requirements.txt
├── run.py
├── runtime.txt
└── TODO.md

I'd prefer everything to be located in lib.

In my model, I add a column like this:
deadline = db.Column(db.Date, default=date.max)

Then the migration script is generated automatically. In upgrade() the operation is like this:
op.add_column('posts', sa.Column('deadline', sa.Date(), nullable=True))

Although no 'default' parameter is assigned in add_column(...) function, I find it works well in my db when a new item is inserted, giving a default date of 9999/12/31.
That it what I want, but how does the script know the default value without any information in migration script?

I was wondering if Flask-Migrate would be able to support the SQLALCHEMY_BINDS option.

I want to have frontend and backend database models be seperated for an application I am developing.

SQLAlchemy provides this option with SQLALCHEMY_BINDS and with defining the context in the table (example see here: http://pythonhosted.org/Flask-SQLAlchemy/binds.html)

Can you support this? If not is there any other recommendation of handling multiple databases in sqlalchemy with migrations?

While running "db init" using the example code in the docs, i get the following error:

Traceback (most recent call last):
  File "managetest.py", line 20, in <module>
    manager.run()
  File "<virtual-env-path>\lib\site-packages\flask_script\__init__.py", line 366, in run
    raise e
alembic.util.CommandError: No such template 'flask'

Something with my environment setup or did the pip install not grab everything?

SQLALCHEMY_ECHO should be added to env.py

I use this line:

config.set_main_option('sqlalchemy.echo',
                       str(current_app.config.get('SQLALCHEMY_ECHO', True)).lower())

I would like to know if there is a way for me listen/tap into revision command in such a way that i could extend it to do something more.

My end game here is, I would like for revision to, after doing its thing =), create a new folder inside my application with the new revision number as its folder name. This new folder is where i am planning to place my seed data in the form of csv files (one for each table), and then I will create a new command seed() which will in turn read these csv files depending on the current alembic version.

Hi,

The following is my app_db.py file at the root of my project.

import os
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand

if os.environ.get('DATABASE_URL') is None:
SQLALCHEMY_DATABASE_URI = 'postgresql://mramgokrujqfer:tIkrP25gX9XQBpZsl7W3MQtrw0@ec2-184-73-175-240.compute-1.amazonaws.com:5432/dev03fouea9lm'
else:
SQLALCHEMY_DATABASE_URI = os.environ['DATABASE_URL']

app = Flask(name)
app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI
db = SQLAlchemy(app)

migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)

def load_models():
from app.models.models import User,Address,NewTable,NewTable23,NewTable45
print 'loaded'

if name == 'main':
load_models()
manager.run()

I then have a apps/models/models.py file that contains the following models

import os
from app_db import db
from flask.ext.sqlalchemy import SQLAlchemy

class JSONSERIALIZER(object):
def to_dict(self):
return {c.name: getattr(self, c.name) for c in self.table.columns}

class User(db.Model,JSONSERIALIZER):
tablename = 'users'
public = ('my_id', 'name')
my_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
addresses = db.relationship("Address", backref=db.backref("user"))
def init(self, id, name):
self.my_id = id
self.name = name
def repr(self):
return "<User('%d','%s')>" % (self.my_id, self.name)

class Address(db.Model,JSONSERIALIZER):
tablename='addresses'
id = db.Column(db.Integer, primary_key=True)
email_address = db.Column(db.String, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.my_id'))

def __init__(self, email_address):
    self.email_address = email_address

def __repr__(self):
    return "<Address('%s')>" % self.email_address

class NewTable(db.Model):
tablename='newtable'
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(128))

class NewTable23(db.Model):
tablename='newtable23'
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(128))

class NewTable45(db.Model):
tablename='newtable45'
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(128))

db.create_all()

Now using python app_db db migrate and then python app_db db update no tables are created. If i create the tables my self, the update command removes them ? What am i doing wrong ?

Flask Script allows passing config options into the create_app factory function. Flask Migrate requires creating an app instance right away.

Model:

class EventQuery(BaseQuery, SearchQueryMixin):
        pass

class Event(db.Model):
    query_class=EventQuery
    #basics
    id = db.Column(db.Integer, primary_key=True)
    slug = db.Column(db.String(255))
    name = db.Column(db.String(100))
    created_at = db.Column(db.BigInteger)
    updated_at = db.Column(db.BigInteger)
    search_vector = db.Column(TSVectorType('name', 'location'))

Error:

File "migrations/versions/4d7450c48143_.py", line 75, in upgrade
sa.Column('search_vector', sa.TSVectorType(), nullable=True),
AttributeError: 'module' object has no attribute 'TSVectorType'

Great plugin, great writeup on your blog. I'm new to migrations and flask.
What do you call the script where you create the app and create the manager? My pre-migrations app called it "init.py", and I never ran it directly; instead I used "python run.py", which was just:

from sand import app

app.run(debug=True)

My working Flask app (pre-migrations) had the following structure (where the name of the app's package is "sand"):
├── run.py
└── sand/
...├──"init.py"
...├── sand.db
...├── models.py
...├── templates/
...├── utils.py
...├── views_api.py
...└── views_web.py

with the "init.py" file looking like:

bunch of imports
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///sand.db"

db = SQLAlchemy(app)

from sand import views_web, models, views_api

After reading your blog post and readme I've modified the init.py file to look like this:

bunch of imports
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///sand.db"

db = SQLAlchemy(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

from sand import views_web, models, views_api

if __name__=="__main__":
    manager.run()

I can still get the server to run with "python run.py". Calling python "init.py" gives me an error "ImportError: No module named sand."

Should I rename "init.py" to something like app.py, and run the migrate commands with "python app.py db"? Realize this is a new project, but can you point me to an example application using Flask-Migrate? Thanks for all the effort you've put into this!

Hi, I'm using peewee ORM for my Flask application, are there any plans to support this excelent module in Flask-Migrate? Peewee already have some helper functions which should greatly simplify its integration:
http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#migrate
Thanks

Flask-Migrate seems to generate an invalid migration when using Postgresql ARRAY:

from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.migrate import Migrate, MigrateCommand
from flask.ext.script import Manager

app = Flask(__name__, instance_relative_config=True)
app.config.from_pyfile('config.cfg')

app.secret_key = 'secret'
app.debug = True

# Flask-SQLAlchemy
db = SQLAlchemy(app)

from sqlalchemy.dialects.postgresql import ARRAY
class MyModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    mylist = db.Column(ARRAY(db.String()), default=[])

# Flask-Migrate
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    manager.run()

This is the migration it generates:

# revision identifiers, used by Alembic.
revision = '5182a5ddb112'
down_revision = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('my_model',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=True),
    sa.Column('mylist', postgresql.ARRAY(String()), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('my_model')
    ### end Alembic commands ###

The error when running upgrade:

    sa.Column('mylist', postgresql.ARRAY(String()), nullable=True),
NameError: global name 'String' is not defined

At the moment I have to manually fix the migration file by replacing String() with sa.String(), and then it will work.

When there are already created tables in the database (without sqlalchemy), the table is detected as it existed but was deleted.
Shouldn't there be a way to provide 'exceptional' tables, so they are not detected as deleted?

Am able to set up and migrate using Flask-Migrate, no issue there. However,
when I run the the app using

python run.py runserver 

(where my manager.run method is in a file called run.py) I get the same output on an error as if I'd run the the app using the -d flag

python run.py runserver -d

Both of which do not give me the debug info on error that you get when you start your app with the conventional way with

app.run(debug=True) 

(incidentally, if I change manager.run() to app.run(debug=True) in my run.py file I get the correct debug output, but of course this messes everything else up.)

Thanks for the work you're putting into this!

With the new alembic version Flask-Migrate can't figure out what version alembic has.

$ ./manage.py 
Traceback (most recent call last):
  File "./manage.py", line 8, in <module>
    from endor import MANAGER
  File "/home/ffwi/endor/endor/__init__.py", line 16, in <module>
    from flask.ext.migrate import Migrate, MigrateCommand
  File "<frozen importlib._bootstrap>", line 2237, in _find_and_load
  File "<frozen importlib._bootstrap>", line 2226, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 1191, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1161, in _load_backward_compatible
  File "/home/ffwi/endor/venv/lib/python3.4/site-packages/flask/exthook.py", line 62, in load_module
    __import__(realname)
  File "/home/ffwi/endor/venv/lib/python3.4/site-packages/flask_migrate/__init__.py", line 9, in <module>
    alembic_version = tuple([int(v) for v in __alembic_version__.split('.')])
  File "/home/ffwi/endor/venv/lib/python3.4/site-packages/flask_migrate/__init__.py", line 9, in <listcomp>
    alembic_version = tuple([int(v) for v in __alembic_version__.split('.')])
ValueError: invalid literal for int() with base 10: 'post1'

I have a column:
code = db.Column(db.Integer(unsigned=True,zerofill=True))
And when I change that column from Integer to String like so:
code = db.Column(db.String())
And I run:
python manage.py db migrate
the migration does not detect any changes.
Should it not detect at least a change in the column data type?
Migration looks like this after:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###

Implementing GUID has proven elusive. I am using flask-sqlalchemy with flask-migrate and postgresql on the backend.

I would like to declare a GUID type and have it be available both to flask-sqlalchemy as well as flask-migrate. How might I implement something similar to this second answer?

I have tried a few different utterly unsuccessfully mean of passing a common object between the two modules. Any help would be appreciated.

How to reproduce:

1. Create table with Integer field.
2. Change it in class to BigInteger.
3. Migrate, upgrade.
4. Field won't change.

To migrate properly you should remove field, migrate, upgrade and then add them as BigInteger. After that migration will be successful with field as BigInteger.

I'm running a database migration, and Alembic complains with
alembic.util.CommandError: Target database is not up to date.

So and I then run a database upgrade
python manage.py db upgrade

Here is the output:
INFO [alembic.migration] Context impl PostgresqlImpl.
INFO [alembic.migration] Will assume transactional DDL.
INFO [alembic.migration] Running upgrade 604497a5e5 -> 431e33e6fce, empty message

The issue is that Alembic never finishes the upgrade (never quits the terminal) and it appears stuck on the third step, I have tried severally, even leaving it for several minutes but it never completes the upgrade.
I have tried running this locally and on the server and it doesn't work (it's not an issue with the connection).

Hi, miguelgrinberg:
I'm trying out this library along with instruction, in the following style of code i have put all the code in one file:

from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand

app = Flask(__name__, static_path='/static')
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data.db'
db = SQLAlchemy(app)
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)

class User(db.Model):
        #code here

if __name__ == '__main__':
    manager.run()

And when i type "python app.py db init" and "python app.py db migrate", the migration script is generated just fine.

Then i delete .db file and "migrations" folder and want to try this in the following setting:
i put all the database model definition in the file "models.py" which is at the same level with "app.py"

from app import db, app

class User(db.Model):
        #code here

And in "app.py"

from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand

import models

app = Flask(__name__, static_path='/static')
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data.db'
db = SQLAlchemy(app)
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)


if __name__ == '__main__':
    manager.run()

And after initialization and "python app.py db migrate", the migration script shows:

# revision identifiers, used by Alembic.
revision = ******
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###

Which means flask-migrate doesn't detect database model definition in this type of code layout.
I'm not sure why this is the case, definitely somewhere has been messed up, but i'm not sure where is it.

Thanks in advance

Hi,

The tests/ directory is not included in the tarball from pypi (https://pypi.python.org/pypi/Flask-Migrate/1.1.0) so I can not execute the testsuite locally. Can you please add the tests/ dir and do a new release?

TIA

Tom

I am using TSVectorType() to create tsvector column, but to upgrade the migration I need to add import sqlalchemy_utils to migration file, How can I do this automatically?

When running a migrate command:

Traceback (most recent call last):
  File "/home/vagrant/EngScripts/resourceman/trunk/venv/bin/xes_rm-manager", line 9, in <module>
    load_entry_point('xes-rm==1.2115', 'console_scripts', 'xes_rm-manager')()
  File "/home/vagrant/EngScripts/resourceman/trunk/src/xes_rm/app.py", line 63, in manager
    from flask.ext.migrate import Migrate, MigrateCommand
  File "<frozen importlib._bootstrap>", line 2214, in _find_and_load
  File "<frozen importlib._bootstrap>", line 2203, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 1191, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1161, in _load_backward_compatible
  File "/home/vagrant/EngScripts/resourceman/trunk/venv/lib/python3.4/site-packages/Flask-0.10.1-py3.4.egg/flask/exthook.py", line 62, in load_module
    __import__(realname)
  File "/home/vagrant/EngScripts/resourceman/trunk/venv/lib/python3.4/site-packages/Flask_Migrate-1.3.0-py3.4.egg/flask_migrate/__init__.py", line 9, in <module>
    alembic_version = tuple([int(v) for v in __alembic_version__.split('.')])
  File "/home/vagrant/EngScripts/resourceman/trunk/venv/lib/python3.4/site-packages/Flask_Migrate-1.3.0-py3.4.egg/flask_migrate/__init__.py", line 9, in <listcomp>
    alembic_version = tuple([int(v) for v in __alembic_version__.split('.')])
ValueError: invalid literal for int() with base 10: 'post2'

I know this is more a SQLlite issue, but I was wondering how you dealt with the many "No support for ALTER of constraints in SQLite dialect" that arise with flask-migrate and alembic. I am stuck right now because db upgrade cannot update the database correctly due to such an issue.

I'm following the basic testing conventions from here http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-vii-unit-testing

It's worked beautifully up till the point where I needed to add some postgres views and functions to my database, db.create_all() obviously has no notion of them.

I am using Alembic (with Flask-Migrate, more thanks to you! ) so conceptually I should just be able to run an upgrade but I'm having trouble figuring out how to do it programmatically.

So instead of db.create_all() in TestCase.setup I have this:

migrate = Migrate(self.app, db)     
config = Config("migrations/alembic.ini")
config.set_main_option("script_location", "migrations")
command.upgrade(config, "head")

But when that tries to run env.py via alembic I get this:

 File "migrations/env.py", line 20, in <module>
    config.set_main_option('sqlalchemy.url', current_app.config.get('SQLALCHEMY_DATABASE_URI'))
  File "/Users/atli/work/server/env/lib/python2.7/site-packages/werkzeug/local.py", line 338, in __getattr__
    return getattr(self._get_current_object(), name)
  File "/Users/atli/work/server/env/lib/python2.7/site-packages/werkzeug/local.py", line 297, in _get_current_object
    return self.__local()
  File "/Users/atli/work/server/env/lib/python2.7/site-packages/flask/globals.py", line 34, in _find_app
    raise RuntimeError('working outside of application context')
RuntimeError: working outside of application context

I must admit I'm not quite familiar enough w. Flask internals to know what that means.
The corresponding lines in migrations/env.py:

from flask import current_app
config.set_main_option('sqlalchemy.url', current_app.config.get('SQLALCHEMY_DATABASE_URI'))
target_metadata = current_app.extensions['migrate'].db.metadata

I realise this is not really an issue for Flask-Migrate per se but it would be interesting nonetheless to know how best to integrate Flask-Migrate with basic Flask testing procedures?

Flask-Migrate does not seem to pick up onupdate="cascade" or ondelete="cascade"

db.ForeignKey(
    'othertable.id',
    onupdate="cascade",
    ondelete="cascade"),
primary_key=True)

even though this is supported by alembic - or does it and I am not seeing it?

Hi, the current upgrade and downgrade commands don't support -x option, which is quite important for things like one off database migrations.

How do I extend command to support this? I can submit a patch if you're able to point me i the right direction

Is there a workaround to directly use alembic commands?

Thanks.

I run

$ python run.py db init

And that runs without issue. Then,

$ python run.py db migrate -m 'First migration'
Traceback (most recent call last):
  File "run.py", line 3, in <module>
    myapp.manager.run()
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/flask_migrate/__init__.py", line 136, in migrate
    version_path=version_path, rev_id=rev_id)
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/alembic/command.py", line 113, in revision
    script.run_env()
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/alembic/script.py", line 390, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/alembic/util.py", line 243, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 19, in <module>
    config.set_main_option('sqlalchemy.url', current_app.config.get('SQLALCHEMY_DATABASE_URI'))
  File "/Users/bfh/.virtualenvs/myapp/lib/python2.7/site-packages/alembic/config.py", line 198, in set_main_option
    self.file_config.set(self.config_ini_section, name, value)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/ConfigParser.py", line 743, in set
    raise TypeError("option values must be strings")
TypeError: <flask_script.commands.Command object at 0x107aba950>: option values must be strings

Any advice for a fella?

I noticed that whenever "db migrate" command is run, it creates a new migration, even empty ones. This does not seem to be a useful behavior. It would be nice if, instead of a empty migration being generated, just a message "Project model has no changes do migrate" was printed.