sequelize-pg-generator v0.9.4

DEPRECIATION NOTICE: Use pg-generator

Project is renamed as pg-generator which

• is flexible and easier to customize, (uses nunjucks templates)

• generates more than sequelize, (provides example templates for sequelize, objection, schwifty etc.)

Links of pg-generator:

• Web: http://www.pg-generator.com
• pg-generator's sequelize page: http://www.pg-generator.com/builtin-templates/sequelize
• npm: https://www.npmjs.com/package/pg-generator
• github: https://github.com/ozum/pg-generator

Other Languages

Türkçe: Turkish Documentation is here

Old module and its documentation is preserved here for reference purposes.

Description

This module is for auto generating Sequelize model from PostgreSQL databases. It reverse engineers your database and generates separate model files for each table. Default configuration parameters are carefully selected and they are sane to use. However it is possible to change most of the behaviours via configuration. This document is based on default configuration. Configuration parameters and default values are written as (Config parameter:value) where appropriate.

Usage

Step 1: Install globally via npm -g

This will install module and CLI command

$ npm install -g sequelize-pg-generator

Step 2: Generate model files

Open terminal, go to your app.js root and create your models automatically into 'model' directory.

$ cd path/to/my/node-app.js
$ spgen -d my_database -u my_user -p my_password

Step 3: Use it in your node.js app

Use Sequelize models provided by auto generated files in your application. See examples below.

var orm = require('./model');
orm.setup('my_database', 'my_user', 'my_password', {
    host: '127.0.0.1',
    logging: false,
    native: false
});
var sequelize = orm.sequelize;
var contact = orm.model('public.contact'); // Can be configured without schema.

Windows Users

Some tips to install on windows. This module uses pg. If you like to use pg-native, below are some tips for windows users for a successful install:

• Python 2 should be installed. As of this writing it is not compatible with Python 3.
• You should add Python to the path and set PYTHONPATH via environment variables.
• PostgreSQL pg_config and libpq.dll must be on path. Usually adding bin and lib folders is ok. (For eaxmple: C:\Program Files\PostgreSQL\9.3\bin C:\Program Files\PostgreSQL\9.3\lib)
• Visual Studio Build Tools (C:\Program Files (x86)\MSBuild) should be installed. Installed automatically after VS 2012. If during install npm asks for different version of Visual Studio you can set it from command line during installation:

  npm install -g sequelize-pg-generator --msvs_version=2013

CLI Options

spgen [options]
-h, --host [host]           IP address or host name of the database server
    --port [port]           Port of database server to connect
-d, --database [database]   Database name
-u, --user [user]           Username to connect to database
-p, --password [password]   Password to connect to database
-s, --schema [schema]       Comma separated names of the database schemas
-o, --output [output]       Output folder
-c, --config [config]       Path of the configuration file
-t, --templateName          Use builtin template folder with given name
    --nolog                 No log output
    --resetConfig           Reset configuration. (Side-step. Not for production.)
    --throwError            Instead of logging errors to console, throws error.

• Fully documented. (JSDoc HTML files are under doc directory),
• Tested,
• No Dependencies on Generated Files,
• Multi schema support,
• One to many relation support (hasMany and belongsTo),
• Many to many relation support (hasMany through and belongsToMany),
• Inter-schema relation support. (i.e. public.account table to other_schema.cutomer table),
• Highly configurable,
• Fully customizable,
• CLI support,
• Smart naming of models and relations,
• Very easy to override auto generated files,
• Exclude tables,
• Debug,
• Table Specific Configuration,
• Validates and prevents naming clash.

WARNING: belongsToMany

For many to many relations Sequelize version 2.0 RC3 and older does not support belongsToMany relation. After this version hasMany through relations are deprecated. Behaviour on this subject can be adjusted via configuration. (Config: generate.hasManyThrough:false and generate.belongsToMany:true)

Features

No Dependencies On Generated Files

Generated files have no dependencies besides core modules and Sequelize.

Multi Schema Support

Supports multi PostgreSQL schemas. It is possible to have other schemas than public. User can select which schemas to reverse engineer via CLI or config. If more than one schema is included, models may be prefixed with schema names (Config: generate.useSchemaName: true, database.schema: "public") to prevent tables with same name in different schemas try to have same model name.

contact = orm.model('public.contact'); // Returns sequelize model for contact table.

No Schema Prefix

User can configure not to prefix model names with schema

// In config
{...
    generate{
        useSchemaName: false
    }
}

contact = orm.model('contact'); // Returns sequelize model for contact table.

One To Many Relation Support

This module automatically detects one to many relations and generates model.hasMany and model.belongsTo sequelize relations.

Many To Many Relation Support

