Sequelize-Auto

Automatically generate models for SequelizeJS via the command line.

Install

npm install sequelize-auto

Prerequisites

You will need to install sequelize; it's no longer installed by sequelize-auto.

You will need to install the correct dialect binding before using sequelize-auto.

Dialect | Install ---|--- MySQL/MariaDB | npm install sequelize mysql2 Postgres | npm install sequelize pg pg-hstore Sqlite | npm install sequelize sqlite3 MSSQL | npm install sequelize tedious

Usage

sequelize-auto -h <host> -d <database> -u <user> -x [password] -p [port]  --dialect [dialect] -c [/path/to/config] -o [/path/to/models] -t [tableName]

Options:
  --help             Show help                                         [boolean]
  --version          Show version number                               [boolean]
  -c, --config       Path to JSON file for Sequelize's constructor "options"
                     flag object as defined here:
                     https://sequelize.org/v5/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor
  -a, --additional   Path to JSON file containing model definitions (for all
                     tables) which are to be defined within a model's
                     configuration parameter. For more info: https://sequelize.org/v5/manual/models-definition.html#configuration
  -h, --host         IP/Hostname for the database.           [string] [required]
  -d, --database     Database name.                          [string] [required]
  -u, --user         Username for database.                             [string]
  -x, --pass         Password for database.                             [string]
  -p, --port         Port number for database (not for sqlite). Ex:
                     MySQL/MariaDB: 3306, Postgres: 5432, MSSQL: 1433   [number]
  -o, --output       What directory to place the models.                [string]
  -e, --dialect      The dialect/engine that you're using: postgres, mysql,
                     sqlite, mssql                                      [string]
  -t, --tables       Comma-separated names of tables to import          [string]
  -T, --skip-tables  Comma-separated names of tables to skip            [string]
  --cm, --caseModel  Set case of model names: c|l|o|p|u
                      c = camelCase
                      l = lower_case
                      o = original (default)
                      p = PascalCase
                      u = UPPER_CASE
  --cf, --caseFile   Set case of file names: c|l|o|p|u
  --cp, --caseProp   Set case of property names: c|l|o|p|u
  -n, --no-write     Prevent writing the models to disk.               [boolean]
  -s, --schema       Database schema from which to retrieve tables      [string]
  -v, --views        Include database views in generated models        [boolean]
  -l, --lang         Language for Model output: es5|es6|esm|ts
                      es5 = ES5 CJS modules (default)
                      es6 = ES6 CJS modules
                      esm = ES6 ESM modules
                      ts = TypeScript                                    [string]
  --sg, --singularize Singularize model and file names from plural table names

On Windows, provide the path to sequelize-auto: node_modules\.bin\sequelize-auto [args]

Example

sequelize-auto -o "./models" -d sequelize_auto_test -h localhost -u my_username -p 5432 -x my_password -e postgres

Produces a file/files such as ./models/User.js which looks like:

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('User', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    username: {
      type: DataTypes.STRING(20),
      allowNull: true
    },
    aNumber: {
      type: DataTypes.SMALLINT,
      allowNull: true
    },
    dateAllowNullTrue: {
      type: DataTypes.DATE,
      allowNull: true
    },
    defaultValueBoolean: {
      type: DataTypes.BOOLEAN,
      allowNull: true,
      defaultValue: true
    }
  }, {
    tableName: 'User',
  });
};

Sequelize-auto also generates an initialization file, ./models/init-models.js, which contains the code to load each model definition into Sequelize:

var DataTypes = require("sequelize").DataTypes;
var _User = require("./User");
var _Product = require("./Product");

function initModels(sequelize) {
  var User = _User(sequelize, DataTypes);
  var Product = _Product(sequelize, DataTypes);

  return {
    User,
    Product,
  };
}
module.exports = { initModels };

This makes it easy to import all your models into Sequelize by calling initModels(sequelize).

var initModels = require("./models/init-models");
...
var models = initModels(sequelize);

models.User.findAll({ where: { username: "tony" }}).then(...);

Alternatively, you can Sequelize.import each model (for Sequelize versions < 6), or require each file and call the returned function:

var User = require('path/to/user')(sequelize, DataTypes);

ES6

You can use the -l es6 option to create the model definition files as ES6 classes, or -l esm option to create ES6 modules. Then you would require or import the classes and call the init(sequelize, DataTypes) method on each class.

TypeScript

Add -l ts to cli options or lang: 'ts' to programmatic options. This will generate a TypeScript class in each model file, and an init-model.ts file to import and initialize all the classes.

The TypeScript model classes are created as described in the Sequelize manual

Example model class, order.ts:

import Sequelize, { DataTypes, Model, Optional } from 'sequelize';

export interface OrderAttributes {
  id?: number;
  orderDate?: Date;
  orderNumber?: string;
  customerId?: number;
  totalAmount?: number;
}

export type OrderPk = "id";
export type OrderId = Order[OrderPk];
export type OrderCreationAttributes = Optional<OrderAttributes, OrderPk>;

export class Order extends Model<OrderAttributes, OrderCreationAttributes> implements OrderAttributes {
  id?: number;
  orderDate?: Date;
  orderNumber?: string;
  customerId?: number;
  totalAmount?: number;

