New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to generate initial database stucture #31

Closed
FunkyLoveCow opened this Issue May 28, 2014 · 16 comments

Comments

Projects
None yet
5 participants
@FunkyLoveCow

FunkyLoveCow commented May 28, 2014

I am hoping that this functionality exists. I am adding Flask-Migrate to an existing project, meaning my database exists and has data already.

Following the instructions on readthedocs, I ran manage.py db init and and manage.py db migrate. I was expecting a populated migration file in my migrations folder. Instead, I have an empty upgrade and downgrade set of methods. This is not what I'm used to when using Django's South

So, my question is: Is there a way to automatically generate the initial migration of an existing database using Flask-Migrate? If so, how can I do so? If not, fair enough.

EDIT:
I've got some more information, because I've researched a little further.

My directory structure is set up like this:

work_dir/
    FlaskControlPanel/
         static/
         templates/
         app.py
         manage.py
         mydb.sqlite
    models/
        __init__.py
        model1.py
        model2.py
    migrations/
    script1.py
    script2.py

I run my commands from work_dir:
FlaskControlPanel\manage.py db init and FlaskControlPanel\manage.py db migrate

Initially this created a second database named mydb.sqlite in the work_dir. I moved into FlaskControlPanel and re-ran init and migrate. This time it created a migrations script in a new directory in FlaskControlPanel instead of work_dir, but dropped my tables in upgrade and created them in downgrade. This is opposite of the expected result.

  • Why is it dropping my tables on this first migration?
  • How can I run this from work_dir and keep the migrations as a subdirectory of work_dir?
  • Is this an appropriate tool to use if the Flask application is a small subset of a larger Python/SQLAlchemy project?

Thank you

@miguelgrinberg

This comment has been minimized.

Show comment
Hide comment
@miguelgrinberg

miguelgrinberg May 28, 2014

Owner

Automatic migrations are generated with the delta between the model definitions and what's in the database. Since these two are identical in your case the migrations are empty.

The easiest way to try Alembic to generate an initial migration for an existing project is to temporarily remove the database, or point the application at another database that is empty. Once the migration is generated you can restore your database and go on from there.

Another alternative is to not generate an initial migration, and just track changes that you make in the future, but of course if you do this you need to have a way to bring your database to that initial state outside of Alembic.

I always go with the first approach.

Owner

miguelgrinberg commented May 28, 2014

Automatic migrations are generated with the delta between the model definitions and what's in the database. Since these two are identical in your case the migrations are empty.

The easiest way to try Alembic to generate an initial migration for an existing project is to temporarily remove the database, or point the application at another database that is empty. Once the migration is generated you can restore your database and go on from there.

Another alternative is to not generate an initial migration, and just track changes that you make in the future, but of course if you do this you need to have a way to bring your database to that initial state outside of Alembic.

I always go with the first approach.

@FunkyLoveCow

This comment has been minimized.

Show comment
Hide comment
@FunkyLoveCow

FunkyLoveCow commented May 28, 2014

Thank you.

@gpjt

This comment has been minimized.

Show comment
Hide comment
@gpjt

gpjt Aug 4, 2017

Hey there -- sorry to resurrect an old issue, but I'm having problems with the suggested solution above. I went through that process, following the specific steps given by presumably-the-same-Miguel over on Stack Overflow, and got alembic.util.exc.CommandError: Target database is not up to date. when I ran the second flask db migrate. Is there something I'm missing?

gpjt commented Aug 4, 2017

Hey there -- sorry to resurrect an old issue, but I'm having problems with the suggested solution above. I went through that process, following the specific steps given by presumably-the-same-Miguel over on Stack Overflow, and got alembic.util.exc.CommandError: Target database is not up to date. when I ran the second flask db migrate. Is there something I'm missing?

@miguelgrinberg

This comment has been minimized.

Show comment
Hide comment
@miguelgrinberg

