title: Sequelize.sync: Create indexes when table does not exist
body: <p>According to <a href="https://sequelize.org/v5/manual/models-definition.html#database-synchronization" rel="nofollow noreferrer">documentation</a> <code>sequelize.sync()</code> without <code>{force: true}</code> or <code>{alter:true}</code> is supposed to ignore already existing tables and create/sync only new ones. But there are at least two use cases when existing tables are not fully ignored and there are errors introduced.</p>
  <p>My setup:</p>
  <ol>
  <li><code>sequelize.sync()</code> is used as a pre-migration step to create tables that don't exist in the schema</li>
  <li><code>sequelize.migrate()</code> is used to alter any existing tables.</li>
  </ol>
  <p>Note: Sequelize models are treated as a single source of truth for reflecting database schema. They are always updated to reflect all the indexes/fields existing in the database.</p>
  <p><strong>Steps to reproduce</strong></p>
  <p><strong>Step 1</strong>: Create <code>User</code> model with two fields <code>name</code> and <code>email</code>. <code>Email</code> has a unique index</p>
  <pre><code>const users = sequelizeClient.define('users', {
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    }, {
      indexes: [
        {
          unique: true,
          fields: ['email'],
        },
      ],
    });
  </code></pre>
  <p>There is no migration, so the table is expected to be created using <code>sequelize.sync()</code>.
  Everything works as expected. Here are the generated SQL scripts.</p>
  <pre><code>Executing (default): CREATE TABLE IF NOT EXISTS &quot;users&quot; (&quot;id&quot;  SERIAL , &quot;name&quot; VARCHAR(255) NOT NULL, &quot;email&quot; VARCHAR(255) NOT NULL, PRIMARY KEY (&quot;id&quot;));
  Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
  Executing (default): CREATE UNIQUE INDEX &quot;users_email&quot; ON &quot;users&quot; (&quot;email&quot;)
  </code></pre>
  <p><strong>Step 2</strong> Add a new field <code>phonenumber</code> to the Users table and add a unique index. Add a migration that will alter the table structure and create the index. The <code>sequelize.sync()</code> is expected to ignore this table but migration is never executed as <code>sequelize.sync()</code> throws the following error.</p>
  <pre><code>
  Executing (default): CREATE TABLE IF NOT EXISTS &quot;users&quot; (&quot;id&quot;  SERIAL , &quot;name&quot; VARCHAR(255) NOT NULL, &quot;email&quot; VARCHAR(255) NOT NULL, PRIMARY KEY (&quot;id&quot;));
  Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
  Executing (default): CREATE UNIQUE INDEX &quot;users_phonenumber&quot; ON &quot;users&quot; (&quot;phonenumber&quot;)
  {&quot;_bitField&quot;:18087936,&quot;_fulfillmentHandler0&quot;:{&quot;name&quot;:&quot;SequelizeDatabaseError&quot;,&quot;parent&quot;:{&quot;name&quot;:&quot;error&quot;,&quot;length&quot;:101,&quot;severity&quot;:&quot;ERROR&quot;,&quot;code&quot;:&quot;42703&quot;,&quot;file&quot;:&quot;indexcmds.c&quot;,&quot;line&quot;:&quot;1083&quot;,&quot;routine&quot;:&quot;ComputeIndexAttrs&quot;,&quot;sql&quot;:&quot;CREATE UNIQUE INDEX \&quot;users_phonenumber\&quot; ON \&quot;users\&quot; (\&quot;phonenumber\&quot;)&quot;},&quot;original&quot;:{&quot;name&quot;:&quot;error&quot;,&quot;length&quot;:101,&quot;severity&quot;:&quot;ERROR&quot;,&quot;code&quot;:&quot;42703&quot;,&quot;file&quot;:&quot;indexcmds.c&quot;,&quot;line&quot;:&quot;1083&quot;,&quot;routine&quot;:&quot;ComputeIndexAttrs&quot;,&quot;sql&quot;:&quot;CREATE UNIQUE INDEX \&quot;users_phonenumber\&quot; ON \&quot;users\&quot; (\&quot;phonenumber\&quot;)&quot;},&quot;sql&quot;:&quot;CREATE UNIQUE INDEX \&quot;users_phonenumber\&quot; ON \&quot;users\&quot; (\&quot;phonenumber\&quot;)&quot;},&quot;_trace&quot;:{&quot;_promisesCreated&quot;:0,&quot;_length&quot;:1},&quot;level&quot;:&quot;error&quot;,&quot;message&quot;:&quot;Unhandled Rejection at: Promise &quot;}
  </code></pre>
  <p>Here's the final model</p>
  <pre><code>const users = sequelizeClient.define('users', {
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      phoneNumber: { // new field
        type: DataTypes.STRING,
        allowNull: false,
      },
  
    }, {
      indexes: [
        {
          unique: true,
          fields: ['email'],
        },
        { // new index
          unique: true,
          fields: ['phoneNumber'],
        },
      ],
    });
  </code></pre>
  <p>Can someone suggest a workaround here so the index creation happens only if the table doesn't exist</p>
  <hr />
  <p>Another use case is when you add a new field with a comment</p>
  <pre><code>    fieldWithComment: {
        type: DataTypes.STRING,
        comment: 'my comment goes here',
      },
  </code></pre>
  <p>Generated SQL which obviously throws an error as the new column does not exist yet.</p>
  <pre><code>CREATE TABLE IF NOT EXISTS &quot;users&quot; (
      &quot;id&quot;   SERIAL, 
      &quot;name&quot; VARCHAR(255) NOT NULL, 
      &quot;email&quot; VARCHAR(255) NOT NULL, 
      &quot;phonenumber&quot; VARCHAR(255) NOT NULL, 
      &quot;fieldWithComment&quot; VARCHAR(255) , PRIMARY KEY (&quot;id&quot;)); 
          COMMENT ON COLUMN &quot;users&quot;.&quot;fieldWithComment&quot; IS 'my comment goes here';
  </code></pre>