sequelize-typescript-generator

Automatically generates typescript models compatible with sequelize-typescript library directly from your source database.

Table of Contents

• Supported databases
• Prerequisites
• Installation
• CLI usage
• Programmatic usage
• Strict mode
• Transform case
• Associations
  • One to One
  • One to Many
  • Many to Many
• Lint

Tested databases

This library is tested on the following databases:

• Postgres (11, 14, 16)
• Mysql (5, 8)
• MariaDB (10, 11)
• SQL Server (2019, 2022)
• SQLite (3)

Prerequisites

See sequelize-typescript installation.

You should also install the specific driver library for your database, see sequelize documentation:

1npm install -S pg pg-hstore # Postgres
2npm install -S mysql2 # MySQL
3npm install -S mariadb # MariaDB
4npm install -S sqlite3 # SQLite
5npm install -S tedious # Microsoft SQL Server

Installation

Local install

1npm install -S sequelize-typescript-generator

Global install (you must install also the peer dependencies globally, see Prerequisites):

1npm install -g sequelize-typescript-generator

NB - Linting models globally is not supported (eslint library does not support global plugins). If you plan to use the library globally and you want your models to be automatically linted, you need to install the following packages locally:

1npm install -S typescript eslint @typescript-eslint/parser

CLI usage

To use the library locally, install npx if not already available in the path:

1npm install -g npx

Then to get usage information type:

1npx stg --help

For a global usage simply type:

1stg --help

1Usage: stg -D <dialect> -d [database] -u [username] -x [password] -h [host] -p
2[port] -o [out-dir] -s [schema] -a [associations-file]-t [tables] -T
3[skip-tables] -i [indices] -C [case] -S [storage] -L [lint-file] -l [ssl] -r
4[protocol] -c [clean]
5
6Options:
7  --help                      Show help                                [boolean]
8  --version                   Show version number                      [boolean]
9  -h, --host                  Database IP/hostname                      [string]
10  -p, --port                  Database port. Defaults:
11                              - MySQL/MariaDB: 3306
12                              - Postgres: 5432
13                              - MSSQL: 1433                             [number]
14  -d, --database              Database name                             [string]
15  -s, --schema                Schema name (Postgres only). Default:
16                              - public                                  [string]
17  -D, --dialect               Dialect:
18                              - postgres
19                              - mysql
20                              - mariadb
21                              - sqlite
22                              - mssql                        [string] [required]
23  -u, --username              Database username                         [string]
24  -x, --password              Database password                         [string]
25  -t, --tables                Comma-separated names of tables to process[string]
26  -T, --skip-tables           Comma-separated names of tables to skip   [string]
27  -i, --indices               Include index annotations in the generated models
28                                                                       [boolean]
29  -o, --out-dir               Output directory. Default:
30                              - output-models                           [string]
31  -c, --clean                 Clean output directory before running    [boolean]
32  -m, --timestamps            Add default timestamps to tables         [boolean]
33  -C, --case                  Transform tables and fields names
34                              with one of the following cases:
35                              - underscore
36                              - camel
37                              - upper
38                              - lower
39                              - pascal
40                              - const
41                              You can also specify a different
42                              case for model and columns using
43                              the following format:
44                              <model case>:<column case>
45                                                                        [string]
46  -S, --storage               SQLite storage. Default:
47                              - memory                                  [string]
48  -L, --lint-file             ES Lint file path                         [string]
49  -l, --ssl                   Enable SSL                               [boolean]
50  -r, --protocol              Protocol used: Default:
51                              - tcp                                     [string]
52  -a, --associations-file     Associations file path                    [string]
53  -g, --logs                  Enable Sequelize logs                    [boolean]
54  -n, --dialect-options       Dialect native options passed as json string.
55                                                                        [string]
56  -f, --dialect-options-file  Dialect native options passed as json file path.
57                                                                        [string]
58  -R, --no-strict             Disable strict typescript class declaration.
59                                                                       [boolean]    
60  -V, --no-views              Disable view generation. Available for: MySQL and MariaDB.
61                                                                       [boolean]                                                                      

