0616 Sequelize

시퀼라이즈=ORM은 자바스크립트 객체와 데이터베이스의 릴레이션을 매핑해주는 도구이다.

npm init -y

npm i sequelize mysql2
npm i express
npm i supervisor
npm i sequelize-cli -g
sequelize init

const express  = require ('express');
const app = express();
const {Sequelize} = require('sequelize');

const sequelize = new Sequelize('mysql','root','qwerpoiu!@#',{
    host:'localhost',
    dialect: 'mysql'
});

app.get('/',(req,res)=>{
    res.send('hello world');
})

app.get('/connetdb',(req,res)=>{
    connect();
    res.send('db connected');
});

async function connect(){
    try{
    await sequelize.authenticate(); //접속 대기
    console.log('connected');
    }
    
    catch(err){
        console.log(err);
    }
}

app.listen(3000,()=>{
    console.log('server is running at port 3000');
});

접속 테스트 완료

https://sequelize.org/master/manual/getting-started.html

Manual | Sequelize

 

suquelize init 을 하면 config 파일이 생성된다.

//config.json


{
  "development": {
    "username": "root",
    "password": "qwerpoiu!@",
    "database": "sequelize_db",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "timezone": "+09:00"
  },
  "test": {
    "username": "root",
    "password": "qwerpoiu!@",
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": "qwerpoiu!@",
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "define":{
    "charset":"utf8",
    "collate":"utf8_bin",
    "timestamps":"true"
  }
}

 

---

npm i sequelize-auto
시퀄라이즈를 더 사용하기 편하게 매핑해둔 npm - 데이터 테이블을 오브젝트로 만들어준다.

//auto.js


const express  = require ('express');
const app = express();
const SequelizeAuto = require('sequelize-auto');
const auto = new SequelizeAuto('nodejs','root','qwerpoiu!@#',{
    host:'localhost',
    port:'3306',
    dialect:'mysql'
});

auto.run(err=>{
    if(err) throw err;
    console.log('complete');
});

실행은 node auto로 해야한다

 

---

findAll( ) 함수 사용하기

https://velog.io/@cadenzah/sequelize-document-2

Sequelize 공식 Document - (2) Querying

 

//mySequelize.js

const initModel = require('./models/init-models');
const {Op,Sequelize} = require('sequelize');
const sequelize = new Sequelize('nodejs','root','qwerpoiu!@#',{
    host:'localhost',
    dialect:'mysql'
});
const models = initModel(sequelize);

module.exports={
    models,
    Op,
};

모듈로 export 했다.

//findall.js

const {Op,models} = require('./mySequelize');


// models.users.findAll().then(results=>{
//     console.log(results);
// });

async function findAllFromProducts(){
    const product = await models.products.findAll({
        where: {
            price:{
                [Op.gt]: 45000
            },
        },
    });
    const json =JSON.stringify(product);
    console.log(json);
};

findAllFromProducts();

45000원보다 높은 가격의 상품들만 출력되었다.

 

const {Op,models} = require('./mySequelize');


// models.users.findAll().then(results=>{
//     console.log(results);
// });

async function findAllFromProducts(){
    const product = await models.products.findAll({
        where: {
            price:{
                [Op.gt]: 45000
            },
            name:{
                [Op.eq]:'닌텐도 스위치 본체 모여봐요 동물의숲 에디션'
            },
        },
    });
    const json =JSON.stringify(product);
    console.log(json);
};

findAllFromProducts();

모동숲 상품만 따로 출력했다.

 

---

sequelize를 이용해 express 서버에 db불러오기

//mySequelize.js


const initModel = require('./models/init-models');
const {Op,Sequelize} = require('sequelize');
const sequelize = new Sequelize('nodejs','root','qwerpoiu!@#',{
    host:'localhost',
    dialect:'mysql'
});
const models = initModel(sequelize);

module.exports={
    models,
    Op,
};

//app.js


const {Op,models} = require('./mySequelize');
const express = require('express');
const app = express();

app.get('/',(req,res)=>{
    res.send('hello world');
});

app.listen(3000,()=>{
    console.log('server is running at 3000');
});

app.get('/users/all',async (req,res)=>{
    const json = await findAllFromUsers();
    console.log(res.body);
    res.send(json);
})

app.get('/users/:id',async (req,res)=>{
    const json = await findAllFromID();
    console.log(res.body);
    res.send(json);
})

async function findAllFromUsers(){
    const users = await models.users.findAll();
    const json =JSON.stringify(users);
    console.log(json);
};

async function findAllFromID(){
    const users = await models.users.findAll({
        where:{
            id:{
                [Op.eq]: '4'
            },
        },
    });
    const json =JSON.stringify(users);
    console.log(json);
};