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]
-h, --host               IP/Hostname for the database.                [string]
-d, --database           Database name.                               [string]
-u, --user               Username for database.                       [string]
-x, --pass               Password for database. If specified without providing
                          a password, it will be requested interactively from
                          the terminal.
-p, --port               Port number for database (not for sqlite). Ex:
                          MySQL/MariaDB: 3306, Postgres: 5432, MSSQL: 1433
                                                                      [number]
-c, --config             Path to JSON file for Sequelize-Auto options and
                          Sequelize's constructor "options" flag object as
                          defined here:
                          https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor
                                                                      [string]
-o, --output             What directory to place the models.          [string]
-e, --dialect            The dialect/engine that you're using: postgres,
                          mysql, sqlite, mssql                         [string]
-a, --additional         Path to JSON file containing model options (for all
                          tables). See the options: https://sequelize.org/master/class/lib/model.js~Model.html#static-method-init
                                                                      [string]
    --indentation        Number of spaces to indent                   [number]
-t, --tables             Space-separated names of tables to import     [array]
-T, --skipTables         Space-separated names of tables to skip       [array]
--caseModel, --cm        Set case of model names: c|l|o|p|u
                          c = camelCase
                          l = lower_case
                          o = original (default)
                          p = PascalCase
                          u = UPPER_CASE
--caseProp, --cp         Set case of property names: c|l|o|p|u
--caseFile, --cf         Set case of file names: c|l|o|p|u|k
                          k = kebab-case
--noAlias                Avoid creating alias `as` property in relations
                                                                     [boolean]
--noInitModels           Prevent writing the init-models file        [boolean]
-n, --noWrite            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]
--useDefine              Use `sequelize.define` instead of `init` for es6|esm|ts
--singularize, --sg      Singularize model and file names from plural table
                          names                                      [boolean]

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:

1module.exports = function(sequelize, DataTypes) {
2  return sequelize.define('User', {
3    id: {
4      type: DataTypes.INTEGER,
5      allowNull: false,
6      primaryKey: true,
7      autoIncrement: true
8    },
9    username: {
10      type: DataTypes.STRING(20),
11      allowNull: true
12    },
13    aNumber: {
14      type: DataTypes.SMALLINT,
15      allowNull: true
16    },
17    dateAllowNullTrue: {
18      type: DataTypes.DATE,
19      allowNull: true
20    },
21    defaultValueBoolean: {
22      type: DataTypes.BOOLEAN,
23      allowNull: true,
24      defaultValue: true
25    }
26  }, {
27    tableName: 'User',
28  });
29};

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

1var DataTypes = require("sequelize").DataTypes;
2var _User = require("./User");
3var _Product = require("./Product");
4
5function initModels(sequelize) {
6  var User = _User(sequelize, DataTypes);
7  var Product = _Product(sequelize, DataTypes);
8
9  return {
10    User,
11    Product,
12  };
13}
14module.exports = { initModels };

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

1var initModels = require("./models/init-models");
2...
3var models = initModels(sequelize);
4
5models.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:

1var 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.

Note that you need TypeScript 4.x to compile the generated files.

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

Example model class, order.ts:

1import Sequelize, { DataTypes, Model, Optional } from 'sequelize';
2import type { Customer, CustomerId } from './customer';
3import type { OrderItem, OrderItemId } from './order_item';
4
5export interface OrderAttributes {
6  id: number;
7  orderDate: Date;
8  orderNumber?: string;
9  customerId: number;
10  totalAmount?: number;
11  status: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
12}
13
14export type OrderPk = "id";
15export type OrderId = Order[OrderPk];
16export type OrderCreationAttributes = Optional<OrderAttributes, OrderPk>;
17
18export class Order extends Model<OrderAttributes, OrderCreationAttributes> implements OrderAttributes {
19  id!: number;
20  orderDate!: Date;
21  orderNumber?: string;
22  customerId!: number;
23  totalAmount?: number;
24  status!: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
25
26  // Order belongsTo Customer via customerId
27  customer!: Customer;
28  getCustomer!: Sequelize.BelongsToGetAssociationMixin<Customer>;
29  setCustomer!: Sequelize.BelongsToSetAssociationMixin<Customer, CustomerId>;
30  createCustomer!: Sequelize.BelongsToCreateAssociationMixin<Customer>;
31  // Order hasMany OrderItem via orderId
32  orderItems!: OrderItem[];
33  getOrderItems!: Sequelize.HasManyGetAssociationsMixin<OrderItem>;
34  setOrderItems!: Sequelize.HasManySetAssociationsMixin<OrderItem, OrderItemId>;
35  addOrderItem!: Sequelize.HasManyAddAssociationMixin<OrderItem, OrderItemId>;
36  addOrderItems!: Sequelize.HasManyAddAssociationsMixin<OrderItem, OrderItemId>;
37  createOrderItem!: Sequelize.HasManyCreateAssociationMixin<OrderItem>;
38  removeOrderItem!: Sequelize.HasManyRemoveAssociationMixin<OrderItem, OrderItemId>;
39  removeOrderItems!: Sequelize.HasManyRemoveAssociationsMixin<OrderItem, OrderItemId>;
40  hasOrderItem!: Sequelize.HasManyHasAssociationMixin<OrderItem, OrderItemId>;
41  hasOrderItems!: Sequelize.HasManyHasAssociationsMixin<OrderItem, OrderItemId>;
42  countOrderItems!: Sequelize.HasManyCountAssociationsMixin;
43
44  static initModel(sequelize: Sequelize.Sequelize): typeof Order {
45    Order.init({
46    id: {
47      autoIncrement: true,
48      type: DataTypes.INTEGER,
49      allowNull: false,
50      primaryKey: true
51    },
52    orderDate: {
53      type: DataTypes.DATE,
54      allowNull: false,
55      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
56      field: 'OrderDate'
57    },
58    orderNumber: {
59      type: DataTypes.STRING(10),
60      allowNull: true,
61      field: 'OrderNumber'
62    },
63    customerId: {
64      type: DataTypes.INTEGER,
65      allowNull: false,
66      references: {
67        model: 'customer',
68        key: 'Id'
69      },
70      field: 'CustomerId'
71    },
72    totalAmount: {
73      type: DataTypes.DECIMAL(12,2),
74      allowNull: true,
75      defaultValue: 0.00,
76      field: 'TotalAmount'
77    },
78    status: {
79      type: DataTypes.ENUM('PROCESSING','SHIPPED','UNKNOWN'),
80      allowNull: false,
81      defaultValue: "UNKNOWN",
82      field: 'Status'
83    }
84  }, {
85    sequelize,
86    tableName: 'order',
87    timestamps: false,
88  });
89  return Order;
90  }
91}