Local usage example:

1npx stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --indices --dialect-options-file path/to/dialectOptions.json --case camel --out-dir models --clean 

Global usage example:

1stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --indices --dialect-options-file path/to/dialectOptions.json --case camel --out-dir models --clean 

Programmatic usage

You can use the library programmatically, as shown in the following example:

1import { IConfig, ModelBuilder, DialectMySQL } from 'sequelize-typescript-generator';
2
3(async () => {
4    const config: IConfig = {
5        connection: {
6            dialect: 'mysql',
7            database: 'myDatabase',
8            username: 'myUsername',
9            password: 'myPassword'
10        },
11        metadata: {
12            indices: true,
13            case: 'CAMEL',
14        },
15        output: {
16            clean: true,
17            outDir: 'models'
18        },
19        strict: true,
20    };
21
22    const dialect = new DialectMySQL();
23
24    const builder = new ModelBuilder(config, dialect);
25
26    try {
27        await builder.build();
28    }
29    catch(err) {
30        console.error(err);
31        process.exit(1);
32    }    
33})();

Strict mode

By default strict mode will be used for models class declaration:

STRICT ENABLED

1import {
2	Model, Table, Column, DataType, Index, Sequelize, ForeignKey, HasOne 
3} from "sequelize-typescript";
4import { passport } from "./passport";
5
6export interface personAttributes {
7    person_id: number;
8    name: string;
9    passport_id: number;
10}
11
12@Table({
13	tableName: "person",
14	timestamps: false 
15})
16export class person extends Model<personAttributes, personAttributes> implements personAttributes {
17
18    @Column({
19    	primaryKey: true,
20    	type: DataType.INTEGER 
21    })
22    @Index({
23    	name: "PRIMARY",
24    	using: "BTREE",
25    	order: "ASC",
26    	unique: true 
27    })
28    person_id!: number;
29
30    @Column({
31    	type: DataType.STRING(80) 
32    })
33    name!: string;
34
35    @Column({
36    	type: DataType.INTEGER 
37    })
38    passport_id!: number;
39
40    @HasOne(() => passport, {
41    	sourceKey: "person_id" 
42    })
43    passport?: passport;
44
45}

You can disable strict mode from both CLI or programmatically:

1npx stg -D mysql -d myDatabase --no-strict  

1const config: IConfig = {
2    connection: {
3        dialect: 'mysql',
4        database: 'myDatabase',
5        username: 'myUsername',
6        password: 'myPassword'
7    },
8    metadata: {
9        indices: true,
10        case: 'CAMEL',
11    },
12    output: {
13        clean: true,
14        outDir: 'models'
15    },
16    strict: false,
17};

STRICT DISABLED

1import {
2	Model, Table, Column, DataType, Index, Sequelize, ForeignKey, HasOne 
3} from "sequelize-typescript";
4import { passport } from "./passport";
5
6@Table({
7	tableName: "person",
8	timestamps: false 
9})
10export class person extends Model {
11
12    @Column({
13    	primaryKey: true,
14    	type: DataType.INTEGER 
15    })
16    @Index({
17    	name: "PRIMARY",
18    	using: "BTREE",
19    	order: "ASC",
20    	unique: true 
21    })
22    person_id!: number;
23
24    @Column({
25    	type: DataType.STRING(80) 
26    })
27    name!: string;
28
29    @Column({
30    	type: DataType.INTEGER 
31    })
32    passport_id!: number;
33
34    @HasOne(() => passport, {
35    	sourceKey: "person_id" 
36    })
37    passport?: passport;
38
39}

Transform case

You can transform table name and fields with one of the following cases:

• underscore
• camel
• upper
• lower
• pascal
• const

You can provide a different case for the model name and columns:

1npx stg -D mysql --case const:camel 

