Nuxt3 Mysql 연동하기 및 서버구성

1. Project 생성 및 hello world 찍어보기

//vscode 터미널 기준
npx nuxi@latest init <project-name>

code <project-name>

//폴더 및 파일 생성 server/api/index.ts
export default defineEventHandler(() => {
    return { helloWorld: 'API' }
  })

 

npm run dev로 서버를 올리고 localhost:3000/api로 접속해서 확인!!

2. DB 연결정보

이제 본격적으로 DB 연동부터 시작해보자!!

먼저 npm install mysql2 설치해주자

 

server/db/connection.ts 파일 생성

import mysql from 'mysql2/promise';

interface Options {
    query: string;
    values?: any[];
}

const pool = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: '',
});

export const sql = async ({ query, values }: Options) => {
    const [rows] = await pool.query(query, values);

    return rows;
};

혹시 DB 구성이 안되었다면 아래글 참고

https://smujihoon.tistory.com/296

 

3. CRUD 쿼리 작성하기

server/model/user.ts 파일 생성

import {sql} from '~/server/db/connection';

export type User = {
    id: string;
    password: string;
    name: string;
};

export const read = async () => {
    const result = await sql({
        query: 'select * from user'
    });

    return result as User[];
}

export const add = async (data: Pick<User, 'id' | 'password' | 'name'>) => {
    const result = await sql({
        query: `insert into user (id, password, name) 
                values (?,?,?)`,
        values: [data.id, data.password, data.name]
    }) as any;

    return result.length === 1 ? (result[0] as User) : null;
}

export const detail = async (id: string) => {
    const result = (await sql({
        query: `select 
                    id, 
                    password, 
                    name 
                from user 
                where id = ?`,
        values: [id]
    })) as any;

    return result.length === 1 ? (result[0] as User) : null;
}

export const login = async (data: Pick<User, 'id' | 'password'>) => {
    const result = (await sql({
        query: `select 
                    id, 
                    password, 
                    name 
                from user 
                where id = ? and password = ?`,
        values: [data.id, data.password]
    })) as any;

    return result.length === 1 ? (result[0] as User) : null;
}

export const update = async (data: Pick<User, 'id' | 'name'>) => {
    await sql({
        query: `update user 
                    set name = ?
                where id = ?`,
        values: [data.name, data.id]
    });

    return await detail(data.id);
}

export const remove = async(id: string) => {
    await sql({
        query: 'delete from user where id = ?',
        values: [id]
    });
    return true;
}

4. Controller 작성하기

server/controller/user.ts 파일 생성

import { H3Event } from 'h3';
import * as userModel from '~/server/model/user';

export const read = async () => {
    try {
        const result = await userModel.read();
        
        return {
            data: result
        }
    } catch {
        throw createError ({
            statusCode: 500,
            statusMessage: 'something went wrong'
        });
    }
}

export const add = async (evt: H3Event) => {
    try {
        const body = await readBody(evt);
        const result = await userModel.add({
            id: body.id,
            name: body.name,
            password: body.password
        });
        
        return {
            data: result
        }
    } catch {
        throw createError ({
            statusCode: 500,
            statusMessage: 'something went wrong'
        });
    }
}

export const detail = async (evt: H3Event) => {
    try {
        const result = await userModel.detail(evt.context.params?.id as string);
        
        return {
            data: result
        }
    } catch {
        throw createError ({
            statusCode: 500,
            statusMessage: 'something went wrong'
        });
    }
}

export const update = async (evt: H3Event) => {
    try {
        const body = await readBody(evt);
        const result = await userModel.update(evt.context.params?.id as string, {
            name: body.name
        });
        
        return {
            data: result
        }
    } catch {
        throw createError ({
            statusCode: 500,
            statusMessage: 'something went wrong'
        });
    }
}

export const remove = async (evt: H3Event) => {
    try {
        const result = await userModel.remove(evt.context.params?.id as string);
        
        return {
            data: result
        }
    } catch {
        throw createError ({
            statusCode: 500,
            statusMessage: 'something went wrong'
        });
    }
}

5. router 생성

호출하기 위한 router를 만들어주자

server/api/[...].ts 생성

import { useBase, createRouter, defineEventHandler } from 'h3';
import * as userController from '~~/server/controller/user';

const router = createRouter();

router.get('/user', defineEventHandler(userController.read));
router.post('/user', defineEventHandler(userController.add));
router.get('/user/:id', defineEventHandler(userController.detail));
router.put('/user/:id', defineEventHandler(userController.update));
router.delete('/user/:id', defineEventHandler(userController.remove));

export default useBase('/api', router.handler);

6. 확인

 

api를 호출해서 신규 회원 추가 및 기존 회원 변경을 해보았다.

포스트맨을 활용하면 body에 데이터를 넣어서 호출 가능하니까 테스트 시 참고하길 바랍니다.