It is sequelized-automate

background

Sequelize is a common ORM library in Node.js that maps database tables to objects in code, enabling you to query and manipulate the database in an object-oriented manner.

For example, the database might have a table user, and Sequelize would map it to a UserModel. Then we could query the database with userModel.findall (), and Sequelize would convert the method to SQL: userModel.findAll (); Select * from user.

When we use Sequelize, we first define a Model manually, such as:

class UserModel extends Model {}
User.init({
  id: DataTypes.INTEGER,
  name: DataTypes.STRING,
  birthday: DataTypes.DATE
}, { sequelize, modelName: 'userModel' });
Copy the code

The UserModel can then be synchronized to the database via sequelize.sync(). In short, you define Models in your code and then create/update the table structure with Models.

But usually when we develop, we create the tables first and then write the business code. Also, our table structure cannot be easily changed; there may be a separate process for changing the table structure. So for the most part, we write Models manually based on the table structure rather than using sequelize.sync() directly to update the table structure.

However, writing Models by hand can be tedious and low-level repetitive when tables are very large. Obviously, this should be handled by a tool, a sequelize-automate tool.

Sequelize – Automate profile

Sequelize-automate is a tool that automatically creates models based on table structures. The main features are as follows:

  • Supports all databases supported by Sequelize, such as MySQL, PostgreSQL, Sqlite, MariaDB, and Microsoft SQL Server

  • Supports the generation of JavaScript/TypeScript/egg.js/Midway. Js styles of Models, and is extensible

  • Supports attributes such as primary key, foreign key, autoincrement, and field comment

  • Supports custom variable naming and file name styles

Take MySQL as an example, assume the following table structure:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary ket'.`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'user name'.`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'user email'.`created_at` datetime NOT NULL COMMENT 'created datetime'.`updated_at` datetime NOT NULL COMMENT 'updated datetime',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='User table'
Copy the code

Use sequelize-automate to automatically generate Model file models/user.js:

const {
  DataTypes
} = require('sequelize');

module.exports = sequelize= > {
  const attributes = {
    id: {
      type: DataTypes.INTEGER(11).UNSIGNED,
      allowNull: false.defaultValue: null.primaryKey: true.autoIncrement: true.comment: "primary key".field: "id"
    },
    name: {
      type: DataTypes.STRING(100),
      allowNull: false.defaultValue: null.primaryKey: false.autoIncrement: false.comment: "user name".field: "name".unique: "uk_name"
    },
    email: {
      type: DataTypes.STRING(255),
      allowNull: false.defaultValue: null.primaryKey: false.autoIncrement: false.comment: "user email".field: "email"
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false.defaultValue: null.primaryKey: false.autoIncrement: false.comment: "created datetime".field: "created_at"
    },
    updated_at: {
      type: DataTypes.DATE,
      allowNull: false.defaultValue: null.primaryKey: false.autoIncrement: false.comment: "updated datetime".field: "updated_at"}};const options = {
    tableName: "user".comment: "".indexes: []};const UserModel = sequelize.define("user_model", attributes, options);
  return UserModel;
};
Copy the code

So we can use it directly in the project:

const Sequelize = require('sequelize');
const UserModel = require('./models/user');

// Option 1: Passing parameters separately
const sequelize = new Sequelize('database'.'username'.'password', {
  host: 'localhost'.dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' | 'sqlite' */
});

const userModel = UserModel(sequelize);
const users = await userModel.findAll();
Copy the code

Sequelize – Automate use

Sequelize-automate Sequelize-Automate provides the sequelize-automate command, which can be installed globally or within a project only.

Global installation

Install sequelize-Automate first

$ npm install -g sequelize-automate
Copy the code

Then, consistent with Sequelize, you need to install dependency packages for the database you are using:

Install one of the following commands, depending on which database you are using
$ npm install -g pg pg-hstore # Postgres
$ npm install -g mysql2
$ npm install -g mariadb
$ npm install -g sqlite3
$ npm install -g tedious # Microsoft SQL Server
Copy the code

The reason for this is that if I use MySQL, I only need to install mysql2 (sequelize uses mysql2 to operate on the MySQL database), and there is no need or need to install other packages.

Install only in the project

If you don’t like sequelize-Automate globally because you need to install mysql2 or other dependencies globally, or if you only want to use it in a project, you can use it in a project only:

$ cd your_project_dir
$ npm install sequelize-automate --save
Copy the code

Then you also need to install the corresponding database dependency package:

Install one of the following commands, depending on which database you are using
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server
Copy the code

Of course, if you are already using Sequelize in your project, you will certainly have a corresponding database dependency package installed.