miguelgrinberg Aug 4, 2017

Owner

Hmm. Looks like I missed a step in those instructions. I have corrected the post, but in any case, at the point you get that error, run a flask db stamp head, which marks the database as fully updated. After that, you should be able to edit the models and generate additional migrations.

Owner

miguelgrinberg commented Aug 4, 2017

Hmm. Looks like I missed a step in those instructions. I have corrected the post, but in any case, at the point you get that error, run a flask db stamp head, which marks the database as fully updated. After that, you should be able to edit the models and generate additional migrations.

@gpjt

This comment has been minimized.

Show comment
Hide comment
@gpjt

gpjt Aug 4, 2017

Many thanks! I'll give that a go.

gpjt commented Aug 4, 2017

Many thanks! I'll give that a go.

@zsiegel92

This comment has been minimized.

Show comment
Hide comment
@zsiegel92

zsiegel92 Aug 24, 2017

@miguelgrinberg thank you for your tireless pedagogy!

My problem:
I have pointed my manager at a different, empty Postgres database, (on localhost). I deleted my migrations folder, and, in the psql shell, ran drop table alembic_version;. Finally, I ran python manage.py db init and db migrate. Incredibly (to me), I got the message INFO [alembic.env] No changes in schema detected.!

How can I generate a fresh migration script WITHOUT writing it by hand?

Why I need this:
My database on Heroku is missing some columns, and I want a migrations script that will generate those columns, along with all dependencies and relationships, etc. I'm worried that if I write that migration script myself, I'll leave out something important. I don't want to have to memorize Postgres syntax, SQLAlchemy syntax, AND Alembic migration syntax, but it seems that that's what I'll have to do, and also know how to keep everything in sync manually, in case my migrations get out of sync again. What a nightmare!

About what I did:

I created a new database using psql followed by "create database <new db name>", then editing the environment variable DATABASE_URL with which my app and manager connect to the database.

I have tried several combinations of dropping all tables and schemas, including:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

and
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;
and even
dropdb <database name>
(the last one is in the bash shell - I guess installing Postgres gives you this command.)

See this SO thread to learn about why people using these commands.

If unclear, I'm still using flask-script and flask-migrate (rather than the newer Flask CLI).

zsiegel92 commented Aug 24, 2017

@miguelgrinberg thank you for your tireless pedagogy!

My problem:
I have pointed my manager at a different, empty Postgres database, (on localhost). I deleted my migrations folder, and, in the psql shell, ran drop table alembic_version;. Finally, I ran python manage.py db init and db migrate. Incredibly (to me), I got the message INFO [alembic.env] No changes in schema detected.!

How can I generate a fresh migration script WITHOUT writing it by hand?

Why I need this:
My database on Heroku is missing some columns, and I want a migrations script that will generate those columns, along with all dependencies and relationships, etc. I'm worried that if I write that migration script myself, I'll leave out something important. I don't want to have to memorize Postgres syntax, SQLAlchemy syntax, AND Alembic migration syntax, but it seems that that's what I'll have to do, and also know how to keep everything in sync manually, in case my migrations get out of sync again. What a nightmare!

About what I did:

I created a new database using psql followed by "create database <new db name>", then editing the environment variable DATABASE_URL with which my app and manager connect to the database.

I have tried several combinations of dropping all tables and schemas, including:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

and
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;
and even
dropdb <database name>
(the last one is in the bash shell - I guess installing Postgres gives you this command.)

See this SO thread to learn about why people using these commands.

If unclear, I'm still using flask-script and flask-migrate (rather than the newer Flask CLI).

@miguelgrinberg

This comment has been minimized.

Show comment
Hide comment
@miguelgrinberg

miguelgrinberg Aug 24, 2017

Owner

Are you importing your database models into your manage.py script?

Owner

miguelgrinberg commented Aug 24, 2017

Are you importing your database models into your manage.py script?

@zsiegel92

