Nodejs+sequelize working with MySQL database

Preface:

I am not very familiar with MySQL, only the command line of simple additions and deletions to check. Some ideas may not be in place please understand.

Sequelize is an ORM-based framework developed for node. JS and io.js that supports databases such as PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL.

Directory:

1. Install MySQL

2. Create directories and files

3. Connect to the database and define the model

4, Operation database (Increase and deletion of check)

5, Sequelize framework of the API Universal solution

1. Install MySQL

Installation Environment: Win7 x64

• go to the official website to download the latest version of MySQL, after entering the official website, click MySQL Community Server, you can see the latest MySQL version of the various download versions (such as 32-bit or 64-bit, MIS format the latter zip format) i downloaded the 5.7.16 version of the ZIP format, and then unzip it.
• Add an environment variable: Simply add your extracted file bin path to the PATH environment variable.
• Open cmd into the compressed bin file under:

Execution: mysqld--initialize This must be done, directly initialize MySQL, used to generate the Data folder

• After extracting the folder root directory there is a configuration file My-default.ini file:

Basedir fill in the file path you extracted, such as: Basedir = "D:\mysql\mysql-5.7.16-winx64"

DataDir fill in the path to your data file: DataDir = "D:\mysql\mysql-5.7.16-winx64\data"

Port General 3306

Add below [mysqld]: skip-grant-tables (This sentence is mainly used to start MySQL omitted user name and password, the web said that the first time to start MySQL default is no user name and password, but I suggest you add this sentence, etc. after setting the user name and password , in removing this sentence)

After modification, save.

• Execute in Bin directory mysqld-install installing MySQL
• can be used after the installation is successful net start MySQL command to start the MySQL server.
• The MySQL server start and stop commands are:

　　　　STOP: Enter net stop MySQL

Startup: input net start MySQL

• Go to MySQL database:

Enter MySQL directly in the bin directory and enter MySQL database (no user name and password required)

• Set user name and password:

After entering the MySQL database, execute:

　　　　 Update user Set Authentication_string=password (' 123456 ') where user= ' root ' and Host = ' localhost ';here the username is set to root and the password is 123456.

• Exit the database, execute quit quit, and then comment out the skip-grant-tables of the configuration file
• To restart the MySQL service, first execute net stop MySQL in the Execute net start MySQL
• Re-enter the MySQL database, you need a user name and password, enter the command as follows:

mysql-uroot-p123456

2. Creating Directories and Files

Write a small case to connect to the database, insert data into the table, update the data in the table, and query the data in the table.

Adopt Express Framework

The project documents are as follows:

is generated directly through EXPRESS-E Mysql-demo.

to create a new MySQL configuration file:

Create the Config folder under the project's root directory, config/index.js

Add the following code to the Index.js file:

'Use Strict'varall ={sequelize:{Username:'Root', Password:'111111', Database:'Test', Host:"localhost", dialect:'MySQL', define: {underscored:false, timestamps:true, paranoid:true        }    }}; Module.exports= ALL;

To define a data table model:

Create a new Model folder under the project root directory, model/user.js

Add the following code to the User.js file:

'Use Strict'Module.exports=function (sequelize,datatypes) {varUser = Sequelize.define ('User', {id:{Type:DataTypes.UUID, PrimaryKey:true, Allownull:false, defaultValue:DataTypes.UUIDV1}, name:{Type:DataTypes.STRING}, a ge:{Type:DataTypes.INTEGER}, height:{Type:DataTypes.INTEGER}, weight : {Type:DataTypes.INTEGER}},{freezetablename:true    }); returnUser;};

Freezetablename:true  This option indicates that the data in the database is consistent with the program, otherwise the name of the table in the database is named in plural form

To connect to a database:

Create a new Sqldb folder under the project root directory, sqldb/index.js

Add the following code to the Index.js file:

'Use Strict'varConfig = require ('.. /config');varSequelize = require ('sequelize');vardb ={sequelize:Newsequelize (Config.sequelize.database,config.sequelize.username,config.sequelize.password,config.sequelize)} ;d B. User= Db.sequelize.import ('.. /model/user.js'); Module.exports= DB;

Finally, the introduction of the Sqldb/index.js file in the App.js file completes the connection of the database and the establishment of the data table.

Add the following code to the App.js file:

var sqldb = require ('./sqldb ');

Sqldb.sequelize.sync ({force:false}). Then (function () {
    Console.log ("Server successed to start");
}). catch (function (err) {
    Console.log ("Server failed to start due to error:%s", err);
});

The Sqldb.sequelize.sync interface is used to synchronize the model to the database.

4. Operational database (add and revise)

To insert data into the user table:

Add the following code to the Routes/index.js file:

Router.post ('/add/user ',function(Req,res,next) {Console.log ("+++++++++++++++++++++++"); varSaveuser ={name:req.body.name, age:req.body.age, Height:req.body.height, Weight:req.body.weight    }; returnDb.sequelize.transaction (function(t) {Console.log ("+++++++++++++++++++"); returnuser.create (saveuser,{transaction:t}). Then (function(Result) {res.send (result); }).Catch(function(Err) {Console.log ("Error occurred:" +err);    }); })});

Querying data in a data table (in the ID field):

Add the following code to the Routes/index.js file:

Router.get ('/get/user/:userid ',function(req,res,next) {    return Db.sequelize.transaction (function(t) {        return  user.findone ({            ID: Req.params.userid        },{            transaction:t        }). Then (function(result) {            Res.send (result);        }). Catch (function(err) {            Console.log ("error:" + Err)     ;});});

Update data:

Router.post ('/update/user/age ',function(req,res,next) {returnDb.sequelize.transaction (function(t) {returnUser.findbyid (req.body.userid,{transaction:t}). Then (function(user) {returnuser.update ({age:req.body.age},{transaction:t}). Then (function(Result) {res.send (result); }).Catch(function(Err) {Console.log ("Error occurred:" +err);        }); })    })});

Here is a bit, do not know is not a bug, I was first query, and then update, if the ID to query, found that the data table has been the first data, the rogue changed to FindByID form query can be. (If only query, do not update, use FindOne can, temporarily do not know what is the reason)

5.sequelize Framework API Pan-Solution

Sequelize currently I use the API is divided into two categories, a class belonging to Sequelize, a class belongs to model.

Sequelize class is to refer to the sequlize module to get a top-level object, we create an instance through it, sequlize sequelize most commonly used API is transaction (start Thing API interface), the interface of the general operation table will call this interface first, As in the above code:

Return db.sequelize.transaction (function (t) {        return User.findone ({    ...    })});    

To query the table data, first call Sequelize's transaction interface to start things.

The API for the Model class Api:model class is primarily used for internal operations of data tables.

such as FindOne, find data.

Create to insert data.

Demo Address

Nodejs+sequelize working with MySQL database