Automatically generate models for SequelizeJS via the command line.
npm install sequelize-auto
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 |
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/api/v6/class/src/sequelize.js~sequelize#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/api/v6/class/src/model.js~model#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]
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);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.
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:
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<OrderAttributes, OrderPk>;
export class Order extends Model<OrderAttributes, OrderCreationAttributes> 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<Customer>;
setCustomer!: Sequelize.BelongsToSetAssociationMixin<Customer, CustomerId>;
createCustomer!: Sequelize.BelongsToCreateAssociationMixin<Customer>;
// Order hasMany OrderItem via orderId
orderItems!: OrderItem[];
getOrderItems!: Sequelize.HasManyGetAssociationsMixin<OrderItem>;
setOrderItems!: Sequelize.HasManySetAssociationsMixin<OrderItem, OrderItemId>;
addOrderItem!: Sequelize.HasManyAddAssociationMixin<OrderItem, OrderItemId>;
addOrderItems!: Sequelize.HasManyAddAssociationsMixin<OrderItem, OrderItemId>;
createOrderItem!: Sequelize.HasManyCreateAssociationMixin<OrderItem>;
removeOrderItem!: Sequelize.HasManyRemoveAssociationMixin<OrderItem, OrderItemId>;
removeOrderItems!: Sequelize.HasManyRemoveAssociationsMixin<OrderItem, OrderItemId>;
hasOrderItem!: Sequelize.HasManyHasAssociationMixin<OrderItem, OrderItemId>;
hasOrderItems!: Sequelize.HasManyHasAssociationsMixin<OrderItem, OrderItemId>;
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:
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:
// 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);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.
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:
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();To set up:
-
Create an empty database called
sequelize_auto_teston your database server (sqlite excepted) -
Create a
.envfile fromsample.envand set your username/password/port etc. The env is read bytest/config.js -
Build the TypeScript from the
srcdirectory into thelibdirectory: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.
