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

ponyorm migration tool #16

Open
steinster opened this Issue Aug 7, 2013 · 45 comments

Comments

Projects
None yet
@steinster

steinster commented Aug 7, 2013

Hello,

I'm not sure if this is the place to put this question. If there is a better place please let me know.

Is there a DB migration tool for pony orm? Something like alembic for sqlalchemy..

@cryptoalexey

This comment has been minimized.

Show comment
Hide comment
@cryptoalexey

cryptoalexey Aug 7, 2013

Member

Hi,
There is no such a tool yet, although we are working on it. Will let you know when it is ready. Thank you for asking.

On Aug 7, 2013, at 11:51 AM, steinster notifications@github.com wrote:

Hello,

I'm not sure if this is the place to put this question. If there is a better place please let me know.

Is there a DB migration tool for pony orm? Something like alembic for sqlalchemy..


Reply to this email directly or view it on GitHub.

Member

cryptoalexey commented Aug 7, 2013

Hi,
There is no such a tool yet, although we are working on it. Will let you know when it is ready. Thank you for asking.

On Aug 7, 2013, at 11:51 AM, steinster notifications@github.com wrote:

Hello,

I'm not sure if this is the place to put this question. If there is a better place please let me know.

Is there a DB migration tool for pony orm? Something like alembic for sqlalchemy..


Reply to this email directly or view it on GitHub.

@steinster

This comment has been minimized.

Show comment
Hide comment
@steinster

steinster Aug 7, 2013

If you need any help with it I would love to help with the project a bit.

steinster commented Aug 7, 2013

If you need any help with it I would love to help with the project a bit.

@cryptoalexey

This comment has been minimized.

Show comment
Hide comment
@cryptoalexey

cryptoalexey Aug 7, 2013

Member

Thank you, I appreciate it, we'll get in touch.

On Aug 7, 2013, at 7:27 PM, steinster notifications@github.com wrote:

If you need any help with it I would love to help with the project a bit.


Reply to this email directly or view it on GitHub.

Member

cryptoalexey commented Aug 7, 2013

Thank you, I appreciate it, we'll get in touch.

On Aug 7, 2013, at 7:27 PM, steinster notifications@github.com wrote:

If you need any help with it I would love to help with the project a bit.


Reply to this email directly or view it on GitHub.

@ghost ghost assigned cryptoalexey Aug 30, 2013

@cryptoalexey

This comment has been minimized.

Show comment
Hide comment
@cryptoalexey

cryptoalexey Aug 30, 2013

Member

Hi,
We have created the mailing list ponyorm-list@ponyorm.com. This is the place to ask questions, share experiences, or discuss the Pony ORM with other users and the Pony ORM developers. You can subscribe to the list at http://ponyorm-list.ponyorm.com
Regards,
Alexey

Member

cryptoalexey commented Aug 30, 2013

Hi,
We have created the mailing list ponyorm-list@ponyorm.com. This is the place to ask questions, share experiences, or discuss the Pony ORM with other users and the Pony ORM developers. You can subscribe to the list at http://ponyorm-list.ponyorm.com
Regards,
Alexey

@guyskk

This comment has been minimized.

Show comment
Hide comment
@guyskk

guyskk commented Oct 30, 2015

+1

@NiklasRosenstein

This comment has been minimized.

Show comment
Hide comment

NiklasRosenstein commented Nov 27, 2015

+1

@andreymal

This comment has been minimized.

Show comment
Hide comment
@andreymal

andreymal Mar 21, 2016

Even third-party tools are still not exist?

andreymal commented Mar 21, 2016

Even third-party tools are still not exist?

@nadermx

This comment has been minimized.

Show comment
Hide comment
@nadermx

nadermx May 12, 2016

Any specific parts I can try and help with getting this done? I need migrations pretty badly at this point

nadermx commented May 12, 2016

Any specific parts I can try and help with getting this done? I need migrations pretty badly at this point

@kozlovsky

This comment has been minimized.

Show comment
Hide comment
@kozlovsky

kozlovsky May 13, 2016

Member

Right now we are working on release with full JSON support, and after that we will switch to working on migrations. Hope the initial version will be released soon after that..

Member

kozlovsky commented May 13, 2016

