mysql nodejs 连接数据库 （大概流程）

 

 

视频链接：https://www.youtube.com/watch?v=Hej48pi_lOc

 

数据库建模用的是Sequelize

 

 

创建表

CREATE DATABASE notes_app;USE notes_app;
CREATE TABLE notes (id integer PRIMARY KEY AUTO_INCREMENT,title VARCHAR(255) NOT NULL,contents TEXT NOT NULL,created TIMESTAMP NOT NULL DEFAULT NOW()
);
INSERT INTO notes (title, contents)VALUES
( 'My First Note','A note about something'),('My Second Note','A note about something else');

 

 

 

第一版

import mysql from 'mysql2'

const pool = mysql.createPool({
    host:'119.91.31.144',
    user:'test1',
    password:'',//数据库密码
    database:'test1'
}).promise()


async function getNotes(){
    const [rows] = await pool.query("SELECT * FROM notes")
    return rows
}

const notes= await getNotes()
console.log(notes)

 

env，需要安装dotenv

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

以下 完整文件

 .env文件，密码自己填数据库的

MYSQL_HOST = '119.91.31.144'
MYSQL_USER = 'test1'
MYSQL_PASSWORD = ''
MYSQL_DATABASE = 'test1'

 

 

 

app.js文件

import express from 'express'

import  {getNote,getNotes,createNote} from './database.js'


const app = express()

app.get('/notes',async (req,res)=>{
    const notes = await getNotes()
    res.send(notes)
})

app.use(express.json())


app.get('/notes/:id',async (req,res)=>{
    const id=req.params.id
    const note = await getNote(id)
    res.send(note)
})



app.post('/notes',async (req,res)=>{
    const {title,contents} = req.body
    const note = await createNote(title,contents)
    res.status(201).send(note)
})


app.use((err, req, res, next) => {
    console.error(err.stack)
    res.status(500).send('Something broke!')
  })

  app.listen(8080,()=>{
    console.log('Server is running on port 8080')
  })

 

database.js文件

import mysql from 'mysql2'
import dotenv from 'dotenv'

dotenv.config()

const pool = mysql.createPool({
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE
}).promise()


export async function getNotes(){
    const [rows] = await pool.query("SELECT * FROM notes")
    return rows
}


export async function getNote(id){
    const [rows] = await pool.query(`
    SELECT *
    FROM notes
    WHERE id = ?`,[id])
    return rows[0]
}


export async function createNote(title,content){
    const [result] = await pool.query(`
    INSERT INTO notes (title,contents)
    VALUES(?, ?)
    `,[title,content])
    const id = result.insertId
    return getNote(id)
}



const notes= await createNote('test','test')
console.log(notes)

 

 

package.json文件，我们跑 npm run dev 运行，需要安装全局的nodemon哦

{
  "name": "test_mysql_c",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "type": "module",
  "scripts": {
    "dev": "npx nodemon app.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "dotenv": "^16.1.4",
    "express": "^5.0.0-beta.1",
    "mysql2": "^3.3.5"
  }
}

 

package-lock.json文件是自动生成的

 

 

//2222222222222222222

一个页面的，新建一个server.js页面

const express = require("express");
const cors = require("cors");
const mysql = require("mysql");

const app = express();

app.use(cors());
app.use(express.json());

const connection = mysql.createConnection({
  host: "",
  user: "",
  password: "",
  database: ""
});

connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL: ' + err.stack);
    return;
  }
  console.log('Connected to MySQL as id ' + connection.threadId);
});

app.get("/api/data", (req, res) => {
  connection.query('SELECT * FROM success', (error, results, fields) => {
    if (error) throw error;
    res.json(results);
  });
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});


// npm install express mysql cors --save

最后node server.js