Use Ts+sequelize to achieve a more concise crud

If it is often used node to do server development of children's shoes, it is inevitable to operate the database, do some additions and deletions ( CRUD , Create Read Update Delete ) of the operation, if it is some simple operation, similar to the timing of the script or something, may directly write SQL statements to achieve the function, And if in some large projects, dozens of, hundreds of tables, there will be some (one-to-many, many-to-many) mapping relationship, then the introduction of a ORM ( Object Relational Mapping ) tool to help us deal with the database can alleviate a part of the unnecessary workload, Sequelize is one of the more popular.

CRUD original edition manually stitching SQL

First of all, to illustrate the direct splicing SQL statement so that the "bottom" mode of operation:

CREATE TABLE animal (  id INT AUTO_INCREMENT,  name VARCHAR(14) NOT NULL,  weight INT NOT NULL,   PRIMARY KEY (`id`));

Create such a table, three fields, self-increment ID, name and weight .
If you use mysql this package to manipulate the database directly, this is probably the case:

const connection = mysql.createConnection({})const tableName = 'animal'connection.connect()// 我们假设已经支持了Promise// 查询const [results] = await connection.query(`  SELECT     id,    name,    weight  FROM ${tableName}`)// 新增const name = 'Niko'const weight = 70await connection.query(`  INSERT INTO ${tableName} (name, weight)  VALUES ('${name}', ${weight})`)// 或者通过传入一个Object的方式也可以做到await connection.query(`INSERT INTO ${tableName} SET ?`, {  name,  weight})connection.end()

It seems to be quite clear, but the problem is that developers need to understand the table structure enough.
If there are more than 10 fields in the table, it will be a big memory cost for the developer, you need to know what type of a field is, and when stitching, you SQL should also pay attention to the order and type of the INSERT, the WHERE type of query parameter, and if you modify the type of a field, the corresponding parameter will be processed.
Such projects are especially scary when it comes to handing over, and newcomers need to learn from the table structure from the beginning.
And there is a problem, if one day need to change the database, give up MySQL , then all the SQL statements are to be modified (because the dialect of each database may be different)

Use of crud Advanced version Sequelize

On the memory of this matter, the machine will certainly be more some numbers than the human brain, so there is ORM , here is used in the Node more popular Sequelize .

What's an ORM?

The first thing you might need to explain ORM is what to do, and you can simply understand that by using an object-oriented approach, you implement the interaction with the database, and the action is done CRUD .
Developers do not need to care about the type of database or the actual table structure, but instead map the tables and fields in the database based on the structure of the objects in the current programming language.

Just like the table above, animal no longer need to be in the code to splice SQL statements, but directly call similar Animal.create , Animal.find you can complete the corresponding action.

How to use Sequelize

First we need to download Sequelize the dependencies:

npm i sequelizenpm i mysql2    # 以及对应的我们需要的数据库驱动

Then create an instance in the program Sequelize :

const Sequelize = require('Sequelize')const sequelize = new Sequelize('mysql://root:jarvis@127.0.0.1:3306/ts_test')//                             dialect://username:password@host:port/db_name// 针对上述的表，我们需要先建立对应的模型：const Animal = sequelize.define('animal', {  id: { type: Sequelize.INTEGER, autoIncrement: true },  name: { type: Sequelize.STRING, allowNull: false },  weight: { type: Sequelize.INTEGER, allowNull: false },}, {  // 禁止sequelize修改表名，默认会在animal后边添加一个字母`s`表示负数  freezeTableName: true,  // 禁止自动添加时间戳相关属性  timestamps: false,})// 然后就可以开始使用咯// 还是假设方法都已经支持了Promise// 查询const results = await Animal.findAll({  raw: true,})// 新增const name = 'Niko'const weight = 70await Animal.create({  name,  weight,})

Sequelize define the various configurations related to the model: docs

Aside from the definition of the model, the use Sequelize is undoubtedly mitigated by a lot of the cost of use, because the definition of the model is generally less likely to change, once defined multiple uses, and the use of manual splicing SQL may need to change a section of the SQL .

It can also help with the conversion of field types, avoiding NaN some careless errors, such as type cast errors or numbers being truncated.