Once installed, you can use sequelize-automate in the project directory with./node_modules/. Bin /sequelize-automate. But we often don’t.

It is recommended to add a script to package.json:

 "scripts": {
    "sequelize-automate": "sequelize-automate"
  },
Copy the code

NPM run sequelize-automate executes sequelize-automate indirectly.

sequelize-automateThe command,

The sequelize-automate command supports the following parameters:

  • --type, -tSpecifies the models code style, currently optional:js ts egg midway
  • --dialect, -eDatabase type, optional:mysql sqlite postgres mssql mariadb
  • --host, -hThe database host
  • --database, -dThe database name
  • --user, -uDatabase user name
  • --password, -pDatabase password
  • --port, -PMySQL/MariaDB 3306, Postgres 5432, SSQL: 1433
  • --output, -oSpecifies the directory to output the models file, which will be generated in the current directory by defaultmodelsfolder
  • --camel, -CWhether the code in the Models file is named with hump, defaultfalse
  • --emptyDir, -rWhether to clear the Models directory (i.e-oSpecified directory), iftrue, the corresponding directory is cleared before generating models, defaultfalse
  • --config, -cSpecify a configuration file. You can specify the parameters of a command in a configuration file

For details about parameters, see sequelize-Automate.

Use the sample

Global command:

sequelize-automate -t js -h localhost -d test -u root -p root -P 3306  -e mysql -o models
Copy the code

If used in a project, you can add the change command to package.json:

"scripts": {
    "sequelize-automate": "sequelize-automate -t js -h localhost -d test -u root -p root -P 3306 -e mysql -o models"
  },
Copy the code

Then use TNPM run sequelize-Automate to automatically generate models.

Specifying a configuration file

Because the command has many parameters, you can specify parameters in the JSON configuration file.

Create a sequelize-automate. Config. json configuration file in the current directory.

{
  "dbOptions": {
    "database": "test"."username": "root"."password": "root"."dialect": "mysql"."host": "localhost"."port": 3306."logging": false
  },
  "options": {
    "type": "js"."dir": "models"}}Copy the code

You can also use JS files:

module.exports = {
  dbOptions: {
    database: "test".username: "root".password: "root".dialect: "mysql".host: "localhost".port: 3306.logging: false
  },
  options: {
    type: "js".dir: "models"}}Copy the code

Use sequelize-automate -c sequelize-automate. Config. json to use sequelize-automate.

The configuration file consists of dbOptions and Options.

dbOptions

DbOptions takes exactly the same parameters as the Sequelize constructor and is database-related information. Sequelize-automate will use dbOptions to create a Sequelize instance. See SRC /index.js#L43.

Here are some of the properties of dbOptions:

dbOptions: {
  database: 'test'.username: 'root'.password: 'root'.dialect: 'mysql'.host: '127.0.0.1'.port: 3306.define: {
    underscored: false.freezeTableName: false.charset: 'utf8mb4'.timezone: '+ 00:00'.dialectOptions: {
      collate: 'utf8_general_ci',},timestamps: false,}};Copy the code

Database USERNAME Password Dialect host port

options

Options are sequelize-Automate configuration items. The main properties are as follows:

options: {
  type: 'js'.// Specify the Models code style
  camelCase: false.// Whether the code in the Models file is named with a hump
  fileNameCamelCase: true.// Model whether to use the hump method for the file name. The default file name is' user_post.js'; If true, the file name is' userpost.js'
  dir: 'models'.// Specify the directory to output the models file
  typesDir: 'models'.// Specify the directory that outputs TypeScript type definitions. Only TypeScript/Midway has type definitions
  emptyDir: false.// Whether to empty 'dir' and 'typesDir' before generating models
  tables: null.// Specify which table models to generate, e.g. ['user', 'user_post']; If null, the modified attribute is ignored
  skipTables: null.// Specify which table models to skip, such as ['user']; If null, the modified attribute is ignored
  tsNoCheck: false.// Whether to add '@ts-nocheck' comments to the models file
}
Copy the code

SRC /index.js#L13

The tsNoCheck attribute is available because the type definition of the Sequelize does not support type: datatypes.integer (255), only type: DataTypes. This will result in errors in TypeScript. So the tsNoCheck attribute is added. If true, @ts-nocheck is automatically added to the model file header, as in:

// @ts-nocheck
import { IApplicationContext, providerWrapper } from 'midway';
import { DataTypes } from 'sequelize';
import { IDB } from './db';
export default async function setupModel(context: IApplicationContext) {
  const db: IDB = await context.getAsync('DB');
  const attributes = {
	 id: {
      type: DataTypes.BIGINT.UNSIGNED,
      allowNull: false.defaultValue: null.primaryKey: true.autoIncrement: true.comment: 'primary key'.field: 'id',},name: {
      type: DataTypes.STRING(100),
      allowNull: false.defaultValue: null.primaryKey: false.autoIncrement: false.comment: null.field: 'name',}};const options = {
    tableName: 'flow'.comment: ' '.indexs: [],};return db.sequelize.define('userModel', attributes, options);
}
providerWrapper([{
  id: 'UserModel'.provider: setupModel,
}]);

Copy the code

API

Sequelize-automate command execution mode, sequelize-Automate itself also provides an interface for users to customize. There are two main ones:

  • automate.getDefinitionsConvert database tables to JSON
  • automate.runGenerating Models code

The usage method is as follows:

const Automate = require('sequelize-automate');

// dbOptions and options have been mentioned before and will not be described here
const dbOptions = {
  // ...
};
const options = {}
  // ...
}

Create a Automate instance
const automate = new Automate(dbOptions, options);

(async function main() {
  // // Get the Models JSON definition
  // const definitions = await automate.getDefinitions();
  // console.log(definitions);

  // or generate code
  const code = await automate.run();
  console.log(code); }) ()Copy the code

The realization of Sequelize – Automate

Sequelize-automate is implemented in a simple way. First, query all table information from the database, including table structure, index, foreign key, etc. Then convert the table information into a JSON definition. Finally, use AST to generate code according to the JSON definition.

Get table information

Querying table information depends on some of sequelize’s methods. That’s why sequelize-Automate relies on Sequelize and has dbOptions as an argument. The main apis used are:

  • QueryInterface.showAllTables
  • QueryInterface.describeTable
  • QueryInterface.showIndex
  • QueryInterface.getForeignKeyReferencesForTable

Many of the apis are not written in the Sequelize documentation, and I only found them by looking at the source code.

Sequelize do better, is for developers to shield the differences among different databases, such as all use this. QueryInterface. ShowIndex return values are the same format. Of course, there are some apis that it does not fully do, such as showAllTables, where some databases return [‘tableName’] and others return {tableName, schema}, as shown in sequelize#11451.

The table information is as follows:

{
    "user": {"structures": {"id": {"type":"INT(11) UNSIGNED"."allowNull":false."defaultValue":null."primaryKey":true."autoIncrement":true."comment":"primary ket"
            },
            "name": {"type":"VARCHAR(100)"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"user name"
            },
            "email": {"type":"VARCHAR(255)"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"user email"
            },
            "created_at": {"type":"DATETIME"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"created datetime"
            },
            "updated_at": {"type":"DATETIME"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"updated datetime"}},"indexes":[
            {
                "primary":true."fields":[
                    {
                        "attribute":"id"."order":"ASC"}]."name":"PRIMARY"."tableName":"user"."unique":true."type":"BTREE"
            },
            {
                "primary":false."fields":[
                    {
                        "attribute":"name"."order":"ASC"}]."name":"uk_name"."tableName":"user"."unique":true."type":"BTREE"}]."foreignKeys": []},"user_post": {"structures": {"id": {"type":"INT(11) UNSIGNED"."allowNull":false."defaultValue":null."primaryKey":true."autoIncrement":true."comment":"primary key"
            },
            "user_id": {"type":"INT(11) UNSIGNED"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"user id"
            },
            "title": {"type":"VARCHAR(255)"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"post title"
            },
            "content": {"type":"TEXT"."allowNull":true."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"post content"
            },
            "created_at": {"type":"DATETIME"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"created datetime"
            },
            "updated_at": {"type":"DATETIME"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"updated datetime"}},"indexes":[
            {
                "primary":true."fields":[
                    {
                        "attribute":"id"."order":"ASC"}]."name":"PRIMARY"."tableName":"user_post"."unique":true."type":"BTREE"
            },
            {
                "primary":false."fields":[
                    {
                        "attribute":"user_id"."order":"ASC"}]."name":"fk_user_id"."tableName":"user_post"."unique":false."type":"BTREE"}]."foreignKeys":[
            {
                "constraint_name":"fk_user_id"."constraintName":"fk_user_id"."constraintSchema":"test"."constraintCatalog":"test"."tableName":"user_post"."tableSchema":"test"."tableCatalog":"test"."columnName":"user_id"."referencedTableSchema":"test"."referencedTableCatalog":"test"."referencedTableName":"user"."referencedColumnName":"id"}}}]Copy the code

Process the Models JSON definition

