A full stack project on vue+mysql+express (iv) part of the explanation------sequelize

First, the introduction of the model

= require ('./db '// Get account this model

Second, the basic operation of the database (increase, delete, change, check)

Increase:

Account.create (data) then (doc =    = doc    res.cookie (' user_id ', user_id)     return Res.json ({      0,      data: {        user_name:user_name,        user_id:user_id,        user_info:user _info,        Avatar:avatar      })  })

By deleting:

function (req,res) {  //  empty all data   Poetrylist.destroy ({    //  Where: {id:123}    // Here we can delete the data that satisfies the condition according to the conditions we define  }). Then (doc) = {    return  Res.json ({      0,      data:doc    })  })})

Change:

Router.post ('/updatauserinfo ',function(req,res) {//Improve user InformationConst USER_ID =req.cookies.user_id Const User_info=req.body.userinfo account.update (User_info,//Here is the field we want to update, separated by commas    {      ' WHERE ': {        ' user_id ': user_id//Here's the condition we want to update that data.}}). Then (DOC)= {    returnRes.json ({code:0, data: {User_info:user_info}}) })})

Check:

Router.post ('/getuserinfo ',function(req,res) {//Improve user InformationConst USER_ID = req.body.user_id?req.body.user_id:req.cookies.user_id Account.findone ({' WHERE ': {' user_id ': user_id},//The Attributes property can specify return results, returning information for those fieldsattributes: [' user_name ', ' Avatar ', ' user_info ', ' User_fans ', ' attention ', ' poetry_num ', ' user_id ']}). Then (DOC)={Poetrylist.findall ({where: {' user_id ': user_id}, Order: [[' Create_temp ', ' DESC '],]}). Then (metadata)= {      returnRes.json ({code:0, data: {User_info:doc, list:metadata}}) })  })})

It's FindOne, and FindAll, FindByID, etc., refer to the official documentation

Three, model relations

Here we need to specify the relationship between the data tables, here to say the relationship, please see the official documents

1. One-to-one (Belongsto, HasOne)

single-to-one correlation is the exact association between the two models, which is achieved by a singular foreign key. The Belongsto Association represents a source model for foreign keys in a one-to-one relationship. varPlayer = This. Sequelize.define (' Player ', {/*attributes*/}), Team= This. Sequelize.define (' team ', {/*attributes*/}); Player.belongsto (Team); //a Teamid property is added to the player to maintain a relationship with the team primary keyby default, a foreign key that belongs to a relationship is generated from the name and primary key name of the target model. The target key is the column that is pointed to by the foreign key column of the source model on the target model. By default, the target key is the primary key that will belongsto the target model in the relationship. To use a custom column, use the Targetkey option to specify that a foreign key representing a one-to-one relationship exists in the target model with the Hasone Association. Although known as hasone associations, most1: The Belongsto Association is typically used in 1 relationships because Belongsto adds a foreign key in the source model, and Hasone adds a foreign key to the target model. HasOne differs from Belongsto in 1:In a 1 relationship, you can use Hasone or belongsto to define it. But their usage scenarios are different.

2. One-to-many (Hasmany)

one-to-Many association refers to a source model that connects multiple target models. Conversely, the target model will have a clear source.  var User = sequelize.define (' user ', {/**/})var Project = Sequelize.define (' project ', {/**/ /// Define Hasmany Association Project.hasmany (user, {as: ' Workers ') adds a projectid or project_id attribute to the user. There will be accessors Getworkers and setworkers in the instance of Project. This is a one-way association, and if there are other correlations between the two models, refer to the many-to-many relationships below. 

3. Many-to-many (Belongs-to-many)

Many-to-many (belongs-to-many) associated belongs-to-'userproject ' Userproject '}); This creates a new model userproject which will have the ProjectID and userid two foreign keys. Whether to use hump naming depends on the two tables associated with it. After defining the through option, Sequelize will attempt to generate the name automatically, but it must be logical. In this example, the method is added for user getusers, Setusers, adduser,addusers to Project, and Getprojects, Setprojects, AddProject, and AddProject s sometimes we rename the connected model and can also use the as implementation. 

Four, multi-table (we conduct a user information model and the article model of the Joint query) as a result

Const SEQUELIZE = require ('./db '= Sequelize.model (' account '= Sequelize.model (' poetrylist ' ) ' user_id ', Targetkey: ' user_id '}); Specify the foreign key fields that the model relationship can query

Router.get ('/getpoetrylist ',function(req, res) {//get a list of articlesPoetrylist.findall ({include: [{//perform a query by using the Include field to federate that modelModel:account,//Execution Modelattributes: [' user_name ', ' Avatar ', ' user_id ']//to select only certain properties you can use attributes: [' foo ', ' Bar ']}], attributes: [' Content ',      ' poetrylist_id ',      ' Recommend ',      ' Star ',      ' user_id ',      ' Create_temp ',      ' Guest_num ',      ' Transmit_content ',      ' transmit_user_id ',      ' Transmit_user_name ',      ' transmit_poetrylist_id ',      ' ID '], order: [//sort by using order[' Create_temp ', ' DESC '],//this; I sort by chronological order]}). Then (DOC)={Supportlist.findall ({//get a list of the current user's liking articlesWhere: {"USER_ID": req.cookies.user_id}}). Then (Suplist= {      //Data Processing       for(Let i = 0; i < doc.length; i++) {doc[i].datavalues.isattention=false        if(suplist.length) { for(Let j = 0; J < Suplist.length; J + +) {            if(doc[i].datavalues.poetrylist_id = = =suplist[j].datavalues.poetrylist_id) {doc[i].datavalues.isattention=true            }          }        } Else{doc[i].datavalues.isattention=false        }      }      returnRes.json ({code:0, Data:doc, suplist:suplist}) })  })})

The above query code transformation bit SQL statement is:

V. Things

Transaction is a subclass in Sequelize that implements transactional functionality, and you can create an instance of the class by calling the Sequelize.transaction () method. In Sequelize, automatic commit/rollback is supported, and users can be manually committed/rolled back.

SequelizeThere are two ways to use transactions:

• PromiseAutomatic commit/rollback based on the result chain
• The other is not automatically committed and rolled back, and the user controls the transaction

Managed transactions (Auto-callback)

Managed transactions are automatically committed or rolled back, and you can sequelize.transaction pass a callback function to the method to start a transaction.

It is important to note that in this way the callback function is passed transaction back to a promise chain that promise then catch cannot be invoked t.commit() or t.rollback() controlled in the chain. In this way, if all chains that use a transaction promise execute successfully, they are automatically committed, and if one of them fails, it is automatically rolled back.

returnSequelize.transaction (function(t) {//to ensure that all query chains have return returns  returnuser.create ({firstName:' Abraham ', LastName:' Lincoln '}, {transaction:t}). Then (function(user) {returnUser.setshooter ({firstName:' John ', LastName:' Boothe '}, {transaction:t}); });}). Then (function(Result) {//Transaction will automatically submit  //Result is a transaction callback in which execution results are used in the promise chain}).Catch(function(err) {//Transaction will automatically roll back  //err is the result of using an exception in a promise chain in a transaction callback});

Non-managed transactions (Then-callback)

A non-managed transaction requires you to force commit or rollback, and if you do not do so, the transaction remains suspended until it times out.

Starting an non-managed transaction is the same as calling a sequelize.transaction() method, but not passing a callback function parameter (you can still pass the option parameter). You can then manually control the transaction in the Promise method that it returns then :

Router.post ('/subscription ',function(req, res) {//Focus on featuresConst DATA ={user_id:req.cookies.user_id, target_id:req.body.target_id}//transaction is a subclass of Sequelize that implements transactional functionality.  //You can create an instance of the class by calling the Sequelize.transaction () method.   //in Sequelize, automatic commit/rollback is supported, and users can be manually committed/rolled back.   returnsequelize.transaction ({autocommit:true}, T= {    returnAttentionlist.findone ({' WHERE ': {        ' user_id ': req.cookies.user_id,' target_id ': req.body.target_id}},{transaction:t}). Then (Doc= {      if(!doc) {        returnAttentionlist.create (Data,{transaction:t}). Then (ret = {          returnAccount.findone ({' WHERE ': {' user_id ': req.cookies.user_id}}, {transaction:t}). Then (RETs= {            //increment self-increment function            returnRets.increment (' attention '). Then (RETSS = {                            return' Success '})}) }, {transaction:t}). Then (Docs= {          returndocs}) } Else {        return100}) . Then (Result= {    returnRes.json ({code:0, Data:result}) })})

End: Altogether wrote four articles, also did not know to say clearly has not, many thought should say, everybody should know, therefore did not have the very detailed explanation, if still have unclear place can refer to GitHub source code: Https://github.com/songdongdong123/vue_chat

A full stack project on vue+mysql+express (iv) part of the explanation------sequelize