node.js Sequelize.js：如何使用迁移和同步

Sequelize.js: how to use migrations and sync

提问by tremby

I'm close to having my project ready to launch. I have big plans for after launch and the database structure is going to change -- new columns in existing tables as well as new tables, and new associations to existing and new models.

我快要准备好启动我的项目了。我对发布后有很大的计划，数据库结构将发生变化——现有表中的新列以及新表，以及与现有模型和新模型的新关联。

I haven't touched migrations in Sequelize yet, since I've only had testing data which I don't mind wiping out every time the database changes.

我还没有接触 Sequelize 中的迁移，因为我只有测试数据，我不介意每次数据库更改时都将其删除。

To that end, at present I'm running sync force: truewhen my app starts up, if I have changed the model definitions. This deletes all the tables and makes them from scratch. I could omit the forceoption to have it only create new tables. But if existing ones have changed this is not useful.

为此sync force: true，如果我更改了模型定义，目前我正在我的应用程序启动时运行。这将删除所有表并从头开始创建它们。我可以省略force让它只创建新表的选项。但是如果现有的已经改变，这没有用。

So once I add in migrations how do things work? Obviously I don't want existing tables (with data in them) to be wiped out, so sync force: trueis out of the question. On other apps I've helped develop (Laravel and other frameworks) as part of the app's deployment procedure we run the migrate command to run any pending migrations. But in these apps the very first migration has a skeleton database, with the database in the state where it was some time early in development -- the first alpha release or whatever. So even an instance of the app late to the party can get up to speed in one go, by running all migrations in sequence.

所以一旦我添加了迁移，事情是如何运作的？显然我不希望现有的表（其中有数据）被清除，所以这sync force: true是不可能的。在我帮助开发的其他应用程序（Laravel 和其他框架）上，作为应用程序部署过程的一部分，我们运行 migrate 命令来运行任何挂起的迁移。但是在这些应用程序中，第一次迁移有一个骨架数据库，数据库处于开发早期的状态——第一个 alpha 版本或其他。因此，即使是聚会迟到的应用程序实例，也可以通过按顺序运行所有迁移，一次性加快速度。

How do I generate such a "first migration" in Sequelize? If I don't have one, a new instance of the app some way down the line will either have no skeleton database to run the migrations on, or it will run sync at the start and will make the database in the new state with all the new tables etc, but then when it tries to run the migrations they won't make sense, since they were written with the original database and each successive iteration in mind.

如何在 Sequelize 中生成这样的“第一次迁移”？如果我没有，那么应用程序的某个新实例将没有框架数据库来运行迁移，或者它会在开始时运行同步并使数据库处于新状态新表等，但是当它尝试运行迁移时，它们将没有意义，因为它们是用原始数据库和每次连续迭代编写的。

My thought process: at every stage, the initial database plus each migration in sequence should equal (plus or minus data) the database generated when sync force: trueis run. This is because the model descriptions in the code describe the database structure. So maybe if there is no migration table we just run sync and mark all the migrations as done, even though they weren't run. Is this what I need to do (how?), or is Sequelize supposed to do this itself, or am I barking up the wrong tree? And if I'm in the right area, surely there should be a nice way to auto generate most of a migration, given the old models (by commit hash? or even could each migration be tied to a commit? I concede I am thinking in a non-portable git-centric universe) and the new models. It can diff the structure and generate the commands needed to transform the database from old to new, and back, and then the developer can go in and make any necessary tweaks (deleting/transitioning particular data etc).

我的思考过程：在每个阶段，初始数据库加上顺序的每次迁移应该等于（加或减数据）时生成的数据库 sync force: true正在运行。这是因为代码中的模型描述描述了数据库结构。所以也许如果没有迁移表，我们只是运行同步并将所有迁移标记为完成，即使它们没有运行。这是我需要做的（怎么做？），还是 Sequelize 应该自己做这件事，或者我是在吠错树？如果我在正确的区域，肯定应该有一个很好的方法来自动生成大部分迁移，考虑到旧模型（通过提交哈希？甚至每个迁移都可以与提交相关联吗？我承认我在想在以 git 为中心的非便携式宇宙中）和新模型。它可以区分结构并生成将数据库从旧数据库转换为新数据库并返回所需的命令，然后开发人员可以进行任何必要的调整（删除/转换特定数据等）。

