We learned how to use Docker to pull a remote mysql database and then connect to it. And manipulate the database from the command line, such as viewing or manipulating tables in the database.

You can also use Node JS to manipulate databases. Mysql is not integrated into the node native module, so you need to install an additional mysql module: search nodejs mysql, which is available on Github

How do I access the MYSQL database after docker is installed? The only way to access the database is to send SQL commands over the network, which are executed by the MYSQL server and then returned.

We can operate MYSQL server directly by entering commands from the command line window. To access MYSQL, node JS programs send SQL commands to the MYSQL server over the network. The package that can access the MYSQL server is often called the MYSQL driver. Different programming languages also need to achieve their own drivers, so this time we use the mysql software package node JS implementation. This is by far the most widely used open source driver.

1. Install

yarn add mysql
Copy the code

2. Connect to the database

Create test.js as shown in the example on the official website.

const mysql      = require('mysql');
const connection = mysql.createConnection({
  host     : 'localhost'.// Address of the connected database. (Default :localhost)
  user     : 'me'.// Mysql connection user name
  password : 'secret'.// Password of the corresponding user
  database : 'my_db'  		// The name of the database to connect to (optional)
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution'.function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();
Copy the code

First, introduce the mysql module. Create a connection object using mysql.createconnection. Provides details on the MySQL server to connect to. To accept an object as a parameter, pass host,user, etc.

In order to connect successfully, you need to pull a mysql from the local docker. Because I downloaded the old version of Docker toolbox, that is, Docker Toolbox, I need to replace the ‘localhost’ corresponding to host with the IP I got after STARTING Docker QuickStart. It can also be obtained from the command line using docker-machine IP. If it’s a new Docker or Max Linux system, you don’t need to replace it.

The connect method is then called on the connection object to connect to the MYSQL database server.

The Connection.query method can be used to query and execute any correct SQL statement. The first argument is an SQL statement string, and the second argument is the callback function after the SQL statement is executed.

Finally, close the connection with connection.end().

The mysql library acts as a mysql client, sending some SQL commands to the server, which reads/writes data from the database and sends it back.

3. Create databases and tables

//test.js
const mysql= require('mysql');
const connection = mysql.createConnection({
    host: '192.168.99.100'.port:3306.user: 'root'.password: '123456'}); connection.connect(); connection.query('CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; '.function (error, results, fields) {
    if (error) throw error;
    console.log('Create database')
    console.log(results)
});

connection.query('use mydb; ')

connection.query(`CREATE TABLE IF NOT EXISTS myuser( name text, age int )`.function (error, results, fields) {
    if (error) throw error;
    console.log('Create table')
    console.log(results)
});

connection.end();
Copy the code

1. Create the SQL statement of the database

The SQL statement that creates the database is still executed with connection.query. ‘CREATE DATABASE IF NOT EXISTS myDB CHARACTER SET UTF8MB4 COLLATE UTf8MB4_unicode_520_ci; ‘

Create a database and say IF NOT EXISTS create a database IF it does NOT exist This is followed by the name of the database to be created.

Never use mysql UTF-8 because the bug is not fixed yet, use UTF8MB4 instead.

2. Create the tables

To create a table, go to a database and create a table in that database. Otherwise, an error will be reported.

connection.query('use mydb; ')Copy the code

This SQL statement can be seen as using a database as on the command line.

CREATE TABLE IF NOT EXISTS myuser error: there must be at least one column of data in the TABLE. Create table (); create table (); create table (); The ” of the outer package string is also changed to a pair of backquotes.

3. Run

Then runnode test.jsThis is success:

4. Command line verification

Connect to mysql from the command lineshow databases;Command to view the existing database, you can see that the myDB database is created.

Then use the databaseuse mydb;Look at the tables in this databaseshow tables;Viewing the detailed contents of a table shows the columns in the table and all their attributesdescribe myuser;You can see that we created the table myUser and wrote data to it

4. The ORM and Sequelize

If we were to access the database directly using the interface provided by the mysql package, our code would be low-level because we would be writing SQL commands directly to access the database.

In fact, the database table is a two-dimensional table, can have many rows and columns. The structure of the table can be mapped to a JS object, and each row can be represented by an object. The fields of each column are each property in the object. This is ORM technology: Object-Relational Mapping maps the table structure of a Relational database to objects

So who’s going to do the conversion? Sequelize is the ORM framework, and Sequelize is the ORM framework that allows us to read and write JS objects, and it helps us turn them into each row in the table.

Sequelize Official document

Follow the documentation, the implementation is relatively simple.

const { Sequelize,Model, DataTypes} = require('sequelize');

const sequelize = new Sequelize('HU'.'root'.'123456', {
    host: '192.168.99.100'.dialect: 'mysql'
});

class User extends Model {}
User.init({
    username: DataTypes.STRING,
    age:DataTypes.INTEGER
}, { sequelize, modelName: 'user' });

(async() = > {await sequelize.sync();
    const jane = await User.create({
    username: 'janedoe'.birthday: new Date(1980.6.20)});const users_name = await User.findAll({
        where: {
            username:'huanqi'}});console.log("All users:".JSON.stringify(users_name, null.2)); }) ();Copy the code

The core concept of Sequelize is the model. In Sequelize, a Model is a class that inherits the Model, which is the structure of a table in a database. An instance object created by this class is equivalent to a row in a table.

First new Sequelize creates a Sequelize connection object that contains information about the mysql database to which it is connected. Then declare a model called User. To initialize the model with user.init, you initialize the table, which attributes (columns) it has and their data types. The second parameter is the object to connect to.

Sequelize.sync () is used to synchronize our model with the table, because the table may already exist or have different columns that need to be synchronized first.

User.create Creates an instance object with the model by adding a row of data to the table. This is all asynchronous and equivalent to a promise, so it’s very neat to use await.

User.findAll Is used to query