Node.js SQL database operation (ORM framework, serialize module and case display)

Posted by zoreli on Mon, 20 Dec 2021 03:10:43 +0100

ORM framework

ORM Object Relational Mapping (Object Relational Mapping) is a technology to solve the mismatch between object-oriented and relational databases. Through the metadata describing the mapping between objects and databases, the objects in the program are automatically persisted to the relational database. Its function is to make a mapping between relational databases and objects When operating the database, you don't need to deal with complex SQL statements, just operate it as usual.

Persistence is to save data (such as objects in memory) to a permanent storage device (such as disk). The main application of persistence is to store the data in memory in a relational database. Of course, it can also be stored in disk files, XML data files, etc.

  • ORM is to associate the objects in the business entity with the relational data in the relational database and encapsulate the database at a high level
  • Object relational mapping (ORM) systems generally exist in the form of middleware
  • The main disadvantage is that it will sacrifice the execution efficiency of the program

ORM technical features:

  1. It improves the development efficiency. ORM can automatically map the fields and attributes between the Entity object and the Table in the database, so we may no longer need a dedicated and huge data access layer.
  2. ORM provides the mapping to the database without sql direct coding, and can obtain data from the database like an operation object.

Sequenize module

  • Serialize is a Promise based node that supports asynchronous operations JS ORM framework, which can be used in node JS environment is easy to use and supports multiple SQL dialects.
  • It supports Postgres, MySQL, SQLite, Microsoft SQL Server and other databases, and has powerful transaction support, association, reading and replication functions.
  • Very suitable for node JS back-end database storage interface, which helps to improve node JS application development efficiency.

characteristic:

  • Powerful model definition and support for virtual types
  • Support perfect data verification and reduce the verification pressure at the front and rear ends
  • Sequenize's query is very comprehensive and flexible

Basic use of serialize

Install the serialize Library: NPM install -- save serialize

Basic usage:

1. Establish connection:

  • The first method: pass parameters separately (most)
const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /*It can be any of 'mysql', 'mariadb', 'postgres' or' mssql ' */
});
  • The second method: pass the connection URL
const Sequelize = require('sequelize');
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

Example:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('testmydb', 'root', 'abc123', {
 	//Connection options
 	host: 'localhost', // Database address
  	dialect: 'mysql', // Specify the database type to connect to
  	pool: {
	    max: 5, // Maximum number of connections in the connection pool
	    min: 0, // Minimum number of connections in the connection pool
	    idle: 10000 // If a thread is not used for 10 seconds, the thread is released
  }
});
//Test connection
sequelize
  .authenticate()
  .then(() => {
    console.log('The connection was established successfully');
  })
  .catch(err => {
    console.error('Failed to connect to database:', err);
  });

2. Define model:

  • ① Use serialize Model. Init (attributes, options) function
const Model = Sequelize.Model;
class User extends Model {}
User.init({
  // Property settings
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  email: {
    type: Sequelize.STRING
    // allowNull (null allowed) defaults to true
  }
}, {
  sequelize,
  modelName: 'user'   //Model name defined here
  // option
});
  • ② Use serialize define(‘name’, {attributes}, {options})
const User = sequelize.define('user', {  // user is the model name
  // attribute
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  email: {
    type: Sequelize.STRING
  }
}, {  // option
});

3. Synchronize model with database (optional)

User.sync({ force: true }).then(() => {
  // The table in the database is consistent with the model definition
  return User.create({
    name: 'Xiao Li',
    email: 'xiaoli@abc.com'
  });
});
  • Option force: true means that if the table already exists, the original table will be deleted before creating a new one

4. Add operation:

// Create a new user
User.create({ name: "Xiao Tong", email: "xiaotong@abc.com" }).then(() => {
  console.log("Added");
});

5. Delete operation:

// Delete the user named Xiao Hong
User.destroy({
  where: {
    name: "Xiao Hong"
  }
}).then(() => {
  console.log("Deleted");
});