Example init-models.ts:

1import { Sequelize } from "sequelize";
2import { Customer, CustomerAttributes, CustomerCreationAttributes } from "./customer";
3import { Order, OrderAttributes, OrderCreationAttributes } from "./order";
4import { OrderItem, OrderItemAttributes, OrderItemCreationAttributes } from "./order_item";
5import { Product, ProductAttributes, ProductCreationAttributes } from "./product";
6import { Supplier, SupplierAttributes, SupplierCreationAttributes } from "./supplier";
7
8export {
9  Customer, CustomerAttributes, CustomerCreationAttributes,
10  Order, OrderAttributes, OrderCreationAttributes,
11  OrderItem, OrderItemAttributes, OrderItemCreationAttributes,
12  Product, ProductAttributes, ProductCreationAttributes,
13  Supplier, SupplierAttributes, SupplierCreationAttributes,
14};
15
16export function initModels(sequelize: Sequelize) {
17  Customer.initModel(sequelize);
18  Order.initModel(sequelize);
19  OrderItem.initModel(sequelize);
20  Product.initModel(sequelize);
21  Supplier.initModel(sequelize);
22
23  Order.belongsTo(Customer, { as: "customer", foreignKey: "customerId"});
24  Customer.hasMany(Order, { as: "orders", foreignKey: "customerId"});
25  OrderItem.belongsTo(Order, { as: "order", foreignKey: "orderId"});
26  Order.hasMany(OrderItem, { as: "orderItems", foreignKey: "orderId"});
27  OrderItem.belongsTo(Product, { as: "product", foreignKey: "productId"});
28  Product.hasMany(OrderItem, { as: "orderItems", foreignKey: "productId"});
29  Product.belongsTo(Supplier, { as: "supplier", foreignKey: "supplierId"});
30  Supplier.hasMany(Product, { as: "products", foreignKey: "supplierId"});
31
32  return {
33    Customer: Customer,
34    OrderItem: OrderItem,
35    Order: Order,
36    Product: Product,
37    Supplier: Supplier,
38  };
39}

Model usage in a TypeScript program:

1// Order is the sequelize Model class
2// OrderAttributes is the interface defining the fields
3// OrderCreationAttributes is the interface defining the fields when creating a new record
4import { initModels, Order, OrderCreationAttributes } from "./models/init-models";
5
6// import models into sequelize instance
7initModels(this.sequelize);
8
9const myOrders = await Order.findAll({ where: { "customerId": cust.id }, include: ['customer'] });
10
11const attr: OrderCreationAttributes = {
12  customerId: cust.id,
13  orderDate: new Date(),
14  orderNumber: "ORD123",
15  totalAmount: 223.45
16};
17const 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

1const SequelizeAuto = require('sequelize-auto');
2const auto = new SequelizeAuto('database', 'user', 'pass');
3
4auto.run().then(data => {
5  console.log(data.tables);      // table and field list
6  console.log(data.foreignKeys); // table foreign key list
7  console.log(data.indexes);     // table indexes
8  console.log(data.hasTriggerTables); // tables that have triggers
9  console.log(data.relations);   // relationships between models
10  console.log(data.text)         // text of generated models
11});

With options:

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

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

1const SequelizeAuto = require('sequelize-auto');
2const Sequelize = require('sequelize');
3
4// const sequelize = new Sequelize('sqlite::memory:');
5const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');
6const options = { caseFile: 'l', caseModel: 'p', caseProp: 'c' };
7
8const auto = new SequelizeAuto(sequelize, null, null, options);
9auto.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.