By defining the model to tell the program, what models, what are the fields of the model, let the program help us remember, rather than let us remember.
We just need to get the corresponding model to do the operation just fine.

It's not enough.

But, while switching to a ORM tool has helped us reduce the memory cost of a large part of it, we still need to know what fields are in the model to use in the business logic, and if the newcomer takes over the project, Still need to look at the definition of the model to know what the field, so there is today to say the real protagonist: Sequelize-typescript

CRUD Ultimate Decorator Implementation Model definition

Sequelize-typescriptis based Sequelize on TypeScript an enhanced version of the implementation, discarding the previous tedious model definition, using adorners directly to achieve the purpose we think of.

How to use Sequelize-typescript

First because it is used TS , so the environment depends on the installation of things will be more:

# 这里采用ts-node来完成举例npm i ts-node typescriptnpm i sequelize reflect-metadata sequelize-typescript

Second, you also need to modify TS the file corresponding to the project to tsconfig.json enable TS the use of the adorner:

{  "compilerOptions": {+   "experimentalDecorators": true,+   "emitDecoratorMetadata": true  }}

Then you can start writing scripts for development, and the Sequelize difference is basically where the model is defined:

// /modles/animal.tsimport { Table, Column, Model } from 'sequelize-typescript'@Table({  tableName: 'animal'})export class Animal extends Model<Animal> {  @Column({    primaryKey: true,    autoIncrement: true,  })  id: number  @Column  name: string  @Column  weight: number}// 创建与数据库的链接、初始化模型// app.tsimport path from 'path'import { Sequelize } from 'sequelize-typescript'import Animal from './models/animal'const sequelize = new Sequelize('mysql://root:jarvis@127.0.0.1:3306/ts_test')sequelize.addModels([path.resolve(__dirname, `./models/`)])// 查询const results = await Animal.findAll({  raw: true,})// 新增const name = 'Niko'const weight = 70await Animal.create({  name,  weight,})

SequelizeThere are a few things that are different from the ordinary:

1. The definition of a model is defined by an adorner.
2. You Sequelize need to specify the corresponding path when instantiating an object model
3. A series of methods related to the model are supported Promise

If you encounter a hint during use, XXX used before model init you can try adding an operator to the front of the instantiation await until the connection to the database is established and then the operation is completed.

But it seems like the code Sequelize is a lot more than that, and at least two files to match, so what's the point?
The answer is OOP one of the important ideas: inheritance .

Using Sequelize-typescript to implement model inheritance

Because TypeScript the core developers included in C# the architect, so TypeScript can see a lot of similar C# traces in this aspect of the model, we can try to use inheritance to reduce some redundant code.

For example, we have animal two new tables based on the table, dog and, there bird must be a difference between them, so we have this definition:

CREATE TABLE dog (  id INT AUTO_INCREMENT,  name VARCHAR(14) NOT NULL,  weight INT NOT NULL,   leg INT NOT NULL,  PRIMARY KEY (`id`));CREATE TABLE bird (  id INT AUTO_INCREMENT,  name VARCHAR(14) NOT NULL,  weight INT NOT NULL,   wing INT NOT NULL,  claw INT NOT NULL,  PRIMARY KEY (`id`));

About dog We have a description of the leg number of legs, about which bird we have a wing claw description of the number of wings and claws.
Deliberately let the number of special fields of the two different, the province has the bar fine said can be added by type the field to distinguish between two different animals:p

If we want to use Sequelize the way, we will have some of the same field definition define three times to achieve, or write a little more flexible, the define use of the Object extraction to use the Object.assign way to achieve similar inheritance effect.

But you Sequelize-typescript can use inheritance directly to achieve the effect we want:

// 首先还是我们的Animal模型定义// /models/animal.tsimport { Table, Column, Model } from 'sequelize-typescript'@Table({  tableName: 'animal'})export default class Animal extends Model<Animal> {  @Column({    primaryKey: true,    autoIncrement: true,  })  id: number  @Column  name: string  @Column  weight: number}// 接下来就是继承的使用了// /models/dog.tsimport { Table, Column, Model } from 'sequelize-typescript'import Animal from './animal'@Table({  tableName: 'dog'})export default class Dog extends Animal {  @Column  leg: number}// /models/bird.tsimport { Table, Column, Model } from 'sequelize-typescript'import Animal from './animal'@Table({  tableName: 'bird'})export default class Bird extends Animal {  @Column  wing: number  @Column  claw: number}