If two tables are joined via a join table this module detects it automatically and generates many to many sequelize relations. If a table has more than one foreign key, then it is considered many to many relation join table.

                  hasMany              hasMany
TABLE:   product --------< line_item >--------- cart
COLUMNS: id                cart_id (FK)         id
         name              product_id (FK)      customer_id (FK)
         color             quantity

This module generates belongsToMany relation and hasMany relation with through option. As of this writing master branch of Sequelize deprecated hasMany through relations. According to version of Sequelize you use, it should be adjusted via config of this module.

Inter-Schema Relation Support

Detects relations between tables in different schemas. For example relations between public.account table and other_schema.customer table.

Highly configurable

This module uses config module. It is also possible to point a custom configuration file via CLI. See configuration parameters below in this document.

Fully Customizable

This module uses consolidate compatible templates to generate model files. It uses Swig by default. User can use his/her custom templates without altering original one by pointing (Config: template.folder and template.engine:'swig') config values. Looking default templates in template folder of this module is highly recommended.

There should at least be three files in custom template folder: index.ext Default template file. ext is whatever extension is used for your template engine. index.js This file is copied with generated files. It's purpose is use generated files utils.js This file is copied with generated files. Contains helper functions.

CLI Support

If this module is installed as suggested globally with npm -g then spgen command would be available system wide to generate model files.

Easily Override Relationship Aliases and Smart Names

If auto generated relationship names and aliases are not so smart for you, it is very easy to override aliases. After first use, a file called alias.json is generated in output folder. Every alias name generated is listed in this file. You can override any names/aliases. Future generated files will use overridden names from this file. If you are OK with some names, leave it as null.

Smart Naming of Models and Relations

sequelize-pg-generator uses table names or schema.table names for model naming. For relations it uses foreign key names and relation names from your database. (You are naming your relations in database meaningfully right?) Both camel case (tableName) or untouched names (table_name) methods can be used via configuration. Naming conventions are based on Sequelize module suggestions and generated explicitly with 'as' parameter.

                  product_cart_line_items              cart_cart_line_items
TABLE:   product -------------------------< line_item >--------------------- cart
COLUMNS: id                                 cart_id (FK)                     id
         name                               product (FK)                     customer_id (FK)
         color                              quantity

NOTE: Beware line_item.cart_id has id suffix but line_item.product hasn't. This inconsistency is made purposefully for the sake of this example.

Type of object          Naming Rule
--------------          -----------
Model                   tableName or schema.tableName
hasMany                 Plural of the relation name in database. Table name from beginning can be stripped.
                        (Config: generate.stripFirstTableFromHasMany:true)
belongsTo               Singular of foreign key. If key name ends with _id it will be stripped. Otherwise
                        'related' is added at the beginning to prevent it gets clash with column name.
                        (Config: generate.prefixForBelongsTo:'related')
belongsToMany           Plural of the join table name + foreign key which refers other table in join table.
hasMany({through:..})   Plural of the join table name + foreign key which refers other table in join table. (DEPRECATED in Sequelize)

For the example structure:

Relation                as                                  Details
--------                --                                  -------
product.hasMany         as:'cartLineItems'                  (Plural) Table name 'product' is stripped from the beginning
                                                            of relation name 'product_cart_line_items'
product.belongsToMany   as:'cartLineItemCarts'              (Plural) _id suffix is stripped from, relation name (table name striiped)
                                                            added to foreign key name 'cart_id'
product.hasMany Through as:'cartLineItemCarts'              (Plural) _id suffix is stripped from, relation name (table name striiped)
                                                            added to foreign key name 'cart_id'
cart.hasMany            as:'cartLineItems'                  (Plural) Table name 'cart' is stripped from the beginning of
                                                            relation name 'cart_cart_line_items'
cart.belongsToMany      as:'relatedCartLineItemProducts'    (Plural) No _id suffix. 'related' and relation name (table name striiped)
                                                            are added as prefix.
cart.hasMany Through    as:'relatedcartLineItemProducts'    (Plural) No _id suffix. 'related' and relation name (table name striiped)
                                                            are added as prefix.
lineItem.belongsTo      as:'relatedProduct'                 (Singular) No _id suffix. 'related' is added as prefix.
lineItem.belongsTo      as:'cart'                           (Singular) _id suffix is stripped from foreign key name
                                                            'cart_id'.

Of course as all attributes, you can modify generated files in a non-destructive way as explained below.

Very Easy to Override Auto Generated Files

By default auto generated files are located path/to/model/definition-files directory. Also there is 'definition-files-custom' directory. Users can create files with same names as auto generated files to override its attributes. There is also utils module generated to make modifications easier.

Those modifications are non destructive, because they override generated file in another file by inheriting it and default index.js file uses inherited files if it exists. Please bear in mind, those modifications occur before sequelize instances are generated.

