The Egg project quickly creates a sequelize data table model

This is the 18th day of my participation in the August Genwen Challenge.More challenges in August

When developing a project with Node, it is inevitable to use a Model to map table structures and fields in a database.

What does that mean?

Database operations

Mysql > select * from user where user name = admin; select * from user where user name = admin;

SELECT * FROM users WHERE name='admin'
Copy the code

In the Node project, when we need to add, delete, change and check the operation in the database, it is nothing more than through the network to send the ADD, delete, change and check SQL statement to achieve.

In doing so, there are problems

• On the one hand, developers are required to master the basic knowledge of SQL and be able to skillfully write SQL statements at the code level.
• On the other hand, it also requires developers to have some knowledge of network security to avoid common security vulnerabilities such as injection SQL injection.

Also, the resulting code is low-level and may not fit the programmer’s mind.

So the community has ORM.

ORM

Some of you might think, well, what’s the difference between a table and a table that we use on our pages?

Below is a data sheet about websites

+----+--------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | taobao | | | | CN 13 https://www.taobao.com/ | 3 | novice tutorial CN | | 4689 | | http://www.runoob.com/ | | 4 weibo | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | +----+--------------+---------------------------+-------+---------+Copy the code

If the data in the table were placed in a front page, it might look something like this:

Our table is rendered from a normal javascript array object structure. Such as

var table = [
    {id: 1.name: 'Google'.url: 'https://www.google.cm/ '.alexa: 1.country: 'USA'}... ]Copy the code

It would be nice if the operation of adding, deleting, modifying and querying a database could be the same as that of an ordinary javascript array

Then came the object-Relational Mapping (ORM) technology, which mapped the table structures in the database into objects. If you want to operate the database, you can directly operate the mapped objects.

How do you map a table in a database to an object in your code?

This is where the ORM framework comes in.

Use egg-sequelize-Auto to quickly generate table models

Sometimes, our database already has a table, but the corresponding model (map object, hereinafter referred to as model) in the code has not been created. Is there any way to generate a model from a good table?

We chose the ORM framework sequelize here. It is a Promise-based Node ORM framework that supports Postgres, MySQL, MariaDB, SQLite, and more.

The steps we’re going to take are as follows,

1. Globally install egg-sequelize-auto with mysql2

npm install -g egg-sequelize-auto

npm install -g mysql2

2. Go to project folder

cd egg-demonstrate

3. Generate the required table structure with the following command

egg-sequelize-auto -o "./model" -d databaseName -h localhost -u username -p port -x password -t tableName

4. Parameters that

-h, –host DATABASE IP address [required]

-d, –database Database name [required]

-u, –user Indicates the user name

– x – pass the password

– p – port port

-c, –config config file [require json file]

-o, –output Target folder

-t, –tables Specifies the name of the data table

-e, –dialect The dialect/engine that you’re using: postgres, mysql, sqlite

example

Suppose I have the following development environment

• The project folder is egg-Demonstrate
• The database address is 192.168.0.205
• The database name is Digapisids
• The data table name is userlogs
• The username root
• Password is 123456

For example, generate the userlogs model

Egg -sequelize-auto -o "./model" -h 192.168.0.205 -d Digapisids -u root -x 123456 -p 3306 -t userlogs

Execute the above statement and in the model folder you can see that a file userlogs.js is generated, which is the model for the table of userlogs (userlogs)

If the database has a large number of tables, including userlogs, users, whitelist, blacklist

I want to generate all the table models at once

Egg -sequelize-auto -o "./model" -d digapisids -h 192.168.0.205 -u root -x 123456 -p 3306 -e mysql

In the Model folder, you can see that many files are generated

How to use the generated model to add, delete, change and check

For example, query all users in the user table

this.ctx.model.models.user.findAndCountAll({
      offset,
      limit,
      order: [['id', 'desc']],
      attributes: ['id', 'username', 'is_superuser','date_joined']
    });

Copy the code

For other cases, see the documentation

Example: www.npmjs.com/package/egg…

Yards cloud: gitee.com/eosgravity/…