Using DbSchema with Postgres, Sequelize, and Sequelize-Auto

README.md

Using DbSchema with Postgres & Sequelize

The road to using Postgres, Sequelize, Sequelize-Auto, and DbSchema have some bumps along the way. More specifically, these "bumps" have to do with automatic incrementing and audit fields. These are the steps I recommend when working with this combination.

Rule #1: Do not use Sync
Sequelize-Auto does not handle indexes. For this reason, any indexes created in DbSchema will be ignored. This is why we use DbSchema to work with database modifications. We only tweak enough of Sequelize's models to work with the columns. Since the models will not have indexes by default, we must never use Sequelize to push changes to the database.

The process...

1. Define Sequences
For an column needing the ID to be automatically incremented, do the following:

a. create the Sequence in your DbSchema project (eg: seq_user_id)
b. in "Add / Edit Column" associate the sequence to the column (options tab)
c. in "Add / Edit Column" set the default value to the nextval function (Data Type tab)

Note:
The nextval function uses the following format:

nextval('%SCHEMA_NAME%.%SEQUENCE_NAME%')

Example:
nextval('authentication.seq_session_id')

2. Define Audit Fields & Indexes
Use DbSchema to create your audit fields and indexes. Although Sequelize can automatically create these fields, Sequelize-Auto does not handle indexes.

3. Create Database
Manually create an empty database within Postgres.

4. Populate the Database
Using DbSchema, populate the DB by selecting Create or Upgrade Schema in Database.

5. Generate Models
Create your NodeJs project and then use sequelize-auto to generate your models:

sequelize-auto -o "./data/models" -d DB_NAME -s SCHEMA_NAME -h localhost -u postgres -p 5432 -x PASSWORD -e postgres

6. Tweak AutoIncrement in Models
Sequelize-Auto generates the models incorrectly. We must change this...

id: {
  type: DataTypes.BIGINT,
  allowNull: false,
  defaultValue: 'nextval(authentication.seq_user_id::regclass)',
  primaryKey: true
},

... to this ...

id: {
  type: DataTypes.BIGINT,
  autoIncrement: true,
  defaultValue: 'nextval(authentication.seq_user_id::regclass)',
  primaryKey: true
},

Note that we have done the following:

a. remove allowNull: false
b. add autoIncrement: true
c. comment defaultValue property

7. Correct Table Options
Sequelize-Auto fails to output the schema name or options. We must change this...

{
  tableName: 'user'
}

... to this ...

{
  tableName: 'user',
  schema: 'authentication',  // Use your schema name
  timestamps: true,
  paranoid: true,
  createdAt: '_c',           // Use your column name
  updatedAt: '_v',           // Use your column name
  deletedAt: '_d'            // Use your column name
}

So far, this is about it.