For example for cart table 'definition-files/cart.js' is generated. User can create 'definition-files-custom/cart.js' and override necessary parts like example below. For all attributes you can look inside auto generated files.

"use strict";
var orm     = require('../index.js'),
    model   = require('../definition-files/public_cart.js'),
    util    = require('../utils.js')(model),
    Seq     = orm.Sequelize();

module.exports = model;

util.getAttribute('id').validate = {... Some Sequelize Validations}; // Add Sequelize validation.
util.getRelation('relatedProducts').details.as = 'soldItems';        // Don't like default relation name? Change it.
util.renameAttribute('customerId', 'clientId');                      // Change name of the attribute.

Exclude Tables

It is possible to exclude some table from auto generation. (Config generate.skipTable:[]) array is used to define excluded tables. sequelize-pg-generator skips those tables and relations from and to those tables.

Debug

When required and executed first time from your app, default index.js file creates a file called debug.js in the model directory. This file can be examined what type of code is used by index.js. It is same code that would be used if there is no index.js file exists. However if this type of static file is used, it is harder to allow modifications in a non-destructive way.

Validates and Prevents Naming Clash

sequelize-pg-generator prevents naming clas by validating all relation names if same name/alias exists on the same table.

Table Specific Configuration

Sometimes for some tables it is needed to have different rules then other tables have. In such situations configuration file allows table level overrides. All 'generate' and 'tableOptions' config parameters can be overridden with 'generateOverride' and 'tableOptionsOverride'.

Below is an example for contact table have specific configuration overrides.

"generate": {
    "columnDescription": true,
    "tableDescription": true,
    ...
},
"generateOverride": {
    "contact": {
        "tableDescription": false
    }
},
"tableOptions": {
    "timestamps": false,
    "camelCase": true,
    "paranoid": false,
    ...
},
"tableOptionsOverride": {
    "contact": {
        "paranoid": true
    }
}
...

Configuration

Configuration parameters and default values are described below. Configuration is enclosed in "sequelize-pg-generator" key, because you may want to combine sequelize-pg-generator configuration with your main application configuration. This way generator's configuration does not clash with yours. node-config allows this.

Default Configuration Settings

Default configuration settings are listed below:

module.exports = {
    "sequelize-pg-generator": {
        "database": {
            "host": "127.0.0.1",
            "port": 5432,
            "user": "user",
            "password": "password",
            "database": "",
            "schema": ["public"]
        },
        "template": {
            "engine": "swig",
            "extension": "html",
            "folder": path.join(__dirname, '..', 'template')
        },
        "output": {
            "log": true,
            "folder": "./model",
            "beautify": true,
            "indent": 4,
            "preserveNewLines": false,
            "warning": true
        },
        "generate": {
            "stripFirstTableFromHasMany": true,
            "addTableNameToManyToMany": false,
            "addRelationNameToManyToMany": true,
            "stripFirstTableNameFromManyToMany": true,
            "hasManyThrough": false,
            "belongsToMany": true,
            "prefixForBelongsTo": "related",
            "useSchemaName": true,
            "modelCamelCase": true,
            "relationAccessorCamelCase": true,
            "columnAccessorCamelCase": true,
            "columnDefault": false,
            "columnDescription": true,
            "columnAutoIncrement": true,
            "tableDescription": true,
            "dataTypeVariable": "Seq",
            "skipTable": []
    },
        "tableOptions": {
            "timestamps": false
        }
    }
};

CAVEAT: Singleton Nature of Configuration

This module uses config module via require('config') for configuration. Config module is a singleton as of this writing, which returns same config object for each request. As a result subsequent calls in the same process return same configuration even configuration file changed and/or sequelize-pg-generator constructor called with different config file.

This is usually no problem since generator supposed to be called once in the same process. However this behaviour prevents testing. Additionally you may want to avoid this behavior whatever reason. To side step this, it is added "resetConfig" option to constructor. If it is set to true it resets and rereads configuration. To do this we clear config module from node cache. It is sub-optimal solution suggested by lorenwest in github issues section.

To activate this behavior just set resetConfig to true or from cli add --resetConfig:

var generator = require('sequelize-pg-generator');
generator(function (err) {
    if (err) { callback(err); }
}, {
    database: 'my_database',
    resetConfig: true
);

sequelize-pg-creator uses the code below:

global.NODE_CONFIG = null;
delete require.cache[require.resolve('config')];
config = require('config');

Template Variables (Customizing Templates)

To create custom templates user can copy default templates or create from scratch then configure "template.folder" to use newly created templates. 3 files are required: index.ext (.ext is whatever your template engine's extension is), index.js, utils.js.

index.js and utils.js files will be copied directly to target model directory. index.ext template is executed for each table to create model files.

Variables available to use in templates are listed below. Please note if a value is undefined, it's key is also is deleted to make it easy to iterate only defined values in templates.

Examples

Eager Loading

sequelize-pg-generator sets up relations with an "as". Otherwise multiple relations between same two tables collides. For example:

account has many contacts as primaryContacts (account -----< contact)

account has many contacts as secondaryContacts (account ----< contact)

In this case sequelize.js requires you to specify "as" alias in the "as" attribute during eager loading.

account = orm.model('public.account'); // Can be configured without schema.
contact = orm.model('public.contact'); // Can be configured without schema.
account.findAll({ include: [ { model: contact, as: "primaryContacts" } ] }).then(function(data) {
    console.log(data[0].primaryContacts[0].name);
});

API

Modules

Kind: Exported function

GeneratorUtil

Kind: global class

• GeneratorUtil
  • new GeneratorUtil(model)
  • .getRelation(as) ⇒ Object
  • .getAttribute(name) ⇒ Object
  • .renameAttribute(oldName, newName)

new GeneratorUtil(model)

generatorUtil.getRelation(as) ⇒ Object

Searches and returns relation with the given alias. Alias is defined in sequelize options with parameter 'as'

Kind: instance method of GeneratorUtil

generatorUtil.getAttribute(name) ⇒ Object

Searches and returns attribute with the given alias. Alias is defined in sequelize options with parameter 'as'

Kind: instance method of GeneratorUtil

generatorUtil.renameAttribute(oldName, newName)

Searches and returns attribute with the given alias. Alias is defined in sequelize options with parameter 'as'

Kind: instance method of GeneratorUtil
Throws:

• Will throw error if there is already an attribute with new name exists or attribute with oldName does not exists.

History & Release Notes

Note

Version history for minimal documentation updates are not listed here to prevent cluttering.

0.9.0

• Added: sequelize-types are added. (pg-structure deprecated it.)

0.8.0 / 2015-10-14

• Added: alias.json file is generated in target directory to let developer easily override relationship names.

0.7.0 / 2015-10-14

• Changed: Location of utils.js is changed to inside of model directory.

0.6.0 / 2015-09-10

• -t --templateName parameter added to spgen. This name is used to choose one of the builtin template directories.
• sequalize4 template added for protect backward compatibility.
• sequalize4 template supports object references property. (references and referencesKey will be depreciated in Sequelize 4)

0.5.4 / 2015-06-16

• pg-structure updated to latest version.

0.5.3 / 2015-06-16

• Added: JSONB support and Boolean default value. Contributed by viniciuspinto (https://github.com/viniciuspinto)

0.4.2 / 2015-04-27

• Added documentation and examples.

0.3.1 / 2015-01-10

• Tested for Sequelize 2.0 RC7

0.3.0 / 2014-12-30

• Removed: pg-native dependency removed. Some users experienced problems during install.
• Added: generate.addRelationNameToManyToMany configuration to prefix relation aliases prevent further name clashes which cannot be prevented by generate.addTableNameToManyToMany. Default: true.
• Added: generate.stripFirstTableNameFromManyToMany configuration added. Default: true
• Changed: generate.addTableNameToManyToMany configuration default is false now.
• Changed: Default naming rule for many to many relations.
• Added: Logging uses Winston module now.
• Added: Doc update for Windows OS users.
• Fixed: Database tables without any column throws error when warning configuration is true.

0.2.0 / 2014-12-27

• Added: Automatic alias and naming validations to prevent name clash.
• Added: generate.addTableNameToManyToMany configuration to prefix relation aliases prevent name clash. Default: true.
• Added: --throwError option added to CLI. This option decides wheter to throw error or simply log.
• Added: Prevent hasMany through and belongsToMany true at the same time.
• Fixed: generate.prefixForBelongsTo aliases are not properly camel cased.
• Fixed: --resetConfig option does not work from CLI
• Doc update

0.1.17 / 2014-12-26

• Fixed: CLI command does not work.
• Added: Required parameters warning.

0.1.15 / 2014-12-26

• Added: Turkish documentation added.
• Fixed: Typos and mistakes in documents.

0.1.12 / 2014-12-23

• Added: Tests added.
• Added: --nolog option added to spgen command.
• Added: --resetConfig option. Also details and caveat added to the document.
• Fix: lib/index.js exported function expects different parameters than written in documentation.
• Fix: Command line arguments fixed.
• Fix: Data type variable name configuration is ignored.
• Document update.

0.1.0 / 2014-12-23

• Initial version.

The MIT License (MIT)

Copyright (c) 2014 Özüm Eldoğan

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.