One thing to note: each model needs to occupy a single file, and export default the way to export
In other words, our file structure is as follows:

├── models│   ├── animal.ts│   ├── bird.ts│   └── dog.ts└── app.ts

Thanks to TypeScript The static type, we are able to easily know the relationships between these models and what fields exist.
VS CodeThere are a lot of dynamic hints that you can get when you combine development, and findAll things like that create are always a hint:

Animal.create<Animal>({  abc: 1,// ^ abc不是Animal已知的属性  })

To use some behavior through inheritance.

The above example just shows how to reuse the model, but what if it is a well-encapsulated method?
Similar to getting all the data in the table, it might be enough to get the data in general JSON , which isfindAll({raw: true})
So we can do a simple package for operations like this, without the need for developers to call them manually findAll :

// /models/animal.tsimport { Table, Column, Model } from 'sequelize-typescript'@Table({  tableName: 'animal'})export default class Animal extends Model<Animal> {  @Column({    primaryKey: true,    autoIncrement: true,  })  id: number  @Column  name: string  @Column  weight: number  static async getList () {    return this.findAll({raw: true})  }}// /app.ts// 这样就可以直接调用`getList`来实现类似的效果了await Animal.getList() // 返回一个JSON数组

Similarly, because the top of our two Dog and Bird inherit from Animal , so the code can be used directly without changes getList .

const results = await Dog.getList()results[0].leg // TS提示错误

But if you use it like above, TS will prompt you incorrectly: [ts] 类型“Animal”上不存在属性“leg”。 .
Haha, what is this again? The attentive classmate may find that getList the return value is of a Animal[] type, so there is no leg attribute on the top, and Bird the two attributes are the same.

So we need to teach TS us about our data structure, so we need to Animal modify the definition and use the paradigm .
We do this by adding a generic definition to the top of the function, and adding a restriction that guarantees that the passed-in type must be inherited from, and Animal that the return value is converted to its type T .

class Animal {  static async getList<T extends Animal>() {    const results = await this.findAll({      raw: true,    })    return results as T[]  }}const dogList = await Dog.getList<Dog>()// 或者不作任何修改，直接在外边手动as也可以实现类似的效果// 但是这样还是不太灵活，因为你要预先知道返回值的具体类型结构，将预期类型传递给函数，由函数去组装返回的类型还是比较推荐的const dogList = await Dog.getList() as Dog[]console.log(dogList[0].leg) // success

Then the use leg of attributes will not be wrong, if you want to use the model, it is important to remember to add extends Animal the constraints, otherwise it TS will be considered here can be passed any type, it is difficult to ensure that the correct compatibility Animal , but inherited from Animal the must be compatible.

Of course as , you can override the parent class method in a subclass if you don't want to write the pattern here.
You don't need the full implementation logic, just get the return value and modify it to the type we want:

class Dog extends Animal {  static async getList() {    // 调用父类方法，然后将返回值指定为某个类型    const results = await super.getList()    return results as Dog[]  }}// 这样就可以直接使用方法，而不用担心返回值类型了const dogList = await Dog.getList()console.log(dogList[0].leg) // success

Summary

This article is just a primer, some simple examples, only to reflect the SQL Sequelize difference between the three (, and Sequelize-typescript ), Sequelize There are more high-level operations, such as mapping relationships, these are Sequelize-typescript reflected in the corresponding, and because the use of adorners, The code needed to implement these features will be much less and will look clearer.

Of course, ORM this kind of thing is not to say to a brain, if it is a beginner, from a personal level I do not recommend to use, because it will be less a chance to contact SQL
If the project structure is not very complex, or the expected future will not be too complicated, then it ORM makes no sense to use it, and it complicates the project structure.
And, to a certain extent, universal means compromise, in order to ensure that the results of multiple databases are consistent, may discard some of the unique features of the database, if explicitly need to use these features, it is ORM not too suitable for
Choose the most appropriate, know the meaning of using something

The final example is on GitHub: Notebook | Typescript/sequelize

Resources:

• mysql | npm
• sequelize
• sequelize-typescript | npm
• WAHT is the advantages of using An ORM