1const config: IConfig = {
2    // [...]
3    metadata: {        
4        case: {
5            model: 'CONST',
6            column: 'CAMEL'    
7        },
8    },
9    // [...]
10};

You can also provide your custom transformer function (code only):

1const config: IConfig = {
2    // [...]
3    metadata: {        
4        case: (value, target) => {
5            // Model transformer
6            if (target === 'model') {
7                return value.toUpperCase();
8            }
9    
10            // Column transformer
11            return value.toLowerCase();
12        }
13    },
14    // [...]
15};

NB: please note that currently case transformation is not supported for non ASCII strings.

Associations

Including associations in the generated models requires a bit of manual work unfortunately, but hopefully it will buy you some time instead of defining them from scratch.

First you have to define a csv-like text file, let's call it associations.csv (but you can call it however you want). In this file you have to put an entry for each association you want to define. The following associations are supported:

• 1:1
• 1:N
• N:N

Some rules for the association file:

• Names of tables and columns in the associations file must be the native names on the database, not the transformed names generated when using a custom case transformation with the flag --case.
• Only , separator is supported.
• Do not use enclosing quotes.

Note that fields generated by associations will be pluralized or singularized based on cardinality.

One to One

In the associations file include an entry with the following structure:

1:1, left_table_key, right_table_key, left_table, right_table

where:

• 1:1 is the relation cardinality
• left_table_key is the join column of the left table
• right_table_key is the join column of the right table
• left_table is the name of the left table
• right_table is the name of the right table

For example given the following tables:

1CREATE TABLE person
2(
3    person_id           INT             PRIMARY KEY,
4    name                VARCHAR(80)     NOT NULL,
5    passport_id         INT             NOT NULL
6);
7
8CREATE TABLE passport
9(
10    passport_id         INT             PRIMARY KEY,
11    code                VARCHAR(80)     NOT NULL
12);

Define a 1:1 association with the following entry in the associations file:

1:1, passport_id, passport_id, person, passport

Then pass the associations file path to the cli:

1npx stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --associations-file path/to/associations.csv --out-dir models --clean 

Global:

1stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --associations-file path/to/associations.csv --out-dir models --clean 

Or programmatically:

1import { IConfig, ModelBuilder, DialectMySQL } from 'sequelize-typescript-generator';
2
3(async () => {
4    const config: IConfig = {
5        connection: {
6            dialect: 'mysql',
7            database: 'myDatabase',
8            username: 'myUsername',
9            password: 'myPassword'
10        },
11        metadata: {
12            indices: false,
13            associationsFile: 'path/to/associations.csv',            
14        },
15        output: {
16            clean: true,
17            outDir: 'models'
18        }
19    };
20
21    const dialect = new DialectMySQL();
22
23    const builder = new ModelBuilder(config, dialect);
24
25    try {
26        await builder.build();
27    }
28    catch(err) {
29        console.error(err);
30        process.exit(1);
31    }    
32})();

This will generate the following models:

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey, HasOne
3} from "sequelize-typescript";
4import { passport } from "./passport";
5
6export interface personAttributes {
7  person_id: number;
8  name: string;
9  passport_id: number;
10}
11
12@Table({
13  tableName: "person",
14  timestamps: false
15})
16export class person extends Model<personAttributes, personAttributes> implements personAttributes {
17
18  @Column({
19    primaryKey: true,
20    type: DataType.INTEGER
21  })
22  person_id!: number;
23
24  @Column({
25    type: DataType.STRING(80)
26  })
27  name!: string;
28
29  @Column({
30    type: DataType.INTEGER
31  })
32  passport_id!: number;
33
34  @HasOne(() => passport, {
35    sourceKey: "passport_id"
36  })
37  passport?: passport;
38
39}

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey, BelongsTo
3} from "sequelize-typescript";
4import { person } from "./person";
5
6export interface passportAttributes {
7  passport_id: number;
8  code: string;
9}
10
11@Table({
12  tableName: "passport",
13  timestamps: false
14})
15export class passport extends Model<passportAttributes, passportAttributes> implements passportAttributes {
16
17  @ForeignKey(() => person)
18  @Column({
19    primaryKey: true,
20    type: DataType.INTEGER
21  })
22  passport_id!: number;
23
24  @Column({
25    type: DataType.STRING(80)
26  })
27  code!: string;
28
29  @BelongsTo(() => person)
30  person?: person;
31
32}

