Feature: db.sync() / model.sync() to allow adding as well as dropping columns and indexes.

[mspisars]

If sync() could (based on orm setting) do a true sync of the defined model, then db upgrades can be trivial. There are some things to consider:

• not null columns without a default (need to require a default)
• smart index sync (dropping and recreating indexes on massive tables takes long -- if index is already there, just skip, don't brute force)
• should be able to drop a column with the option to rename it to __{colname} so data can be preserved (even if just as a safety net)
  • this would imply that columns starting with "__" should be ignored by the orm.

These are just thoughts, any feedback, objections?

[dresende]

Columns that are not defined in db.define are ignored. What you're describing is a more robust sync. It already creates tables only if they don't exist. The thing missing (and a bit more complicated) is looking at an already defined table and change properties that have incorrect type, rename properties that exist and aren't used and add properties that are missing. This isn't easy but it's on my plans :)

[mspisars]

Exactly... glad to hear it's in the plans!

[dresende]

I'll leave this open to link any progress I do on this.

[alyraz]

+1 on this being a super useful feature. what is the best way to remove a column / change a data type / generally update the schema as is? sorry for the noob question, this is my first foray into node world rails and I feel a bit lost with my migrations! but overall, this orm seems awesome and i am excited to have found it! much more explicit and legible than any other I've seen, so thank you dresende!

[dxg]

Currently you need to write an ALTER TABLE query and db.driver.execQuery( ... ).

[dresende]

Thank you @alyraz . I see a little traction on the plugins side, I'm hoping to make some requested changes and be able to probably create a better sync method in the core or in the plugins.

[ChrisCinelli]

+1

[takempf]

I'd love to see something like this. I'm currently mulling over how to deal with updating my development database, and it looks like a clean slate is my only real option at the moment...

[dresende]

I would really love to do this in a different module. @SPARTAN563 @dxg anyone want to start it? :)

ORM could provide a database connection and an object with the columns of each table (and indexes). The model just needs to sync it (ALTER, CREATE,.. ).

[dxg]

I have taken a tentative look at some other migration libraries and it doesn't look too complicated. Bit short for time at the moment though.. might revisit in a few weeks if noone else picks it up. Edit: The current thing I'm working on will likely need to be a precursor to any migration functionality. Nearly done, just gotta fix some multi-key tests.

[dresende]

This is in progress right now:

https://github.com/dresende/node-sql-ddl-sync

[dxg]

sql-ddl-sync takes a very automatic approach. It's not possible to manipulate data during a migration.

My co-worker took a more rails like approach which builds on top of sql-ddl-sync: /locomote/node-migrate-orm2 Basically you can write migration steps, and add any processing code along the way.

It still needs a bit of polishing and extra functionality, but it's well on it's way.