Right now we are working on release with full JSON support, and after that we will switch to working on migrations. Hope the initial version will be released soon after that..

@Vic020

This comment has been minimized.

Show comment
Hide comment
@Vic020

Vic020 Jun 20, 2016

+1
get in touch

Vic020 commented Jun 20, 2016

+1
get in touch

@hackrole

This comment has been minimized.

Show comment
Hide comment
@hackrole

hackrole Jun 21, 2016

+1, waiting for your staff

hackrole commented Jun 21, 2016

+1, waiting for your staff

@misakar

This comment has been minimized.

Show comment
Hide comment
@misakar

misakar commented Aug 7, 2016

+1

@nadermx

This comment has been minimized.

Show comment
Hide comment
@nadermx

nadermx Sep 14, 2016

@kozlovsky I saw that you pushed the json update! Any word or any suggestions on how I could contribute with some code in the method you have in mind in order to get migrations done quicker. Having to do manual alter tables and indexing is getting to be cumbersome

nadermx commented Sep 14, 2016

@kozlovsky I saw that you pushed the json update! Any word or any suggestions on how I could contribute with some code in the method you have in mind in order to get migrations done quicker. Having to do manual alter tables and indexing is getting to be cumbersome

@darkf

This comment has been minimized.

Show comment
Hide comment
@darkf

darkf Oct 14, 2016

Yeah, this is the main missing feature of Pony right now. It's excellent otherwise, but really needs migrations (preferably automatic).

darkf commented Oct 14, 2016

Yeah, this is the main missing feature of Pony right now. It's excellent otherwise, but really needs migrations (preferably automatic).

@pingf

This comment has been minimized.

Show comment
Hide comment
@pingf

pingf Oct 19, 2016

really needed!

pingf commented Oct 19, 2016

really needed!

@sighalt

This comment has been minimized.

Show comment
Hide comment
@sighalt

sighalt commented Nov 28, 2016

+1

@TimothyBramlett

This comment has been minimized.

Show comment
Hide comment

TimothyBramlett commented Nov 29, 2016

+1

@pylover

This comment has been minimized.

Show comment
Hide comment
@pylover

pylover commented Nov 29, 2016

+1

@danielhrisca

This comment has been minimized.

Show comment
Hide comment
@danielhrisca

danielhrisca Nov 30, 2016

Any progress with this topic?

danielhrisca commented Nov 30, 2016

Any progress with this topic?

@d-me-tree

This comment has been minimized.

Show comment
Hide comment
@d-me-tree

d-me-tree commented Dec 3, 2016

+1

@darkf

This comment has been minimized.

Show comment
Hide comment
@darkf

darkf Dec 3, 2016

@amalashkevich @kozlovsky Any status update on this? There are approximately a kajillion people waiting on and +1ing this, it would be nice to see where it's going or if we should migrate (pun intended) to another ORM with this functionality.

darkf commented Dec 3, 2016

@amalashkevich @kozlovsky Any status update on this? There are approximately a kajillion people waiting on and +1ing this, it would be nice to see where it's going or if we should migrate (pun intended) to another ORM with this functionality.

@kozlovsky

This comment has been minimized.

Show comment
Hide comment
@kozlovsky

kozlovsky Dec 3, 2016

Member

The core migration functionality is done, we need approximately two weeks to release the first version

Member

kozlovsky commented Dec 3, 2016

The core migration functionality is done, we need approximately two weeks to release the first version

@darkf

This comment has been minimized.

Show comment
Hide comment
@darkf

darkf Dec 4, 2016

Thanks for the update! Good to hear.

darkf commented Dec 4, 2016

Thanks for the update! Good to hear.

@danielhrisca

This comment has been minimized.

Show comment
Hide comment
@danielhrisca

danielhrisca Dec 4, 2016

Great news! Thank you for your work!

danielhrisca commented Dec 4, 2016

Great news! Thank you for your work!

@famargar

This comment has been minimized.

Show comment
Hide comment
@famargar

famargar Dec 12, 2016

Keep us posted!

famargar commented Dec 12, 2016

Keep us posted!

@kafeg

This comment has been minimized.

Show comment
Hide comment
@kafeg

kafeg Dec 13, 2016