Once you have the table information, you need to translate the table information into the definition of sequelize Models. For example: convert “type”:”INT(11) UNSIGNED” to “type”:” datatypes.integer (11).UNSIGNED”, handle indexes, handle foreign keys, etc. For example, MySQL increments only need to be set to AUTO_INCREMENT while PostgreSQL increments only need to be set to serial. Set defaultValue to extval(my_datA_id_seq :: regClass), see Sequelize-automate #9.

The resulting models definition, also known as getDefinitions, returns JSON as follows:

[{"modelName":"user_model"."modelFileName":"user"."tableName":"user"."attributes": {"id": {"type":"DataTypes.INTEGER(11).UNSIGNED"."allowNull":false."defaultValue":null."primaryKey":true."autoIncrement":true."comment":"primary ket"."field":"id"
            },
            "name": {"type":"DataTypes.STRING(100)"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"user name"."field":"name"."unique":"uk_name"
            },
            "email": {"type":"DataTypes.STRING(255)"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"user email"."field":"email"
            },
            "created_at": {"type":"DataTypes.DATE"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"created datetime"."field":"created_at"
            },
            "updated_at": {"type":"DataTypes.DATE"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"updated datetime"."field":"updated_at"}},"indexes": []}, {"modelName":"user_post_model"."modelFileName":"user_post"."tableName":"user_post"."attributes": {"id": {"type":"DataTypes.INTEGER(11).UNSIGNED"."allowNull":false."defaultValue":null."primaryKey":true."autoIncrement":true."comment":"primary key"."field":"id"
            },
            "user_id": {"type":"DataTypes.INTEGER(11).UNSIGNED"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"user id"."field":"user_id"."references": {"key":"id"."model":"user_model"}},"title": {"type":"DataTypes.STRING(255)"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"post title"."field":"title"
            },
            "content": {"type":"DataTypes.TEXT"."allowNull":true."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"post content"."field":"content"
            },
            "created_at": {"type":"DataTypes.DATE"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"created datetime"."field":"created_at"
            },
            "updated_at": {"type":"DataTypes.DATE"."allowNull":false."defaultValue":null."primaryKey":false."autoIncrement":false."comment":"updated datetime"."field":"updated_at"}},"indexes":[
            {
                "name":"fk_user_id"."unique":false."type":"BTREE"."fields": ["user_id"]}]}Copy the code

The subsequent run method generates different styles of Models code, such as JS, TS, or egg.js, based on the JSON. Of course, developers can use this JSON definition to generate other styles of code.

Generate models using AST

Once you have the JSON definition of the Models, you can generate models from the definition. In this process, I chose to use an AST, which is the AST of the Models, and then generate code based on the AST. The main tools used are @babel/parser @babel/generator @babel/types @babel/traverse.

For example, generate an AST for the string “primary key” :

const t = require('@babel/types');

const str = t.stringLiteral('Primary key');
// { type: 'StringLiteral', value: 'Primary key' }
Copy the code

{comment: “primary key”}

const obj = t.objectProperty(t.identifier('comment'), t.stringLiteral("Primary key"));
/** { type: 'ObjectProperty', key: { type: 'Identifier', name: 'comment' }, value: { type: 'StringLiteral', value: 'Primary' }, computed: false, shorthand: false, decorators: null } */
Copy the code

You can then generate code based on the AST:

const generator = require('@babel/generator').default;

const code = generate(obj);
// { code: 'comment: "Primary"', map: null, rawMappings: null }
Copy the code

JsescOption. Minimal is set to true if you want to support Chinese, otherwise you output Unicode characters:

const obj = t.objectProperty(t.identifier('comment'), t.stringLiteral("Primary key"));

const code1 = generate(obj);
{ code: 'comment: "\\u4E3B\\u952E"'.map: null.rawMappings: null }

const code2 = generate(obj, {
  jsescOption: {
    minimal: true,}});// {code: 'comment: "id", map: null, rawMappings: null}
Copy the code

conclusion

At the beginning, I wrote sequelize-Automate because every time the table structure was modified, I had to manually modify the models in the code, which was very cumbersome and easy to write wrong. When there were too many tables, it became even more troublesome to write. So I developed this little tool to let the tool do as much as possible.

The sequelize/sequelize-auto package can also be used to automatically generate models, but it has not been updated for several years. The sequelize version is still 3.30. Now Sequelize has been updated to 6.0; And it still has a lot of bugs that are hard to use. I also looked at the code and felt it was a mess, full of nested callbacks and hard to maintain. Its generated code also uses string concatenation, which is less advanced, high-end, accurate, and predictable than AST. Do not hesitate to use sequelize-automate!