When I run the sequelize binary with the --initcommand it gives me an empty migrations directory. When I then run sequelize --migrateit makes me a SequelizeMeta table with nothing in it, no other tables. Obviously not, because that binary doesn't know how to bootstrap my app and load the models.

当我使用--init命令运行 sequelize 二进制文件时，它给了我一个空的迁移目录。当我运行sequelize --migrate它时，它使我成为一个 SequelizeMeta 表，里面什么都没有，没有其他表。显然不是，因为那个二进制文件不知道如何引导我的应用程序并加载模型。

I must be missing something.

我肯定错过了什么。

TLDR: how do I set up my app and its migrations so various instances of the live app can be brought up to date, as well as a brand new app with no legacy starting database?

TLDR：我如何设置我的应用程序及其迁移，以便实时应用程序的各种实例可以更新，以及一个没有遗留起始数据库的全新应用程序？

回答by f1nn

Generating the "first migration"

生成“第一次迁移”

In your case, the most reliable way is to do it almost manually. I would suggest to use sequelize-clitool. The syntax is rather plain:

在您的情况下，最可靠的方法是几乎手动完成。我建议使用sequelize-cli工具。语法相当简单：

sequelize init
...
sequelize model:create --name User --attributes first_name:string,last_name:string,bio:text

This will create both model AND migration. Then, manually merge your existing models with generated with sequelize-cli, and do the same with migrations. After doing this, wipe database (if possible), and run

这将创建模型和迁移。然后，手动将现有模型与使用 sequelize-cli 生成的模型合并，并对迁移执行相同操作。执行此操作后，擦除数据库（如果可能），然后运行

sequelize db:migrate

This will create schema will migrations. You should do this only once to switch to proper process of schema developments (without sync:force, but with authoritative migrations).

这将创建架构将迁移。您应该只执行一次以切换到模式开发的正确过程（没有同步：强制，但有权威的迁移）。

Later, when you need to change schema:

稍后，当您需要更改架构时：

1. Create a migration: sequelize migration:create
2. Write up and down functions in your migration file
3. According to your changes in migration file, change your model manually
4. Run sequelize db:migrate

1. 创建迁移： sequelize migration:create
2. 在迁移文件中写入 up 和 down 函数
3. 根据您在迁移文件中的更改，手动更改您的模型
4. 跑 sequelize db:migrate

Running migrations on production

在生产中运行迁移

Obviously you can't ssh to production server and run migrations by hands. Use umzug, framework agnostic migration tool for Node.JS to perform pending migrations before app starts.

显然，您无法通过 ssh 连接到生产服务器并手动运行迁移。使用umzug，Node.JS 的框架不可知迁移工具在应用程序启动之前执行挂起的迁移。

You can get a list of pending/not yet executed migrations like this:

您可以获得一个挂起/尚未执行的迁移列表，如下所示：

umzug.pending().then(function (migrations) {
  // "migrations" will be an Array with the names of
  // pending migrations.
}); 

Then execute migrations (inside callback). The execute method is a general purpose function that runs for every specified migrations the respective function:

然后执行迁移（内部回调）。execute 方法是一个通用函数，它为每个指定的迁移运行相应的函数：

umzug.execute({
  migrations: ['some-id', 'some-other-id'],
  method: 'up'
}).then(function (migrations) {
  // "migrations" will be an Array of all executed/reverted migrations.
});

And my suggestion is to do it before app starts and tries to serve routes every time. Something like this:

我的建议是在应用程序启动之前执行此操作，并尝试每次都提供路由。像这样的东西：

umzug.pending().then(function(migrations) {
    // "migrations" will be an Array with the names of
    // pending migrations.
    umzug.execute({
        migrations: migrations,
        method: 'up'
    }).then(function(migrations) {
        // "migrations" will be an Array of all executed/reverted migrations.
        // start the server
        app.listen(3000);
        // do your stuff
    });
});

I can't try this right now, but at first look it should work.