Hi! Very important feature! Waiting for release)

kafeg commented Dec 13, 2016

Hi! Very important feature! Waiting for release)

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Dec 28, 2016

Hi, any news of this feature?

ghost commented Dec 28, 2016

Hi, any news of this feature?

@nadermx

This comment has been minimized.

Show comment
Hide comment
@nadermx

nadermx Jan 10, 2017

Sorry to keep pestering, but should we start a gofundme page to get this done?

nadermx commented Jan 10, 2017

Sorry to keep pestering, but should we start a gofundme page to get this done?

@andreymal

This comment has been minimized.

Show comment
Hide comment
@andreymal

andreymal Feb 10, 2017

Well, bump again )
I would like to continue working on my project after the implementation of migrations, so I'm waiting for it

andreymal commented Feb 10, 2017

Well, bump again )
I would like to continue working on my project after the implementation of migrations, so I'm waiting for it

@Vitalium

This comment has been minimized.

Show comment
Hide comment
@Vitalium

Vitalium commented Feb 10, 2017

Me too :)

@pingf

This comment has been minimized.

Show comment
Hide comment
@pingf

pingf Feb 22, 2017

'approximately two weeks' becomes two months 😞

pingf commented Feb 22, 2017

'approximately two weeks' becomes two months 😞

@darkf

This comment has been minimized.

Show comment
Hide comment
@darkf

darkf Feb 22, 2017

Yeah, any word on this feature @kozlovsky?

darkf commented Feb 22, 2017

Yeah, any word on this feature @kozlovsky?

@kozlovsky

This comment has been minimized.

Show comment
Hide comment
@kozlovsky

kozlovsky Feb 22, 2017

Member

Hi guys, the task of writing good migration tools turns out to be more complex than I expected.

The delay was caused by the following reason: in current version of Pony some schema names are incompatible with a future migration tool.

One example is two entities Foo and Bar which have a many-to-many relationship. That is, Foo class has a bars attribute of type Set, while Bar class has a foos attribute of the same type. In that case, current version of Pony names underlying many-to-many table as bar_foo (that is, it used the names of two entities sorted in alphabetic order). This works if there are only one many-to-many attribute between this pair of entities. If there are several such attributes, they become called bar_foo_2, bar_foo_3, etc. This is very error-prone when using migrations, because introducing new many-to-many attribute between the same two entities can change the meaning of all bar_foo_N intermediary tables, which lead to corrupted data.

Another problematic situation is with a long names which don't fit within specified database maximum object name length. Current version of Pony just cut such names to maximum allowable length, but this can lead to situation when several different columns/foreign keys/indexes has the same name. The most problematic database in this aspect is Oracle, because in it the maximum name length cannot be greater than 30 chars. So even several independent columns can be erroneously cut to the same name. PostgreSQL and MySQL has less restrictive constraints of 63 and 64 max symbols in name, but even that may be not enough for automatically generated names of foreign keys and composite indexes. The correct solution is to add some unique md5 hash to the end of a truncated identifier name, if the length of the original name is too big.

The migration-enabled version of Pony will handle the names of tables, columns, foreign keys and indexes in that enhanced way. But what to do with existing databases? We think they should be upgraded to this new naming standard before migrations can be applied to them. In order to do that, we add a new system table pony_version, and if that table is not exists, we interpret that as that the database was used with pre-migrations version of Pony, and in that case new Pony refuses to work with that database, until an upgrade was performed. The implementation of that upgrade was rather complex, because many different types of objects should be renamed: if some table is renamed, we need to rename its foreign keys and indexes as well, and in different databases it requires different SQL commands.

At this moment we have correct implementation of that upgrade logic for all supported databases, and also implementation of the migration tool. What rest is to combine upgrade logic and migration logic together and propose some API which will be convenient to user. We plan to replace db.bind(**settings.db_params) and db.generate_mapping(create_tables=True) database methods to db.connect(**settings.db_params) method and another db.migrate(**settings.db_params) method which can handle command-line arguments such as migrate make, migrate apply and migrate showsql, but this part of work requires several more days to be implemented correctly.

