Sequelize: Sequelize.sync() should support ALTER statements instead of full DROP+CREATE statements

[ShimShamSam]

It would be nice if we could call sync() and have it compare the model schema to the database schema and make only the changes necessary using ALTER statements. The major benefit is preservation of data. If implemented correctly, this could probably eliminate most of the need for migration files.

[gustavpursche]

That would be really awesome, yes. I just had a look on jugglingdb - they do it with a method. As it is really dangerous to delete data, when changing a model I would prefer the solution jugglindb had ( http://jugglingdb.co/schema.3.html ).

schema.isActual(function(err, actual) {
    if (!actual) {
        schema.autoupdate();
    }
});

[leroy0211]

I really like this feature. The reason I don't use sequelize yet is because this is not available yet.
The schema's we use have to be updated very often en quickly because new data has to be stored.

[emanuelsaringan]

Hi! Did you already find a way to do a DB sync in sequelize without wiping out all records? Thanks!

[lyptt]

I made use of sequelize's migrate tool to keep the DB in sync with the models, it's a bit more effort but it seems to work fairly well

[emanuelsaringan]

Will look into that. Thanks!

[fixe]

Anyone working on a PR?

[mickhansen]

Not yet, it's a major feature. It's something i really want to do but its not my highest priority.

[lucachaco]

+1

[clouddueling]

This would make my life so much easier! +1

[mickhansen]

Just an update for you all:

We have been considering this - And we need to do a few things:
A mechanism for identifying a difference between the current model schema and the target table schema. Also needs to identify missing junction tables ideally.
This difference needs to be represented by some kind of data structure.
This data structure could then be transformed to either a manual migration (allowing inspection) or to sql and run by sync.

[mickhansen]

As for a timeline, we probably want to look at improving the migration ecosystem first, since that's a very viable solution to this issue aswell.

[clouddueling]

@mickhansen If you need a beta tester please give me a holler!

[alekbarszczewski]

I think that despite comparing model schema with db schema, it should still generate migrations instead of updating database on the fly. First of all it would allow to look through migrations history and migrations details.

[mickhansen]

@alekbarszczewski like i wrote, if you design it properly it generating migrations or updating the database on the fly would be same basically. All the work lies in identifying changes, if you can then represent those changes in a data format you could easily generate either SQL or migrations from them.

[clouddueling]

I found a couple examples:

• http://south.readthedocs.org/en/latest/commands.html#schemamigration
• https://github.com/nshahzad/MySQLDiff
• http://adamspiers.org/computing/mysqldiff/

[alekbarszczewski]

@mickhansen agreed :)

[neekey]

+1， looking forward to it :)

[tolgaek]

@gustavpursche I do like the jugglingdb way of being able to check if a sync is needed

[maor]

+1

[rainder]

+1 That is the only reason why I don't use sequelize. Even doctrine 1.4 was generating migrations from diff.

[anton-johansson]

+1

I agree, this would be wonderful.

[jackiesun8]

+1 *N .haha.

[idchlife]

Started diving into sequalize as I realized that there are no updates on schema with alter.
Right now I will use Sequalize after all, but writing simple migrations by hand is somehow waste of time for me, because I'm also using Symfony2 with Doctrine at many side projects.

Looking forward to see auto migrations in Sequalize

[agchou]

+1

[Madd0g]

huge +1

I was hoping there was a way of detecting an out-of-sync model, that should be top priority, automatically creating the migrations seems so much harder than just listing columns and finding a mismatch.

[mickhansen]

@Madd0g well there's also a whole group of people that think something like this would be really irresponsible - what if there's a bug that suddenly whipes your table for some reason? (bugs happen in software, often).

It's not our highest priority, but something we do want to get to (but we are really only two developers in our spare time :))

[Madd0g]

@mickhansen - I meant that being able to see that a model is out-of-sync should be top priority among other related things like auto-migrate and other _really_ scary and complex things like detecting/fixing out-of-sync properties in relationships.

Just detecting the change within a single model and not doing anything until the dev chooses to do force: true, is not really dangerous. I would really welcome that feature.

[mickhansen]

