npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

sequelize-auto-null-fix

v0.8.4

Published

Automatically generate bare sequelize models from your database.

Downloads

4

Readme

Sequelize-Auto

Build Status Build status Code Climate Test Coverage

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]
-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
    --caseFile, --cf     Set case of file names: c|l|o|p|u
    --caseProp, --cp     Set case of property names: c|l|o|p|u
--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]
    --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:

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.

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);

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 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();

Resources

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.