One to Many

1:N, left_table_key, right_table_key, left_table, right_table

where:

• 1:N is the relation cardinality
• left_table_key is the join column of the left table
• right_table_key is the join column of the right table
• left_table is the name of the left table
• right_table is the name of the right table

For example given the following tables:

1CREATE TABLE races
2(
3    race_id             INT             PRIMARY KEY,
4    race_name           VARCHAR(80)     NOT NULL
5);
6
7CREATE TABLE units
8(
9    unit_id             INT             PRIMARY KEY,
10    unit_name           VARCHAR(80)     NOT NULL,
11    race_id             INT             NOT NULL
12);

Define a 1:N association with the following entry in the associations file:

1:N, race_id, race_id, races, units

Build models:

1npx stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --indices --associations-file path/to/associations.csv --out-dir models --clean 

This will generate the following models:

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey, HasMany
3} from "sequelize-typescript";
4import { units } from "./units";
5
6export interface racesAttributes {
7  race_id: number;
8  race_name: string;
9}
10
11@Table({
12  tableName: "races",
13  timestamps: false
14})
15export class races extends Model<racesAttributes, racesAttributes> implements racesAttributes {
16
17  @Column({
18    primaryKey: true,
19    type: DataType.INTEGER
20  })
21  race_id!: number;
22
23  @Column({
24    type: DataType.STRING(80)
25  })
26  race_name!: string;
27
28  @HasMany(() => units, {
29    sourceKey: "race_id"
30  })
31  units?: units[];
32
33}

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey, BelongsTo
3} from "sequelize-typescript";
4import { races } from "./races";
5
6export interface unitsAttributes {
7  unit_id: number;
8  unit_name: string;
9  race_id: number;
10}
11
12@Table({
13  tableName: "units",
14  timestamps: false
15})
16export class units extends Model<unitsAttributes, unitsAttributes> implements unitsAttributes {
17
18  @Column({
19    primaryKey: true,
20    type: DataType.INTEGER
21  })
22  unit_id!: number;
23
24  @Column({
25    type: DataType.STRING(80)
26  })
27  unit_name!: string;
28
29  @ForeignKey(() => races)
30  @Column({
31    type: DataType.INTEGER
32  })
33  race_id!: number;
34
35  @BelongsTo(() => races)
36  race?: races;
37
38}

Many to Many

In the associations file include an entry with the following structure:

N:N, left_table_key, right_table_key, left_table, right_table, join_table

where:

• N:N is the relation cardinality
• left_table_key is the join column of the left table
• right_table_key is the join column of the right table
• left_table is the name of the left table
• right_table is the name of the right table
• join_table is the name of the join table

For example given the following tables:

1CREATE TABLE authors
2(
3    author_id       INT             primary key,
4    full_name       VARCHAR(80)     not null
5);
6
7CREATE TABLE books
8(
9    book_id         INT             PRIMARY KEY,
10    title           VARCHAR(80)     not null
11);
12
13CREATE TABLE authors_books
14(
15    author_id       INT             not null,
16    book_id         INT             not null,
17    PRIMARY KEY (author_id, book_id)
18);

Define an N:N association with the following entry in the associations file:

N:N, author_id, book_id, authors, books, authors_books

Build models:

1npx stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --indices --associations-file path/to/associations.csv --out-dir models --clean 