@Madd0g Yeah ideally i would like to be able to run a base diff that can present the differences in some kind of format that we can then either transform to migrations, raw queries or execute immediately.

[h0vhannes]

+1

[zeeshanjan82]

+1

[zeeshanjan82]

I was initially really impressed with sequelize and coming from the .net world where I could do things much faster using framework like Entity Framework but the issue with sequelize is It really does not provide a way to sync the models and database other than using migration scripts and I am sure no programmer in the world would really like to update database on 2 places, models and then writing the migrations for them. I think if the sync {force: false} option is provided which could sync alter tables also then there is no need for migration scripts and those will become obsolete.

[mickhansen]

@zeeshanjan82 I would personally never use sync({alter: true}), and i'm a contributor. Bugs happen and i wouldn't want my production DB to disappear because of MAGIC.

[gustavpursche]

Now 2 years after my comment I agree to @mickhansen. I still think it would be nice, to allow an altering process, but since sequelize-auto already exists, it's not that hard, to create visual diffs or even replace/ enhance your model files ...

[mickhansen]

@gustavpursche It would still be a nice feature since the underlying functionality could be used to generate migrations you could manually inspect before running.

[gustavpursche]

@mickhansen Agree. But 2 years ago I gave my "+1" to auto-altering the table, without manual checks, why I wouldn't anymore. Maybe a long winter-night will bring a "sequelize-model-diff" to life :)

[diosney]

+1 to it, would be awesome.

Django even integrated south into the core.

link provided above: http://south.readthedocs.org/en/latest/commands.html#schemamigration

already integrated on django:
https://docs.djangoproject.com/en/1.8/topics/migrations/
https://docs.djangoproject.com/en/1.8/ref/django-admin/#django-admin-makemigrations

[floross]

+1

[matthieuh]

+1

[bloodhawk]

+1

[vperezma]

+1

[schettino]

+1

[kewang]

+1

[lianyi]

+1

[gimenete]

I've just released an open source module that compares two postgresql databases and generates SQL statements to modify the schema so the first db matches the second one.

https://github.com/gimenete/dbdiff

[GeoffreyPlitt]

@gimenete You are a champion among men.

[gimenete]

@GeoffreyPlitt Feel free to open an issue if you find something that could be improved or any suggestion :)

[nodecode]

+1

[pc3b3r]

+1

[gzsupos]

+1

[marcsolanadal]

+1

[yuredd]

+1

[n321203]

+1
We could use https://github.com/gimenete/dbdiff as starting point.

[janmeier]

@n321203 However dbdiff is only part of the way - since it only supports postgres, and as far as I can see needs to connect to two existing databases

[gimenete]

@janmeier I'm the author of dbdiff. I can make dbdiff support other databases in the near future. Which database are you using?

I don't think sequelize should use dbdiff internally to start supporting alter sql statements in .sync() but one thing we could do is a new module that using dbdiff and sequelize calls sync() in an empty database and compares this database with the regular one.

[mickhansen]

@gimenete MySQL, SQLite, Postgres, MSSQL
dbdiff looks great but i'm not sure it's a fit here, a better approach will likely still be to have describeTable work well enough that it can be compared to the attributes a model has.

[gimenete]

@mickhansen @janmeier @n321203 I've just released a module that uses dbdiff behind the scenes. It adds a new method to Sequelize: syncDiff(). Check it out:
https://github.com/gimenete/sequelize-sync-diff

It only supports Postgres though. I could add MySQL in the near future.

[trentjones21]

+1

[vekexasia]

+1

[janmeier]

_Note_: Please use reactions instead of filling the thread with +1 comments

[jerryladoo]

+1

[gimenete]

I've released a new version of dbdiff https://github.com/gimenete/dbdiff

This is a big refactoring and the first step for supporting multiple databases. Also now it can be used as a library (not only a cli) to compare two databases, two schemas, or generate a JSON-serializable object with the schema representation of an existing database. This could be integrated into sequelize this way: sequelize could create a "desired" schema representation and dbdiff could compare it with the current database schema. @mickhansen

I've also added "safety" levels of the SQL output. Check the docs for further info.

[idchlife]

@gimenete WOW! Actually developers of sequelize should consider using your library

[xiaofan2406]