我现在不能尝试这个，但乍一看它应该可以工作。

UPD Apr. 2016

UPD 2016 年 4 月

After a year, still useful, so sharing my current tips. For now, I'm installing sequelize-clipackage as required livedependancy, and then modify NPM startup scripts in package.jsonlike this:

一年后，仍然有用，所以分享我目前的技巧。现在，我正在sequelize-cli根据需要的实时依赖安装包，然后package.json像这样修改 NPM 启动脚本：

...
"scripts": {
  "dev": "grunt && sequelize db:migrate && sequelize db:seed:all && node bin/www",
  "start": "sequelize db:migrate && sequelize db:seed:all && node bin/www"
},
...

The only thing I need to do on production server is npm start. This command will run all migrations, apply all seeders and start app server. No need to call umzug manually.

我需要在生产服务器上做的唯一一件事是npm start. 此命令将运行所有迁移，应用所有播种机并启动应用服务器。无需手动调用 umzug。

回答by user1916988

Just learning this myself, but I think I would recommend using migrations now so you get used to them. I've found the best thing for figuring out what goes in the migration is to look at the sql on the tables created by sequelize.sync()and then build the migrations from there.

只是自己学习这个，但我想我建议现在使用迁移，以便您习惯它们。我发现找出迁移中发生的事情的最佳方法是查看创建的表上的 sql sequelize.sync()，然后从那里构建迁移。

migrations -c [migration name] 

Will create the template migration file in a migrations directory. You can then populate it with the fields you need created. This file will need to include createdAt/updatedAt, fields needed for associations, etc.

将在迁移目录中创建模板迁移文件。然后，您可以使用您需要创建的字段填充它。该文件需要包含createdAt/ updatedAt、关联所需的字段等。

For initial table creation down should have:

对于初始表创建下来应该有：

migration.dropTable('MyTable');

But subsequent updates to the table structure can leave this out and just use alter table.

但是对表结构的后续更新可以忽略这一点而只使用alter table。

./node_modules/.bin/sequelize --migrate

An example create would look like:

创建示例如下所示：

module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable(
        'MyTable',
        {
          id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
          },
          bigString: {type: DataTypes.TEXT, allowNull: false},
          MyOtherTableId: DataTypes.INTEGER,
          createdAt: {
            type: DataTypes.DATE
          },
          updatedAt: {
            type: DataTypes.DATE
          }
        });
    done();
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable('MyTable');
    done();
  }

To redo from start:

从头开始重做：

./node_modules/.bin/sequelize --migrate --undo
./node_modules/.bin/sequelize --migrate

I'm using coffee to run a seed file to populate the tables after:

我正在使用咖啡运行种子文件来填充表：

coffee server/seed.coffee

This just has a create function in it that looks something like:

它只有一个 create 函数，看起来像：

user = db.User.create
  username: 'bob'
  password: 'suruncle'
  email: '[email protected]'
.success (user) ->
  console.log 'added user'
  user_id = user.id
  myTable = [
    field1: 'womp'
    field2: 'rat'

    subModel: [
      field1: 'womp'
     ,
      field1: 'rat'
    ]
  ]

Remember to take your sync()out of index in your models or it will overwrite what the migrations and seed do.

请记住sync()在模型中删除索引，否则它会覆盖迁移和种子的作用。

Docs are at http://sequelize.readthedocs.org/en/latest/docs/migrations/of course. But the basic answer is you have to add everything in yourself to specify the fields you need. It doesn't do it for you.

当然，文档位于http://sequelize.readthedocs.org/en/latest/docs/migrations/。但基本的答案是您必须自己添加所有内容以指定您需要的字段。它不适合你。

回答by meyer9

For development, there is now an option to sync the current tables by altering their structure. Using the latest version from the sequelize github repo, you can now run sync with the alterparameter.

对于开发，现在可以选择通过更改其结构来同步当前表。使用sequelize github repo 中的最新版本，您现在可以使用alter参数运行同步。

Table.sync({alter: true})

A caveat from the docs:

来自文档的警告：

Alters tables to fit models. Not recommended for production use. Deletes data in columns that were removed or had their type changed in the model.

