I’m participating in nuggets Creators Camp # 4, click here to learn more and learn together!

preface

Using Koa as the Web framework, three articles will explain node.js programming interface, server side rendering (SSR) and MySQL database

🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (a) | interface
🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (2) | rendering of a service
🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (3) | article database

Having covered interfaces and server-side rendering in the previous two articles, we’ll move on to the most important aspect of the back-end — the database

Preliminary knowledge

The foundation for this article is that you are familiar with the content of the first article

🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (a) | interface

🚩 Learning Objectives

Koa + Mysql to achieve four interfaces, data stored in the data

New User (POST request)
Querying user information (GET request)
Changing the user password (GET request)
Delete user (GET request)

What is a database?

“A warehouse that organizes, stores, and manages data according to data structures”

🌰 a simple example

Database storage information is required to register an account
The login account needs to read the database information
Changing the password requires modifying the database information

Database has four main operations [add, delete, change, query]

Add: Adds one or more pieces of data to the database
Delete:… Delete…
Change:… Change……
Check:… Query…

Name a few common databases

The database	cost	model	Download address
MySQL	Community Edition free	Relational database	Downloads.mysql.com/archives/in…
Oracle	charge	Relational database
SQL Server	charge	Relational database
MongoDB	Community Edition free	Nonrelational database	www.mongodb.com/try/downloa…
Redis	Community Edition free	Nonrelational database	redis.io/download
Access	charge	Relational database

There are two kinds of database models: relational database and non-relational database

What is the difference between a relational (SQL) and a non-relational (NOSQL) database?

The difference between the two is very much, the detailed difference please baidu

🌰 take an inappropriate chestnut

Relational: Data is stored in tabular form, like the table above, with rows and columns

Nonrelational: Data stores combinations of information, such as objects and key-value equivalents

This article takes MySQL as an example to explain the back-end interface and database use in combination with Koa

2. Preparation

🚧 MySQL 8.0

MySQL not installed? 🤔

👉 MySQL 8.0 download address

Installation steps refer to the following digging friends of the article, I will not repeat the writing

Environment installation — MySQL installation

Windows Install mysql in detail

💥 must remember the password of the setting!

Run a command in the CMD window to check whether the installation is complete

mysql -V
Copy the code

Output the MySQL version to indicate that the installation is complete

mysql -u root -p
Copy the code

As shown, the link is successful

Verify that MySQL is running properly and close the command line. Software operation is used instead of command line

🚦 Startup error

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)
Copy the code

Solution: Check whether the MySQL service is started

🚧MySQL management tool

Navicat PreMium 15 is recommended for a 14-day free trial

Run Navicat and create a new connection
Fill in the link information

Connection name: Write whatever you want

Host: localhost

Port: 3306

User name: root

Password: password set during installation

Click the [Test connection] button in the lower left corner of 💡. If “Connection succeeded” pops up, the connection is ok
Expand the connection to see some system databases for MySQL, and later create a new library of your own
Execute SQL statement
- Right – click on the data -> New query
- Enter the SQL statement in the query window that is displayed
- Click on therunbutton
💡 Create a database locally by right-clicking or using a visual window

💥 Remember after executing SQL [right click – refresh]

Edit the data

To edit the data in the form, you need to click the [mark] below to submit the change, otherwise it will not take effect

🚧 Node. Js

Node.js version >= 10

Koa-generator and Koa need to be installed

If you are not familiar with Koa, this article is recommended

🌰 to name a few, using Koa two days to start Node.js backend development (a)

🚧 Request tool

I use Hoppscotch (formerly Postwoman) you can also use other request tools, Postman, Apifox, ApiPost, etc

👉 making

3. Some knowledge

Before you start writing code, learn some database knowledge and concepts.

😜 is simple and popular

3.1 Fields and Records

MySQL stores data in tables where rows are called records and columns are called fields

🌰 for example

In Excel, the age column is usually referred to as the age field in the database.

In Excel, it’s usually called a triple row, but in the database it’s called a triple record.

A database can have multiple tables that can be associated with each other through fields

🌰 for example

Table A and table B are associated through their respective fields 1. With association, the data can be checked jointly. For example, to query the age and gender of Zhang SAN in the table below, it is necessary to associate the age table and gender table.

Age table

The name	age
Zhang SAN	18

Gender table

The name	gender
Zhang SAN	male

💥 note:

Do not use Chinese table and field names in actual development

3.2 the primary key

The full name is “PRIMARY KEY constraint,” and typically one PRIMARY KEY is set for each table.

💡 is intended to ensure the uniqueness of this record. Primary keys are required to establish associations between tables

Only one primary key can be defined per table
The primary key cannot be repeated and cannot be NULL
The combination of two fields can be used as a primary key

🌰 for example

Use the name in table 2.1 as the primary key for the age table and the name as the primary key for the gender table. The two tables can be associated by name

In actual development, due to the same name problem, the name is not used as the primary key

3.3 Data Types

There are also data types and lengths in databases, and field types and lengths are typically specified when you create a table

Several commonly used data types

type	The size of the	format	use
int	4 Bytes		Large integer value
float	4 Bytes		Single-precision floating point value
double	8 Bytes		Double – precision floating – point value
varchar	0-65535 bytes		Variable length string
blob	0-65 535 bytes		Long text data in binary form
date	3 Bytes	YYYY-MM-DD	Date value
datetime	8 Bytes	YYYY-MM-DD HH:MM:SS	Mixes date and time values
timestamp	4 Bytes	YYYYMMDD HHMMSS	The time stamp

For details on the value range, see 👉 MySQL data types in the rookie tutorial

🌰 for example

# define3Bit integer variable age ageint(3) # define20Bit field string name Namevarchar(20)
Copy the code

💡 Note: the length M in int(M) is independent of the size of the numeric type you store

Explain in detail

3.4 SQL writing

From the previous section, you know that SQL statements operate on tables, fields, and records (ps: There are others, of course, but not for this article 😜)

From the increase, delete, change and check four aspects to see the WRITING of SQL statements, simple split SQL statements, composed of 3 parts

action(Create a table, modify the table, view the table….)
The operating table(table name, field…)
conditions(where)…

The following statements can be executed in Navicat to see the effect

3.4.1 track to add

Create a database – name: example
```
CREATE DATABASE example;
Copy the code
```

Create table name: people, age (name is primary key)

CREATE TABLE people(name varchar(25) PRIMARY KEY, age int(3));
Copy the code

Add field – Add sex field to people table

ALTER TABLE people ADD sex varchar(2);
Copy the code

Added a record – added “Zhang SAN, 18, male” to people table

insert into people (name, age, sex) values('Joe'.18.'male');
Copy the code

💡 Keywords in SQL statements are case insensitive

3.4.2 change

Change the table name – change the people table name to person
```
ALTER TABLE people RENAME TO person;
Copy the code
```
Change fields – Change the age field in the person table to years, the same type
```
ALTER TABLE person CHANGE age years int(3);
Copy the code
```
Change fields – Change the length of the YEARS field in the person table to 2
```
ALTER TABLE person MODIFY years int(3);
Copy the code
```
💡 Use CHANGE to CHANGE the field name and MODIFY the data type.

😁 detailed difference between self baidu
Change records – Change the age of “Joe” in person to 20
```
UPDATE person SET years=20 WHERE name='Joe'
Copy the code
```
Years =20, sex=’ female ‘

Rule 3.4.3 check

Query records (all results) – Query all data in the PERSON table
```
SELECT * FROM person
Copy the code
```

Select * from person where name = ‘age’

SELECT years FROM person WHERE name='Joe'
Copy the code

Select * from person where name = ‘zhang’ and sex = ‘male’

SELECT * FROM person WHERE name='Joe' AND sex='male'
Copy the code

Query records – Multi-table search

Select * from 'person'; select * from 'mail';CREATE TABLE mail(name varchar(25) PRIMARY KEY, msg varchar(200)); Insert a piece of dataINSERT INTO mail (name, msg) values('Joe'.'Please come over this weekend! '); # Check everyone's age and informationSELECT person.name, person.years, mail.msg FROM person, mail WHERE person.name = mail.name
Copy the code

For example, inner join left join right join

😁 detailed usage baidu by oneself

3.4.4 delete

Delete field – Delete sex field from person table

ALTER TABLE person DROP COLUMN sex;
Copy the code

Delete Record – Deletes the record named “Joe” in person
```
DELETE FROM person WHERE name='Joe';
Copy the code
```
Delete table – Drop the person table
```
DROP TABLE person;
Copy the code
```
Deleting a Database
```
DROP DATABASE example;
Copy the code
```

MySQL knowledge is very much, a variety of statements to practice. Now that we know how to write simple statements, let’s look at how to write SQL statements in Node.js.

4. MySQL is used in Node.js

🌰 to name a few, after learning how to write interfaces with Koa in node.js backend development (1), continue to write this code in the previous project

Koa+MySQL to achieve the increase, delete, change and check 4 functions

Add user
Querying User Information
Modifying User Information
Delete user

4.1 Connecting a Database

First, create a new database, koADB

CREATE DATABASE koadb;
Copy the code

Install MySQL NPM package mysql2

Why mysql2?

NPM home page 🤡 Faster, Higher, Stronger

npm i mysql2
Copy the code

Create the connection file pool.js for the data in the root directory of the project

const mysql = require('mysql2')

// Make sure to change it to your connection information
const config = {
  database: 'koadb'./ / database
  username: 'root'./ / user
  password: '000000'./ / password
  port: '3306'.// MySQL port number
  host: 'localhost'   / / MySQL address
}

const pool = mysql.createPool(config)

module.exports = conn
Copy the code

Note that conn creates a data connection by modifying the connection information in config

4.2 call SQL

Reference pool.js first, and then refer to the following notation

No parameter SQL

const [rows, fields] = await pool.query('SELECT * FROM USER')
Copy the code

The participation of SQL

const [rows, fields] = await pool.query('SELECT * FROM USER WHERE NAME = ? '['Joe'])
Copy the code

Pre-generated SQL

const sql = pool.format('SELECT * FROM USER WHERE NAME = ? '['Joe'])
const [rows, fields] = await pool.query(sql)
Copy the code

Error handling

const sql = pool.format('SELECT * FROM USER WHERE NAME = ? '['Joe'])
pool.query(sql,(err, rows, fields) = >{
    / /...
})
Copy the code

4.3 Design the data table

According to the four functions, two data tables are designed, one user table and one password table

User-user table

field	The data type	The length of the	meaning
id	int	5	A primary key
name	varchar	32	The user name
level	int	1	level

PWD – Password table

field	The data type	The length of the	meaning
id	int	5	A primary key
name	varchar	32	The user name
password	varchar	18	password

The two tables are related by the ID field

4.4 Creating a Data table

Create a user-user table

CREATE TABLE USER (
	id INT ( 5 ) PRIMARY KEY,
name VARCHAR ( 32 ),
level INT ( 1 ));
Copy the code

Double-click the table name to view the created table to make sure there are no problems

Create the PWD – password table

CREATE TABLE PWD (
	id INT ( 5 ) PRIMARY KEY,
name VARCHAR ( 32 ),
password VARCHAR ( 18 ));
Copy the code

Double-click the table name to view the created table to make sure there are no problems

4.5 transactions

There is one last concept you need to understand before you start writing code: transactions

A transaction is a series of rigorous operations in an application, all of which must complete successfully or all changes made in each operation will be undone.

🌰 for example

A new user needs to add data to each of the user and PWD tables. After using transactions, either both inserts succeed or both inserts fail.

There will not be one success and one failure, which ensures data consistency

/ / to omit...
// Start the transaction
const conn = await pool.getConnection()
await conn.beginTransaction()
try {
    // ...
    / / MySQL operation
    // ...
    await conn.commit() // Commit the transaction
    conn.release() // Release the connection
} catch (e) {
    console.log(e)
    conn.rollback() // Rollback the transaction
}
Copy the code

5. The interface

Once you have the database connection, create four interfaces.

New User –/sql/addUser
Query information –/sql/getUser
Change password –/sql/changUserPassword
Deleting a User –/sql/deleteUser

According to 🌰 for a few examples, using Koa two days to get started with Node.js backend development (a) learning content, create SQL.js

Reference the newly created query.js

const router = require('koa-router') ()const pool = require('.. /pool')

router.prefix('/sql')

module.exports = router
Copy the code

Add the call to app.js

/ /... omit
const sql = require('./routes/sql')
/ /... omit
app.use(sql.routes(), sql.allowedMethods())
Copy the code

💡 Before writing an interface, write an SQL statement and define interface parameters according to the SQL statement

5.1 Adding a User (POST)

📢 Add the addUser interface in sql.js to create new users. This interface is also the most complex of the four.

Each user goes to the two tables user and PWD to store the level and password

SQL statement for creating a user

Insert a row into the user tableINSERT INTO USER ( id, name, level )
VALUES
	( '1'.'Maokai'.0); Insert a password into the password tableINSERT INTO PWD ( id, name, password )
VALUES
	( '1'.'Joe'.'123456$' );
Copy the code

4 values id, name, level and password need to be passed. Id needs to be automatically generated, and other values are obtained by the pass parameter.

Request parameters:

{
    name:' '.password:' '.level:' '
}
Copy the code

📘 ID generation logic: Take the current maximum ID in the user table, add 1 to the new ID, expressed in SQL statement. (You can also set the id increment while building the table)

SELECT 
	COALESCE( max( id ), 0 ) + 1 id
FROM USER
Copy the code

✨ Interface code

/* * New user */
router.post('/addUser'.async (ctx, next) => {
  const { name, password, level } = ctx.request.body
  try {
    if(! (name && password && level)) {throw 'Missing arguments'
    }
    // Get a new ID
    const idSQL = 'SELECT	COALESCE( max( id ), 0 ) + 1 id FROM USER'
    const [[{ id }]] = await pool.query(idSQL)
    // Get the MySQL connection
    const conn = await pool.getConnection()
    await conn.beginTransaction()

    const userSQL = pool.format('INSERT INTO USER ( id, name, level ) VALUES( ?, ?, ? )', [id, name, level])
    const pwdSQL = pool.format('INSERT INTO PWD ( id, name, password ) VALUES( ?, ?, ? )', [id, name, password])

    await conn.query(userSQL)
    await conn.query(pwdSQL)
    // Commit the transaction
    await conn.commit()
    // Release the MySQL connection
    conn.release()
    ctx.body = 'User added successfully, id:${id}The name:${name}`
  } catch (e) {
    conn.rollback()
    ctx.body = e
  }
})
Copy the code

Testing interface with Hoppscotch (POST)

http://localhost:3000/sql/addUser
Copy the code

Request parameters

{
  "name": "MaoKai"."password": "123456$"."level": 1
}
Copy the code

You can see that name and ID are returned correctly

The picture is larger, wait patiently 👇

5.2 Querying Information (GET)

📢 Add the getUser interface to sql.js to query user information.

Each user information is stored in two tables, user and PWD, but the query interface only needs the User table

SQL statement to query information

SELECT
	* 
FROM USER 
WHERE
	id = 1
Copy the code

From SQL we can see that only 1 value ID needs to be passed

Routing parameters using Koa: /getUser/1

✨ Interface code

/* * Query information */
router.get('/getUser/:id'.async (ctx, next) => {
  const { id } = ctx.params
  try {
    if(! id) {throw 'Missing arguments'
    }
    const userSQL = pool.format('SELECT * FROM USER WHERE ID = ? ', [id])
    const [rows] = await pool.query(userSQL)
    const { name, level } = rows[0]
    ctx.body = 'User Info: \n ID:${id}\n name:${name}\n level:${level}`
  } catch (e) {
    ctx.body = e
  }
})
Copy the code

Testing interface with Hoppscotch (POST)

http://localhost:3000/sql/getUser/1
Copy the code

You can see that the user information is returned correctly

5.3 Changing the Password (POST)

📢 Add the changPassword interface in sql.js to change passwords. The logic is to verify the old password, change it if it is correct, and return if it is incorrect

Only the PWD table is required to change the password

SQL statement for changing passwords

# Query old passwordSELECT password 
FROM	pwd 
WHERE
	id = 1Change the password to the new password UPDATE PWDSET password = '123'
WHERE
	id = 1
Copy the code

3 values id, password and newPassword need to be passed from SQL

Request parameters:

{
    id:' '.newPassword:' '.password:' '
}
Copy the code

✨ Interface code

/* * Change the password */
router.post('/changPassword'.async (ctx, next) => {
  const { id, password, newPassword } = ctx.request.body
  try {
    if(! (id && password && newPassword)) {throw 'Missing arguments'
    }
    const pwdSQL = pool.format('SELECT password current FROM PWD WHERE ID = ? ', [id])
    const [[{ current }]] = await pool.query(pwdSQL)

    if(password ! == current) {throw 'Wrong password, please check'
    }

    const newPwdSQL = pool.format('UPDATE pwd SET password = ? WHERE ID = ? ', [newPassword, id])
    await pool.query(newPwdSQL)

    ctx.body = `id:${id}The password is successfully changed. New password:${newPassword}`
  } catch (e) {
    ctx.body = e
  }
})
Copy the code

Testing interface with Hoppscotch (POST)

http://localhost:3000/sql/changPassword
Copy the code

Request parameters

{
  "id": 1."password": "123456$"."newPassword": "@ @ @"
}
Copy the code

You can see that the pasword field in the database has been updated

The picture is larger, wait patiently 👇

5.4 Deleting a User (GET)

📢 Add the deleteUser interface in sql.js to delete a user.

Each user information is stored in two tables: user and PWD. You need to delete the user at the same time.

Delete the SQL statement, this time using a join statement

DELETE
	user,pwd 
FROM
	user,pwd 
WHERE
	user.id = pwd.id
	AND user.id = '1'
Copy the code

From SQL we can see that only 1 value ID needs to be passed

Route parameters using Koa: /deleteUser/1

✨ Interface code

/* * Delete user */
router.get('/deleteUser/:id'.async (ctx, next) => {
  const { id } = ctx.params
  try {
    if(! id) {throw 'Missing arguments'
    }
    const userSQL = pool.format('SELECT * FROM USER WHERE ID = ? ', [id])
    const [rows] = await pool.query(userSQL)
    const { name, level } = rows[0]
    ctx.body = 'User Info: \n ID:${id}\n name:${name}\n level:${level}`
  } catch (e) {
    ctx.body = e
  }
})
Copy the code

Testing interface with Hoppscotch (POST)

http://localhost:3000/sql/getUser/1
Copy the code

You can see that the user was deleted (note: no error is reported even if the user does not exist)

The last

Here, the Koa backend quick start is over, 3 articles simply write some Koa knowledge, dig friends in this area also have many excellent articles, but we have to find their own.

🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (a) | interface
🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (2) | rendering of a service
🌰 for several chestnuts, introduction to two days with Koa Node. Js back-end development (3) | article database.

😜 long march always feeling, point a concern line ~

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

🌰 to name a few, using Koa two days to start Node.js backend development (three)

preface

What is a database?

2. Preparation

🚧 MySQL 8.0

🚧MySQL management tool

🚧 Node. Js

🚧 Request tool

3. Some knowledge

3.1 Fields and Records

3.2 the primary key

3.3 Data Types

3.4 SQL writing

3.4.1 track to add

3.4.2 change

Rule 3.4.3 check

3.4.4 delete

4. MySQL is used in Node.js

4.1 Connecting a Database

4.2 call SQL

4.3 Design the data table

4.4 Creating a Data table

4.5 transactions

5. The interface

5.1 Adding a User (POST)

5.2 Querying Information (GET)

5.3 Changing the Password (POST)

5.4 Deleting a User (GET)

The last

🌰 to name a few, using Koa two days to start Node.js backend development (three)

preface

What is a database?

2. Preparation

🚧 MySQL 8.0

🚧MySQL management tool

🚧 Node. Js

🚧 Request tool

3. Some knowledge

3.1 Fields and Records

3.2 the primary key

3.3 Data Types

3.4 SQL writing

3.4.1 track to add

3.4.2 change

Rule 3.4.3 check

3.4.4 delete

4. MySQL is used in Node.js

4.1 Connecting a Database

4.2 call SQL

4.3 Design the data table

4.4 Creating a Data table

4.5 transactions

5. The interface

5.1 Adding a User (POST)

5.2 Querying Information (GET)

5.3 Changing the Password (POST)

5.4 Deleting a User (GET)

The last

Related Posts

[27]

What are JSON, json. parse, json.stringify

ZooTeam Front-end Weekly | Issue 148