This comment has been minimized.

Show comment
Hide comment
@zsiegel92

zsiegel92 Aug 24, 2017

I had not been previously, but I just imported the module with my models, and have the same result. I am also importing my app, which imports my views, which import those models.

Database migrations have worked for me at every step of the way. When I make changes, the migration scripts correctly identify them. When I push those scripts to Heroku, I am able to migrate using heroku run python db upgrade. My issue is that I've made several changes to my database since last pushing to Heroku, but I've deleted those migration scripts (which I did because of an issue that is relatively common on SO). How do I migrate my database on Heroku to be in sync with my models?

One idea I had was to simply let Heroku generate its own migrations, using heroku run python db migrate, but I think that won't work because Heroku's filesystems are ephemeral, and this would constitute editing my repo programatically, which doesn't seem like part of Heroku (especially since my app actually lives on GitHub). (weird idea: Is there a way to store migration scripts in a database and use them from there?)

Thank you for your fast response, @miguelgrinberg !! I will keep my words of admiration to a minimum.

zsiegel92 commented Aug 24, 2017

I had not been previously, but I just imported the module with my models, and have the same result. I am also importing my app, which imports my views, which import those models.

Database migrations have worked for me at every step of the way. When I make changes, the migration scripts correctly identify them. When I push those scripts to Heroku, I am able to migrate using heroku run python db upgrade. My issue is that I've made several changes to my database since last pushing to Heroku, but I've deleted those migration scripts (which I did because of an issue that is relatively common on SO). How do I migrate my database on Heroku to be in sync with my models?

One idea I had was to simply let Heroku generate its own migrations, using heroku run python db migrate, but I think that won't work because Heroku's filesystems are ephemeral, and this would constitute editing my repo programatically, which doesn't seem like part of Heroku (especially since my app actually lives on GitHub). (weird idea: Is there a way to store migration scripts in a database and use them from there?)

Thank you for your fast response, @miguelgrinberg !! I will keep my words of admiration to a minimum.

@miguelgrinberg

This comment has been minimized.

Show comment
Hide comment
@miguelgrinberg

miguelgrinberg Aug 24, 2017

Owner

How do I migrate my database on Heroku to be in sync with my models?

Export the schema of your Heroku db using pgsql, then import that schema to a brand new local db that you configure into your application. Then generate a migration based on that.

Owner

miguelgrinberg commented Aug 24, 2017

How do I migrate my database on Heroku to be in sync with my models?

Export the schema of your Heroku db using pgsql, then import that schema to a brand new local db that you configure into your application. Then generate a migration based on that.

@zsiegel92

This comment has been minimized.

Show comment
Hide comment
@zsiegel92

zsiegel92 Aug 24, 2017

Brilliant. I will do my best to figure out how to do that, and will comment here with the commands I use when I do.

I am still curious regarding how Alembic is tracking my models. If I, say, uninstall Alembic and re-install it (and also point at an empty database), surely it will have to generate a full, from-scratch migration script. No?

(This seems like something several people on the internet are searching for - an Alembic migration script that fully configures a database, from scratch).

zsiegel92 commented Aug 24, 2017

Brilliant. I will do my best to figure out how to do that, and will comment here with the commands I use when I do.

I am still curious regarding how Alembic is tracking my models. If I, say, uninstall Alembic and re-install it (and also point at an empty database), surely it will have to generate a full, from-scratch migration script. No?

(This seems like something several people on the internet are searching for - an Alembic migration script that fully configures a database, from scratch).

@miguelgrinberg

This comment has been minimized.

Show comment
Hide comment
@miguelgrinberg

miguelgrinberg Aug 24, 2017

Owner

Yes, it should have generated a complete migration (which would not have been useful in your case, since you already have a partially migrated db in Heroku).

I think your set up must be incorrect in some way. You are either pointing your config at a different database that is not empty, or somehow the models are not seen by Alembic (which apparently they are, if migrations worked for you before).