Resuming, we actively working on migration tool, but my estimates about the time it requires to be implemented turn out to be completely incorrect. I think the work on migration tool is finished on 90% at this moment, but it will be unrealistically to say we can release migrations during this week. I really hope we can release it during the following week, but as history has shown, it is very hard to give correct estimations about the time required for implementing of such a complex tool as migration support. But we are working on it, and I think the final is already close.

Member

kozlovsky commented Feb 22, 2017

Hi guys, the task of writing good migration tools turns out to be more complex than I expected.

The delay was caused by the following reason: in current version of Pony some schema names are incompatible with a future migration tool.

One example is two entities Foo and Bar which have a many-to-many relationship. That is, Foo class has a bars attribute of type Set, while Bar class has a foos attribute of the same type. In that case, current version of Pony names underlying many-to-many table as bar_foo (that is, it used the names of two entities sorted in alphabetic order). This works if there are only one many-to-many attribute between this pair of entities. If there are several such attributes, they become called bar_foo_2, bar_foo_3, etc. This is very error-prone when using migrations, because introducing new many-to-many attribute between the same two entities can change the meaning of all bar_foo_N intermediary tables, which lead to corrupted data.

Another problematic situation is with a long names which don't fit within specified database maximum object name length. Current version of Pony just cut such names to maximum allowable length, but this can lead to situation when several different columns/foreign keys/indexes has the same name. The most problematic database in this aspect is Oracle, because in it the maximum name length cannot be greater than 30 chars. So even several independent columns can be erroneously cut to the same name. PostgreSQL and MySQL has less restrictive constraints of 63 and 64 max symbols in name, but even that may be not enough for automatically generated names of foreign keys and composite indexes. The correct solution is to add some unique md5 hash to the end of a truncated identifier name, if the length of the original name is too big.

The migration-enabled version of Pony will handle the names of tables, columns, foreign keys and indexes in that enhanced way. But what to do with existing databases? We think they should be upgraded to this new naming standard before migrations can be applied to them. In order to do that, we add a new system table pony_version, and if that table is not exists, we interpret that as that the database was used with pre-migrations version of Pony, and in that case new Pony refuses to work with that database, until an upgrade was performed. The implementation of that upgrade was rather complex, because many different types of objects should be renamed: if some table is renamed, we need to rename its foreign keys and indexes as well, and in different databases it requires different SQL commands.

At this moment we have correct implementation of that upgrade logic for all supported databases, and also implementation of the migration tool. What rest is to combine upgrade logic and migration logic together and propose some API which will be convenient to user. We plan to replace db.bind(**settings.db_params) and db.generate_mapping(create_tables=True) database methods to db.connect(**settings.db_params) method and another db.migrate(**settings.db_params) method which can handle command-line arguments such as migrate make, migrate apply and migrate showsql, but this part of work requires several more days to be implemented correctly.

Resuming, we actively working on migration tool, but my estimates about the time it requires to be implemented turn out to be completely incorrect. I think the work on migration tool is finished on 90% at this moment, but it will be unrealistically to say we can release migrations during this week. I really hope we can release it during the following week, but as history has shown, it is very hard to give correct estimations about the time required for implementing of such a complex tool as migration support. But we are working on it, and I think the final is already close.

@pingf

This comment has been minimized.

Show comment
Hide comment
@pingf

pingf Apr 7, 2017

any news on this feature?

pingf commented Apr 7, 2017

any news on this feature?

@luishdzupiita

This comment has been minimized.

Show comment
Hide comment
@luishdzupiita

luishdzupiita Jul 27, 2017

any news on this feature? +1

luishdzupiita commented Jul 27, 2017

any news on this feature? +1

@danielhrisca

This comment has been minimized.

Show comment
Hide comment
@danielhrisca

danielhrisca Jul 27, 2017

Many are waiting nervously for this feature to come to pony. I hope it's not going to take much longer

danielhrisca commented Jul 27, 2017

Many are waiting nervously for this feature to come to pony. I hope it's not going to take much longer

@kickapoo

This comment has been minimized.

Show comment
Hide comment
@kickapoo

kickapoo Oct 15, 2017

In my case this is the only thing that block me for full pass to pony.
Please @kozlovsky if you could give another feedback about the current state will be most appriciated.

kickapoo commented Oct 15, 2017