  // Order hasMany OrderItem
  getOrderItems!: Sequelize.HasManyGetAssociationsMixin<OrderItem>;
  setOrderItems!: Sequelize.HasManySetAssociationsMixin<OrderItem, OrderItemId>;
  addOrderItem!: Sequelize.HasManyAddAssociationsMixin<OrderItem, OrderItemId>;
  removeOrderItem!: Sequelize.HasManyRemoveAssociationsMixin<OrderItem, OrderItemId>;
  hasOrderItem!: Sequelize.HasManyHasAssociationsMixin<OrderItem, OrderItemId>;
  countOrderItems!: Sequelize.HasManyCountAssociationsMixin;
  // Order belongsTo Customer
  getCustomer!: Sequelize.BelongsToGetAssociationMixin<Customer>;
  setCustomer!: Sequelize.BelongsToSetAssociationMixin<Customer, CustomerId>;
  createCustomer!: Sequelize.BelongsToCreateAssociationMixin<Customer>;

  static initModel(sequelize: Sequelize) {
    Order.init({
    id: {
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: true,
      primaryKey: true,
      field: 'Id'
    },
    orderDate: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      unique: true,
      field: 'OrderDate'
    },
    orderNumber: {
      type: DataTypes.STRING(10),
      allowNull: true,
      unique: true,
      field: 'OrderNumber'
    },
    customerId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Customer',
        key: 'Id'
      },
      unique: true,
      field: 'CustomerId'
    },
    totalAmount: {
      type: DataTypes.DECIMAL(19,4),
      allowNull: true,
      defaultValue: 0,
      field: 'TotalAmount'
    }
  }, {
    sequelize,
    tableName: 'Order',
    timestamps: false
    });
  return Order;
  }
}

Example init-models.ts:

import { Sequelize } from "sequelize";
import { Customer, CustomerAttributes, CustomerCreationAttributes } from "./customer";
import { Order, OrderAttributes, OrderCreationAttributes } from "./order";
import { OrderItem, OrderItemAttributes, OrderItemCreationAttributes } from "./order_item";
import { Product, ProductAttributes, ProductCreationAttributes } from "./product";
import { Supplier, SupplierAttributes, SupplierCreationAttributes } from "./supplier";

export {
  Customer, CustomerAttributes, CustomerCreationAttributes,
  Order, OrderAttributes, OrderCreationAttributes,
  OrderItem, OrderItemAttributes, OrderItemCreationAttributes,
  Product, ProductAttributes, ProductCreationAttributes,
  Supplier, SupplierAttributes, SupplierCreationAttributes,
};

export function initModels(sequelize: Sequelize) {
  Customer.initModel(sequelize);
  Order.initModel(sequelize);
  OrderItem.initModel(sequelize);
  Product.initModel(sequelize);
  Supplier.initModel(sequelize);

  Order.belongsTo(Customer, { foreignKey: "id"});
  Customer.hasMany(Order, { foreignKey: "customerId"});
  OrderItem.belongsTo(Product, { foreignKey: "id"});
  Product.hasMany(OrderItem, { foreignKey: "productId"});
  OrderItem.belongsTo(Order, { foreignKey: "id"});
  Order.hasMany(OrderItem, { foreignKey: "orderId"});
  Product.belongsTo(Supplier, { foreignKey: "id"});
  Supplier.hasMany(Product, { foreignKey: "supplierId"});

  return {
    Customer,
    Order,
    OrderItem,
    Product,
    Supplier,
  };
}

Model usage in a TypeScript program:

// Order is the sequelize Model class
// OrderAttributes is the interface defining the fields
// OrderCreationAttributes is the interface defining the fields when creating a new record
import { initModels, Order, OrderCreationAttributes } from "./models/init-models";

// import models into sequelize instance
initModels(this.sequelize);

const myOrders = await Order.findAll({ where: { "customerId": cust.id } });

const attr: OrderCreationAttributes = {
  customerId: cust.id,
  orderDate: new Date(),
  orderNumber: "ORD123",
  totalAmount: 223.45
};
const newOrder = await Order.create(attr);

Configuration options

For the -c, --config option, various JSON/configuration parameters are defined by Sequelize's options flag within the constructor. See the Sequelize docs for more info.

Programmatic API

const SequelizeAuto = require('sequelize-auto');
const auto = new SequelizeAuto('database', 'user', 'pass');

auto.run().then(data => {
  console.log(data.tables);      // table list
  console.log(data.foreignKeys); // foreign key list
  console.log(data.text)         // text of generated files
});

With options:

const auto = new SequelizeAuto('database', 'user', 'pass', {
    host: 'localhost',
    dialect: 'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql',
    directory: './models', // where to write files
    port: 'port',
    caseModel: 'c', // convert snake_case column names to camelCase field names: user_id -> userId
    caseFile: 'c', // file names created for each model use camelCase.js not snake_case.js
    singularize: true, // convert plural table names to singular model names
    additional: {
        timestamps: false
        // ...options added to each model
    },
    tables: ['table1', 'table2', 'myschema.table3'] // use all tables, if omitted
    //...
})

Or you can create the sequelize instance first, using a connection string, and then pass it to SequelizeAuto:

const SequelizeAuto = require('sequelize-auto');
const Sequelize = require('sequelize');

// const sequelize = new Sequelize('sqlite::memory:');
const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname');
const options = { caseFile: 'l', caseModel: 'p', caseProp: 'c' };

const auto = new SequelizeAuto(sequelize, null, null, options);
auto.run();

Resources

• Changelog

Testing

To set up:

1. Create an empty database called sequelize_auto_test on your database server (sqlite excepted)

2. Create a .env file from sample.env and set your username/password/port etc. The env is read by test/config.js

3. Build the TypeScript from the src directory into the lib directory:

   npm run build

Then run one of the test commands below:

# mysql only
npm run test-mysql

# postgres only
npm run test-postgres

# mssql  only
npm run test-mssql

# sqlite only
npm run test-sqlite

Also see the sample directory which has an example including database scripts, export script, and a sample app.