Owner

miguelgrinberg commented Aug 24, 2017

Yes, it should have generated a complete migration (which would not have been useful in your case, since you already have a partially migrated db in Heroku).

I think your set up must be incorrect in some way. You are either pointing your config at a different database that is not empty, or somehow the models are not seen by Alembic (which apparently they are, if migrations worked for you before).

@zsiegel92

This comment has been minimized.

Show comment
Hide comment
@zsiegel92

zsiegel92 Aug 24, 2017

It has worked so far, so I suspect it's okay. I configure the app from a config.py file with
app.config.from_object(os.environ['APP_SETTINGS'])
where

class Config(object):
    SQLALCHEMY_DATABASE_URI = os.environ['DATABASE_URL']
class DevelopmentConfig(Config):
    more stuff
class ProductionConfig(Config):
    more stuff

My os.environ['DATABASE_URL'] is postgresql://localhost/poolchat4 (where poolchat4 is my new, fresh database's name), and os.environ['APP_SETTINGS'] is config.DevelopmentConfig. So the database Flask sees seems to be the new, fresh one I'm creating.

In fact, I've tried fully deleting my development database, so I don't know how or from what Alembic is detecting the old schema!

This is getting very me-specific. I will post here when I figure it out. Thank you for all your help, @miguelgrinberg .

zsiegel92 commented Aug 24, 2017

It has worked so far, so I suspect it's okay. I configure the app from a config.py file with
app.config.from_object(os.environ['APP_SETTINGS'])
where

class Config(object):
    SQLALCHEMY_DATABASE_URI = os.environ['DATABASE_URL']
class DevelopmentConfig(Config):
    more stuff
class ProductionConfig(Config):
    more stuff

My os.environ['DATABASE_URL'] is postgresql://localhost/poolchat4 (where poolchat4 is my new, fresh database's name), and os.environ['APP_SETTINGS'] is config.DevelopmentConfig. So the database Flask sees seems to be the new, fresh one I'm creating.

In fact, I've tried fully deleting my development database, so I don't know how or from what Alembic is detecting the old schema!

This is getting very me-specific. I will post here when I figure it out. Thank you for all your help, @miguelgrinberg .

@zsiegel92

This comment has been minimized.

Show comment
Hide comment
@zsiegel92

zsiegel92 Aug 24, 2017

I can see a full description of my database by calling vars(app.extensions['migrate'].db.metadata). In fact, I've made a route that prints exactly this.

This is not in Alembic migration script syntax, but it contains things like

{
	'tables': immutabledict({
		'trips': Table('trips', MetaData(bind=None), 
			Column('carpooler_id', Integer(), ForeignKey('carpooler.id'), table=, primary_key=True, nullable=False), 
			Column('pool_id', Integer(), ForeignKey('pool.id'), table=, primary_key=True, nullable=False), 
			Column('address', String(), table=), 
			Column('num_seats', Integer(), table=), 
			Column('preWindow', Integer(), table=), 
			Column('on_time', Integer(), table=), 
			Column('must_drive', Integer(), table=), 
			Column('selfRep', Text(), table=), 
			Column('selfFormalRep', Text(), table=), 
			Column('poolRepLoaded', Integer(), table=), 
			Column('carpoolerRepLoaded', Integer(), table=), 
			schema=None)
		}),
	'_fk_memos': defaultdict(, 
		 {('carpooler', 'id'): [ForeignKey('carpooler.id'), ForeignKey('carpooler.id')], ('teams', 'id'): [ForeignKey('teams.id'), ForeignKey('teams.id')], ('pool', 'id'): [ForeignKey('pool.id'), ForeignKey('pool.id'), ForeignKey('pool.id')]}
		 )
}

Whereas Alembic migrations (for the same model) look like this:

from alembic import op
import sqlalchemy as sa
revision = 'e21af240a3c5'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
	op.create_table('trips',
	sa.Column('carpooler_id', sa.Integer(), nullable=False),
	sa.Column('pool_id', sa.Integer(), nullable=False),
	sa.Column('address', sa.String(), nullable=True),
	sa.Column('num_seats', sa.Integer(), nullable=True),
	sa.Column('preWindow', sa.Integer(), nullable=True),
	sa.Column('on_time', sa.Integer(), nullable=True),
	sa.Column('must_drive', sa.Integer(), nullable=True),
	sa.Column('selfRep', sa.Text(), nullable=True),
	sa.Column('selfFormalRep', sa.Text(), nullable=True),
	sa.Column('poolRepLoaded', sa.Integer(), nullable=True),
	sa.Column('carpoolerRepLoaded', sa.Integer(), nullable=True),
	sa.ForeignKeyConstraint(['carpooler_id'], ['carpooler.id'], ),
	sa.ForeignKeyConstraint(['pool_id'], ['pool.id'], ),
	sa.PrimaryKeyConstraint('carpooler_id', 'pool_id')
	)

If anyone else has to build a migration script from scratch, please join me here in trying to systematize the process.

zsiegel92 commented Aug 24, 2017

I can see a full description of my database by calling vars(app.extensions['migrate'].db.metadata). In fact, I've made a route that prints exactly this.

This is not in Alembic migration script syntax, but it contains things like

{
	'tables': immutabledict({
		'trips': Table('trips', MetaData(bind=None), 
			Column('carpooler_id', Integer(), ForeignKey('carpooler.id'), table=, primary_key=True, nullable=False), 
			Column('pool_id', Integer(), ForeignKey('pool.id'), table=, primary_key=True, nullable=False), 
			Column('address', String(), table=), 
			Column('num_seats', Integer(), table=), 
			Column('preWindow', Integer(), table=), 
			Column('on_time', Integer(), table=), 
			Column('must_drive', Integer(), table=), 
			Column('selfRep', Text(), table=), 
			Column('selfFormalRep', Text(), table=), 
			Column('poolRepLoaded', Integer(), table=), 
			Column('carpoolerRepLoaded', Integer(), table=), 
			schema=None)
		}),
	'_fk_memos': defaultdict(, 
		 {('carpooler', 'id'): [ForeignKey('carpooler.id'), ForeignKey('carpooler.id')], ('teams', 'id'): [ForeignKey('teams.id'), ForeignKey('teams.id')], ('pool', 'id'): [ForeignKey('pool.id'), ForeignKey('pool.id'), ForeignKey('pool.id')]}
		 )
}

Whereas Alembic migrations (for the same model) look like this:

from alembic import op
import sqlalchemy as sa
revision = 'e21af240a3c5'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
	op.create_table('trips',
	sa.Column('carpooler_id', sa.Integer(), nullable=False),
	sa.Column('pool_id', sa.Integer(), nullable=False),
	sa.Column('address', sa.String(), nullable=True),
	sa.Column('num_seats', sa.Integer(), nullable=True),
	sa.Column('preWindow', sa.Integer(), nullable=True),
	sa.Column('on_time', sa.Integer(), nullable=True),
	sa.Column('must_drive', sa.Integer(), nullable=True),
	sa.Column('selfRep', sa.Text(), nullable=True),
	sa.Column('selfFormalRep', sa.Text(), nullable=True),
	sa.Column('poolRepLoaded', sa.Integer(), nullable=True),
	sa.Column('carpoolerRepLoaded', sa.Integer(), nullable=True),
	sa.ForeignKeyConstraint(['carpooler_id'], ['carpooler.id'], ),
	sa.ForeignKeyConstraint(['pool_id'], ['pool.id'], ),
	sa.PrimaryKeyConstraint('carpooler_id', 'pool_id')
	)

If anyone else has to build a migration script from scratch, please join me here in trying to systematize the process.

@zsiegel92