更改表格以适合模型。不推荐用于生产用途。删除模型中已删除或其类型已更改的列中的数据。

回答by Nestor Magalhaes

Now with the new sequelize migration is very simple.

现在使用新的 sequelize 迁移非常简单。

This is a example what you can do.

这是您可以做什么的示例。

    'use strict';

    var Promise = require('bluebird'),
        fs = require('fs');

    module.exports = {
        up: function (queryInterface, Sequelize) {

            return Promise
                .resolve()
                .then(function() {
                    return fs.readFileSync(__dirname + '/../initial-db.sql', 'utf-8');
                })
                .then(function (initialSchema) {
                    return queryInterface.sequelize.query(initialSchema);
                })
        },

        down: function (queryInterface, Sequelize) {
            return Promise
                .resolve()
                .then(function() {
                    return fs.readFileSync(__dirname + '/../drop-initial-db.sql', 'utf-8');
                })
                .then(function (dropSql) {
                    return queryInterface.sequelize.query(dropSql);
                });
        }
    };

Remember you have to set:

记住你必须设置：

"dialectOptions": { "multipleStatements": true }

"dialectOptions": { "multipleStatements": true }

on database config.

关于数据库配置。

回答by Sergey Karasev

Use version. Version of the application depends on the version of the database. If the new version requires an update of a database, create migration for it.

使用版本。应用程序的版本取决于数据库的版本。如果新版本需要更新数据库，请为其创建迁移。

update: I decided to abandon the migration (KISS) and run script update_db (sync forse: false) when it is needed.

更新：我决定放弃迁移 ( KISS) 并在需要时运行脚本 update_db (sync forse: false)。

回答by kiddouk

A bit late, and after reading the documentation, you don't need to have that first migration that you are talking about. All you have to do is to call syncin order to create the tables.

有点晚了，在阅读文档后，您不需要进行您正在谈论的第一次迁移。您所要做的就是调用sync以创建表。

sequelize.sync()

sequelize.sync()

You can also run a simple model synchronization by doing something like:

您还可以通过执行以下操作来运行简单的模型同步：

Project.sync()but I think that sequelize.sync()is a more useful general case for your project (as long as you import the good models at start time).

Project.sync()但我认为这sequelize.sync()对您的项目来说是一个更有用的一般情况（只要您在开始时导入好的模型）。