In my case this is the only thing that block me for full pass to pony.
Please @kozlovsky if you could give another feedback about the current state will be most appriciated.

@kozlovsky

This comment has been minimized.

Show comment
Hide comment
@kozlovsky

kozlovsky Oct 16, 2017

Member

At this moment we have some internal branch which can handle simple migrations (like, adding/deleting entities, attributes and relationships). I hope we can push it to GitHub really soon. But before that we need to do some final review during this week.

Member

kozlovsky commented Oct 16, 2017

At this moment we have some internal branch which can handle simple migrations (like, adding/deleting entities, attributes and relationships). I hope we can push it to GitHub really soon. But before that we need to do some final review during this week.

@johanngomes

This comment has been minimized.

Show comment
Hide comment
@johanngomes

johanngomes Nov 7, 2017

Hi, @kozlovsky and team. Any news on when we can have this migration functionality? Thanks,

johanngomes commented Nov 7, 2017

Hi, @kozlovsky and team. Any news on when we can have this migration functionality? Thanks,

@kozlovsky

This comment has been minimized.

Show comment
Hide comment
@kozlovsky

kozlovsky Nov 8, 2017

Member

Hi, I just pushed a new branch orm-migrations with the first version of PonyORM migrations. You can read the description of API here: https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate

The code is very raw at this point and not intended for production usage yet, we still need to work on it. I think really simple use-cases for PostgreSQL and MySQL should work, but SQLite support is limited, and merge functionality is untested. Adding entity or attribute should work, but, say, changing inheritance hierarchy or modifying a type of relationship will raise NotImplementedError. Anyway, I hope the working version of migrations is close.

Member

kozlovsky commented Nov 8, 2017

Hi, I just pushed a new branch orm-migrations with the first version of PonyORM migrations. You can read the description of API here: https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate

The code is very raw at this point and not intended for production usage yet, we still need to work on it. I think really simple use-cases for PostgreSQL and MySQL should work, but SQLite support is limited, and merge functionality is untested. Adding entity or attribute should work, but, say, changing inheritance hierarchy or modifying a type of relationship will raise NotImplementedError. Anyway, I hope the working version of migrations is close.

@johanngomes

This comment has been minimized.

Show comment
Hide comment
@johanngomes

johanngomes Nov 8, 2017

Thank you for your update, @kozlovsky !

johanngomes commented Nov 8, 2017

Thank you for your update, @kozlovsky !

@hammeronthenet

This comment has been minimized.

Show comment
Hide comment
@hammeronthenet

hammeronthenet Dec 1, 2017

Hi @kozlovsky,
nice!

One question: what's better to install 0.8 version? When do you think 0.8 will be available on pip?

Thank you!

hammeronthenet commented Dec 1, 2017

Hi @kozlovsky,
nice!

One question: what's better to install 0.8 version? When do you think 0.8 will be available on pip?

Thank you!

@zxytim

This comment has been minimized.

Show comment
Hide comment
@zxytim

zxytim Jun 25, 2018

How's migration going now?

zxytim commented Jun 25, 2018

How's migration going now?

@patrickporto

This comment has been minimized.

Show comment
Hide comment
@patrickporto

patrickporto Jun 26, 2018

In moment has some alternative for schema migrations on PonyORM?

patrickporto commented Jun 26, 2018

In moment has some alternative for schema migrations on PonyORM?

@nadermx

This comment has been minimized.

Show comment
Hide comment
@nadermx

nadermx Jun 26, 2018

I can confirm that migrations is working, if you want to install it via pip currently do the following

pip install git+git://github.com/ponyorm/pony.git@orm-migrations

All the documentation on how to do migration can be found here

Also if you are using pony with flask blueprints, please note ref another issue

generate_mapping call from app/init.py to some submodule like app/main.py which is not imported when from app import models line of migrate.py module is executed

nadermx commented Jun 26, 2018

I can confirm that migrations is working, if you want to install it via pip currently do the following

pip install git+git://github.com/ponyorm/pony.git@orm-migrations

All the documentation on how to do migration can be found here

Also if you are using pony with flask blueprints, please note ref another issue

generate_mapping call from app/init.py to some submodule like app/main.py which is not imported when from app import models line of migrate.py module is executed

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