preface
The previous article covered how to create projects, access routes, and create modules. This article covers database connections and usage.
Since this is a back-end project, of course you need to be able to connect to the database, otherwise you might as well write static pages.
This tutorial uses MySQL. One might ask why not use MongoDB… Well, since the company uses MySQL and I wrote this tutorial based on my project experience, MongoDB hasn’t been tested yet, so I’m not going to confuse you.
GitHub project address, welcome everyone Star.
MySQL
First, make sure you have a database to connect to. If not, download one from the MySQL website and run it locally. Installation tutorial here is not narrated, “Baidu, you know”.
Navicat Premium Visualization tools are recommended for database management.
After connecting to the database with Navicat, create a new library:
Open the nest_zero_to_one library, open Tables and find it empty. Create a new table, open Query on the toolbar and add a new Query:
Copy the following code into the box and click Run above to complete the table creation:
CREATE TABLE `admin_user` (
`user_id` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'user ID',
`account_name` varchar(24) NOT NULL COMMENT 'User account',
`real_name` varchar(20) NOT NULL COMMENT 'Real Name',
`passwd` char(32) NOT NULL COMMENT 'password',
`passwd_salt` char(6) NOT NULL COMMENT 'Password salt',
`mobile` varchar(15) NOT NULL DEFAULT '0' COMMENT 'Mobile number',
`role` tinyint(4) NOT NULL DEFAULT '3' COMMENT 'administrator user roles: 0 - | | 1 - administrator 2 - development & testing & operation | 3 - ordinary users (can see)',
`user_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'state: effective | failure | 0-1-2 - delete',
`create_by` smallint(6) NOT NULL COMMENT 'creator ID',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
`update_by` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Modifier ID',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
PRIMARY KEY (`user_id`),
KEY `idx_m` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Background User table';
Copy the code
Now you can see the admin_user table on the left:
Let’s insert two random pieces of data to facilitate the subsequent query:
2. Database configuration of the project
Start by creating a folder config (the same as SRC) at the root of the project to house various configurations.
Then create a new file db.ts:
// config/db.ts
const productConfig = {
mysql: {
port: 'Database Port',
host: 'Database address',
user: 'Username',
password: 'password',
database: 'nest_zero_to_one'./ / the library
connectionLimit: 10.// Connection restriction}};const localConfig = {
mysql: {
port: 'Database Port',
host: 'Database address',
user: 'Username',
password: 'password',
database: 'nest_zero_to_one'./ / the library
connectionLimit: 10.// Connection restriction}};// There is no process.env.node_env on the local run to distinguish between development environment and production environment.
const config = process.env.NODE_ENV ? productConfig : localConfig;
export default config;
Copy the codePs: This file is not synchronized to Github, you need to configure according to the actual situation
Sequelize: Sequelize: Sequelize: Sequelize: Sequelize: Sequelize
$NPM I sequelize sequelize-typescript mysql2 -s or $YARN add sequelize sequelize-typescript mysql2 -sCopy the codeCreate sequelize.ts: sequelize.ts: sequelize.ts: sequelize.ts: sequelize.ts
// src/database/sequelize.ts
import { Sequelize } from 'sequelize-typescript';
import db from '.. /.. /config/db';
const sequelize = new Sequelize(db.mysql.database, db.mysql.user, db.mysql.password || null, {
// Custom host; Default value: localhost
host: db.mysql.host, // Database address
// Custom port; Default value: 3306
port: db.mysql.port,
dialect: 'mysql',
pool: {
max: db.mysql.connectionLimit, // The maximum number of connections in the pool
min: 0.// The minimum number of connections in the pool
acquire: 30000,
idle: 10000.// If a thread has not been used in 10 seconds, the thread is released
},
timezone: '+ 08:00'.// Time zone 8 EAST
});
// Test the database link
sequelize
.authenticate()
.then((a)= > {
console.log('Database connection successful');
})
.catch((err: any) = > {
// Print output when database connection fails
console.error(err);
throw err;
});
export default sequelize;
Copy the codeThree, database connection test
Ok, now let’s test the database connection.
Let’s rewrite the logic of user.service.ts:
// src/logical/user/user.service.ts
import { Injectable } from '@nestjs/common';
import * as Sequelize from 'sequelize'; // Introduce the Sequelize library
import sequelize from '.. /.. /database/sequelize'; // Introduce the Sequelize instance
@Injectable(a)export class UserService {
async findOne(username: string) :Promise<any | undefined> {
const sql = `
SELECT
user_id id, real_name realName, role
FROM
admin_user
WHERE
account_name = '${username}'`; // A plain SQL query
try {
const res = await sequelize.query(sql, {
type: Sequelize.QueryTypes.SELECT, // Query mode
raw: true.// Whether to display the result as an array assembly
logging: true.// Whether to print SQL statements to the console. Default is true
});
const user = res[0]; // The result is an array, we only take the first one.
if (user) {
return {
code: 200.// Return status code, which can be customized
data: {
user,
},
msg: 'Success'}; }else {
return {
code: 600,
msg: 'No such person'}; }}catch (error) {
return {
code: 503,
msg: `Service error: ${error}`}; }}}Copy the codeSave the file and you will see the console refresh (if started with YARN start:dev) and print the following statement:
This indicates that the previous configuration is effective, we try to request the interface with the previous parameters:
If “No user named Kid” is displayed, the database does not have a user named Kid.
Let’s change to the correct existing username and try again:
By logging: true, errors in SQL statements can be found more clearly during Bug debugging. However, it is recommended that the test is stable and closed before going online, otherwise the log will be very complicated:
MySQL > select * from MySQL; MySQL > select * from MySQL; MySQL > select * from MySQL;
conclusion
This article describes how to prepare data for MySQL, how to configure Sequelize, how Nest connects to MySQL through Sequelize, and how to verify the connection with a simple query.
At this point, it is strongly recommended to use write native SQL statements to manipulate the database.
Although Sequelize provides many convenient methods, you can browse the Sequelize V5 documentation to learn more. However, by observing the logging printed statements, I found that there were a lot of unnecessary operations, which affected performance in the case of high concurrency.
And if you don’t use native queries, you have to create objects to map to database tables, and then every time the tool is updated, you have to spend time and cost to learn, and if the database changes fields, the mapping will be wrong, and the project will crash with crazy errors (personally).
And the use of native SQL, only need to learn a language is enough, change a tool, can also be used, and even if changed the field, will only be in the request interface error, then for that statement modification is good, and now find replacement function so powerful, batch modification is not difficult.
Most importantly, if you are going from the front end to the back end, or even from the zero base to the back end, it is still recommended to have a solid foundation of SQL, otherwise you will not know the difference between LEFT JOIN, LEFT JOIN and RIGHT JOIN. Then I was scolded by the DB supervisor… True story).
Write more, analyze more, read more console error, more performance considerations, is the fastest way to get started.
UPDATE statement (s) WHERE condition (s); UPDATE statement (s) WHERE condition (s); UPDATE statement (s)
In the next article, I’ll show you how to use JWT (Json Web Token) for single sign-on.
This article is included in the NestJS practical tutorial, more articles please pay attention to.
`