This will generate the following models:

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey, BelongsToMany
3} from "sequelize-typescript";
4import { books } from "./books";
5import { authors_books } from "./authors_books";
6
7export interface authorsAttributes {
8  author_id: number;
9  full_name: string;
10}
11
12@Table({
13  tableName: "authors",
14  timestamps: false
15})
16export class authors extends Model<authorsAttributes, authorsAttributes> implements authorsAttributes {
17
18  @Column({
19    primaryKey: true,
20    type: DataType.INTEGER
21  })
22  author_id!: number;
23
24  @Column({
25    type: DataType.STRING(80)
26  })
27  full_name!: string;
28
29  @BelongsToMany(() => books, () => authors_books)
30  books?: books[];
31
32}

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey, BelongsToMany
3} from "sequelize-typescript";
4import { authors } from "./authors";
5import { authors_books } from "./authors_books";
6
7export interface booksAttributes {
8  book_id: number;
9  title: string;
10}
11
12@Table({
13  tableName: "books",
14  timestamps: false
15})
16export class books extends Model<booksAttributes, booksAttributes> implements booksAttributes {
17
18  @Column({
19    primaryKey: true,
20    type: DataType.INTEGER
21  })
22  book_id!: number;
23
24  @Column({
25    type: DataType.STRING(80)
26  })
27  title!: string;
28
29  @BelongsToMany(() => authors, () => authors_books)
30  authors?: authors[];
31
32}

1import {
2  Model, Table, Column, DataType, Index, Sequelize, ForeignKey
3} from "sequelize-typescript";
4import { authors } from "./authors";
5import { books } from "./books";
6
7export interface authors_booksAttributes {
8  author_id: number;
9  book_id: number;
10}
11
12@Table({
13  tableName: "authors_books",
14  timestamps: false
15})
16export class authors_books extends Model<authors_booksAttributes, authors_booksAttributes> implements authors_booksAttributes {
17
18  @ForeignKey(() => authors)
19  @Column({
20    primaryKey: true,
21    type: DataType.INTEGER
22  })
23  author_id!: number;
24
25  @ForeignKey(() => books)
26  @Column({
27    primaryKey: true,
28    type: DataType.INTEGER
29  })
30  book_id!: number;
31
32}

Lint

By default each generated model will be linted with a predefined set of rules to improve readability:

1export const eslintDefaultConfig = {
2    parser:  '@typescript-eslint/parser',
3    parserOptions:  {
4        ecmaVersion:  2018,
5        sourceType:  'module',
6    },
7    plugins: [
8        '@typescript-eslint',
9    ],
10    extends:  [],
11    rules:  {
12        'padded-blocks': ['error', { blocks: 'always', classes: 'always', switches: 'always' }],
13        'lines-between-class-members': ['error', 'always' ],
14        'object-curly-newline': ['error', {
15            'ObjectExpression': 'always',
16            'ObjectPattern': { 'multiline': true },
17            'ImportDeclaration': { 'multiline': true, 'minProperties': 3 },
18            'ExportDeclaration': { 'multiline': true, 'minProperties': 3 },
19        }],
20        'object-property-newline': ['error'],
21        'indent': ['error', 'tab'],
22    },
23};

You can provide your own set of rules that matches your coding style. Just define a file with the linting rules (see eslint docs) and pass it to the cli like the following:

1npx stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --lint-file path/to/lint-file --out-dir models --clean 

Globally:

1stg -D mysql -h localhost -p 3306 -d myDatabase -u myUsername -x myPassword --lint-file path/to/lint-file --out-dir models --clean 

Or you can pass eslint options programmatically:

1import { IConfig, ModelBuilder, DialectMySQL } from 'sequelize-typescript-generator';
2
3(async () => {
4    const config: IConfig = {
5        connection: {
6            dialect: 'mysql',
7            database: 'myDatabase',
8            username: 'myUsername',
9            password: 'myPassword'
10        },        
11        lintOptions: {
12            configFile: 'path/to/lint-file',
13            fix: true,
14        },
15        output: {
16            clean: true,
17            outDir: 'my-models',
18        },
19    };
20
21    const dialect = new DialectMySQL();
22
23    const builder = new ModelBuilder(config, dialect);
24
25    await builder.build();
26})();

License

MIT License