(taken from http://sequelizejs.com/docs/latest/models#database-synchronization)

（取自http://sequelizejs.com/docs/latest/models#database-synchronization）

This will create all initialstructures. Afterwards, you will only have to create migrations in order to evolve your schemas.

这将创建所有初始结构。之后，您只需创建迁移即可发展您的架构。

hope it helps.

希望能帮助到你。

回答by Fernando Cordeiro

Sequelize can run arbitrary SQL asynchronously.

Sequelize 可以异步运行任意 SQL 。

What I would do is:

我会做的是：

• Generate a Migration (To use as first migration);
• Dump your database, something like: mysql_dump -uUSER -pPASS DBNAME > FILE.SQL
• Either paste the full dump as text (Dangerous) or load a File with the full dump in Node:
  • var baseSQL = "LOTS OF SQL and it's EVIL because you gotta put \ backslashes before line breakes and \"quotes\" and/or sum" + " one string for each line, or everything will break";
  • var baseSQL = fs.readFileSync('../seed/baseDump.sql');
• Run this dump on Sequelize Migration:

• 生成迁移（用作第一次迁移）；
• 转储您的数据库，例如： mysql_dump -uUSER -pPASS DBNAME > FILE.SQL
• 将完整转储粘贴为文本（危险）或在 Node 中加载包含完整转储的文件：
  • var baseSQL = "LOTS OF SQL and it's EVIL because you gotta put \ backslashes before line breakes and \"quotes\" and/or sum" + " one string for each line, or everything will break";
  • var baseSQL = fs.readFileSync('../seed/baseDump.sql');
• 在 Sequelize Migration 上运行此转储：

module.exports = {
  up: function (migration, DataTypes) {
    var baseSQL = "whatever" // I recommend loading a file
    migration.migrator.sequelize.query(baseSQL);
  }
}

That should take care of setting up the database, albeit the async thing may become a problem. If that happens, I'd look at a way to defer returning the upsequelize function until the async queryfunction is finished.

这应该负责设置数据库，尽管异步事情可能会成为一个问题。如果发生这种情况，我会考虑一种方法来推迟返回upsequelize 函数，直到异步query函数完成。

More about mysql_dump: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
More about Sequelize Migrations: http://sequelize.readthedocs.org/en/latest/docs/migrations/
More about Running SQL from within Sequelize Migration: https://github.com/sequelize/sequelize/issues/313

更多关于 mysql_dump：http
://dev.mysql.com/doc/refman/5.1/en/mysqldump.html 更多关于 Sequelize Migrations：http://sequelize.readthedocs.org/en/latest/docs/migrations/
更多关于从 Sequelize Migration 中运行 SQL：https: //github.com/sequelize/sequelize/issues/313

回答by Leonardo Rodriguez

Friend I had the same question and managed to understand how to use them.

朋友我有同样的问题，并设法了解如何使用它们。

I started without ORM sequelize therefore I already had a data model.
I had to generate the models automatically with sequelize-auto and generate their migrations with this file that you create https://gist.github.com/ahelord/a7a7d293695b71aadf04157f0f7dee64and put in sync ({Force: false})
This is in dev.I would have to version the model and the migrations and execute them every time I pull the code.

我开始时没有使用 ORM sequelize，因此我已经有了一个数据模型。
我必须使用 sequelize-auto 自动生成模型，并使用您创建的这个文件生成它们的迁移https://gist.github.com/ahelord/a7a7d293695b71aadf04157f0f7dee64并放入同步 ( {Force: false})
这是在开发中。我必须版本模型和迁移，并在每次我提取代码时执行它们。

In production the server is only upstairs so you only have to run migrations and in each commit manage as you will version the model without stopping the backend

在生产中，服务器仅在楼上，因此您只需运行迁移并在每次提交中进行管理，因为您将在不停止后端的情况下对模型进行版本控制

回答by TWilly

Here is my current workflow. I'm open to suggestions.

这是我目前的工作流程。我愿意接受建议。

1. Set sequelize to create tables that don't exist
2. Set sequelize to drop and re-create all tables in a blank database called _blank
3. Use a mysql tool to compare _blank and and sync changes using that tool. Still looking for an affordable tool that can do this on mac. MySql workbench looks like you can import a model from an existing schema, and then sync schema. Trying to figure out how to do this via command line to make it easy.

1. 设置 sequelize 创建不存在的表
2. 将 sequelize 设置为删除并重新创建名为 _blank 的空白数据库中的所有表
3. 使用 mysql 工具比较 _blank 并使用该工具同步更改。仍在寻找可以在 mac 上执行此操作的经济实惠的工具。MySql 工作台看起来您可以从现有架构导入模型，然后同步架构。试图弄清楚如何通过命令行执行此操作以使其变得容易。

That way you don't have to manually update the migrations table and have to worry about fat fingers, but you still get an ORM.

这样你就不必手动更新迁移表，也不必担心胖手指，但你仍然会得到一个 ORM。

回答by Vedran Maricevic.

There is even more simple way (avoiding Sequalize). Which goes like this:

还有更简单的方法（避免 Sequalize）。是这样的：

1. You type a command inside your project: npm run migrate:new

2. This creates 3 files. A js file, and two sql files named up and down

3. You put your SQL statement in those files, which is pure sql
4. Then you type: npm run migrate:upor npm run migrate:down

1. 你在你的项目中输入一个命令：npm run migrate:new

2. 这将创建 3 个文件。一个js文件，以及两个名为up和down的sql文件

3. 你把你的 SQL 语句放在那些文件中，这是纯 sql
4. 然后输入：npm run migrate:up或npm run migrate:down

For this to work, please have a look at the db-migratemodule.

为此，请查看db-migrate模块。

Once you get it setup (which is not difficult), changing your DB is really easy and saves a lot of time.

一旦你设置好了（这并不难），改变你的数据库真的很容易并且可以节省很多时间。