README.md
# Sequelize-Auto
<!-- [](https://greenkeeper.io/) -->
[](https://travis-ci.org/sequelize/sequelize-auto) [](https://ci.appveyor.com/project/durango/sequelize-auto) [](https://codeclimate.com/github/sequelize/sequelize-auto) [](https://codeclimate.com/github/sequelize/sequelize-auto/coverage)
Automatically generate models for [SequelizeJS](https://github.com/sequelize/sequelize) 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/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]`
## 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](http://docs.sequelizejs.com/en/latest/docs/models-definition/#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](https://sequelize.org/master/manual/typescript.html)
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<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`:
```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](https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor) 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](https://sequelize.org/master/manual/getting-started.html#connecting-to-a-database),
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](https://github.com/sequelize/sequelize-auto/blob/master/CHANGELOG.md)
## 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](https://github.com/sequelize/sequelize-auto/tree/master/sample) directory which has an example including database scripts, export script, and a sample app.