As we all know, the advent of Node has given front-end developers more power and power. Part of this power is Sequelize, an ORM that maps form data in a database to JS objects, making it easy to manipulate the database using javascript.

Establish a connection

To use a database, you first need to connect to it.

  1. Sequelize will set up connection pooling at initialization, so if you are connecting to a database from a single process, it is best to create only one instance per database. If you want to connect to the database from multiple processes, you must create one instance for each process, but each instance should have a maximum connection pool size divided by the number of instances.
const Sequlize = require('seqlize');

const sequelize = new Sequelize('database'.'username'.'password', {
    host: 'lcocalhost'.dialect: 'mysql'|'sqlite'|'postgres'|'mssql'.pool: {
    max: 5.min: 0.acquire: 30000.idle: 10000
  },
  
   storage: 'path/to/database.sqlite',})Copy the code
  1. Constructor arguments:
  • Database: indicates the database name.
  • Username: the username used to authenticate the database.
  • Password: indicates the user password.
  • Host: indicates the host of the relational database.
  • Port: indicates the port number of the host.
  • Dialect: Type of the database to connect to. eg: mysql
  • DialectModulePath: If specified, load the dialect library from this path. For example, if you want to use pg.js instead of PG when connecting to a PG database, you should specify “pg.js” here.
  • Storage: For SQLite only (SQLite is a lightweight database). The default is’ : the memory: ‘.
  1. Test connection:.Authenticate () completes the test.
sequelize
  .authenticate()
  .then((a)= > {
    console.log('Success.');
  })
  .catch(err= > {
    console.error('Failed', err);
  });
Copy the code

Create the Module

  1. Concept: A model can be understood as a table in a database.
  2. Definition: Define using the define() function. For details, see SequeLize.
  3. In Sequelize-typescript, you can use decorators to quickly and easily define modules.
import {Table, Column, Model, HasMany} from 'sequelize-typescript';

@Table
class Person extends Model<Person> {

    @Column
    name: string;
    
    @Column
    birthday: Date;
    
    @HasMany((a)= > Hobby)
    hobbles: Hobby[];
    
}
Copy the code
  • Primary Key: This can be implemented in two ways@Column({primaryKey: true}) || @PrimaryKey @Column(together).
  • Timestamps: timestamps. A complete, verifiable piece of data that represents the existence of a piece of data prior to a particular time, usually a sequence of characters that uniquely identifies a moment in time. Using digital signature technology to generate data, signature object includes the original file information, signature parameters, signature time and other information. The default is false.
  • @ CreatedAt, @ UpdatedAt, @ DeletedAt:
 @CreatedAt
  creationDate: Date;
 
  @UpdatedAt
  updatedOn: Date;
  
  @DeletedAt
  deletionDate: Date;
Copy the code
  • Add method:
import {Sequelize} from 'sequelize-typescript';
 
const sequelize =  new Sequelize({
        ...
        modelPaths: [__dirname + '/**/*.model.ts']});// or
sequelize.addModels([__dirname + '/**/*.model.ts']);
Copy the code

The use of the Module

  1. Data query: Data query returns model instances.
  • Find: Finds a specific element in the database. Returns NULL if it cannot be found
import User from '.. /module/User';

public async fineUserById( id ) {
    const users = await User.find({
        where: { id: id}
    });
    console.log(users);  //user is the data that matches the search criteria
}
Copy the code
  • FindOne: returns the first data that matches the search criteria.
  • FindOrCreate: Returns the element that matches the query condition if the element exists. If not, such an element is created based on the query criteria.
  • FindAll: Finds all data that matches the conditions. The above are several common methods to search for data. For specific methods, please refer to relevant documents

Raw SQL statement query

The methods encapsulated in Sequelize can be used to query data, but for more complex queries or developers who are used to writing SQL statements, it is easier to query data using raw SQL statements. Sequelize also supports passing in SQL statements to complete the query.

The query is completed using the sequelize.query() function, which supports passing in two arguments. The first argument is a string of SQL statements, and the second argument is passed in an Option object to do some query configuration. For detailed parameter Settings, see the official website

// Use the SQL statement to complete the query
const data = await sequelize.query('SELECT * FROM Student WHERE id in(SELECT studentId FROM Class WHERE id = 1) LIMIT 6');
Copy the code

When using SQL statements to query directly, you need to pay attention to security issues (SQL injection) when there are variables in statements. Sequezlize takes this into account, so we pass in some variables with the second argument to replace the variables in the original SQL statement, avoiding SQL injection.

// id is a variable
let id = 1;
const data = await sequelize.query(
    'SELECT * FROM Student WHERE id in(SELECT studentId FROM Class WHERE id = :id) LIMIT 6', {
    replacements: { id: id },
    type: sequelize.QueryTypes.SELECT
});
Copy the code

That’s the basic use of Sequelize, and I’ll cover other aspects of Sequelize in a future article.