will this be considered in 4.0 release?

[mickhansen]

@xiaofan2406 It's not breaking so it's not tied to any specific release.

[neoxack]

+1

[sylvainlap]

+1

[alexey2baranov]

+1

make 2 parameters --dump-sql and --force like in Doctrine2 schema tool. --dump-sql to write migration SQL into console only. This is a safe way to migration on production database. And another key --force for applying commands to target database

[frogcjn]

apgdiff http://apgdiff.com
maybe help to migrate from different versions of PostgreSQL schema generated by sequelize.
just use
pg_dump --schema-only "address-to-your-old-postgresDB" > ~/Desktop/schema0
pg_dump --schema-only "address-to-your-new-postgresDB" > ~/Desktop/schema1

and use apgdiff to generate alter commands.

[Synvox]

Any update on this after two years?

[frogcjn]

I think the easiest way to implement this feature is to use sql diff tool to generated alter SQL

[manuelfink]

+1

[felixfbecker]

Please don't +1 issues. It clutters the thread without adding value to the discussion and spams maintainers with notifications. Use GitHub reactions to upvote features.

[Wats0ns]

Still no PR about this ?

[TangMonk]

This issue opened 4 years ago, And how's the work?

[janmeier]

@TangMonk Appearently no-one has needed this enough that they actually took the time to sit down and write the code (Which would require a significant amount of time).

Remember that almost everyone here works on sequelize on their own time :)

[TangMonk]

@janmeier sorry, I understand, thanks everyone's work, I wish I could help.

[bwoodlt]

Can't wait for this to be out. Will come very handy and wouldnt have to drop table to see new changes!

[lbouriez]

+1

[ChristianTucker]

We've been using Sequelize in Production. Not having this option is leaving us stumped trying to find the best way to make modifications to the database in a production environment.

[meyer9]

I've submitted a patch for this. #7230

Just waiting for a merge, but you can use my branch for now if you really need this feature - jmeyer2k/sequelize

[idchlife]

dear god

[gotojmp]

good job

[ahelord]

How Work ? link doc? video?

[sushantdhiman]

sequelize.sync({ alter: true })

[bwoodlt]

Do I need to update sequelize via npm or need a specific branch?

[mc-zone]

It seems that the latest version v3.30.4 does not include this change. Wait for release.

[janmeier]

This feature has landed in master, so it will be available in v4

[ahelord]

@janmeier Friend, this functionality is important when you could leave this version thank the collaboration.Friend, this functionality is important when you could leave this version thank the collaboration.

[cyberdude]

Woah! It made it!!!!!!!

[atheros]

Yeah, but latest pre-release is 4.0.0-2 from 2016-09-12T07:13:18.104Z.

Is there a plan to release 4.0.0-3 which would include this feature?

[Vincz]

Any update about this feature?
For me it's clearly a game changer regarding the choice of an ORM. Have been using knex for a while, but schema updates and migrations are such a pain.

[meyer9]

It's in master. It will be released in 4.0.0 otherwise you can use the
github version.

On Mon, May 1, 2017 at 5:26 AM Vincent [e-mail] wrote:

Any update about this feature?
For me it's clearly a game changer regarding the choice of an ORM. Have
been using knex for a while, but schema updates and migrations are such a
pain.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/sequelize/sequelize/issues/537#issuecomment-298326392,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABMIk93yFxsvUica5rOW1xke1077uYd7ks5r1c-PgaJpZM4AkYlY
.

[agramdass]