This comment has been minimized.

Show comment
Hide comment
@zsiegel92

zsiegel92 Aug 25, 2017

@miguelgrinberg problem solved!!

I use an app factory model:

##Module: app.py
#
from app_factory import create_app
app = create_app(__name__)

and

##Module: database.py
#
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

and

##Module: app_factory.py
#
from database import db
import models

def create_app(name=__name__):
	app = Flask(name)
	app.config.from_object(os.environ['APP_SETTINGS'])
	db.init_app(app)
	login_manager.init_app(app)
	with app.app_context():
		db.create_all()
	return app

and

##Module: manager.py
#
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from models import Carpooler, Pool, Trip, Team,team_membership,team_affiliation
from app import app, db
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)
if __name__ == '__main__':
	manager.run()

and

##Module: models.py
#
from database import db
class Carpooler(db.Model):
	__tablename__ = 'carpooler'
	id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    ...

I read this SO post about db.create_all (by @davidism ), and saw that metadata is registered when create_all is called. After deleting my database, and even my Postgres server, I suspected that the metadata was not being stored anywhere, but was being generated in the app before migration scripts could ask the app "what is missing from your database metadata?".

So, I commented out with app.app_context(): db.create_all(), after which python manage.py db migrate did in fact generate a "from-scratch" migration file. Then I pushed the migration to heroku, ran heroku pg:reset, and used heroku run python manage.py db upgrade to set the database models. So grateful to be over this issue.

@miguelgrinberg , can you comment regarding the appropriate place to include db.create_all? Perhaps just before manager.run() in manage.py? The most knowledgable folks seem to put it in a script external to the app, but many people put it in their app.

zsiegel92 commented Aug 25, 2017

@miguelgrinberg problem solved!!

I use an app factory model:

##Module: app.py
#
from app_factory import create_app
app = create_app(__name__)

and

##Module: database.py
#
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

and

##Module: app_factory.py
#
from database import db
import models

def create_app(name=__name__):
	app = Flask(name)
	app.config.from_object(os.environ['APP_SETTINGS'])
	db.init_app(app)
	login_manager.init_app(app)
	with app.app_context():
		db.create_all()
	return app

and

##Module: manager.py
#
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from models import Carpooler, Pool, Trip, Team,team_membership,team_affiliation
from app import app, db
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)
if __name__ == '__main__':
	manager.run()

and

##Module: models.py
#
from database import db
class Carpooler(db.Model):
	__tablename__ = 'carpooler'
	id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    ...

I read this SO post about db.create_all (by @davidism ), and saw that metadata is registered when create_all is called. After deleting my database, and even my Postgres server, I suspected that the metadata was not being stored anywhere, but was being generated in the app before migration scripts could ask the app "what is missing from your database metadata?".

So, I commented out with app.app_context(): db.create_all(), after which python manage.py db migrate did in fact generate a "from-scratch" migration file. Then I pushed the migration to heroku, ran heroku pg:reset, and used heroku run python manage.py db upgrade to set the database models. So grateful to be over this issue.

@miguelgrinberg , can you comment regarding the appropriate place to include db.create_all? Perhaps just before manager.run() in manage.py? The most knowledgable folks seem to put it in a script external to the app, but many people put it in their app.

@davidism

This comment has been minimized.

Show comment
Hide comment
@davidism

davidism Aug 25, 2017

Contributor

You don't include it. Migrations are handling creating tables.

Contributor

davidism commented Aug 25, 2017

You don't include it. Migrations are handling creating tables.

@miguelgrinberg

This comment has been minimized.

Show comment
Hide comment
@miguelgrinberg

miguelgrinberg Aug 25, 2017

Owner

Right, Alembic fully replaces db.create_all(). Just remove it and you should be fine.

Owner

miguelgrinberg commented Aug 25, 2017

Right, Alembic fully replaces db.create_all(). Just remove it and you should be fine.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment