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 -d -u -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:

`js
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:

`js
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).

`js
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:

`js
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.

> 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:

`js
import Sequelize, { DataTypes, Model, Optional } from 'sequelize';
import type { Customer, CustomerId } from './customer';
import type { OrderItem, OrderItemId } from './order_item';

export interface OrderAttributes {
id: number;
orderDate: Date;
orderNumber?: string;
customerId: number;
totalAmount?: number;
status: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
}

export type OrderPk = "id";
export type OrderId = Order[OrderPk];
export type OrderCreationAttributes = Optional;

export class Order extends Model implements OrderAttributes {
id!: number;
orderDate!: Date;
orderNumber?: string;
customerId!: number;
totalAmount?: number;
status!: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';

// Order belongsTo Customer via customerId
customer!: Customer;
getCustomer!: Sequelize.BelongsToGetAssociationMixin;
setCustomer!: Sequelize.BelongsToSetAssociationMixin;
createCustomer!: Sequelize.BelongsToCreateAssociationMixin;
// Order hasMany OrderItem via orderId
orderItems!: OrderItem[];
getOrderItems!: Sequelize.HasManyGetAssociationsMixin;
setOrderItems!: Sequelize.HasManySetAssociationsMixin;
addOrderItem!: Sequelize.HasManyAddAssociationMixin;
addOrderItems!: Sequelize.HasManyAddAssociationsMixin;
createOrderItem!: Sequelize.HasManyCreateAssociationMixin;
removeOrderItem!: Sequelize.HasManyRemoveAssociationMixin;
removeOrderItems!: Sequelize.HasManyRemoveAssociationsMixin;
hasOrderItem!: Sequelize.HasManyHasAssociationMixin;
hasOrderItems!: Sequelize.HasManyHasAssociationsMixin;
countOrderItems!: Sequelize.HasManyCountAssociationsMixin;

static initModel(sequelize: Sequelize.Sequelize): typeof Order {
Order.init({
id: {
autoIncrement: true,
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true
},
orderDate: {
type: DataTypes.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
field: 'OrderDate'
},
orderNumber: {
type: DataTypes.STRING(10),
allowNull: true,
field: 'OrderNumber'
},
customerId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: 'customer',
key: 'Id'
},
field: 'CustomerId'
},
totalAmount: {
type: DataTypes.DECIMAL(12,2),
allowNull: true,
defaultValue: 0.00,
field: 'TotalAmount'
},
status: {
type: DataTypes.ENUM('PROCESSING','SHIPPED','UNKNOWN'),
allowNull: false,
defaultValue: "UNKNOWN",
field: 'Status'
}
}, {
sequelize,
tableName: 'order',
timestamps: false,
});
return Order;
}
}
`

Example init-models.ts:

`js
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, { as: "customer", foreignKey: "customerId"});
Customer.hasMany(Order, { as: "orders", foreignKey: "customerId"});
OrderItem.belongsTo(Order, { as: "order", foreignKey: "orderId"});
Order.hasMany(OrderItem, { as: "orderItems", foreignKey: "orderId"});
OrderItem.belongsTo(Product, { as: "product", foreignKey: "productId"});
Product.hasMany(OrderItem, { as: "orderItems", foreignKey: "productId"});
Product.belongsTo(Supplier, { as: "supplier", foreignKey: "supplierId"});
Supplier.hasMany(Product, { as: "products", foreignKey: "supplierId"});

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

Model usage in a TypeScript program:

`js
// 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 }, include: ['customer'] });

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

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

auto.run().then(data => {
console.log(data.tables); // table and field list
console.log(data.foreignKeys); // table foreign key list
console.log(data.indexes); // table indexes
console.log(data.hasTriggerTables); // tables that have triggers
console.log(data.relations); // relationships between models
console.log(data.text) // text of generated models
});
`

With options:

`js
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:
`js
const SequelizeAuto = require('sequelize-auto');
const Sequelize = require('sequelize');

// const sequelize = new Sequelize('sqlite::memory:');
const sequelize = new Sequelize('postgres://user:pass@example.com: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.