@meyer9 Did a couple of changes to your implementation that fixes column mappings and added a drop column option (so that columns are not dropped by default). Also fixed query generator for unique and foreign keys alteration for postgresql (don't use enum on my side, perhaps that might still be problematic).
Since you have the test scenarios/environment, perhaps you can validate the changes to be working correctly also on your side.
repo: https://github.com/lekkerapps/sequelize

Thanks

[meyer9]

Looks great! There are a couple small things to change. You should submit a PR so that more people can review the code though. @agramdass

[pvsousalima]

How do i use the latest version to try on these changes? is it the following defined?

sequelize.sync({ alter: true })

and install sequelize 4.0?

[meyer9]

Yeah, you need to install it from the github by npm-installing
sequelize/sequelize. 4.0.0 isn't out yet.

On Thu, May 25, 2017 at 7:50 AM, Pedro Victor de Sousa Lima <
[email protected]> wrote:

How do i use the latest version? is it the

sequelize.sync({ alter: true })

and install sequelize 4.0?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/sequelize/sequelize/issues/537#issuecomment-304029606,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABMIk367705eFbHCEit3goFf4bcKQezhks5r9ZUugaJpZM4AkYlY
.

[robbyemmert]

@meyer9 What do you think about adding an option to preserve removed columns (probably by re-naming them, but I am open to other options). If there is support for this, I could create a PR. This would allow implementers to more safely keep schemas up to date, while being able to revert changes if necessary. Could also be great for that "oh shoot, I want that column after all" moment.

For example:

sequelize.sync({
    alter: true,
    preserveColumnsOnSync: true // PLEASE recommend a better name
})

This would add the following benefits:

1. If you remove a column named foo of type Boolean from the schema, instead of removing that column, it will be re-named to something like __removed__foo__Boolean.
2. If you re-add a column with the same name (foo) and same type back into the schema, your existing column will be used, being renamed back to foo.
3. Data is not lost in the process
4. Legacy data could be cleaned up safely at a later time, in a more deliberate manner.

[meyer9]

It sounds like a good idea to me especially since this is only for
development/debugging. Also, possibly make this on by default. I'm sure
there will be users who use alter in production and I think it would be a
good safeguard against data loss.

On Sun, Aug 20, 2017 at 9:46 PM, Robby Emmert [e-mail]
wrote:

@meyer9 https://github.com/meyer9 What do you think about adding an
option to preserve removed columns (probably by re-naming them, but I am
open to other options). If there is support for this, I could create a PR.
This would allow implementers to more safely keep schemas up to date, while
being able to revert changes if necessary. Could also be great for that "oh
shoot, I want that column after all" moment.

For example:

sequelize.sync({
alter: true,
preserveColumnsOnSync: true // PLEASE recommend a better name
})

This would add the following benefits:

1. If you remove a column named foo of type Boolean from the schema,
   instead of removing that column, it will be re-named to something like
   __removed__foo__Boolean.
2. If you re-add a column with the same name (foo) and same type back
   into the schema, your existing column will be used, being renamed back to
   foo.
3. Data is not lost in the process
4. Legacy data could be cleaned up safely at a later time, in a more
   deliberate manner.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/sequelize/sequelize/issues/537#issuecomment-323645364,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABMIk7A4qaXYPXMMCwOvo-r2JHktfKhGks5saQufgaJpZM4AkYlY
.

[manast]

after testing alter: true in a moderate complex database -> many different kind of errors with relations, enums and what not. Seems like the best thing to do is keep away from all this automatic stuff as far away as you can and do your migrations by custom scripts or you are going to create the perfect scenario for a disaster... my two cents.

[janmeier]

@manast I definitely agree with you. At snaplytics we do all migrations by hand as well

[Vincz]

I disagree. It's not because it's complex that it's impossible. I have been using Doctrine ORM auto update with "ALTER TABLE" for years on countless PHP projects with complex databases and never get any issue with it (and you have the option to preview the queries about to be execute). I'm sorry but I really don't get the argument "Let's do all by hand it's better", especially for developers.

[v1r]

The sync({alter: true}) does not take in consideration the field attribute, atleast for MySql.

[kauffmanes]

@v1r it also isn't working for sqlite. Is there additional configuration that needs to be set for this?

[v1r]

@kauffmanes nop, I am not sure for sqlite.

[FarzanRNobakht]

It would be very nice if the significant limitations of the tool are disclosed in a topic on the manual page.

[campbellbrendene]

Bump! Any progress on this?

[Hiroki111]

The sync({alter: true}) does not take in consideration the field attribute, atleast for MySql.

I stumbled on the same issue today (I'm suing SQLite btw).
According to the API doc, this "Alters tables to fit models", but it doesn't run ALTER statements that are defined by queryInterface.changeColumn in migration files.

I wonder what sync({alter: true}) is supposed to do...?