6. Change operation:

// Change the mailbox of users without mailbox to it@abc.com
User.update({ eamil: "it@abc.com" }, {
  where: {
    email: null
  }
}).then(() => {
  console.log("It has been changed");
});

7. Find operation:

// Find all users
User.findAll().then(users => {
  console.log("All users:", JSON.stringify(users, null, 4));
});

Promises and async/await

  • Serialize uses Promise to control the asynchronous operation process. then() method can be used for adding, querying, modifying and deleting operations.
  • If node JS version support, you can use ES2017 async/await syntax to write asynchronous calling code for serialize.
  • All Promise objects of serialize are also Promise objects of Bluebird, and can also be operated using Bluebird API.
  • The Promise object returned by serialize can also be operated through the co module.

Sequential multi table Association

Source and destination:

const User = sequelize.define('user',{
  name: Sequelize.STRING,
  email: Sequelize.STRING
});
const Project = sequelize.define('project',{
  name: Sequelize.STRING
});
User.hasOne(Project);

Foreign key:

  • Serialize when you create an association between models, foreign key references with constraints are automatically created
  • Creating a relationship between the Task and the User model inserts the foreign key userId in the tasks table and uses the foreign key as a reference to the users table
const Task = sequelize.define('task', {
	title: Sequelize.STRING 
});
const User = sequelize.define('user', {
	username: Sequelize.STRING
});
User.hasMany(Task); // Automatically add userId to Task model
Task.belongsTo(User); //The userId is also automatically added to the Task model

One to one Association:

  • belongsTo Association
  • The belongsTo association has a foreign key with a one-to-one relationship on the source model

Example: Player uses the foreign key teamId of the players table as part of the Team

const Player = sequelize('player',{
	/* Attribute definition */
});
const Team = sequelize('team', {
	/* Attribute definition */
});
Player.belongsTo(Team); // Add the teamId property to the Team model to save the Team's primary key value

One to one Association:

  • hasOne Association
  • hasOne association is the association of foreign keys with one-to-one relationship on the target model

Example: adding projectId attribute to User model

const User = sequelize.define('user', {
	/* ... */
});
const Project = sequelize.define('project',{
	/* ... */
});
Project.hasOne(User)   // Unidirectional Association

Differences between hasOne and belongsTo:

  • hasOne inserts an association key into the target model
  • belongsTo inserts an association key into the source model

Example:

const Player = sequelize.define('player', {/* Attribute definition */});
const Coach = sequelize.define('coach', {/* Attribute definition */});
const Team  = sequelize.define('team', {/* Attribute definition */});
  • When information about the association exists in the source model, you can use the belongsTo Association. In this example, Player is applicable to the belongsTo association because it has the teamId column.
Player.belongsTo(Team) // teamId will be added to the source model Player
  • When information about the association exists in the target model, you can use hasOne Association. In the example, the Coach is suitable for hasOne association because the Team model stores the information of its Coach as the Coach ID column.
Coach.hasOne(Team) // coachId will be added to the target Team model

One to many association:

  • One to many association connects one source to multiple targets, and multiple targets are connected to the same specific source:
const User = sequelize.define('user', {/* ... */})
const Project = sequelize.define('project', {/* ... */})
Project.hasMany(User, {as: 'Workers'})
  • hasMany() is used to define a one to many association
  • To associate records on different columns, you can use the sourceKey option to specify the source key:
const City = sequelize.define('city', { countryCode: Sequelize.STRING });
const Country = sequelize.define('country', { isoCode: Sequelize.STRING });
// Countries and cities can be connected according to the country code
Country.hasMany(City, {foreignKey: 'countryCode', sourceKey: 'isoCode'});
City.belongsTo(Country, {foreignKey: 'countryCode', targetKey: 'isoCode'});

Many to many association:

  • Many to many associations are used to connect sources to multiple targets, and targets can also be connected to multiple sources:
Project.belongsToMany(User, {through: 'UserProject'});
User.belongsToMany(Project, {through: 'UserProject'});

Book borrowing management case

This case is used to record the borrowing information of books. In order to simplify the experimental process, There are only two tables books and readers. There is a one to many relationship between them. A book can correspond to multiple readers and use the scheme of automatically establishing the table structure. In the actual application development, the data part is often separated as the model part, which is conducive to the decoupling and expansion of each module.

1. Define book data model:

const Sequelize = require('sequelize'); 
module.exports = (sequelize) => {
    var Book = sequelize.define('book', {
        isbn: { type: Sequelize.STRING },
        name: { type: Sequelize.STRING },
        author: { type: Sequelize.STRING },
        press: { type: Sequelize.STRING },
        price: { type: Sequelize.DECIMAL(10, 2) },
        pubdate: { type: Sequelize.DATEONLY }
    });      
    return Book;
};

2. Define the reader data model:

const Sequelize = require('sequelize'); 
module.exports = (sequelize) => {
    var Reader = sequelize.define('reader', {
        name: { type: Sequelize.STRING },
        mobile: { type: Sequelize.STRING },
        email: { type: Sequelize.STRING }
    });
    return Reader;
};

3. Synchronous data model:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('testmydb', 'root', 'abc123', {
  //Connection options
  host: 'localhost', // Database address
  dialect: 'mysql', // Specify the database type to connect to
  define: {
    'charset':'utf8'     //Solve Chinese input problem
  },
  pool: {    // Establish connection pool
    max: 5, // Maximum number of connections in the connection pool
    min: 0, // Minimum number of connections in the connection pool
    idle: 20000 // If a thread has not been used within 20 seconds, release the thread
  }
});
const Book = require('./book_model')(sequelize);//Import Book model
const Reader = require('./reader_model')(sequelize);//Import Reader model
Book.hasMany(Reader);   //A book has multiple readers
Reader.belongsTo(Book);  //A reader corresponds to a book
sequelize.sync();    //Automatically synchronize all models, which should be used when using association. After adding data for the first time, you can comment it out
exports.Book = Book;
exports.Reader = Reader;

4. Add data:

const Book  = require('./mydb').Book;
const Reader  = require('./mydb').Reader;
async function addBook() {
  const result = await Book.create(
    {              
      isbn:"9787115474582",
      //(omitted here)
  },
    { include: [Reader] } //Specify the association relationship, and the reader data is automatically inserted into the reader table
  );  
  return result;  
}
addBook().then(data => {
  console.log("Added data:", JSON.stringify(data, null, 4)); // Get the returned content
}).catch(error => {
  console.log(error);   // Capture error
});

5. Get data:

const Book  = require('./mydb').Book;
const Reader  = require('./mydb').Reader;
async function getBook() {
  const result = await Book.findAll();  
  return result;  
}
getBook().then(data => {
  console.log("Queried data:", JSON.stringify(data, null, 4)); // Get the returned content
}).catch(error => {
  console.log(error);   // Capture error
});

6. Change data:

const Book  = require('./mydb').Book;
const Reader  = require('./mydb').Reader;
async function updateBook() {
  const result = await Book.update(
    { price:98.00 },
    { where: { id: 1 } }    
  );  
  return result;  
}
updateBook().then(data => {
  console.log("To modify data ID:", JSON.stringify(data, null, 4)); // Get the returned content
}).catch(error => {
  console.log(error);   // Capture error
});

7. Delete data:

const Book  = require('./mydb').Book;
const Reader  = require('./mydb').Reader;
async function delBook() {
  const result = await Book.destroy(
    { where: { id: 1 } }    
  );  
  return result;  
}
delBook().then(data => {
  console.log("Delete data ID:", JSON.stringify(data, null, 4)); // Get the returned content
}).catch(error => {
  console.log(error);   // Capture error

Topics: node.js Database MySQL orm sequelize

Hot Topics

©2024 Programmer Think